Mysql Backup and Recovery detailed _mysql

How many types of MySQL backups are available, please describe briefly:
Database logical backup \ Physical Backup
Physical backup is divided into Lengbei and hot standby

A. Direct copying of data files to a safe place for preservation
B. Use of mysqlhostcopy for data-preparation
C. Backing up data using mysqldump
D. Use MySQL synchronous replication to achieve real-time data synchronization backup

There are two main types of logical backups commonly used: one is to make the data into an INSERT statement that can completely reproduce the data in the current database, and the other is to use the data in logical backup software to separate the data from the database table into the text after it has been separated by a specific delimiter.

For the first generation of INSERT statements, we can do this directly using the tool mysqldump from MySQL. The downside of this approach is that it can lead to inconsistent or incomplete data. Solution: One is to provide database query services by adding write locks to the database system; The second is for the storage engine that supports transactions, INNODB BDB can achieve the consistency and integrity of backup data by controlling the entire backup process in one transaction and can not affect the normal operation of the database.
The recovery method is run directly through the mysql<backup.sql.
The second type directly generates the data format. The space is small, the data format is clear. However, there is no script for database structure. Not easy to control
Implementation: implemented by select******* to outfile from*** command. The recovery method is done by the load data infile and Mysqlimport commands.

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

What are the physical data files?

One is the log file 6 categories: error log errors logs, binary logs binary log, update log updates logs, query log queries logs, slow query log, and InnoDB redo log.

Second, data files? For MyISAM,. FRM table structure information. MYD Data Information-index information for myi data. For InnoDB . ibd files (exclusive tablespace) and. Ibdata (shared table space) files.

Three is replication file? Master.info is stored in the slave-side data directory, storing information about slave and master, relay log and relay log index mainly stores the binary log information that the I/O process reads from the master side. The slave-side SQL thread then reads the parsed log information from the binary log into the query statement that master can execute. Index is the path that holds the Binarylog, which is the directory file.

Four is the system file? such as MY.CNF, PID file is a mysqld application in a process file to store their own process ID and is the socket file it only under Linux, you can not through the TCP/IP network protocol directly connect MySQL

If you are doing Lengbei, copy all the data files and log files directly to the backup set where you store them.

Hot-standby methods have different scenarios for a few databases

For the MyISAM storage engine, the practice is to lock the database table to prevent write operations, you can copy the physical files directly, or you can use the MySQL special mysqlhotcopy (the principle is to lock the table, then operate) program to complete the corresponding backup task.

Flush tables with Read lock

Cp-r Test/tmp/backup/test

Unlock tables;

InnoDB Database engine, there is a commercial software ibbackup, online physical backup function. There is also an open source tool xtrabackup,

If, during the backup process, the backup of the InnoDB data file is completed, the entire library is locked and the data and. frm of the MyISAM engine are replicated, so if you have more MyISAM tables, the lock library will last a long time. If you are running on the main library, be careful.

The same can only be done incrementally through the xtrabackup backup, in fact, this tool is only the backup 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/
How MySQL is backed up

1.mysqldump
Low efficiency, backup and restore speed are slow, any data insert and update operations will be suspended

2.mysqlhotcopy
Mysqlhotcopy is specifically for the MyISAM data table backup, in the process of backup, any data insert and update operations will be suspended

3. Prepare one from the server, the special backup (Master-slave way)

4.xtrabackup is a Percona open source project, hot backup InnoDB, Xtradb,myisam (Will lock the table)

Xtrabackup has two main tools: Xtrabackup, Innobackupex
Xtrabackup can only back up InnoDB and xtradb two types of datasheets, not backup MyISAM data tables
innobackupex-1.5.1 encapsulates Xtrabackup, is a script wrapper, so it can simultaneously back up both InnoDB and MyISAM, but requires a read lock when processing MyISAM
Øxtra Backup principle
Remember the LSN number at the start, and then start to copy the file, while shipping
Line a background Process monitor redo logs and change the copy Benie came to
Xtrabackup_logfile.

Innobackupex can back up MyISAM tables and frm files. When
After the Xtrabackup is finished, execute flush tables with read lock, avoid
Free data changes, and then refresh all MyISAM tables to disk. Copy the knot
After the bundle, release the lock.

Mysqlbinlog is also a recovery tool that handles binary files based on a point in time.

Ø Backup: Direct copy

Ø Restore:
U-point-in-time Restore: Mysqlbinlog--stop-date= "2005-04-20 9:59:59"
/var/log/mysql/bin.123456 | Mysql-u root–pmypwd

Mysqlbinlog--start-date= "2005-04-20 10:01:00"
/var/log/mysql/bin.123456| Mysql-u root–pmypwd

Ø Log Point Restore:
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 a shorthand for Logical Volume Manager (Logical Volume management), a mechanism for managing disk partitions in a Linux environment.
LSN definition:

Log ordinal
The log sequence number (LSN) identifies where a particular log file is recorded in the log file.

LSN is used by many components in the db2® product to maintain database consistency and integrity. Among other things, LSN is important for implementation and rollback operations, crash and roll back recovery, and database operation synchronization in partitioned database environments.

The growth rate of LSN in the log file is directly associated with database activity. That is, the LSN grows as the transaction occurs and the entry is written to the log file. The more activity in the database, the faster the LSN grows.

Upper limit of log ordinal

In DB2 V9.5 and earlier versions, the log sequence number (LSN) is a 6-byte number. Starting with FP3, the LSN ranges from 0x0000 0000 0000 (When the database was first created) to 0xFFFF 0000 0000 (about 256 bytes). Before the FP3, the upper limit is 0xFFFF FFFF FFFF. As the records are added to the log file, the LSN continues to grow during the database lifetime.