025、MySQL日志格式和主从从级联复制

转载

mob604756e58279 2021-07-28 10:18:00

文章标签 mysql sql sed 数据 log日志 文章分类 MySQL 数据库

mysql binlog日志格式

总结格式的优缺点：

statement：历史比较悠久。会记录完整的sql语句。binlog空间比较小，跨库操作丢失数据。针对一些函数uuid，rand() 不安全性（SBR，statement based replication）
row：安全性很高，不会出现丢失数据的情况。binlog空间占用比较大，会记录每行操作的语句。mysql5.6 binlog_rows_query_log_events 开启也可以看到完整sql语句了。在生产中建议使用row这种binlog记录格式（RBR，row based replication）

下面演示statement格式丢失数据。

1、主库修改参数，只记录test库的binlog

[root@localhost ~]# vi /etc/my.cnf
binlog-do-db=test
--binlog-do-db与binlog-ignore-db两个参数互斥
[root@localhost ~]# pkill mysql
[root@localhost ~]# mysqld_safe --defaults-file=/etc/my.cnf &
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000016 |      191 | test         |                  | e1fe2aff-afc8-11eb-a7c9-08002765b4fe:1-597 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)
--可以看到，Binlog_Do_DB有值

2、验证row模式下，跨库更新，不会丢数据

主库执行：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | ROW               |
| date_format              | %Y-%m-%d          |
| datetime_format          | %Y-%m-%d %H:%i:%s |
| default_week_format      | 0                 |
| innodb_file_format       | Antelope          |
| innodb_file_format_check | ON                |
| innodb_file_format_max   | Antelope          |
| time_format              | %H:%i:%s          |
+--------------------------+-------------------+
8 rows in set (0.00 sec)

mysql> delete from test.t where id=2;
Query OK, 1 row affected (0.17 sec)

mysql> select * from test.t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

从库验证：

mysql> select * from test.t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

3、主库修改binlog模式为statement，并在mysql库向test库插入数据

mysql> set global binlog_format=statement;
Query OK, 0 rows affected (0.21 sec)
--重新进入，查看日志模式已更改
mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| binlog_format            | STATEMENT         |

在mysql库向test库插入数据：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into test.t values(3);
Query OK, 1 row affected (0.14 sec)

mysql> select * from test.t;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)

从库查看，验证跨库更新，数据丢失：

mysql> select * from test.t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

结论：在只记录某个库的binlog时(binlog-do-db)，日志格式statement跨库操作，从库会丢失数据。

搭建主从从环境

级联复制搭建：机器环境干净的

1、从库1和从库2清理环境

mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.11 sec)

mysql> show slave status;
Empty set (0.00 sec)

2、主库备份，该备份给从库1使用

[root@localhost ~]# mysqldump -uroot -proot --single-transaction -A > /u01/bakdata/mysqldump/all.sqlWarning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost ~]# scp /u01/bakdata/mysqldump/all_for_slave.sql 192.168.100.112:/u01/bakdata/mysqldump/
root@192.168.100.112's password: 
all_for_slave.sql                                                               100%  640KB   8.9MB/s   00:00

3、主库赋权

mysql> grant replication slave on *.* to 'uslave'@'192.168.100.%' identified by 'uslave';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、从库1恢复并搭建主从

[root@localhost ~]# mysql -uroot -proot < /u01/bakdata/mysqldump/all_for_slave.sql 
Warning: Using a password on the command line interface can be insecure.
--ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
--若有以上报错，需在从库执行：stop slave; reset master;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.111',MASTER_USER='uslave',MASTER_PASSWORD='uslave',MASTER_PORT=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.12 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.111
                  Master_User: uslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2613
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5、从库1确认开启参数

mysql> show variables like 'log_slave_updates';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+
1 row in set (0.00 sec)
--该参数作用是：使从库也产生binlog日志，从库2去读取该binlog日志，写入从库2的relay log日志。

6、从库1执行备份，该备份给从库2使用，需要加参数--dump-slave

[root@localhost ~]# mysqldump --single-transaction --dump-slave -A -uroot -proot > /u01/bakdata/mysqldump/for_slave2.sql
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost ~]# scp /u01/bakdata/mysqldump/for_slave2.sql 192.168.100.113:/u01/bakdata/mysqldump/

7、从库1赋权

mysql> grant replication slave on *.* to 'uslave1'@'192.168.100.%' identified by 'uslave1';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

8、从库2初始化，启动，恢复数据

[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/u01/data/mysql
[root@localhost ~]# mysqld_safe --defaults-file=/etc/my.cnf &
[root@localhost ~]# mysql < /u01/bakdata/mysqldump/for_slave2.sql

9、从库2搭建主库，从库2的主库是从库1

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.112',MASTER_USER='uslave1',MASTER_PASSWORD='uslave1',MASTER_PORT=3306,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.20 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.112
                  Master_User: uslave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 631159
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 34719
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

10、在主库更新数据，验证从库1和从库2是否也更新

mysql> insert into t values(2);
Query OK, 1 row affected (0.09 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

至此，主从从的级联复制模式搭建完成。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：安装 Druid 安装的时候提示 JAVA 版本的问题

下一篇：292 Nim Game Nim游戏

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯