续：MySQL的gtid模式

为什么要启用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来对日志就行回放