MySQL Database Backup using mysqldump command

Sometimes when you have large databases, phpmyadmin or similar tool might fall when it comes to exporting or importing. The easiest way is to use command line. In this tutorial, we will learn how to use the mysqldump command to make a backup one or more databases in a MySQL Server.

MySQL provide a great command line utility to take backup of your MySQL database and restore it called mysqldump. mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. This tutorial will show you the easiest ways to backup the data in your MySQL database using mysqldump.

Creating A Backup

This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

mysqldump --opt -u [uname] -p [pass] [dbname] > [backupfile.sql]

1. [uname] Your database username
2. [pass] The password for your database (note there is no space between -p and the password)
3. [dbname] The name of your database
4. [backupfile.sql] The filename for your database backup
5. [–opt] The mysqldump option

For example, to backup a database named ‘blog‘ with the username ‘root‘ and with no password to a file blog_backup.sql, you should accomplish this command:

mysqldump -u root -p blog > blog_backup.sql

This command will backup the ‘blog‘ database into a file called blog_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only posts and comments tables from the ‘blog’ database accomplish the command below. Each table name has to be separated by space.

mysqldump -u root -p blog posts comments > blog_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space. for example to backup database blog and forum you should accomplish this command:

mysqldump -u root -p --databases blog forum > blog_and_forum_backup.sql

If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

mysqldump -u root -p --all-databases > all_backup.sql

Back up MySQL Database with Compress

when you have large databases, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

Alfa Adhitya Member since December 18, 2019

Computer science education cannot make anybody an expert programmer any more than studying brushes and pigment can make somebody an expert painter – Eric S