MySQL

From Notes

Often when upgrading MySQL from an older version (< 4.1) and using PHP 4 or other older clients, passwords must be maintained in an older format than what is supported by MySQL 5. In this case, passwords can be converted using the following syntax:

update user set password = old_password('password') where user = 'username';
flush privileges;

To maintain passwords in the old format, add the following line to the configuration file:

#/etc/my.cnf
old_passwords=1

• MySQL Replication

Database Backups

Simple, but very effective:

mysqldump --quick -u root -p <dbname> | gzip > <dbname>-dump.gz

To restore:

gzip -dc <dbname>-dump.gz | mysql -u root -p <dbname>

Reset MySQL Password

In /etc/my.cnf (or wherever you have the configuration file), add the following line in the DB definition:

#/etc/my.cnf
skip-grant-tables

Restart MySQL:

/etc/init.d/mysqld restart

Reset your password with the following command:

mysql -e "update user set password = password('pass') where user = 'root'" mysql

Edit /etc/my.cnf to read the grant tables, securing your database once again:

#/etc/my.cnf
-skip-grant-tables

Restart MySQL:

/etc/init.d/mysqld restart

Schema Reserved Word

Sometime between v 3 and v 5 schema was changed to be a keyword, to fix this in dump files, replace schema with `schema` and re-import.

sed -i 's/ schema/ \`schema\`/g' <dumpfile>.dump