How to Backup and Restore MySQL Database using Command Line

On 6/15/2015

It’s a good practice to regularly backup the databases to avoid losing data. And at times you may want to move the database from development to production environment or to some remote server. Either way you have to backup the database, and this mysql tutorial will show you the simple way to backup and restore mysql database using command line. MySQL Database Management System provides a powerful utility called ‘mysqldump’ which is available with the mysql installation and you can find it inside the ‘bin’ directory.

how-to-backup-restore-mysql-database-command-line

How to Backup and Restore MySQL Database using Command Line

You can use the ‘mysqldump’ command line utility to create dumps of one or more mysql databases and even generates the output as csv, sql or xml file. The dumps contains the set of sql commands (like DROP, CREATE, INSERT) required to reproduce the databases from scratch.

Backup MySQL Database using mysqldump Command

Use the mysqldump command to backup the database like this,

$ mysqldump [option] -u [username] -p[password] [database name] > [dump file]

The [option] can be anyone of the mysqldump options. For example using the option ‘--no-data’ will copy only the database structure without the table data.

The [username] is the mysql database username.

The [password] is the mysql database password. (Please note that there is no space between the ‘-p’ and the ‘[password]’).

The [database name] is the name of mysql database to backup.

The [dump file] is the file name where the dump is stored.

Backup A Single MySQL Database

Say for example, to backup a mysql database ‘db_employees’ with mysqldump command use this,

$ mysqldump -u myusername -pmyusername db_employees > employees.sql

Backup Multiple MySQL Databases

To backup more than one databases in mysql, use the ‘--databases’ option followed by the database names separated with space between them like this,

$ mysqldump -u myusername -pmyusername --databases db_employees db_library > two_db_backup.sql

Backup All MySQL Databases

To backup all the available databases in the mysql server use ‘--all-databases’ option along with mysqldump command like this,

$ mysqldump -u myusername -pmyusername --all-databases > all_db_backup.sql

Backup MySQL Database Table

At times you may want to backup only a table instead of an entire database. In that case use the table name following the db name like this,

$ mysqldump -u myusername -pmyusername db_library tbl_books > books.sql

Restore the MySQL Database from the Dump File

The mysqldump utility only creates MySQL dumps and these are nothing but a set of sql commands to be executed. In order to restore the databases from the dumps, you should create the database in mysql with the same name and run the dump file using mysql command.

$ mysql -u [username] -p[password] [database name] < [dump file]

The [username] is the mysql database username.

The [password] is the mysql database password. (Please note that there is no space between the ‘-p’ and the ‘[password]’).

The [database name] is the name of mysql database to be restored.

The [dump file] is the name of the dump file.

To restore the mysql database ‘db_employees’ from the previously created dump file use this,

$ mysql --u myusername -pmyusername db_employees < employees.sql

To restore only a single mysql database from the complete dump file, you have to explicitly mention it like this,

$ mysql -u myusername -pmyusername --one-database db_library < all_db_backup.sql

To restore the mysql database table from the dump file, mention the database name to restore like this,

$ mysql -u myusername -pmyusername db_library < books.sql

I hope now you have a better understanding of backup and restore mysql databases using command line.

Related Read:

No comments:

Post a Comment

Contact Form

Name

Email *

Message *