mysql backup and restore details

How many kinds of MYSQL backup are there? Please briefly describe:
Logical database backup \ physical backup
Physical backup and cold standby and hot standby

A. Directly copy the data file to a safe place for saving
Prepare score notes using MYSQLHOSTCOPY
C. Back up data using MYSQLDUMP
D. Use the synchronous copy of MYSQL to realize real-time data synchronous backup

There are two types of logical backup in common use: one is to generate data into insert statements that can completely reproduce the data in the current database; the other is to record the data of the database table in text with a specific separator through the logical backup software.

For the first generation of insert statements we can do this directly using mysqldump, a tool that comes with mysql. The downside of this approach is that it can lead to data that is not identical, or incomplete. Solution: 1 is to add write lock in the database system, only to provide database query service; Second, for the storage engine that supports transactions, INNODB BDB can control the entire backup process in one transaction to achieve the integrity and integrity of the backup data. And can not affect the normal operation of the database.
The recovery method is mysql < backup.sql runs directly.
The second directly generates the data format. Small footprint, clear data format. But there are no scripts for database structure. Not easy to control
Implementation: select******* to outfile from*** command. The way to recover is through 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 database backup, the main object is the database's physical data files, log files and configuration files.

What are the physical data files?
1 is the six categories of log files: error log error Log, 2-base log binary Log, update log update log, query log query log, slow query log slow query log, innodb redo log. 2 is a data file? For myisam,.frm table structure information.myd data information.myi data index information. For Innodb,.ibd (exclusive table space) and.ibdata (Shared table space) files. 3 is the replication file? master.info stores slave and master information in the data directory of slave terminal, relay log and relay log index mainly store binary log information read by I/O process from Master terminal, and then the SQL thread of slave terminal reads the parsed log information from binary log. Convert to query statements that master can execute. index is the directory where binarylog is located. 4 is the system file? For example, my.cnf and pid files are a process file in mysqld application to store its own process id and socket files, which are only available under linux. mysql can be connected directly without the network protocol tcp/ip In case of cold backup, copy all the data files and log files directly to the place where the backup set is stored. The hot standby method has different schemes for a few databases For the myisam storage engine, the solution is to lock the database tables to prevent write operations, either by copying the physical files directly, or by using mysql's specialized mysqlhotcopy program, which locks the tables and then operates them. flush tables with read lock cp -R test /tmp/backup/test unlock tables; For innodb database engine, there is a business software ibbackup, online physical backup function. There is also an open source tool called xtrabackup, If during the backup process, after the backup of INNODB data files is completed, the entire library will be locked and the data of non-transaction engines such as MYISAM and.frm will be copied. So if you have more MYISAM tables, the lock will last a long time. If you're running on the main library, be careful. Also, incremental backups can only be done via xtrabackup, which is a tool that simply backs up the log information of innobd.

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/
Backup mode of mysql

1.mysqldump
It is inefficient, backup and restore are slow, and any data insertion and update operations are suspended

2.mysqlhotcopy
mysqlhotcopy is dedicated to the backup of myisam data tables. During the backup, any data insertion and update operations will be suspended

3. Prepare 1 slave server for backup (master-slave)

4.xtrabackup is an open source project of percona, hot backup innodb,XtraDB,MyISAM(can lock tables)

Xtrabackup has two main tools: xtrabackup and innobackupex
xtrabackup can only backup InnoDB and XtraDB data tables, but not MyISAM data tables
innobackupex-1.5.1 encapsulates xtrabackup, which is a script encapsulation, so it can backup innodb and myisam at the same time, but it needs to add a read lock when processing myisam
& Oslash; xtra backup principle
Remember the LSN number at the beginning, then start copying the file and shipping it at the same time
Line 1 background process monitors the redo log and copies the changes down
xtrabackup_logfile.

innobackupex can backup myisam tables and frm files. when
When xtrabackup is finished, flush tables with read lock, avoid
No data changes, then flush all myisam tables to disk. Copies"
After the beam, release the lock.

mysqlbinlog is also a recovery tool that processes base 2 files based on point in time

& Oslash; Backup: copy directly

& Oslash; Restore:
U time point 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

& Oslash; 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 short for Logical Volume Manager (logical volume management), which is a mechanism for managing disk partitions in the Linux environment.
LSN definition:

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

LSN by DB2 & reg; Many components in the product are used to maintain the integrity and integrity of database 1. Among other things, LSN is important for implementation and rollback operations, crash and rollforward recovery, and synchronization of database operations in a partitioned database environment.

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

Upper limit of log sequence number

In DB2 V9.5 and earlier, the log sequence number (LSN) is a six-byte number. Starting with FP3, LSN ranges from 0x0000, 0000, 0000 (when the database was first created) to 0xFFFF, 0000, 0000 (approximately 256 terabytes). Before FP3, the upper limit is 0xFFFF FFFF FFFF. As records are added to log files, LSN grows over the lifetime of the database.