Re: reverting to passwd-less root w/out --skip-grant-tables
Log in as root and run: set password = ''; OR update mysql.user set Password='' where User='root'; this is TOTALLY INSECURE but if that's what you want, then it should do the trick. Regards, Darren Cassar www.mysqlpreacher.com www.securich.com On Wed, Aug 26, 2009 at 2:33 AM, Joe mysql@bluepolka.net wrote: I'm trying to get back to an earlier state where we started mysqld withOUT --skip-grant-tables but the root user had no password. Yes, insecure, but we're in restoration mode here. How do I reset/revert the root password to no password without running with --skip-grant-tables? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=i...@mysqlpreacher.com
Re: recovery help needed
I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hi Query Optimization steps
Hi! bharani kumar wrote: Then which DB engine is better. Which fruit is better, an apple or a banana? And if X is better, why do farmers still grow Y, and why do people buy it? More serious: Different engines offer different features, as described in the manual. The most obvious differences are full-text search (MyISAM) versus transactions and referential integrity (InnoDB), but there are more. During your database schema design you should for each table select the engine which is appropriate. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Step # 1 : Stop mysql service # /etc/init.d/mysql stop Step # 2: Start to MySQL server w/o password: # mysqld_safe --skip-grant-tables Step # 3: Connect to mysql server using mysql client: # mysql -u root Step # 4: Setup new MySQL root user password mysql use mysql; mysql update user set password=PASSWORD(NEW-ROOT-PASSWORD) where User='root'; mysql flush privileges; mysql quit Step # 5: Stop MySQL Server: # /etc/init.d/mysql stop Step # 6: Start MySQL server and test it # /etc/init.d/mysql start # mysql -u root -p Note: You can check structure of table 'user' mysql SHOW CREATE TABLE 'user'; And update other required fields also. Enjoy ;-) On Wed, Aug 26, 2009 at 2:36 PM, Martijn Engler mart...@crystal-labs.nlwrote: I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat
upgrading from 4.1 to 5.0 trick
Hello All, I'm in the process of upgrading my database from 4.1 to 5.0 on CentOS. I've been testing the mysqlcheck --check-upgrade --auto-repair command, and on one of my MYISAM tables, it's taking forever to upgrade the table. It has about 114 million rows, and I'm guessing it needs to be upgraded due to the VARCHAR columns. Anyway, it's been running for a day and a half, and I finally had to kill it. So will this old trick still work? I've done this many times on 4.1 with great success: In mysql 5.0 - I create two new empty tables, one identical to the original and one identical but with no indexes. I name these tables with _ion and _ioff suffixes. I then do a insert into table_ioff select * from source which inserts just the original data into the new table, but doesn't have to rebuild any indexes. I then flush the tables. Then in the file system, I swap the table_ion.frm and table_ion.MYI files with the table_ioff ones. Flush tables again. I then just use myisamchk -r to repair the index file. It runs in about an hour. Can I do this same thing to upgrade the tables, instead of using mysqlcheck, which seems to be rebuilding the table row-by-row, instead of sorting (which myisamchk does). thanks. -Hank
Re: Fail to change MySQL data directory on ubuntu
Yes, error 13, permission denied. Check selinux setup. I had this same problem last week on a CentOS machine. I had to change the object type of the new data dir to mysqld-something. I'm on a mobile phone and can't remember the exact cmd. On Tuesday, August 25, 2009, Jia Chen chen.1...@gmail.com wrote: I run sudo /etc/init.d/mysql start and check the syslog by running sudo tail -f /var/log/syslog This is what I get Aug 25 22:18:06 chenj-desktop mysqld_safe[10934]: started Aug 25 22:18:06 chenj-desktop kernel: [11083.933531] type=1503 audit(1251253086.020:43): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop kernel: [11083.933581] type=1503 audit(1251253086.020:44): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] option 'thread_stack': unsigned value 128 adjusted to 131072 Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 InnoDB: Operating system error number 13 in a file operation. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: The error means mysqld does not have the access rights to Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: the directory. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File name ./ibdata1 Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File operation call: 'create'. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: Cannot continue operation. Aug 25 22:18:06 chenj-desktop kernel: [11083.962674] type=1503 audit(1251253086.048:45): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=110 name=/home/chenj/MySQL_data/ibdata1 pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld_safe[10944]: ended Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Does this message tell me any thing about the problem? Thanks. Best, Jia Johnny Withers wrote: First, check the error log, if you can't find it, start mysql from the cmd line by running safe_mysqld it should print errors to console. If it is a permission issue, it might be caused be selinux, you'll need to change the object type od that new directory to mysqld- something. I can't recall the command. A google search on selinux and mysql should produce good results. On Tuesday, August 25, 2009, chen jia chen.1...@gmail.com wrote: Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new directory. I also update /etc/apparmor.d/usr.sbin.mysql so that news lines with /var/lib/mysql replaced by /media/disk/MySQL_data are added. However, after sudo /etc/init.d/apparmor reload I try sudo /etc/init.d/mysql start I got * Starting MySQL database server mysqld [fail] If I change the datadir line in /etc/mysql/my.conf back to the original one, I can start MySQL successfully. I think I have done everything needed to change MySQL data directory. Why am I still getting this error? Where can I start to look for the causes? Thanks. Jia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives:
RE: Hi Query Optimization steps
-Original Message- From: joerg.bru...@sun.com [mailto:joerg.bru...@sun.com] Sent: Wednesday, August 26, 2009 5:08 AM To: mysql@lists.mysql.com Cc: bharani kumar Subject: Re: Hi Query Optimization steps Hi! bharani kumar wrote: Then which DB engine is better. Which fruit is better, an apple or a banana? And if X is better, why do farmers still grow Y, and why do people buy it? [JS] Good answer. I did performance consulting for over 25 years, on *NIX and other operating systems, and there was one response that suited all questions: It depends. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fail to change MySQL data directory on ubuntu
I'm at work now, this is the cmd I used: chcon -R -u system_u -r object_r -t mysql_db_t /data (my data lives in /data/mysqlXX -- were XX is the server version) On Wed, Aug 26, 2009 at 8:02 AM, Johnny Withers joh...@pixelated.netwrote: Yes, error 13, permission denied. Check selinux setup. I had this same problem last week on a CentOS machine. I had to change the object type of the new data dir to mysqld-something. I'm on a mobile phone and can't remember the exact cmd. On Tuesday, August 25, 2009, Jia Chen chen.1...@gmail.com wrote: I run sudo /etc/init.d/mysql start and check the syslog by running sudo tail -f /var/log/syslog This is what I get Aug 25 22:18:06 chenj-desktop mysqld_safe[10934]: started Aug 25 22:18:06 chenj-desktop kernel: [11083.933531] type=1503 audit(1251253086.020:43): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop kernel: [11083.933581] type=1503 audit(1251253086.020:44): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] option 'thread_stack': unsigned value 128 adjusted to 131072 Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 InnoDB: Operating system error number 13 in a file operation. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: The error means mysqld does not have the access rights to Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: the directory. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File name ./ibdata1 Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File operation call: 'create'. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: Cannot continue operation. Aug 25 22:18:06 chenj-desktop kernel: [11083.962674] type=1503 audit(1251253086.048:45): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=110 name=/home/chenj/MySQL_data/ibdata1 pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld_safe[10944]: ended Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Does this message tell me any thing about the problem? Thanks. Best, Jia Johnny Withers wrote: First, check the error log, if you can't find it, start mysql from the cmd line by running safe_mysqld it should print errors to console. If it is a permission issue, it might be caused be selinux, you'll need to change the object type od that new directory to mysqld- something. I can't recall the command. A google search on selinux and mysql should produce good results. On Tuesday, August 25, 2009, chen jia chen.1...@gmail.com wrote: Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new directory. I also update /etc/apparmor.d/usr.sbin.mysql so that news lines with /var/lib/mysql replaced by /media/disk/MySQL_data are added. However, after sudo /etc/init.d/apparmor reload I try sudo /etc/init.d/mysql start I got * Starting MySQL database server mysqld [fail] If I change the datadir line in /etc/mysql/my.conf back to the original one, I can start MySQL successfully. I think I have done everything needed to change MySQL data directory. Why am I still getting this error? Where can I start to look for the causes? Thanks. Jia -- MySQL
Re: Fail to change MySQL data directory on ubuntu
Hi Johnny, Thanks you so much! Your command fixed the problem beautifully. Now, MySQL can start successfully. I can create and drop databases without problem. Thanks again. Best, Jia Johnny Withers wrote: I'm at work now, this is the cmd I used: chcon -R -u system_u -r object_r -t mysql_db_t /data (my data lives in /data/mysqlXX -- were XX is the server version) On Wed, Aug 26, 2009 at 8:02 AM, Johnny Withers joh...@pixelated.net mailto:joh...@pixelated.net wrote: Yes, error 13, permission denied. Check selinux setup. I had this same problem last week on a CentOS machine. I had to change the object type of the new data dir to mysqld-something. I'm on a mobile phone and can't remember the exact cmd. On Tuesday, August 25, 2009, Jia Chen chen.1...@gmail.com mailto:chen.1...@gmail.com wrote: I run sudo /etc/init.d/mysql start and check the syslog by running sudo tail -f /var/log/syslog This is what I get Aug 25 22:18:06 chenj-desktop mysqld_safe[10934]: started Aug 25 22:18:06 chenj-desktop kernel: [11083.933531] type=1503 audit(1251253086.020:43): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop kernel: [11083.933581] type=1503 audit(1251253086.020:44): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] option 'thread_stack': unsigned value 128 adjusted to 131072 Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 InnoDB: Operating system error number 13 in a file operation. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: The error means mysqld does not have the access rights to Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: the directory. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File name ./ibdata1 Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File operation call: 'create'. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: Cannot continue operation. Aug 25 22:18:06 chenj-desktop kernel: [11083.962674] type=1503 audit(1251253086.048:45): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=110 name=/home/chenj/MySQL_data/ibdata1 pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld_safe[10944]: ended Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Does this message tell me any thing about the problem? Thanks. Best, Jia Johnny Withers wrote: First, check the error log, if you can't find it, start mysql from the cmd line by running safe_mysqld it should print errors to console. If it is a permission issue, it might be caused be selinux, you'll need to change the object type od that new directory to mysqld- something. I can't recall the command. A google search on selinux and mysql should produce good results. On Tuesday, August 25, 2009, chen jia chen.1...@gmail.com mailto:chen.1...@gmail.com wrote: Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new
Re: Best practice to disable log bin for mysql_upgrade
According to this page in the MySQL 5.0 Manual: http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html - mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables: mysqlcheck --all-databases --check-upgrade --auto-repair mysql [fix_priv_tables] ... [fix_priv_tables] represents a script generated interally by mysql_upgrade that contains SQL statements to upgrade the tables in the mysql database. - Is there a way to generate this [fix_priv_tables] script outside of an actual upgrade, so that I could apply it myself? Is this [fix_priv_tables] script the same as the share/mysql_fix_privilege_tables.sql script distributed with MySQL? -RG Russell E Glaue wrote: I am upgrading minor MySQL versions, 5.0.67 to 5.0.84 I have a master-master replication setup, and want to upgrade both installations. The ideal procedure for upgrading mysql using the mysql_upgrade command is to have binary logging turned off during the execution of mysql_upgrade. My situation is I do not want to turn off binary logging for the entire server, I would like to turn off binary logging just for the session of the mysql_upgrade connection. mysql_upgrade does not support anything like --disable-log-bin (which seems is a feature that should be supported for this cli app) (1) So it seems my only option is to turn off binary logging for the entire server while I execute mysql_upgrade. Which also means blocking write access to the server while it runs so that statements I do want logged for replication do not occur while binary logging is off. Is there another simple way to achieve this? Or what is best practice that achieves the least amount of down time? Alternately, there are sql files in the share directory of each archive: mysql-5.0.67-linux-i686-glibc23/share/ |-- fill_help_tables.sql |-- mysql_fix_privilege_tables.sql |-- mysql_system_tables.sql |-- mysql_system_tables_data.sql |-- mysql_system_tables_fix.sql `-- mysql_test_data_timezone.sql mysql-5.0.84-linux-i686-glibc23/share/ |-- fill_help_tables.sql |-- mysql_fix_privilege_tables.sql |-- mysql_system_tables.sql |-- mysql_system_tables_data.sql |-- mysql_system_tables_fix.sql `-- mysql_test_data_timezone.sql Is it true (yes or no) that the difference between these 6 sql files from one distribution to the next is what would be changed if the mysql_upgrade was executed to upgrade an installation from 5.0.67 to 5.0.84 ? According to this: http://forge.mysql.com/worklog/task.php?id=4991 There are two scripts: share/mysql_system_tables.sql and share/mysql_system_tables_fix.sql These two scripts comprise the content of: share/mysql_fix_privilege_tables.sql The following is true about these three files: cat share/mysql_system_tables.sql share/mysql_system_tables_fix.sql share/mysql_fix_privilege_tables.sql To upgrade the schema of the mysql server, only the share/mysql_fix_privilege_tables.sql sql script need be applied. Is this correct? Is it true for MySQL 5.0.x ? References: (1) http://bugs.mysql.com/bug.php?id=46638thanks=4 mysql_upgrade needs --disable-log-bin option (2) http://forums.mysql.com/read.php?20,275140,275140#msg-275140 MySQL Forums :: General :: Best practice to disable log bin for mysql_upgrade (3) http://forums.mysql.com/read.php?11,274796,274796#msg-274796 MySQL Forums :: Install :: How to best disable log bin for mysql_upgrade -RG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: reverting to passwd-less root w/out --skip-grant-tables
Thanks for the tips, all. Looks like we've got it restored via --skip-grant-tables and restoring some missing user rows (which caused me not to be able to see DBs in 'show databases'). I was also confused about being able to load an empty string '' into the non-null mysql.user.password field. Thanks again. On Tuesday 25 August 2009 @ 21:51, Walter Heck - OlinData.com wrote: Is there a specific reason you cannot do it with --skip-grant-table? You should theoretically also be able to overwrite the files user.* (there should be 3) in /var/lib/mysql/mysql/ (replace everything up to and including teh first mysql in that path with your mysql data dir) when the server is stopped with a copy from a fresh install. That will wipe all users in your database though, and might have unforeseen consequences depending on what you had defined before. Backup first though! Walter On Wed, Aug 26, 2009 at 03:33, Joemysql@bluepolka.net wrote: I'm trying to get back to an earlier state where we started mysqld withOUT --skip-grant-tables but the root user had no password. Yes, insecure, but we're in restoration mode here. How do I reset/revert the root password to no password without running with --skip-grant-tables? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
free ETL tool for files using MySQL
Dear all, Benetl, a free ETL tool for files now using MySQL is out in version 3.0. You can freely download it at : www.benetl.net You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier www.benetl.net Founder of Benetl Java project manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
