Tuesday 1 March 2011

Mysql database: Migrate and Repair

To repair, just issue this command: 
mysqlcheck -uroot -pxxxxx --auto-repair --optimize --databases your_db
Man page

Migrate mysql using file 

Usually, it is less-time consuming to migrate a database using file, instead of using mysqldump to make a *.sql file, and then restore the database. Sometimes, like when the *.sql file is not available, it is the only way to re-alleviate a website.

So to migrate the file directly:

1)Stop MySQL server; 
2) if necessary, rename the folder containing the individual table files to whatever you want the database to be named (e.g. 'drupal'); 
3) copy the folder directly into /var/lib/mysql
4) set permissions on the folder: as root, run 'chown -R mysql /var/lib/mysql/*'; 
5) restart MySQL server; 
6) If you've used a new mysql user, need to update the settings.php accordingly