Data compression backup technology in MySQL

As the amount of data continues to increase, database backup becomes more and more difficult. Backup not only requires data integrity and consistency, but also requires backup speed and backup file size to meet actual needs. Data compression backup technology emerged as the times require and has become one of the indispensable technical means for database backup.

MySQL is currently one of the most popular relational databases. Its official backup tool mysqldump cannot meet the needs of compressed backup. Therefore, this article will introduce the detailed process of using the compression commands tar and gzip on Linux systems with appropriate parameters to achieve MySQL compressed backup.

1. Compression backup command

On Linux systems, tar is a very commonly used compression command. Its usage is as follows:

tar [cxtzJvfpP] [name-of-archive] [files-or-directories-to-archive]

Among them, the meaning of each parameter is as follows:

• c: Create a new file;
• x: Extract files from the archive;
• t: List the file list in the archive;
• z: Compress/decompress the file through gzip;
• j: Compress/decompress the file through bzip2;
• v: Display the file name when processing the file;
• f: Use the archive file name, note that there must be a space after f;
• p: Retain the original permissions and attributes of the file;
• P: Do not use absolute paths, keep relative paths;
• name-of-archive: Specify the compressed file name;
• files-or-directories-to-archive: Specify files or directories to compress.

What we want to use here is the parameter czf, which means creating a new tar package, compressing it through gzip, and displaying the file name when processing the file. The specific command is as follows:

tar czf backup.tar.gz /path/to/backup/files/

Among them, /path/to/backup/files/ specifies the file or directory to be backed up.

2. MySQL backup command

Before backing up MySQL, you need to create a user with SELECT, SHOW VIEW, RELOAD, SUPER, and LOCK TABLES permissions. Taking the root user as an example, you can use the following command to create a backup user:

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT SELECT, SHOW VIEW, RELOAD, SUPER, LOCK TABLES ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;

Then, you can use the following command to back up the database:

mysqldump -u backupuser -p'mypassword' --single-transaction --skip-lock-tables dbname | gzip > backup.sql.gz

Among them, --single-transaction and --skip- The lock-tables parameters correspond to the backup settings of MySQL transactions and table locks respectively. dbname represents the name of the database to be backed up, and backup.sql.gz is the full name of the file saved after backup.

3. Automated backup

In practical applications, automated backup is the most common and reliable backup method. Under the Linux system, you can use the crontab command to execute the backup command regularly and save the backup file to the specified directory. The following is an example of performing a backup at 1 a.m. every day:

0 1 * * * tar czf /backup/dbbackup-`date +%Y-%m-%d`.tar.gz /path/to/backup/files/ && mysqldump -u backupuser -p'mypassword' --single-transaction --skip-lock-tables dbname | gzip > /backup/backup-`date +%Y-%m-%d`.sql.gz

Among them, %Y represents the year, %m represents the month, and %d represents the date. The command in the backtick will automatically create a new backup based on the date information. file name.

4. Restore backup

When you need to restore the database, you can use the following command:

gunzip backup.sql.gz

Extract the backup file and obtain the backup file in .sql format. Then, import this file into the MySQL database:

mysql -u root -p dbname < backup.sql

where dbname represents the name of the database where data needs to be restored.

5. Summary

This article introduces the detailed process of using tar and gzip combined with appropriate parameters to implement MySQL backup under the Linux system. Through automated backup, backup management can be greatly simplified, the efficiency and reliability of data recovery can be improved, and users can better protect data security.

The above is the detailed content of Data compression backup technology in MySQL. For more information, please follow other related articles on the PHP Chinese website!