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]
- [uname] Your database username
- [pass] The password for your database (note there is no space between -p and the password)
- [dbname] The name of your database
- [backupfile.sql] The filename for your database backup
- [–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]