1. Uncategorized

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 mysqldumpmysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP tableCREATE 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]
Comments to: MySQL Database Backup using mysqldump command

    Your email address will not be published. Required fields are marked *

    Attach images - Only PNG, JPG, JPEG and GIF are supported.