In this tutorial you are going to learn how to backup MySQL databases via command line on Linux server. Also, we'll cover restoring a mysql database through command line.
Do you know that MySQL provides an amazing tool to backup and restore databases?
Let's dive in and learn about it.
mysqldump — A Database Backup Utility
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format..
Definition
How to Backup a Database?
You can use mysqldump command utility to dump database or tables data into textfiles with SQL queries. Later, MySQL can restore database by executing all the SQL queries stored in dumped text files.
There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:
mysqldump [mysqldump [options]db_name[tbl_name...]options] --databasesdb_name...
mysqldump [options] --all-databases
If you want to backup single database, execute below command to dump database contents to text file.
mysqldump sampledb > sampledb.sqlTo backup multiple databases, execute below command.
mysqldump --databases database_db1 database_db2 > mutilple_databases.sqlFor all databases on server, execute below command.
mysqldump --all-databases > all_databases.sqlAll above commands generates .sql file which will be used for restoring databases.
How to Restore a Database?
You can restore MySQL database using dump files ( .sql ) created in earlier steps. The SQL files contain a series of SQL queries so it's easy to restore through mysql command.
To restore database "sampledb" from SQL dump file, use below command.
mysql sampledb < sampledb.sqlIn the above command, sampledb is the name of the database you want to restore, and sampledb.sql is the name of the backup file to be restored.
To restore one particular database from SQL dump file, use below command.
mysql --one-database sampledb < all_databases.sqlOnce command executed successfully, check your database.
So far, you have learned how to backup database using mysqldump tool and restore a database through mysql command.
Let us know what you think of this article and we'd love to hear from you.