MySQL: From Dumps to Restores

by

A MySQL dump is a file that contains SQL commands, which, when executed, would lead to the recreation of the MySQL database at the moment of the dump’s export. Essentially, a MySQL dump database is a logical MySQL backup database.

mysqldumpis a utility that creates a MySQL dump. It is part of MySQL and MariaDB and gets installed alongside the MySQL client.

Basic mysqldump Command

To simply create a database dump, you can use the following command:

mysqldump db_name

Executing this command will output an SQL script to the console. However, this is not usually very useful. What you would probably need to do is to dump the MySQL database to a file. Therefore, we redirect the command’s output (stdout) to a file:

mysqldump db_name > db_name.sql

Without parameters, the mysqldumputility creates a logical backup of the entire database. This backup will include the database creation command, table creation commands, and commands to populate these tables.

mysqldump Authentication

To connect to the DB, you need to specify the connection data. By default, you need to specify the login and password for connection in the parameters.

mysqldump -u user -ppassword db_name > db_name.sql

The -p parameter is used to specify the password for the user passed with -u. If nothing is specified after -p, the mysqldump utility will request the password during execution. If you want to specify the password directly in the command, you need to append it directly to the -p flag without any space, as shown above. However, for security reasons, specifying the password in this manner can be unsafe as it shows up in the process list. The password can be passed via an MYSQL_PWD and a special file to maintain security.

On Linux

For Linux, you can interact with the DB as a superuser.

sudo mysqldump db_name > db_name.sql

In the examples below, user parameters will be omitted.

Useful mysqldump parameters

The mysqldumputility has about 125 parameters, most of them are specialized and almost never used.

However, there is a whole set of notable parameters, the use of which can be useful in most cases, and yet they are not enabled by default.

–single-transaction vs –lock-tables

The --lock-tables option is enabled by default. This parameter locks the database to prevent modifications during the dump process. Such locking assists in creating a transactionally consistent backup but at the cost of database downtime.

The --single-transaction option allows for a hot backup of MySQL, meaning there’s no need to lock database queries during the dump process. This parameter only works for tables on the InnoDB engine. However, since InnoDB is the default engine, it’s likely you can use it.

Therefore, if you did not specify a table engine when creating your tables, you should definitely use --single-transaction.

If you have engines other than InnoDB (for example, MyISAM or MEMORY), you can use the --skip-lock-tables parameter, but in this case, there’s a risk of getting an inconsistent dump, which may lead to foreign key errors during the dump restore.

You can find out which engines your tables are using with the following query:

SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

–routines and –events

By default, routines and events are not included in the dump. If you have any in your database, they will be lost during the restore, and you will likely not notice this immediately. To prevent their loss, it is advisable to always use these parameters, which are not enabled by default for some reason.

–default-character-set=utf8mb4

The utf8mb4 character set ensures maximum compatibility with all possible symbols in your database. If this character set is not specified, any Unicode symbols can trigger errors like:

ERROR 1366 (HY000) at line XXX: Incorrect string value: '\xF0\x9F\x98\x80' for column 'column_name' at row 1

–hex-blob

This option encodes all BLOB fields into their hexadecimal representation. This helps avoid any issues that might arise due to encoding or special characters in binary data.

mysqldump backup Example

dump full database

To create a dump of an entire database, use the following command structure:

mysqldump --single-transaction --routines --triggers --events --hex-blob --default-character-set=utf8mb4 database_name > database_name.sql

mysqldump large database

When dealing with a large database, you might want to use the --quick and --compress options to speed up the process and reduce the size of the output file:

mysqldump --single-transaction --quick --compress --routines --triggers --events --hex-blob --default-character-set=utf8mb4 [database_name] > [output_file].sql
  • The --quick option forces the mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. This can help deal with the memory limitations that might be encountered when dealing with large databases.
  • The --compress option compresses all information sent between the client and the server, reducing the amount of data sent over the network, which can be highly beneficial for large databases. However, keep in mind that this might increase the CPU usage due to the need to perform compression/decompression operations.

mysqldump Backup Data Only

To generate a backup that solely contains data (only INSERT statements) without any additional information such as table structure, routines, triggers, or events, you can use the --no-create-info, --skip-triggers, --skip-routines, and --skip-events, --skip-comments options. Here’s the revised command:

mysqldump --single-transaction --no-create-info --skip-triggers --skip-routines --skip-events --hex-blob --skip-comments --default-character-set=utf8mb4 database_name > output_file.sql

Export Specific MySQL Tables with mysqldump

To dump a specific MySQL table, use this command:

mysqldump database_name table_name > table_name.sql

To dump multiple tables, extend the command as follows:

mysqldump database_name table_name1 table_name2 table_name3 > backup.sql

This command exports table_name1, table_name2, and table_name3, storing the data in a single backup.sql file. For data transfer, mysqldump provides valuable options like --where and --insert-ignore:

  • --where filters records that are included in the dump
  • --insert-ignore replaces ‘INSERT INTO’ with ‘INSERT IGNORE’, preventing errors from duplicate entries by ignoring them. Here’s an example of using these options:
mysqldump  database_name table_name --where="date > '2023-01-01'" --insert-ignore > table_name.sql

This command exports data from table_name where the date is later than January 1, 2023, and ignores duplicate entries.

Other Useful Uses of mysqldump

mysqldump All Databases

Typically, you need to backup not just one DB, but all DBs in the DBMS at once. To do this, use the following command:

mysqldump --all-databases > all_databases.sql

mysqldump gzip

Since mysqldumpoutputs the dump to stdout, it can be intercepted and immediately archived using gzip:

mysqldump -u username -p database_name | gzip > backup.sql.gz

On Windows, this process is a bit more complex, but it can still be accomplished using third-party utilities such as 7-Zip.

mysql restore dump

The easiest way to restore a dump is with the mysql utility, by simply redirecting the file directly to the utility, like so:

mysql -u user -p < all-database.sql

Depending on how the dump was created, you may need to specify the name of the DB into which the data is being loaded:

mysql -u user -p database_name < database_name.sql

MySQL Automatic Backup

Linux

Use crontab -e and add the following line for the backup to run daily at 1 AM:

0 1 * * * mysqldump -u username -p password --all-databases > /path/to/backup/all_databases.sql

Windows

Use Windows Task Scheduler to schedule the execution of mysqldump at a specific time. For this, create a batch file with the mysqldump command and set up Task Scheduler to run it according to schedule.

Here’s a simple example of what the batch file could look like:

@echo off
set MYSQL_DIR=C:\Program Files\MySQL\MySQL Server 5.7\bin
set BACKUP_DIR=D:\backups
set DB_NAME=database_name
set DB_USER=username
set DB_PASSWORD=password

"%MYSQL_DIR%\mysqldump.exe" -u %DB_USER% -p%DB_PASSWORD% %DB_NAME% > "%BACKUP_DIR%\%DB_NAME%_backup.sql"

Automating MySQL Backups with Timestamps

Linux

To generate a backup file with a timestamp in its name in Linux, you can use the date command within the mysqldump command like so:

mysqldump -u username -p password --all-databases > /path/to/backup/all_databases_$(date +%Y%m%d_%H%M%S).sql

This command will create a backup with a name like all_databases_20230601_010000.sql, where the timestamp is in the format YYYYMMDD_HHMMSS.

Now, add this command to your crontab:

0 1 * * * mysqldump -u username -p password --all-databases > /path/to/backup/all_databases_$(date +\%Y\%m\%d_\%H\%M\%S).sql

Windows

In Windows, you can use the %date% and %time% environment variables to add a timestamp to your backup file. Here’s how you could modify the batch script:

@echo off
set MYSQL_DIR=C:\Program Files\MySQL\MySQL Server 5.7\bin
set BACKUP_DIR=D:\backups
set DB_NAME=database_name
set DB_USER=username
set DB_PASSWORD=password

for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do (
set DATE=%%k%%j%%i
)
for /f "tokens=1-4 delims=/:." %%i in ('time /t') do (
set TIME=%%i%%j
)
"%MYSQL_DIR%\mysqldump.exe" -u %DB_USER% -p%DB_PASSWORD% %DB_NAME% > "%BACKUP_DIR%\%DB_NAME%_backup_%DATE%_%TIME%.sql"

The modified script now creates a backup file with a name like database_name_backup_20230601_0100.sql, where the timestamp is in the format YYYYMMDD_HHMM.

Graphic utilities based on mysqldump

MySQL Workbench

MySQL Workbench provides an intuitive interface for working with MySQL, including the ability to use mysqldumpto create backups. Here are the basic steps to create a backup using MySQL Workbench:

  1. Launch MySQL Workbench and connect to your database server.
  2. Under the Management section, select Data Export.
  3. Select the database or tables that you wish to backup.
  4. Specify your export options, including file format and location for the save.
  5. Click on Start Export to create your backup.

phpMyAdmin

phpMyAdmin is a web interface for managing MySQL databases, which also allows you to create backups using mysqldump. Here’s how to do it:

  1. Login to phpMyAdmin and select the database you wish to backup.
  2. Go to the Export tab.
  3. In the Export method section, select Custom to be able to customize export parameters.
  4. Select the tables for export and other options as needed.
  5. Click on Go to create your backup.

SqlBak

SqlBak is a service that allows you to backup and restore MySQL Server databases, as well as other DBMS, according to a predefined schedule. It utilizes the mysqldump tool to create backups specifically for MySQL databases and can be used on both Windows and Linux operating systems. Below is a concise tutorial on how to install the SqlBak App and configure a backup job.

  1. After creating an SqlBak account, please proceed to the Download page. Select the operating system (OS) that you are using, and then follow the provided instructions to install the SqlBak App.
  2. The next step is to establish a connection to your MySQL Server.
    • If you are running SqlBak on Linux, you can establish the connection by using the following command:
      sudo sqlbak --add-connection --db-type=mysql --user=root
    • If you are using the Windows version, click on the “+ Add” button and select “MySQL Server (TCP/IP)” as the DBMS type from the drop-down list. Then, provide the necessary credentials to establish a connection with your MySQL Server.
  3. Once you have successfully established the connection, navigate to the “Dashboard” page. To create a backup job, click on the “Add new job” button located in the upper right corner of the screen.
  4. In the settings window that appears, follow the step-by-step configuration process, which is designed to be intuitive and easy to understand. Once you have completed the configuration, click on the “Save” button located in the upper right corner of the window.

Conclusion

In conclusion, mysqldump is a valuable tool for backing up MySQL databases, yet it requires deep expertise and constant monitoring. For a more convenient and automated backup management, consider using SqlBak. This service provides automation, monitoring and convenience, allowing you to focus on the core aspects of your business. Regular and reliable data backup is the key to successful and secure business operation.

Leave a Comment