MySQL: Database Backup and Restore Using Command Line

MySQL, one of the most commonly & widely used database in world for web applications. Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Several high-traffic web sites use MySQL for its data storage and logging of user data, including Flickr, Facebook, Wikipedia, Google, Nokia and YouTube.

MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump command line utility is available with MySQL installation (bin directory) that can be used to take backup of databases and perform various task using command line. 

 1. Backup of a MySQL database using mysqldump.

   mysqldump -h[host_name] –u[user_name] –p[user_password] -P[port_number] [database_name] > [dump file]
   or
   mysqldump --host=host_name –-user [user_name] –-password=[user_password] --port[port_number] [database_name] > [database_dump_file.sql]

Example

   mysqldump -h10.123.254.110 –uroot –ppassword -P1234 db_test > database_dump_file.sql
   or
   mysqldump --host=10.123.254.110 –-user root –-password=myrootpassword --Port1234 db_test > database_dump_file.sql

Note: These two are optional but it will be a good practice to use them in command 
Host name: Server name or IP Address where mysql is running. 
Port: Default port is 3306. If mysql is running on 3306 port then need not to specify port but if mysql is running on different port then it must be specified. 

2. Backup multiple databases in MySQL.

   mysqldump -h[host_name] –u[user_name] –p[user_password] -P[port_number] [database name 1] [database name 2] .. > [database_dump_file]
   Example:
   mysqldump -h10.123.254.110 –uroot –ppassword -P1234 db_first db_second db_third > database_dump_file.sql

3. Backup all databases in MySQL.

   shell> mysqldump -h[host_name] –u[user_name] –p[user_password] -P[port_number] –all-databases > [database_dump_file]

4. Backup a specific table in MySQL.

   shell> mysqldump -h[host_name] -u[user_name] -p[user_password] [database_name] [table_name] \> dump_file.sql
   Example:
   shell> mysqldump -h10.123.254.110 -uroot -ppassword -P1234 db_mobile users \> db_mobile_users.sql

5. Restoring MySQL database. 
The mysqldump utility is only used to take the MySQL dump. To restore the database from the dump file (created in previous step), use mysql command.

   shell> mysql -h[host_name] -u[user_name] -p[user_password] [database name] < [database_dump_file]
   Example:
   shell> mysql -h10.123.254.110 -uroot -prootpassword new_database_restore < database_dump_file.sql

TIP: Now we know how to take backup of databases and restore them back. Make it a habit to always validate the backup files after taking the backup. As sometimes backup files are not properly created and when we need to restore data using those backup files that can put in critical situation.