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 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,
Related Read:
$ 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.
