What is mysqldump?
Mysqldump is a command-line utility that creates a logical backup of a MySQL database. It generates SQL Statements, which can be used to recreate database objects and data. The command can also generate output in XML, delimited text, or CSV formats.
This tool can only be used if your database process is accessible and running. If your database is inaccessible for any reason, you can create a physical backup, which is a copy of the file system structure that contains your data.
This command is simple to use, but there is one issue that arises when restoring the database. When you create a backup of a MySQL database, it creates a backup file that contains the SQL commands required to rebuild or restore the database. When you restore the database, the command now executes all of the SQL statements that were used to create tables and insert data. If you have a large database, the restoration process can take a long time.
What are the Prerequisites?
- To run the backup, you’ll need a working MySQL server and a database user.
- You will need root privileges or a user account with “sudo” privileges to access the system.
How to Backup a Database?
-
General Syntax
The General Syntax of the mysqldump command is given below:
|
- mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
-
Parameters
The different parameters used in the mysqldump utility are as follows:
- -u [user_name]: It is a username that will be used to connect to the MySQL server for generating the backup using the mysqldump utility.
- -p [password]: It is the MySQL user’s valid password.
- [option]: It is the configuration option that allows you to customize the backup.
- [database name]: It is the name of the database to be backed up.
- [table “” not found /]
: This is an optional parameter. If you want to take the backup of specific tables, then you can specify the names in the command. - “<” OR ”>”: This character indicates whether you are creating a backup or restoring a database. You can use “>” to create the backup and “<” to restore it.
- [dumpfilename.sql]: The backup file’s path and name.
-
Description
The description of the different attributes in the mysqldump syntax is as follows:
- Before beginning the backup process, mysqldump prompts for a password.
- Determined by the size of the database, the backup process will take some time to complete.
- The backup database will be created in the directory where the command is executed.
- The expression -$(date + %F) appends a timestamp to the filename.
-
Example Use Cases
- Creating a backup of an entire Database Management System (DBMS):
mysqldump –all-databases –single-transaction –quick –lock-tables=false > full-backup-$(date +%F).sql -u root -p
- Backing up a specific database:
In the example shown below, you can replace db_1 with the database name you want to back up:
mysqldump -u [username] -p db_1 –single-transaction –quick –lock-tables=false > db_1-backup-$(date +%F).sql
- Backing up a single table from any database:
In the example below, the table table1 is exported from the database named db_1:
mysqldump -u [username] -p –single-transaction –quick –lock-tables=false db_1 table1 > db_1-table1-$(date +%F).sql
The mysqldump command options used in the above use cases are:
- –single-transaction: It issues a BEGIN SQL statement before dumping data from the server.
- –quick: It helps in dumping tables row by row. This provides an extra layer of security for systems with limited RAM and/or large databases because the storage of tables in memory could become troublesome.
- –lock-tables=false: It helps in not locking tables for the backup session.
How to Automate Backups with cron?
To schedule database backups on a routine or constant basis, entries can be added to /etc/crontab.
The steps followed to automate the backup process are as follows:
- Step 1: Create a file that contains the login information for the MySQL root user who will be performing the backup. It should be noted that the system user whose home directory this file is stored in is not necessarily related to any MySQL users.
(File Name: /home/example_user_206/.mylogin.cnf)
[client]
user = root
password = MySQL root user’s password
- Step 2: As shown below, you can now restrict the credentials file’s permissions.
chmod 600 /home/example_user_206/.mylogin.cnf
- Step 3: Now, you can create the cron job file.
An example that showcases the cron job to back up the entire database management system every day at say 2 am is shown below:
(File Name: /etc/cron.daily_1/mysqldump)
0 2 * * * /user/bin/mysqldump –defaults-extra-file=/home/example_user_206/.my.cnf -u root –single-transaction –quick –lock-tables=false –all-databases > full-backup-$(date +\%F).sql
How to Restore a Backup?
Backups can be restored using the Restoration command. The General Syntax of the Restoration command is given below:
mysql -u [username] -p [databaseName] < [filename].sql
There are different purposes for using the restoration command. These are:
- Restore an entire DBMS Backup
- Restore a single database dump
- Restore a single table
-
Restore an entire DBMS Backup
mysql -u root -p < full-backup.sql
As you can see from the above comand, you will be prompted for the password of the MySQL root user. The MySQL database system’s current data will be overwritten.
-
Restore a single database dump
mysql -u [username] -p db1 < db1-backup.sql
An empty or old destination database must previously exist for the data to be imported into, and the MySQL user running the command should have “write” access to that database.
-
Restore a single table
mysql -u dbadmin -p db1 < db1-table1.sql
To receive the data, you must have a destination database ready.
Conclusion
This post introduces the ways to use mysqldump to back up MySQL in detail. It also gives a quick overview of the restoration process.
