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