Directory: one, incremental backup and recovery
Second, MySQL master-slave synchronization
One incremental backup with incremental recovery:
1 Enable Binlog log files for MySQL services for real-time incremental backups of data
(1) Binlog Log Introduction
(2) Enable Binlog log
(3) manually generate a new Binlog log file
(4) Delete existing binlog log files
(5) Recovering data using Binlog logs
(1) Binlog Log Introduction:
One of the MySQL database service log files that records user connections
After the database server, execute an SQL command other than the query.
(2) Enable Binlog log:
1,VIM/ETC/MY.CNF (Configuration Master profile)
[Mysqld]
server_id=51 //define host number
Log_bin //start Binlog log
binlog_format="mixed"//define Logging format
Record format: statement: Record SQL command for each modified data
Row: Does not log the SQL statement security context and only saves which record is modified
Mixed: mix of both, record SQL commands and record security context, and modified records
2, Systemctl restart mysqld (restart service)
Default storage location for log files:
cd/var/lib/mysql/
Host name-bin.index log index file (records the current binlog log file name)
You can view it with cat
Hostname-bin. log file for the SQL command (the binary execution code is stored inside)
How to view:
mysqlbinlog [options] mysql51-bin.000001
Mysqlbinlog mysql51-bin.000001 | Grep-i Insert
Options are:
--start-datetime= "Yyyy-mm-dd hh:mm:ss"//Start date Time
--stop-datetime= "Yyyy-mm-dd hh:mm:ss"//End Date Time
--start-position= Digital--stop-position= Digital
Define the log file name storage location:
1,mkdir/logdir; Chown MYSQL/LOGIDR
2,vim/etc/my.cnf
[Mysqld]
server_id=51
log_bin="/logdir/hcy"
binlog_format= "Mixed"
3,systemctl Restart Mysqld
Note: Once configured, the original log file is invalid, and the newly generated command is present under/logdir/.
(3) manually generate a new Binlog log file
Why should I manually generate a new binlog log file?
The system default log file is only larger than 500M to generate a new log file and cannot be
Separate tables for individual libraries to record log files.
1, in the state of landing:
Execute Mysql->flush logs;
2 under the operating interface:
Execute mysql-uroot-p password-e ' flush logs '
Or: mysqldump-uroot-p password--fulsh-logs library name > Xxx.sql
3, restarting the MYSQLD service will also generate a new log file (this is not used in a production environment)
(4) Delete existing binlog log files
Use system commands to delete RM (index file does not synchronize updates)
To delete using the SQL command:
Mysql->reset master; Delete all log files reinitialize log files
Mysql->purge Master logs to "log name"; Delete log file before specified number
(5) command format for recovering data using Binlog logs
Mysqlbinlog "Options" Log file name | Mysql-uroot-p Password
Options for specifying offsets:
--start-position= Digital--stop-position= Digital
Options for specifying the time:
--start-datetime= "Yyyy-mm-dd hh:mm:ss"//Start date Time
--stop-datetime= "Yyyy-mm-dd hh:mm:ss"//End Date Time
2, using 3rd party software to provide commands Innobackupex
1, find the resources to install the package:
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
RPM-UVH view the dependent packages and install the dependent packages to install the main package.
Perl-dbd-mysql PERL-DIGEST-MD5
libev-4.15-1.el6.rf.x86_64.rpm
Rpm-qa | grep Percona//List package name
RPM-QL percona-xtrabackup-24//list the files provided by the package
/usr/bin/innobackupex (Integrated xtrabackup supports InnoDB and xtradb and MyISAM)
/usr/bin/xtrabackup ( can only back up InnoDB, and xtradb)
2. Use the Innobackupex command to make a backup request?
(1) Ensure that all libraries in the database, the storage engine is InnoDB.
(2) To ensure that only the initial configuration of the main directory 3 libraries
(3) for the first time full backup, back up 3 main libraries
The above operation is a must, mainly for the software to build the environment.
Syntax format of the 3,innobackupex command
Innobackupex < options >
--user user--password password--databases database Name list
--incremental Directory name--incremental-basedir directory name
Innobackupex--user root--password 123456--databases= "
MySQL performance_schema sys gamedb "backup directory name--no-timestamp (full backup)
4, incremental backup
Innobackupex--user root--password 123456--databases= "
MySQL performance_schema sys gamedb "backup directory name--incremental directory name
--incremental-basedir= directory Name of the last backup data store (incremental backup)
Note: The directory specified for the backup must be empty.
5, Full recovery
Innobackupex--user root--password 123456--databases= "
MySQL performance_schema sys gamedb "--apply-log--redo-only full backup directory
Incremental recovery:
Innobackupex--user root--password 123456--databases= "
MySQL performance_schema sys gamedb "--apply-log--redo-only the directory where full backup data is stored
--incremental-basedir= Directory Name
Copy files:
Innobackupex--user root--password 123456--databases= "
MySQL performance_schema sys gamedb "--copy-back stores the directory for full backup data
principle Problem: if one day the main directory of the database is taken out, you can use the recovery log, first restore the full backup directory
In the iterative recovery of incremental catalog data, can not leapfrog recovery. Executes the command--copy-back command after recovering a full backup. Last in Chown-r mysql:mysql mysql (directory)
Backup configuration file Description:
Cat xtrabackup_checkpoints ( log SQL command operation sequence number )
Backup_type = full-backuped//backup type (full backup)
FROM_LSN = 0//log serial number
TO_LSN = 4624642//log serial number
LAST_LSN = 4624651//log serial number
Compact = 0
Recover_binlog_info = 0
Xtrabackup_logfile ( stored record SQL command corresponds to serial number of xtrabackup_checkpoints )
Library Directory: ( data generated after SQL command execution )
Mysql1.5 (Binlog incremental backup and recovery, Innobackupex)