当前位置：首页 > article >正文

续：MySQL的gtid模式

article 2025/1/31 14:09:26

为什么要启用gtid?

master端和slave端有延迟

##设置gtid

master slave1 slave2

[root@mysql1 ~]# vim /etc/my.cnf
[root@mysql1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
slow_query_log=on
gtid_mode=on
enforce-gtid-consistency=on
[root@mysql1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL........ SUCCESS!
Starting MySQL........... SUCCESS!

[root@mysql3 ~]# vim /etc/my.cnf
[root@mysql3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server_id=3
gtid_mode=on
enforce-gtid-consistency=on
[root@mysql3 ~]# systemctl restart mysqld
Failed to restart mysqld.service: Unit not found.
[root@mysql3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL..... SUCCESS!

把slave1也重启一下

[root@mysql2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL......... SUCCESS!

#

#看一下master的日志

[root@mysql1 ~]# mysql -uroot -predhat
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit;
Bye
[root@mysql1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240827 11:39:36 server id 1 end_log_pos 123 CRC32 0x1aa93b7f Start: binlog v 4, server v 5.7.44-log created 240827 11:39:36 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
eErNZg8BAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB4Ss1mEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AX87qRo=
'/*!*/;
# at 123
#240827 11:39:36 server id 1 end_log_pos 154 CRC32 0x7a75cfb5 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ; #说明已经开启了
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

#重新设定slave：

#1. 在所有从slave上关掉slave

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
#2.

mysql> change master to
-> master_host='172.25.254.6',
-> master_user='repl',
-> master_password='redhat',
-> master_auto_position=1
-> ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154 #自动识别到了
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....略

#slave2也同上，只不过遇到点问题

[root@mysql3 ~]# mysql -uroot -predhat

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

mysql> change master to
-> master_host='172.25.254.6',
-> master_user='repl',
-> master_password='redhat',
-> master_auto_position=1
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.25.254.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysql3-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting #出现问题，连不上io
Slave_SQL_Running: Yes

...略

mysql> exit;
Bye

##解决
[root@mysql3 ~]# ps aux | grep mysqld
root 57157 0.0 0.0 113412 1588 pts/2 S 11:38 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql3.pid
mysql 57322 0.1 9.1 1144772 170096 pts/2 Sl 11:38 0:07 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=mysql3.err --pid-file=/data/mysql/mysql3.pid --socket=/data/mysql/mysql.sock
root 58302 0.0 0.0 112808 968 pts/2 S+ 13:18 0:00 grep --color=auto mysqld
[root@mysql3 ~]# kill 57322
[root@mysql3 ~]# ps aux | grep mysqld
root 58309 0.0 0.0 112808 968 pts/2 R+ 13:19 0:00 grep --color=auto mysqld
[root@mysql3 ~]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS!
[root@mysql3 ~]# mysql -uroot -predhat

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.6
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql3-relay-bin.000004
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...略
gtid就做好了，用全局的id来对日志就行回放