Many people, this is operations engineers, when it comes to writing such and such a solution, it is very headache. Is not a certain degree of unified search, is the same sentence in N groups all group hair again: “there is a certain program, can share it?? Ask for help, gentlemen “, estimate nine times out of ten, all lost in the sea, no news.

In fact, is it really hard, or is it that you don’t have the whole idea of backup organized? The quality of a plan lies in whether the layman can understand the meaning of the expression at a glance without much thought.

A good backup plan consists of the following:

  • Why do YOU need a backup?

  • What are the backup methods?

  • What is the difference between certain backup methods?

  • Backup actual combat operation overview

  • Resume actual combat operation overview

  • Other Remarks

This article will explain what constitutes a complete backup solution step by step from the above perspectives and some practical experience. If you need to learn more about Mysql database, you can reply “Mysql” in the background of the public account: Worker brother technology road to obtain a comprehensive Mysql database learning guide.

Why do YOU need database backups?

A lot of people, a look at this title, certainly mouth will answer, this is not nonsense yao. What if there is no backup fault? Run way? Data is sand sculpture development (not spray) deleted by mistake how to do? Pan back?

Of course, everyone knows the importance and necessity of backups.

1. Ensure data security and integrity

Enterprise data security should be the lifeblood of the enterprise, once lost or caused damage, the loss of customers and money, or bankruptcy (there have been precedents in).

The purpose of backup is to ensure that data deleted or damaged by human error, improper operation, or deliberate operation can be restored in a timely and effective manner without adversely affecting service running.

2. Provide uninterrupted service for business

In the actual production environment, the database must have 7×24×365 uninterrupted service capability, which is also one of the reasons to back up the database.

Backup mode of the database

Common backup methods include:

  • Logical backup

  • The physical backup

1. Logical backup

Logical backup is to use the mysqldump command of the MySQL database, or use a third-party tool, and then export the data in the database as a file in the form of SQL statements. To restore data, run related commands (such as source) to extract SQL statements from backup files and run them again in the database.

Examples are as follows:

mysqldump -A -B --single-transaction >/server/backup/mysql_$(date +%F).sqlCopy the code

Generally, backup is compressed to save disk space, as shown below

Mysqldump -a -b --single-transaction | gzip>/server/backup/mysql_$(date +%F).sqL.gzCopy the code

Restore operation

cd /server/backup/gzip -o mysql_$(date +%F).sql.gz

mysql -uroot -pMyadmin -h mysqldb.mingongge.com

> source /server/backup/mysql_$(date +%F).sqlCopy the code


Advantages and application scenarios of logical backup

Advantages: simple, easy to operate, with convenient tools, reliable.

Usage scenario: The database can be used when the data volume is small. If the data volume is larger than 20 GB, the backup speed is slow and the database performance may be affected to some extent.

2. Physical backup

Physical backup is to use commands such as cp, tar, and SCP to copy one or more data files stored in a database and store them in other directories for backup.

In this backup mode, data will be written to the database during backup, which may cause data loss to some extent. During data restoration, ensure that the directory path, version, and configuration of the newly installed data are highly consistent with that of the original data. Otherwise, problems may occur.

Therefore, this type of physical backup, which often needs to be performed in an down state, is generally not desirable for actual production databases. Therefore, this method is more suitable for database physical migration, this method is more efficient in this scenario.

Advantages and application scenarios of physical backup

Advantages: Fast speed, high efficiency.

Scenario: Used for maintenance and database physical migration.

In the actual production environment, the specific method to be used depends on requirements and application scenarios.

Overview of full and incremental backups

After introducing the full mode, let’s introduce the concepts of incremental and full backup.

What is full backup?

Full backup: A full backup of all data in a database, or in a specific database, at one time.


Back up all data in the database

Mysqldump -a -b --single-transaction | gzip>/server/backup/All_data_$(date +%F).sqL.gzCopy the code

Back up data for a library

mysqldump -A -B --single-transaction testDB1 | gzip > / server/backup /testDB1_$(date +%F).sql.gzCopy the code

What is incremental backup?

Incremental backup: Backs up the updated or added data in the database between the last full backup and the next full backup.

Note: Full backup is a file, and incremental backup is the MySQL binlog file. So incremental backup is often referred to as backing up binlog log files.


What’s the difference?

Full backup: Requires a long backup time and a short recovery time, because the number of files is small and maintenance is convenient. However, a full backup takes up a certain amount of disk space, which affects the performance of the database (that is, the backup is performed at 0:00). Because of the large number of files, it is not convenient for the server to save too many files locally. Full backup files of important services may need to be manually downloaded or migrated to a storage space other than the server.

Incremental backup: The backup is simple, but the recovery is complicated. Because the number of binlog files is large, all binlog files need to be parsed into SQL statements, as follows:

mysqlbinlog testDB1-bin.000001 testDB1-bin.000002 >./bin.sqlCopy the code

Then, you can restore it by restoring it

mysql -uroot -pMyadmin -h mysqldb.mingongge.com

> source /server/backup/bin.sqlCopy the code

Or do the following

cd /server/backup

mysql testDB1 <./bin.sqlCopy the code


Backup and restore operations

Scripts and scheduled tasks are used to back up the Mysql database.

Common execution policies are:

  • A full backup is performed at 0:00 every day

  • Perform incremental backup based on service requirements

Share an example of a backup solution I worked on in the early days of a startup

Aliyun database server backup scheme

Solution a:

Currently, the primary and secondary databases are synchronized, and the secondary database enables the binlog function for remote backup. For the current data volume, you only need to perform periodic full and incremental backup on the basis of the secondary database.

1, create a backup directory mkdir/server/database backup, backup, 2 to the specified directory mysqldump - single ws-transaction - F - B phoenix_coupon_production | gzip >/server/backup/phoenix_$(date +%F).sql.gz mysqldump --single-transaction -F -B ywotx|gzip > / server/backup/ywotx_ $(date + % F.) SQL. Gz find/server/backup / -type F - name *.sql. Gz - mtime + 7 | xargs rm-f3. Periodically back up binary files. Refresh binlog to generate new files, judge whether files are old or new by script, and then back up old log files mysqladmin -uroot -pywotx! 123 flush-logs# Refresh logs to generate new log filesCopy the code

Eventually, backup files are synchronized or manually downloaded to the remote backup server for remote storage to implement double backup storage of database backup files and prevent server hardware failures.

Scheme 2

When the amount of data increases in the later period, the database needs to be separated from read and write to realize the architecture of master write, slave read, and master/slave synchronization. Backup is still carried out according to the original backup scheme. Data can be backed up by separate databases and tables to prevent the problem of recovery time caused by a large amount of data and improve recovery efficiency.


Create backup directory Mkdir /server/backup#/bin/sh
#create by mingongge at 2017-06-01BACKUPDIR=/server/backup DATE= 'DATE +%F' USER=root PASSWD= "123456" CMD= "mysql -u"$USER-p$PASSWD"DUMPCMD =" mysqldump - u$USER-p$PASSWD- single ws-transaction - F"for dbname in `${CMD}E "show the databases" | sed 1 d `doThe mkdir -p${BACKUPDIR}/${dbname}
for tablename in`${CMD}- D${dbname}E "show tables" | sed 1 d `do
${DUMPCMD} --tables${dbname} ${tablename} |gzip > ${BACKUPDIR}/${dbname}/${tablename}_$(DATE).sql.gz
done
done

find /server/backup/${dbname}-type f - name *.sql. Gz - mtime + 7 | xargs rm-f3. Periodically backup binary files (incremental) Backup method is the same as scheme 1Copy the code

Backup frequency:

  • The database is fully prepared at 0:00 every day

  • Incremental backup is performed every day at 03:00 9:00 15:00 21:00

When the database is fully backed up once a day, binlog logs are updated and new log files are generated. Therefore, in the next incremental backup, binlog logs are refreshed and new log files are generated to implement incremental backup of database operations after full backup. Once data problems are found, Immediately refresh the binlog to create a new log file, manually back up the original log file, and then find out the point where the data problem occurred, so as to use the log file to restore the data between the point where the data problem occurred, and then recover the data between the point where the problem occurred and the time period when the problem was discovered.

Add a backup server and configure it as follows:

Example configuration: 2-core /4G/40G + 200G Efficient cloud disk classic network 1M 295 YUAN/month

Program Summary:

For local database server data backup files basically keep only one week time, backup server on demand (usually reserved for at least 30 days), 30 days keep data including database complete file with the incremental backup files, later period can be modified according to the actual production needs, keep length will only add to the corresponding server disk space, Increase certain cost, other need not change, the operation is more flexible, convenient.

If you need a full set of backup programs, you can reply “backup” to obtain the download address of the full set of programs in the background of the public number of the road of technology. Interested readers can try more keywords: “project, MySQL, escape.”