Mysqldump+binlog Backup for MySQL

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational databases, and MySQL is the best rdbm for Web applications .S(relational databases Management system, relational database management systems) application software.

The most common standardized language for database . MySQL Software uses a dual licensing policy, divided into Community edition and commercial version, due to its small size, speed, low total cost of ownership, especially Open source This feature, the general development of small and medium-sized web sites have chosen MySQL as the site database.  

Thanks to the performance of its community edition, PHP and Apache make a good development environment.

Self-perception The survival of the database represents the lifeblood of a company, and the database GG (game over) is estimated to be about the same company. So the data backup is especially important. There are three kinds of backup of database at present; Hot standby, cold, Win Bei.

Three types of backup methods:

1. hot- Standby: The database can be read and writable in the case of backup, no impact

2. Cold: Backup In the event that the database is stopped

3. Win Bei: Back up the #需要一个全局读锁来保持数据的一致性 if the database is not writable.

Backup forms are divided into two types: physical and logical backups.

Physical backup: Copying data files directly

Pros: No additional tools required, direct copy recovery directly copy files can be

Cons: Related to storage engines, with weak cross-platform capabilities

Logical backup: A backup to export data saved from the database

Advantages: Can be processed using the editor, recovery is simple, can be based on network recovery, help to avoid data corruption

Disadvantage: Backup file is large, backup is slow, can not guarantee the precision of floating-point number, after using logical backup data, it is necessary to rebuild indexes manually, which consumes CPU resources.

The backup type of the data is roughly three kinds: 1. Full backup # Back up the entire database a copy of 2. Incremental backup # Only some of the added data is backed up 3. Differential backup

There are three types of backup methods commonly used: they are usually made up of a combination of two types.

1.mysqldump+binlog #完全备份加上增量备份

2.xtrabackup

3.LVM Snapshot + Incremental backup

Now implement an enterprise often with backup methods, mysqldump+binlog!

This backup needs to be implemented. The binary log function is opened and configured within the/ETC/MY.CNF file.

Specifies the file name (the. log suffix is omitted to have a bin.000001 under/var/lib/mysql/)

Mysqldump is the MARIADB command Binlog is a binary backup method, which forms a complete backup. What the hell do we do, let's take a look

1. First create a directory dedicated to log backups: #我会创建库表代表数据!

2. Make a full backup: #相关mysqldump命令可以mysqldump--help to view

#mysqldump--all-database all Library--locak-all-tables lock table (prevent write)

3. Now insert data into the database, representing the increment! Show master status; View position value (this value can also be used as a master-slave copy)

Numeric value after data is inserted

4. Specify the files specified when the binary log is turned on (each flush logs a binary log re-record a new file) so now is the bin.000010 file, show Master status, and the currently used file name.

#这时候差不多就完成了!!! But!!! In general, the database is in use suddenly interrupted or service shut down, at that time you are not possible to do backup, so the last time to do backup to the time of the database hangs the data is not backed up! Now insert the data to delete the database, simulate the situation.

Because the contents of the database are in a file or directory in the form of mysql/, directly shuju This database is deleted, then Shuju this database does not exist.

5. The most important thing at this time is to protect the last data that is not backed up. (That is, the data that was added after the last incremental backup, the binary log file still exists)

First show master status, #查看这时候的position值, and then execute the mysqlbinlog command from the last 436 to the value of this view backup. I won't be able to see the value.

6. At this time to find a machine without data to recover all the data, it completed the recovery data.

Before the data is recovered

The data for the first insert, the second increment, and the third increment are restored.

Binary logs are especially important, so data protection is a matter of binary logging.

Mysqldump+binlog Backup for MySQL