MYSQL automatic backup policy selection and practices

MYSQL automatic backup policy selection and Practice (transfer) currently popular backup methods: 1. Logical backup: use mysql's mysqldump tool for backup. Back up SQL files. Advantage: the biggest advantage is that it can work with the running mysql automatically. During the running period, it can ensure that the backup is the current point, and it will automatically

MYSQL automatic backup policy selection and Practice (transfer) currently popular backup methods: 1. Logical backup: use mysql's mysqldump tool for backup. Back up SQL files. Advantage: the biggest advantage is that it can work with the running mysql automatically. During the running period, it can ensure that the backup is the current point, and it will automatically

MYSQL automatic backup policy selection and practices ()

There are several popular backup methods:

1,Logical backup:Use the mysqldump tool that comes with mysql to back up data. Back up SQL files.
Advantage: it can work with running mysql automatically,
During the running period, you can ensure that the backup is the current point. It will automatically lock the corresponding operation table and cannot be modified by other users (only accessible ). The modification operation may be blocked. SQL files can be easily transplanted.

Disadvantage: the backup speed is slow. If the data volume is large. It takes a lot of time. If the database server is in the service status provided to the user, during this long operation, it means to lock the table (generally, it is read-locked and only supports reading and writing data ). Then the service will be affected.


Note: the so-called automatic collaboration with the mysql server actually refers to adding parameters to control the mysql server. For example, locking all tables can only be read and cannot be written.

-- Lock-all-tables


2,Physical backup:Directly copy the mysql data directory.

Direct Copy is only applicable to tables of the myisam type. This type of table is independent from the machine. However, you cannot use myisam tables when designing databases. You cannot: Because the myisam table is independent of the machine and is easy to transplant, you can choose this table. This is not the reason for choosing it.

Disadvantage: you cannot operate on the running mysql Server (during the copy process, users can access and update data through applications, so that the current data cannot be backed up) it may be unable to be transplanted to other machines.

In more cases, you will select the table type based on the business characteristics (for example, innodb must be used to support the transaction mechanism), query speed and service performance.

Make sure the table is not in use.
If the server changes a table when you are copying it (a user is performing update and insert operations, the copied data loses the meaning of the backup (it cannot be restored to the current point accurately ).

If the database table is modified during the file system backup process, it enters the inconsistent state of the file subject of the backup table, and it will be meaningless for future recovery tables.

The best way to ensure your copy integrity is to close the server, copy files, and restart the server.
Or, you need to lock the corresponding table (causing access problems to the front-end users ).

?

Why is it not portable to directly copy files?

Mysqldump generates text files that can be transplanted to other machines or even machines with different hardware structures. Directly copying files cannot be transplanted to other machines, unless the table to be copied uses the MyISAM storage format. An ISAM table can only be copied between machines with the same hardware structure. For example, copying files from s parc's Solaris machine to Intel's Solaris machine (or vice versa) won't work. The MyISAM Table Storage Format introduced by MySQL3.23 solves this problem because it is independent from the machine. Therefore, if both of the following conditions are met, You can directly copy the file to a machine with different hardware structures: that is, the version of MySQL3.23 or later must be run on the other machine, and the file must be expressed as a MyISAM table instead of an ISAM table.

?

3,Dual-machine hot backup.

Mysql databases do not have an incremental backup mechanism. Backup is a big problem when the data volume is too large. Fortunately, the mysql database provides a master-slave backup mechanism (that is, dual-machine Hot Backup)
Advantage: suitable for large data volumes. Now I understand. Large Internet companies use heat engines to back up mysql Data. Create multiple database servers for master-slave replication.

The common problem with master-slave replication is to ensure that data is not congested and there is no delay. This problem can still be tolerated and some solutions can be improved. After all, there are gains and losses. This is a very effort-saving method.

?

========================================================== ==========

?

What kind of backup policy should I use currently:

Physical backup and fast recovery, of course, should be stored on a machine. Should I use physical backup or logical backup?
The platform will be migrated in the future. To ensure versatility. I can tolerate the gap between the recovery speed of about 1 minute (SQL file backup mode, the recovery speed is not physically fast, physical backup can be directly copied to overwrite the original file ). Therefore, I prefer logical backup for cross-platform purposes. SQL file format.

There are currently no multiple hardware backup methods. Limited technical staff and labor-intensive maintenance are required. So it is excluded.

?

?

Solution:
1. Overall policy: Write a scheduled task. Automatic backup at night or early morning (considering that the database server cannot be stopped)
After the code is successfully backed up, delete the previous one. Avoid occupying a large amount of data on the disk.

2. Considering that the initial data volume is so small. Use mysqldump for backup. Set automatic backup at a.m. (AM when there is basically no one to access.


3. Logical backup: I can tolerate the gap between recovery speed and 1 minute. Therefore, I prefer logical backup for cross-platform purposes. SQL file format.

4. Back up data every day. Because mysqldump was locked in the early morning to access the database server. It has almost no impact on the server. Therefore, you can back up data every day. An SQL file is generated every day. There will be a lot of files.
Therefore, after each successful backup. Delete previous files. Retain the backup SQL file for the last week.


Backup Tool Path:/usr/bin/mysqldump
Backup data Storage path:/data/backdata/

?

5. Writing backup scripts

Ideas:

5.1 call mysqldump in the shell script to generate a backup file (this tool can generate an SQL file to the disk)

5.2. Each backup record is logged. When the backup operation is performed and the file name is generated. In this way, you can check whether the backup is successful in the future.

Deleted files are also recorded as log information.

5.3? Let the crontab process in linux call the script for execution.

Command: crontab-e

Add the code to the open file: 0 05 *** script path/mysqlback. sh

?

?

?

Content of mysqlback. sh:

?

#/Bin/bash
DB_NAME = "****"
DB_USER = "****"
DB_PASS = "****"
BIN_DIR = "/usr/bin"
BACK_DIR = "/data/backdata"
DATE = "mysql-'date + '% Y % m % d-% H: % M: % S ''"
LogFile = "$ BACK_DIR"/dbbakup. log # directory for saving log records
BackNewFile = $ DATE. SQL

$ BIN_DIR/mysqldump -- opt -- force-u $ DB_USER? -P $ DB_PASS $ DB_NAME> $ BACK_DIR/$ DATE. SQL


Echo ------------------------- "$ (date +" % y-% m-% d % H: % M: % S ")" ------------------- >>> $ LogFile?



Echo? CreateFile: "$ BackNewFile" >>$ LogFile


# Find "/data/backdata/"-cmin + 1-type f-name "*. SQL"-print> deleted.txt

Find "/data/backdata/"-ctime + 7-type f-name "*. SQL"-print> deleted.txt

Echo-e "delete files: \ n" >>$ LogFile?

# Delete matched files cyclically
Cat deleted.txt | while read LINE
Do
??? Rm-rf $ LINE
?? ? Echo $ LINE> $ LogFile
Done


Echo "-----------------------------------------------------------------"> $ LogFile