How do you back up all the MySQL databases on a server?
To make a local backup, simply type:
mysqldump -u utilisateur_autorisé –password=mot_de_passe –all-databases > backup.sql
You can also compress your backup on the fly:
mysqldump -u utilisateur_autorisé –password=mot_de_passe –all-databases | gzip>backup.sql.gz
Finally, if you have several machines, you can also back up your database remotely:
From the backup server:
mysqldump -u utilisateur_autorisé –password=mot_de_passe –all-databases –host=serveur_sql > backup.sql | gzip>backup.sql.gz
From the MySQL server:
mysqldump -u utlisateur_autorisé –password=mot_de_passe –all-databases | gzip | ssh root@serveur_backup « cat > /home/backup/backup.sql.gz »
You can also make a "binary" backup of your databases. If they are very large, this type of backup can offer certain advantages, such as speed:
First, put the queries in progress on hold.
mysql -u root -p
>FLUSH TABLES WITH READ LOCK ;
In another terminal, you can then make a binary backup of your databases:
tar cvfz /home/backup/backup_sql.tar.gz /var/lib/mysql
As in the examples above, you can also send it directly to a remote server:
tar cvfz /home/backup/backup_sql.tar.gz /var/lib/mysql | ssh root@serveur_backup « cat > /home/backup/backup_sql.tar.gz »
Then return to the first terminal and release the queries:
>UNLOCK TABLES;
You can also do the same thing by stopping the database, then restarting it once the operation is complete:
/etc/rc.d/init.d/mysql stop
/etc/rc.d/init.d/mysql start
How do you check and repair a MySQL database?
There are 2 main methods for checking and repairing a MySQL database. The first uses the "mysqlcheck" command:
mysqlcheck tbase -p: checks the « tbase » database
mysqlcheck –all-databases -p: checks all the installed databases
mysqlcheck –auto-repair –all-databases -p: checks and then automatically repairs the errors detected on the various databases
For more information: mysqlcheck –help
The second method uses "myisamchk". Unlike mysqlcheck, myisamchk does not need MySQL to be running: it analyses the tables directly in their binary form in the directory /var/lib/mysql/toto/bla.MYI. Example:
myisamchk -i -o /var/lib/mysql/toto/bla.MYI -> checks and repairs the « bla » table of the « toto » database.
For more information: myisamchk –help
How do you restore a MySQL dump from the command line?
There are 2 ways to import a .sql file:
1) Use phpMyAdmin
2) Use the mysql command
If the file is called sauvegarde.sql, you can import it directly from the command line, without using phpMyAdmin, with the following command:
mysql -u utilisateur -p –database=nom_de_la_base
How do you transfer a MySQL database from one server to another without an intermediate file?
The simplest way is to combine the two methods above: create a dump and import it, but without an intermediate file. Use a pipe instead. Here is how:
mysqldump -u utilisateur_autorisé –password=mot_de_passe –all-databases –host=serveur_sql |mysql -u utilisateur –host=serveur_sql -pmot_de_passe –database=nom_de_la_base
–
Christophe Casalegno
http://twitter.com/Brain_0verride
http://www.facebook.com/brain.override
1 comment