"Go" MySQL incremental backup recovery actual case

SOURCE Address: http://seanlook.com/2014/12/05/mysql_incremental_backup_example/A small number of databases can be fully backed up every day, as this will not take much time, but when the database is large, it is unlikely that a full backup will take place every day, and an incremental backup can be used.  The principle of incremental backup is to use the MySQL binlog log. The MySQL version of this operation is 5.5.40  forLinux (x86_64). Incremental backup to ensure that binary logs are turned on, refer to the MySQL log system:1Mysql> Show variables like'%log_bin%'; First make a full backup of the PAK database:1$ mysqldump-h localhost-upak-ppwd-p3306--master-data=2--single-transaction--opt Pak >Pak_bak_full.sql This time you will get a fully-prepared file pak_bak_full.sql. The mysqldump operation causes the log to scroll once, assuming that the new Binlog file is MySQL-bin.000002. 1analog Insert data and misoperation a. Insert some data into a table in the Pak library, and then execute the flush logs command. This will result in a new binary log file MySQL-bin.000003, mysql-bin.000002 saves all the changes that have been made after the full preparation, and the operation to add the record is saved in the mysql-the bin.00002. B. Add two records to the T_user table in the Pak library and delete the T_user table by mistake. The actions of adding records in T_user and deleting tables are recorded in MySQL-the bin.000003. 2. Start the recovery process do not log:1MySQL >Set Globalsql_log_bin=0; 3. Import full Data first1  2  3$ mysql-h Localhost-upak-ppwd <pak_bak_full.sql or MySQL> source/path/backup/Pak_bak_full.sql We can also see the binlog position when fully prepared:1  2Head- -Backup-file.sql |grep'Change MASTER'--Change MASTER to master_log_file='mysql-bin.000001', master_log_pos=4321; To view the location in your current binary log:1Mysql>Show master status;  According to the above two position can probably determine which several binlog files need to be fully recovered. 4. Restore Mysql-bin.000002position or point-in-time before recovery, full-ready binlog need to be fully recovered, multiple files separated by spaces1$ mysqlbinlog/var/lib/mysql/mysql-bin.000002| Mysql-uroot-p This time the query can get the first two data. 5. Recover part Mysql-bin.000003This log contains the new record and the two parts of the mis-deleted table, we need to restore to the new record, the location of the previous error delete operation. If you know the command of the misoperation, such as drop table, you can find the position before the misoperation in the Binlog file by using the following method: (as shown in the following information, the POS before the drop table is 775, the datetime141204  the: ,: 04 or POS 882 when the drop table operation is completed)1  2  3  4  5  6  7  8  9$ mysqlbinlog/var/lib/mysql/mysql-bin.000003|grep-c5 'DROP TABLE'  #141204  the: -: toServer ID1End_log_pos775Xid =376COMMIT/*!*/; # at775  #141204  the: ,:GenevaServer ID1End_log_pos882Query thread_id=TenExec_time=0Error_code=0SET TIMESTAMP=1417676884/*!*/; DROP TABLE ' T_user '/*generated by Server*/  /*!*/; # at882Restore command:1$ mysqlbinlog/var/lib/mysql/mysql-bin.000003--stop-position=775| Mysql-h Localhost-uroot-p If position is difficult to determine, but knows the exact (server) time to restore to, you can also use DateTime:1$ mysqlbinlog/var/lib/mysql/mysql-bin.000003--stop-datetime="2014-12-04 15:08:00"| Mysql-uroot-p If the database is migrated instead of the wrong operation, then no position or datetime is required and incremental recovery is done with all binlog files. Remember to turn on logging when you are sure that your recovery is successful: MySQL>Set Globalsql_log_bin=1; Error1. Unknown variable 'default-character-Set=UTF8 ' When using mysqlbinlog to view the binary log, the following error is prompted:2. /usr/local/mysql/bin/mysqlbinlog:unknown variable 'default-character-Set=UTF8 '3. The reason is that in my order to unify the MySQL client-to-server character encoding, theThe/etc/my.cnf file's [client], [mysqld] section joins the default-character-Set=Utf8,mysqlbinlog will read the configuration from [client] in my.cnf, but Mysqlbinlog does not recognize this option (said to be a bug). 4. There are two ways to deal with this bug: First, it's natural to note the character set configuration in [client]; second, use loose-default-character-Set= UTF8. Loose-is added before the option., indicating that this option is skipped when the program does not recognize this option, and a warning is given. 5.

"Go" MySQL incremental backup recovery actual case