Importance of Backup

It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replica servers.

 

Generate backup using MySQLdump utility

Mysqldump is a logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in XML, delimited text, or CSV format.

Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, PROCESS (as of MySQL 8.0.21) if the --no-tablespaces option is not used, and (as of MySQL 8.0.32) the RELOAD or FLUSH_TABLES privileged with --single-transaction if both gtid_mode=ON and --set-gtid=purged=ON|AUTO.

To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

 MySQL prerequisites:

1. Space requirements

2. Sql_mode

3. Unique_checks and foreign_key_checks

4. Privileges required for running mysqldump

• SELECT
• SHOW VIEW (If any database has Views)
• TRIGGER (If any table has one or more triggers)
• LOCK TABLES (If you use an explicit --lock-tables)

5. Max_allowed_packet

 

Syntax of the MySQLdump command

$ mysqldump   -u [database_username] –p [database_password] [options] [database_name] [tablename] > [database_backup_file.sql]

• -u [database_username]: The username to connect to the MySQL server.
• -p[database_password]: The valid password of the MySQL user.
• [options]: The configuration option to customise the backup
• [database_name]: Name the database you want to take backup.
• [tablename]: Name of that table name that you want to take backup. This is an optional parameter.
• “<” OR ”>”: This character indicates whether we are generating the backup of the database or restoring the database. You can use “>” to generate the backup and “<” to restore the backup
• [database_backup_file.sql]: Path and name of the backup file that you want to save the dump file.

 

Backup a single database

$ mysqldump  -u root  -p datapatrol > datapatrol.sql

 

A dump file is generated after the backup is done

After the backup is taken successfully, let us open the backup file to view the content of the backup file

As you can see in the above image, the backup file contains the various T-SQL statements that can be used to re-create the objects.

This script starts with some comments that give some general information about this backup. For example, the first line includes information about the mysqldump program.

Then, the third line identifies the host (localhost) and the database (datapatrol). Unfortunately, only the first database in the backup is listed, Which is probably a bug. Finally, the fifth line identifies the version of the MySQL server.

 

Backup of multiple databases or all the databases

$ mysqldump  -u root  -p --databases test Saurabh Shubham datapatrol > databases_26062023.sql

or

$ mysqldump  -u root  -p --all-databases >all_databases_26062023.sql

 

Backup of a specific table

If you want to generate the backup of a specific table, then you must specify the name of the tables after the name of the database. The following command generates the backup of the employee table of the datapatrol database.

$ mysqldump  -u root  -p datapatrol employee > datapatrol_employee.sql

 

Create a compressed MySQL database backup

If your database size is large then you can compress the output to save disk space. You will need to simply pipe the output to the gzip utility and redirect it to a file. Here is the syntax. 

$ mysqldump   -u [database_username] –p [database_password] [options] [database_name] [tablename] | gzip > file_name.sql.gz

 

Taking compressed MySQLdump backup

$ mysqldump  -u root  -p datapatrol | gzip > datapatrol.sql.gz

 

As we see above our datapatrol.sql.gz compressed output file was created 

Common options for the MySQLdump program