当前位置：Gxlcms > 数据库问题 > 使用binlog日志， XtraBackup备份工具 ，MySQL AB复制

使用binlog日志， XtraBackup备份工具 ，MySQL AB复制

时间：2021-07-01 10:21:17 帮助过：26人阅读

  • [mysqld]
  • .. ..
  • log-bin-index=mysql-bin                             //启用二进制日志，并指定前缀
  • server_id=1
  • binlog_format=STATEMENT //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT
  • .. ..
  • [root@dbsvr1 ~]# systemctl restart mysqld.service

    • 2）确认binlog日志文件

    新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：

    1. [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
    2. /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index

    其中mysql-bin.index文件记录了当前保持的二进制文件列表：

    1. [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
    2. ./mysql-bin.000001

    重启MySQL服务程序，或者执行SQL操作“FLUSH LOGS;”，会生成一份新的日志：

    1. [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
    2. /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index
    3. /var/lib/mysql/mysql-bin.000002
    5. [root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index
    6. ./mysql-bin.000001
    7. ./mysql-bin.000002

    步骤二：利用binlog日志重做数据库操作

    1）执行数据库表添加操作

    创建db1·库tb1表，表结构自定义：

    1. mysql> CREATE DATABASE db1;
    2. Query OK, 1 row affected (0.05 sec)
    4. mysql> USE db1;
    5. Database changed
    6. mysql> CREATE TABLE tb1(
    7. -> id int(4) NOT NULL,name varchar(24)
    8. -> );
    9. Query OK, 0 rows affected (0.28 sec)

    插入3条表记录：

    1. mysql> INSERT INTO tb1 VALUES
    2. -> (1,‘Jack‘),
    3. -> (2,‘Kenthy‘),
    4. -> (3,‘Bob‘);
    5. Query OK, 3 rows affected (0.12 sec)
    6. Records: 3 Duplicates: 0 Warnings: 0

    确认插入的表记录数据：

    1. mysql> SELECT * FROM tb1;
    2. +----+--------+
    3. | id | name |
    4. +----+--------+
    5. | 1 | Jack |
    6. | 2 | Kenthy |
    7. | 3 | Bob |
    8. +----+--------+
    9. 3 rows in set (0.00 sec)

    2）删除前一步添加的3条表记录

    执行删除所有表记录操作：

    1. mysql> DELETE FROM tb1;
    2. Query OK, 3 rows affected (0.09 sec)

    确认删除结果：

    1. mysql> SELECT * FROM tb1;
    2. Empty set (0.00 sec)

    步骤三：通过binlog日志恢复表记录

    binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。

    根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

    1）查看mysql-bin.000002日志内容

    1. [root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
    2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    4. DELIMITER /*!*/;
    5. # at 4
    6. #170412 12:05:32 server id 1 end_log_pos 123 CRC32 0x6d8c069c Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup
    7. # Warning: this binlog is either in use or was not closed properly.
    8. ROLLBACK/*!*/;
    9. BINLOG ‘
    10. jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    11. AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    12. AZwGjG0=
    13. ‘/*!*/;
    14. # at 123
    15. #170412 12:05:32 server id 1 end_log_pos 154 CRC32 0x17f50164 Previous-GTIDs
    16. # [empty]
    17. # at 154
    18. #170412 12:05:59 server id 1 end_log_pos 219 CRC32 0x4ba5a976 Anonymous_GTID last_committed=0 sequence_number=1
    19. SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
    20. # at 219
    21. #170412 12:05:59 server id 1 end_log_pos 310 CRC32 0x5b66ae13 Query thread_id=3 exec_time=0 error_code=0
    22. SET TIMESTAMP=1491969959/*!*/;
    23. SET @@session.pseudo_thread_id=3/*!*/;
    24. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    25. SET @@session.sql_mode=1436549152/*!*/;
    26. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    27. /*!\C utf8 *//*!*/;
    28. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
    29. SET @@session.lc_time_names=0/*!*/;
    30. SET @@session.collation_database=DEFAULT/*!*/;
    31. CREATE DATABASE db1
    32. /*!*/;
    33. # at 310
    34. #170412 12:06:23 server id 1 end_log_pos 375 CRC32 0x2967cc28 Anonymous_GTID last_committed=1 sequence_number=2
    35. SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
    36. # at 375
    37. #170412 12:06:23 server id 1 end_log_pos 502 CRC32 0x5de09aae Query thread_id=3 exec_time=0 error_code=0
    38. use `db1`/*!*/;
    39. SET TIMESTAMP=1491969983/*!*/;
    40. CREATE TABLE tb1(
    41. id int(4) NOT NULL,name varchar(24)
    42. )
    43. /*!*/;
    44. # at 502
    45. #170412 12:06:55 server id 1 end_log_pos 567 CRC32 0x0b8cd418 Anonymous_GTID last_committed=2 sequence_number=3
    46. SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
    47. # at 567
    48. #170412 12:06:55 server id 1 end_log_pos 644 CRC32 0x7e8f2fa0 Query thread_id=3 exec_time=0 error_code=0
    49. SET TIMESTAMP=1491970015/*!*/;
    50. BEGIN
    51. /*!*/;
    52. # at 644
    53. #170412 12:06:55 server id 1 end_log_pos 772 CRC32 0x4e3f728e Query thread_id=3 exec_time=0 error_code=0 //插入表记录的起始时间点
    54. SET TIMESTAMP=1491970015/*!*/;
    55. INSERT INTO tb1 VALUES(1,‘Jack‘),(2,‘Kenthy‘), (3,‘Bob‘)
    56. /*!*/;
    57. # at 772
    58. #170412 12:06:55 server id 1 end_log_pos 803 CRC32 0x6138b21f Xid = 10
    59. //确认事务的时间点
    60. COMMIT/*!*/;
    61. # at 803
    62. #170412 12:07:24 server id 1 end_log_pos 868 CRC32 0xbef3f472 Anonymous_GTID last_committed=3 sequence_number=4
    63. SET @@SESSION.GTID_NEXT= ‘ANONYMOUS‘/*!*/;
    64. # at 868
    65. #170412 12:07:24 server id 1 end_log_pos 945 CRC32 0x5684e92c Query thread_id=3 exec_time=0 error_code=0
    66. SET TIMESTAMP=1491970044/*!*/;
    67. BEGIN
    68. /*!*/;
    69. # at 945
    70. #170412 12:07:24 server id 1 end_log_pos 1032 CRC32 0x4c1c75fc Query thread_id=3 exec_time=0 error_code=0 //删除表记录的时间点
    71. SET TIMESTAMP=1491970044/*!*/;
    72. DELETE FROM tb1
    73. /*!*/;
    74. # at 1032
    75. #170412 12:07:24 server id 1 end_log_pos 1063 CRC32 0xccf549b2 Xid = 12
    76. COMMIT/*!*/;
    77. SET @@SESSION.GTID_NEXT= ‘AUTOMATIC‘ /* added by mysqlbinlog */ /*!*/;
    78. DELIMITER ;
    79. # End of log file
    80. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    81. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    2） 执行指定Pos节点范围内的sql命令恢复数据

    根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：

    1. [root@dbsvr1 ~]# mysqlbinlog \
    2. --start-datetime="2017-04-12 12:06:55" \
    3. --stop-datetime="2017-04-12 12:07:23" \
    4. /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
    5. Enter password:                                  //验证口令

    3）确认恢复结果

    1. mysql> SELECT * FROM db1.tb1;
    2. +----+--------+
    3. | id | name |
    4. +----+--------+
    5. | 1 | Jack |
    6. | 2 | Kenthy |
    7. | 3 | Bob |
    8. +----+--------+
    9. 3 rows in set (0.00 sec)

    2 XtraBackup备份工具

    2.1 问题

    1. 安装XtraBackup软件包。
    2. 使用XtraBackup执行完整备份、增量备份。
    3. 准备数据恢复目录。

    2.2 步骤

    实现此案例需要按照如下步骤进行。

    步骤一：安装XtraBackup软件包

    1）了解软件包描述信息

    1. [root@dbsvr1 pub]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
    2. Name : percona-xtrabackup-24
    3. Version : 2.4.6
    4. Release : 2.el7
    5. Architecture: x86_64
    6. Install Date: (not installed)
    7. Group : Applications/Databases
    8. Size : 32416340
    9. License : GPLv2
    10. Signature : DSA/SHA1, 2017年02月27日 星期一 20时28分17秒, Key ID 1c4cbdcdcd2efd2a
    11. Source RPM : percona-xtrabackup-24-2.4.6-2.el7.src.rpm
    12. Build Date : 2017年02月27日 星期一 20时27分21秒
    13. Build Host : vps-centos7-x64-01.ci.percona.com
    14. Relocations : (not relocatable)
    15. URL : http://www.percona.com/software/percona-xtrabackup
    16. Summary : XtraBackup online backup for MySQL / InnoDB
    17. Description :
    18. Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines

    2）安装依赖包perl-DBD-MySQL perl-Digest-MD5 libev

    使用RHEL 7自带的即可，yum方式安装：

    1. [root@dbsvr1 pub]# yum -y install perl-DBD-MySQL perl-Digest-MD5
    2. libev使用网上找的rpm包 libev-4.15-1.el6.rf.x86_64.rpm //该包由讲师提供
    3. [root@dbsvr1 pub]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm

    如果未安装这些依赖包，则直接安装percona-xtrabackup时会报错：

    代码

    3）安装percona-xtrabackup

    1. [root@dbsvr1 pub]#rpm -ivh percona-xtrabackup-*.rpm
    2. 警告：percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY
    3. 准备中... ################################# [100%]
    4. 正在升级/安装...
    5. 1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%]
    6. 2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%]
    7. 3:percona-xtrabackup-24-debuginfo-2################################# [100%]

    4）确认安装的主要程序/脚本

    1. [root@dbsvr1 pub]# rpm -ql percona-xtrabackup-24-2.4.6-2.el7.x86_64
    2. /usr/bin/innobackupex
    3. /usr/bin/xbcloud
    4. /usr/bin/xbcloud_osenv
    5. /usr/bin/xbcrypt
    6. /usr/bin/xbstream
    7. /usr/bin/xtrabackup
    8. /usr/share/doc/percona-xtrabackup-24-2.4.6
    9. /usr/share/doc/percona-xtrabackup-24-2.4.6/COPYING
    10. /usr/share/man/man1/innobackupex.1.gz
    11. /usr/share/man/man1/xbcrypt.1.gz
    12. /usr/share/man/man1/xbstream.1.gz
    13. /usr/share/man/man1/xtrabackup.1.gz

    步骤二：使用XtraBackup执行数据库备份

    --host 主机名

    --port 3306

    --user 用户名

    --password 密码

    --databases="库名"

    --databases="库1 库2"

    --databases="库.表"

    --no-timestamp 不用日期命名备份文件存储的子目录，使用备份的数据库名做备份目录名

    --no-timestmap 不使用日期命名备份目录名

    1）做一个完整备份

    默认情况下，备份文件存储的子目录会用日期命名，

    innobackupex作为客户端工具，以mysql协议连入mysqld，将数据备份到/backup文件夹：

    1. [root@dbsvr1 ~]# innobackupex --user=root --password=1234567 /backup/mysql –no-timestamp
    2. 170425 11:05:44 innobackupex: Starting the backup operation
    4. IMPORTANT: Please check that the backup run completes successfully.
    5. At the end of a successful backup run innobackupex
    6. prints "completed OK!".
    8. Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    9. 170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    10. Using server version 5.7.17
    11. innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    12. xtrabackup: uses posix_fadvise().
    13. xtrabackup: cd to /var/lib/mysql
    14. xtrabackup: open files limit requested 0, set to 1024
    15. xtrabackup: using the following InnoDB configuration:
    16. xtrabackup: innodb_data_home_dir = .
    17. xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
    18. xtrabackup: innodb_log_group_home_dir = ./
    19. xtrabackup: innodb_log_files_in_group = 2
    20. xtrabackup: innodb_log_file_size = 50331648
    21. InnoDB: Number of pools: 1
    22. 170425 11:05:45 >> log scanned up to (2543893)
    23. xtrabackup: Generating a list of tablespaces
    24. InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
    25. 170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
    26. 170425 11:05:45 [01] ...done
    27. 170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
    28. 170425 11:05:46 [01] ...done
    29. 170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
    30. 170425 11:05:46 [01] ...done
    31. 170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
    32. 170425 11:05:46 [01] ...done
    33. 170425 11:05:46 >> log scanned up to (2543893)
    34. .. ..
    35. 170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
    36. 170425 11:06:00 [01] ...done
    37. 170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
    38. 170425 11:06:00 [01] ...done
    39. 170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
    40. 170425 11:06:00 [01] ...done
    41. 170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
    42. 170425 11:06:00 [01] ...done
    43. 170425 11:06:00 Finished backing up non-InnoDB tables and files
    44. 170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    45. xtrabackup: The latest check point (for incremental): ‘2543884‘
    46. xtrabackup: Stopping log copying thread.
    47. .170425 11:06:00 >> log scanned up to (2543893)
    49. 170425 11:06:00 Executing UNLOCK TABLES
    50. 170425 11:06:00 All tables unlocked
    51. 170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
    52. 170425 11:06:00 [00] ...done
    53. 170425 11:06:00 Backup created in directory ‘/backup/‘
    54. 170425 11:06:00 [00] Writing backup-my.cnf
    55. 170425 11:06:00 [00] ...done
    56. 170425 11:06:00 [00] Writing xtrabackup_info
    57. 170425 11:06:00 [00] ...done
    58. xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
    59. 170425 11:06:01 completed OK

    确认备份好的文件数据：

    1. [root@dbsvr1 ~]#ls /backup/
    2. backup-my.cnf ib_buffer_pool mysql sys xtrabackup_info
    3. db1 ibdata1 performance_schema xtrabackup_checkpoints xtrabackup_logfile

    2）做一个增量备份（基于前一步的完整备份）

    随意做一些新增或更改库表的操作，比如在db1库中新建一个mytb的表：

    1. mysql> USE db1;
    2. Database changed
    3. mysql> CREATE TABLE mytb(id int(4), name varchar(24));
    4. Query OK, 0 rows affected (0.38 sec)
    5. mysql> INSERT INTO tb1 VALUES
    6. -> (1,‘bon‘),
    7. -> (2,‘bo‘),
    8. Query OK, 2 rows affected (0.12 sec)
    9. Records: 2 Duplicates: 0 Warnings: 0
    10. mysql> SELECT * FROM tb1;
    11. +------+------+
    12. | id | name |
    13. +------+------+
    14. | 1 | bob |
    15. | 2 | bo |
    16. +------+------+
    17. 2 rows in set (0.00 sec)

    以前一次保存到/backup的完整备份为基础，做一个增量备份，保存到/incr01/，指定增量备份参照的基本目录（完整备份目录）需要用到选项--incremental-basedir。相关操作如下：

    1. [root@dbsvr1 ~]# innobackupex --user=root --password=12345678 --incremental /incr01 --incremental-basedir=/backup/ --no-timestamp
    2. 170425 11:30:14 innobackupex: Starting the backup operation
    4. IMPORTANT: Please check that the backup run completes successfully.
    5. At the end of a successful backup run innobackupex
    6. prints "completed OK!".
    8. Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
    9. 170425 11:30:14 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
    10. Using server version 5.7.17
    11. innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
    12. incremental backup from 2543884 is enabled.
    13. xtrabackup: uses posix_fadvise().
    14. xtrabackup: cd to /var/lib/mysql
    15. xtrabackup: open files limit requested 0, set to 1024
    16. xtrabackup: using the following InnoDB configuration:
    17. xtrabackup

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！