MySQL database backup

MySQL database backup can be taken using the mysqldump utility. The backup can be taken for all databases, a single database or even for selected tables. The best part about mysqldump is that it is very easy to use. When you execute mysqldump, an sql file is created, that contains SQL queries to rebuild the database and create all the tables with the data populated. This process takes time according to the data you possess. Outlined below are the steps to take a MySQL database backup using mysqldump.

How to take mysql backup using mysqldump?

MySQL backup command general syntax is:

mysqldump -u [user_name] –p [option] [database_name]  > [dumpfile_name.sql]

Where:

1. -u [user_name]: Username to connect to the MySQL server.
2. [option]: Options to customize the backup. See more options here
3. [database name]: Database that you want to take backup
4. [dumpfile_name.sql]: Path and name of the backup file.

Once the command is run, you will be prompted to enter the password for the database user. Only after authenticating with the right password will the dump process start.

Backup a single database:

The most common use of mysqldump is usually to backup a single database. Follow the below steps to back up a single database:

1. On shell, execute the below command to back up a database named ‘DB1’. Mention the file name you would want the backup created.

mysqldump -u root -p DB1 > DB1_bkp.sql

Where, DB1 is the database and DB1_bkp.sql is the backup SQL file name.

Backup multiple MySQL databases:

To backup multiple MySQL databases, you will need to use --database option followed by the list of databases to be backed up. See steps below:

1. On shell, execute the below command to back up databases ‘DB1’,’DB2’ and ‘DB3’. Mention the file name you would want the backup created.

mysqldump -u root -p – -databases DB1 DB2 DB3 > DB123_bkp.sql

Where, DB1, DB2 and DB3 are the databases and DB123_bkp.sql is the backup SQL file name.

Backup all databases:

To backup all databases, invoke mysqldump with the – – all-databases option. See steps below:Take Shell

1. Execute the below command to back up all the databases. Mention the file name you would want the backup created.

mysqldump -u root -p – -all-databases > DB_bkp.sql

Where DB_bkp.sql is the backup SQL file name.

Additional Options:

1. If you want to back up a single table from a database, you can execute the below command:

mysqldump [database_name] [table_name] > [dumpfile_name.sql]

For multiple tables table_1, table_2, table_3, use the below command:

mysqldump [database_name] [table_1] [table_2] [table_3] > [dumpfile_name.sql]

2. If the database size is large, you can zip the backup sql file using the below command:

mysqldump [database_name] | gzip > [dumpfile_name.sql.gz]

3. If you want to add the time stamp on the backed-up file, add the file name as:

[dumpfilename]-$(date +%F).sql

Follow the above steps to take the MySQL database backup. Check this article to restore a database.