MySQL Backup Procedure

= Backup =

The 'mysqldump' utility will create a list of SQL statements which can recreate the database. It is best to redirect this output to a file.

linux:~ # mysqldump -uMyUser -pMyPass -A -q > dump_file.sql


 * -u Username
 * -p Password
 * -A All Databases (that the user -u has GRANT access to)
 * -q Quick
 * > Redirect console output to a file > dump_file.sql

= Restore =

What you have is a file of SQL Statements which can be restored as follows.

Start with the root user and redirect the input into the command from the file.

linux:~ # mysql -uroot -ppassword < dump_file.sql


 * -u Username
 * -p Password
 * < Redirect SQL file as input < RDF_Website.sql

= User permissions = Recreate the database user(s) with the GRANT options.

linux:~ # mysql -uroot -ppassword Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 170 to server version: 4.1.10a Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> grant all on MyDB01.* to 'MyUser'@'localhost' identified by 'MyPass' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> grant all on MyDB02.* to 'MyUser'@'localhost' identified by 'MyPass' with grant option; Query OK, 0 rows affected (0.00 sec) Check Grants mysql> SHOW GRANTS for 'MyUser'@'localhost'; +--+ | Grants for MyUser@localhost | +--+ | GRANT USAGE ON *.* TO 'MyUser'@'localhost' IDENTIFIED BY PASSWORD '*A1361938C1D8A0E651E99019408F217F32F60078'| | GRANT ALL PRIVILEGES ON `MyDB01`.* TO 'MyUser'@'localhost' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `MyDB02`.* TO 'MyUser'@'localhost' WITH GRANT OPTION | +--+ 3 rows in set (0.00 sec) mysql>

= See also =
 * mySql
 * datABase
 * backup