Mysql backup and recovery

Brief description of the number of MYSQL backups:
Database logical backup \ physical backup
Physical backup is divided into cold backup and Hot Backup

A. Directly copy the data file to A safe place for storage.
B. Use MYSQLHOSTCOPY for data sharding
C. Use MYSQLDUMP to back up data
D. use MYSQL synchronous replication to implement real-time data synchronization and backup.

Common logical backup mainly involves two types: one is to generate the data into an insert statement that can completely reproduce the data in the current database, and the other is to use the logical backup software, split the data in the database table with a specific separator and record it in the text.

For the first insert statement generation, we can directly use mysqldump, a mysql tool. This method may cause data inconsistency or incomplete. Solution: one is to add a write lock to the database system to only provide database query services. The other is for the storage engine that supports transactions, innodb bdb can control the entire backup process in a transaction to ensure the consistency and integrity of the backup data, without affecting the normal operation of the database.
The recovery method is directly run through mysql <backup. SQL.
Second, directly generate the data format. The occupied space is small and the data format is clear. But there is no database structure script. Not easy to control
Implementation Method: run the select *** to outfile from *** command. The restoration method is implemented by running the load data infile and mysqlimport commands.

This process is quite complex and requires real-time recovery testing to ensure that backup data is available.
The physical backup of the database mainly targets physical data files, log files, and configuration files of the database.

What are physical data files? First, there are 6 categories of Log files: error Log, binary log, update log, query log, slow query Log, and innodb redo log. Second, data files? For myisam, The. frm table structure information. myd data information. The index information of myi data. For Innodb, The. ibd file (exclusive tablespace) and. ibdata (shared tablespace) files. What is the replication file? Master.info is stored in the data directory of the slave end and stores information about the slave and master, relay log and relay log index mainly store the binary log information read by the I/O process from the Master, then, the SQL thread on the slave side reads the parsed log information from the binary log and converts it to the query statement that the master can execute. Index indicates the path where binarylog is stored, that is, the directory file. 4. system files? For example, my. the cnf and pid files are a process file in the mysqld application that stores its own process id and socket files that are only available in linux. You can directly connect to mysql without using the TCP/IP network protocol.
For cold backup, copy all the data files and log files directly to the storage location of the Backup set,
The Hot Standby method has different solutions for a small number of databases.For the myisam storage engine, the method is to lock the database table to prevent write operations. You can directly copy physical files or use mysql's dedicated mysqlhotcopy (the principle is to lock the table now, then perform the operation) program to complete the corresponding backup task. Flush tables with read lockcp-R test/tmp/backup/testunlock tables;
For the innodb database engine, there is a commercial software ibbackup, online physical backup function. There is also an open-source tool xtrabackup. If the INNODB data file is backed up during the backup process, the entire database will be locked and non-transaction engine data and such as MYISAM will be copied. frm; so if you have a large number of MYISAM tables, the lock database will continue for a long time. If you are running on the master database, pay attention to it.
Likewise, you can only use xtrabackup for Incremental backup. In fact, this tool only backs up innobd log information.

4 xtrabackup/ibbackup
Xtrabackup -- backup -- datadir =/var/lib/mysql/-- target-
Dir =/data/backups/mysql/

Xtrabackup -- backup -- defaults-file =/etc/my. cnf -- target-
Dir =/data/backups/mysql/
Mysql backup method

1. mysqldump
Low Efficiency, slow backup and restoration, and any data insertion and update operations will be suspended

2. mysqlhotcopy
Mysqlhotcopy is used to back up the myisam data table. During the backup process, any data insertion and update operations will be suspended.

3. Prepare a slave server for dedicated backup (master-slave mode)

4. xtrabackup is an open-source project of percona. It supports hot backup of innodb, XtraDB, and MyISAM (locks the table)

Xtrabackup has two main tools: xtrabackup and innobackupex.
Xtrabackup can only back up InnoDB and XtraDB data tables, but cannot back up MyISAM Data Tables
The innobackupex-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so innodb and myisam can be backed up at the same time, but a read lock is required when processing myisam
Ø xtra backup Principle
Remember the LSN at the beginning, and then start copying the backup file.
Line A background process to monitor the redo log and copy the changes
Xtrabackup_logfile.

Innobackupex can back up myisam tables and frm files. When
Execute flush tables with read lock after xtrabackup ends to avoid
Free of data changes, and then refresh all myisam tables to the disk. Copy and backup
Release the lock.

Mysqlbinlog is also a recovery tool that processes binary files based on time points.

Ø backup: Copy Data Directly

Restore:
Ü restore by Time: mysqlbinlog -- stop-date = "9:59:59"
/Var/log/mysql/bin.123456 | mysql-u root-pmypwd

Mysqlbinlog -- start-date = "10:01:00"
/Var/log/mysql/bin.123456 | mysql-u root-pmypwd

Ø log point Restoration:
Mysqlbinlog -- stop-position = "368312"/var/log/mysql/bin.123456 \
| Mysql-u root-pmypwd
Mysqlbinlog -- start-position = "368315"/var/log/mysql/bin.123456 \
| Mysql-u root-pmypwd \

LVM is short for Logical Volume Manager (Logical Volume management). It is a mechanism for managing disk partitions in Linux.
LSN definition:

Log No.
The log Sn (LSN) identifies the location where a specific log file is recorded in the log file.

LSN is used by many components in DB2 to maintain database consistency and integrity. In addition to other functions, LSN also plays an important role in implementing and performing rollback operations, crash and Rollback Recovery in the partitioned database environment, and synchronizing database operations.

The growth rate of LSN in log files is directly related to database activities. That is to say, as the transaction occurs and the entries are written into the log file, the LSN increases. The more activities in the database, the faster the LSN grows.

Maximum log number

In DB2 V9.5 and earlier versions, the log serial number (LSN) is a 6-byte number. Starting from FP3, The LSN ranges from 0x0000 0000 0000 (when a database is created for the first time) to 0 xFFFF 0000 0000 (about 256 bytes. Before FP3, the upper limit is 0 xFFFF FFFF. As records are added to log files, the LSN continues to grow in the database life cycle.