在项目配置之前, mysql8.0如何安装, 见我的上篇文章CentOS7 64位安装mysql8.0教程
- 环境
数据库: 主(192.168.1.10), 从 (192.168.0.102), 从(192.168.0.107)
-
配置主节点(192.168.1.10)
配置之前:
[root@wzb104 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 2608 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
编辑my.conf文件:
vim /etc/my.cnf在里面添加一下的配置
# 如果备份多个数据库，重复设置这个选项即可, 需要同步的数据库，如果没有本行，即表示同步所有的数据库
binlog-do-db=test
# 被忽略的数据库,如果有多个的话, 重复设置这个选项即可
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
log-bin=mysql-bin
server-id=1
记得保存, 接着重启一下mysql:
[root@wzb104 ~]# clear
[root@wzb104 ~]# vim /etc/my.cnf
[root@wzb104 ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
Failed to restart mysql.service: Unit mysql.service failed to load: No such file or directory.
[root@wzb104 ~]# systemctl restart mysqld;
[root@wzb104 ~]# systemctl status mysqld;
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 六 2020-03-14 23:46:29 CST; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 22223 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 22248 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─22248 /usr/sbin/mysqld
3月 14 23:46:26 wzb104 systemd[1]: Starting MySQL Server...
3月 14 23:46:29 wzb104 systemd[1]: Started MySQL Server.
登录上去, 查看binlog
[root@wzb104 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 155 | test | information_schema,mysql | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
mysql> exit;
Bye
从数据库配置(192.168.1.102):
server-id=2
# 需要同步的数据库，如果没有本行，即表示同步所有的数据库, 与主数据库一样
replicate-do-db=test
# 被忽略的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
log-bin=mysql-slave-bin
从数据库配置(192.168.1.107):
server-id=2
# 需要同步的数据库，如果没有本行，即表示同步所有的数据库
replicate-do-db=test3
# 被忽略的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
log-bin=mysql-slave-bin
上面的所有从节点都得重启:
[root@wzb103 ~]# systemctl restart mysqld
[root@wzb103 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 日 2020-03-15 10:09:49 CST; 11s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5251 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5275 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─5275 /usr/sbin/mysqld
3月 15 10:09:44 wzb103 systemd[1]: Starting MySQL Server...
3月 15 10:09:49 wzb103 systemd[1]: Started MySQL Server.
配置从数据库(192.168.0.102和192.168.0.107)节点的master配置(下面是查看从数据库的主数据库):
[root@wzb102 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status;
Empty set (0.00 sec)
# 把主节点绑定在自己的身上
mysql> change master to master_host='192.168.0.10', master_user='root', master_password='Abc!@123';
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.10
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wzb102-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: test2
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.01 sec)
ERROR:
No query specified
# 上面的IO线程和sql线程还没有开启
mysql> start slave
-> ;
Query OK, 0 rows affected (0.01 sec)
# 查看它是否开启
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.10
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wzb102-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: test2
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'root@192.168.0.10:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 200315 10:58:56
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
上面有个错误IO线程在连接中, 还有一个问题就是:
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'root@192.168.0.10:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.官网的解决方案即使:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.10
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wzb103-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: test3
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'root@192.168.0.10:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 200315 11:26:56
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> start salve user = 'root' password = 'Abc!@123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'salve user = 'root' password = 'Abc!@123'' at line 1
mysql> mysql --ssl-mode=DISABLED -h [masterIP] -uroot -pAbc!@123 --get-server-public-key;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql --ssl-mode=DISABLED -h [masterIP] -uroot -pAbc!@123 --get-server-public-ke' at line 1
mysql> mysql --ssl-mode=DISABLED -h [masterIP] -uroot -p'Abc!@123' --get-server-public-key;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql --ssl-mode=DISABLED -h [masterIP] -uroot -p'Abc!@123' --get-server-public-' at line 1
mysql> exit;
Bye
[root@wzb103 ~]# cealr
-bash: cealr: 未找到命令
[root@wzb103 ~]# clear
[root@wzb103 ~]# mysql --ssl-mode=DISABLED -h192.168.0.10 -uroot -pAbc!@123 --get-server-public-key
-bash: !@123: event not found
[root@wzb103 ~]# mysql --ssl-mode=DISABLED -h192.168.0.10 -uroot -p --get-server-public-key
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'192.168.0.107' (using password: YES)
[root@wzb103 ~]# mysql --ssl-mode=DISABLED -h192.168.0.10 -uroot -p --get-server-public-key
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status \G
Empty set (0.00 sec)
mysql> change master to master_host='192.168.0.10', master_user='root', master_password='Abc!@123';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.10
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: wzb104-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 200315 12:02:21
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> exit;
Bye
上面的问题解决了, 但是还有一个问题:
Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
重启一下mysql, 这里参照了Mysql thinks Master & Slave have the same server-id 和 Same ID error when I try to replicate databases
[root@wzb103 ~]# systemctl restart mysqld;
[root@wzb103 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 155
Relay_Log_File: wzb103-relay-bin.000006
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test3
Replicate_Ignore_DB: mysql,information_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 155
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9a4455d2-6605-11ea-b393-000c29f3c379
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
如果在跑去master配置错了, 你可以把它的主节点清除掉:
mysql>stop slave;
QueryOK, 0 rowsaffected (0,00 sec)
mysql>reset slave all;
QueryOK, 0 rowsaffected (0,04 sec)
mysql> show slave status\G
Emptyset (0,00 sec)- 查看主数据库的binlog:
[root@wzb104 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 93
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 4156 | test | information_schema,mysql | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log' at line 1
mysql> show binary log;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log' at line 1
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 178 | No |
| mysql-bin.000002 | 178 | No |
| mysql-bin.000003 | 4156 | No |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.19, Binlog ver: 4 |
| mysql-bin.000003 | 124 | Previous_gtids | 1 | 155 | |
| mysql-bin.000003 | 155 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 234 | Query | 1 | 426 | use `test`; CREATE TABLE `test`.`user` (
`id` int NOT NULL,
`name` varchar(58) NULL,
PRIMARY KEY (`id`)
) /* xid=69 */ |
| mysql-bin.000003 | 426 | Anonymous_Gtid | 1 | 505 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 505 | Query | 1 | 580 | BEGIN |
| mysql-bin.000003 | 580 | Table_map | 1 | 636 | table_id: 90 (test.user) |
| mysql-bin.000003 | 636 | Write_rows | 1 | 687 | table_id: 90 flags: STMT_END_F |
| mysql-bin.000003 | 687 | Xid | 1 | 718 | COMMIT /* xid=95 */ |
| mysql-bin.000003 | 718 | Anonymous_Gtid | 1 | 797 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 797 | Query | 1 | 872 | BEGIN |
| mysql-bin.000003 | 872 | Table_map | 1 | 928 | table_id: 90 (test.user) |
| mysql-bin.000003 | 928 | Write_rows | 1 | 973 | table_id: 90 flags: STMT_END_F |
| mysql-bin.000003 | 973 | Xid | 1 | 1004 | COMMIT /* xid=98 */ |
| mysql-bin.000003 | 1004 | Anonymous_Gtid | 1 | 1083 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1083 | Query | 1 | 1158 | BEGIN |
| mysql-bin.000003 | 1158 | Table_map | 1 | 1214 | table_id: 90 (test.user) |
| mysql-bin.000003 | 1214 | Write_rows | 1 | 1260 | table_id: 90 flags: STMT_END_F |
| mysql-bin.000003 | 1260 | Xid | 1 | 1291 | COMMIT /* xid=101 */ |
| mysql-bin.000003 | 1291 | Anonymous_Gtid | 1 | 1368 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1368 | Query | 1 | 1474 | DROP DATABASE `test` /* xid=161 */ |
| mysql-bin.000003 | 1474 | Anonymous_Gtid | 1 | 1551 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1551 | Query | 1 | 1708 | CREATE DATABASE `test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' /* xid=163 */ |
| mysql-bin.000003 | 1708 | Anonymous_Gtid | 1 | 1785 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1785 | Query | 1 | 1915 | use `test`; DROP TABLE IF EXISTS `user` /* generated by server */ |
| mysql-bin.000003 | 1915 | Anonymous_Gtid | 1 | 1994 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 1994 | Query | 1 | 2334 | use `test`; CREATE TABLE `user` (
`id` int(0) NOT NULL,
`name` varchar(58) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic /* xid=179 */ |
| mysql-bin.000003 | 2334 | Anonymous_Gtid | 1 | 2413 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 2413 | Query | 1 | 2488 | BEGIN |
| mysql-bin.000003 | 2488 | Table_map | 1 | 2544 | table_id: 102 (test.user) |
| mysql-bin.000003 | 2544 | Write_rows | 1 | 2595 | table_id: 102 flags: STMT_END_F |
| mysql-bin.000003 | 2595 | Table_map | 1 | 2651 | table_id: 102 (test.user) |
| mysql-bin.000003 | 2651 | Write_rows | 1 | 2696 | table_id: 102 flags: STMT_END_F |
| mysql-bin.000003 | 2696 | Table_map | 1 | 2752 | table_id: 102 (test.user) |
| mysql-bin.000003 | 2752 | Write_rows | 1 | 2798 | table_id: 102 flags: STMT_END_F |
| mysql-bin.000003 | 2798 | Xid | 1 | 2829 | COMMIT /* xid=180 */ |
| mysql-bin.000003 | 2829 | Anonymous_Gtid | 1 | 2906 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 2906 | Query | 1 | 3035 | use `test`; DROP TABLE `user` /* generated by server */ /* xid=197 */ |
| mysql-bin.000003 | 3035 | Anonymous_Gtid | 1 | 3112 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 3112 | Query | 1 | 3242 | use `test`; DROP TABLE IF EXISTS `user` /* generated by server */ |
| mysql-bin.000003 | 3242 | Anonymous_Gtid | 1 | 3321 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 3321 | Query | 1 | 3661 | use `test`; CREATE TABLE `user` (
`id` int(0) NOT NULL,
`name` varchar(58) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic /* xid=207 */ |
| mysql-bin.000003 | 3661 | Anonymous_Gtid | 1 | 3740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 3740 | Query | 1 | 3815 | BEGIN |
| mysql-bin.000003 | 3815 | Table_map | 1 | 3871 | table_id: 103 (test.user) |
| mysql-bin.000003 | 3871 | Write_rows | 1 | 3922 | table_id: 103 flags: STMT_END_F |
| mysql-bin.000003 | 3922 | Table_map | 1 | 3978 | table_id: 103 (test.user) |
| mysql-bin.000003 | 3978 | Write_rows | 1 | 4023 | table_id: 103 flags: STMT_END_F |
| mysql-bin.000003 | 4023 | Table_map | 1 | 4079 | table_id: 103 (test.user) |
| mysql-bin.000003 | 4079 | Write_rows | 1 | 4125 | table_id: 103 flags: STMT_END_F |
| mysql-bin.000003 | 4125 | Xid | 1 | 4156 | COMMIT /* xid=208 */ |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
51 rows in set (0.00 sec)
-
最后测试主从是否同步:
尝试删除test数据库的时候如下:
在增加数据的时候如下: 
扫一扫
717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
