MySQL5.7实现GTID功能的实验流程及结果

下文给大家带来MySQL5.7实现GTID功能的实验流程及结果，希望能够给大家在实际运用中带来一定的帮助，

MYSQL涉及的东西比较多，理论也不多，网上有很多书籍，今天我们就用在行业内累计的经验来做一个解答。

192.168.205.37: as master server 192.168.205.47: as slave server 

OS: centos 7 1810 with mini install mysql-5.7.26-el7-x86_64.tar.gz

GTID(global transaction ID)全局事务标识符，mysql5.6版本开始支持，GTID复制不像传统的复制方式（异步延复制、半同步复制）需要找到binlog和pos点，只需要知道master的IP、端口、账号、密码即可，开启GDIT后，执行change master to master_auto_position=1即可，它会自动寻找同步。我们使用mysql5.7开启两个server，一个为主另一个为从，测试GDIT功能。

1. 解压缩文件

   [root@centos7 mysql]#tar xvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/  

2. 创建软链接

   [root@centos7 local]#ln -s mysql-5.7.26-el7-x86_64/ mysql

3. 添加mysql帐号

   [root@centos7 local]#useradd -r -s /bin/false mysql

4. 准备环境变量

   [root@centos7 mysql]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@centos7 mysql]#echo $PATH  

5. 准备数据目录(可以不建，初始化数据库时会自动创建)

   [root@centos7 mysql]#mkdir /data/mysql[root@centos7 mysql]#chown mysql:mysql /data/mysql

6. 安装相关包（跟据需要安装，初始化时会提示）

   [root@centos7 mysql]#yum install libaio

7. 初步化数据库

   [root@centos7 mysql]#mysqld --initialize --user=mysql --datadir=/data/mysql2019-08-12T00:43:03.799485Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2019-08-12T00:43:04.007086Z 0 [Warning] InnoDB: New log files created, LSN=457902019-08-12T00:43:04.043130Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2019-08-12T00:43:04.100702Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 24ddb90b-bc9a-11e9-856e-000c2956e1ea.2019-08-12T00:43:04.101693Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2019-08-12T00:43:04.102159Z 1 [Note] A temporary password is generated for root@localhost: Ia-ClrMga7L/

8. 修改数据库配置文件

   [root@centos7 mysql]#cp -b /etc/my.cnf{,.bak}[root@centos7 mysql]#rpm -qf /etc/my.cnfmariadb-libs-5.5.60-1.el7_5.x86_64[root@centos7 mysql]#vi /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.sock                                                                                                      log-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.pid[client]socket=/data/mysql/mysql.sock

9. 准备启动脚本

   [root@centos7 mysql]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@centos7 mysql]#chkconfig --list[root@centos7 mysql]#chkconfig --add mysqld[root@centos7 mysql]#chkconfig --list      [root@centos7 mysql]#service mysqld startStarting MySQL.Logging to '/data/mysql/mysql.log'. SUCCESS! 

10. 修改密码

    [root@centos7 mysql]#mysql -p"Ia-ClrMga7L/"[root@centos7 mysql]#mysqladmin -uroot -p"Ia-ClrMga7L/" password centos[root@centos7 mysql]#mysql -pcentos

11. 数据库的用户帐号存放的表发生了变化，没有password，用authentication_string代替

    mysql> desc user;mysql>  select user,host,authentication_string from user;+---------------+-----------+-------------------------------------------+| user          | host      | authentication_string                     |+---------------+-----------+-------------------------------------------+| root          | localhost | *128977E278358FF80A246B5046F51043A2B1FCED || mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |+---------------+-----------+-------------------------------------------+3 rows in set (0.00 sec)

12. 编辑配置文件设置主云服务器，开启GTID功能

    [root@centos7 ~]#vi /etc/my.cnf        [mysqld]server-id=37log-bin                                                                                                                            gtid_mode=ONenforce_gtid_consistencydatadir=/data/mysqlsocket=/data/mysql/mysql.socklog-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.pid[client]socket=/data/mysql/mysql.sock[root@centos7 ~]#service mysqld restartShutting down MySQL. SUCCESS! Starting MySQL. SUCCESS! 

13. 创建复制帐号

    mysql> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';Query OK, 0 rows affected, 1 warning (0.00 sec)

14. 在从服务器上设置配置文件

    [root@centos7 ~]#vi /etc/my.cnf         [mysqld]server-id=47gtid_mode=ONenforce_gtid_consistencydatadir=/data/mysqlsocket=/data/mysql/mysql.socklog-error=/data/mysql/mysql.logpid-file=/data/mysql/mysql.pid                                                                                                     [client]socket=/data/mysql/mysql.sock[root@centos7 ~]#service  mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! 

15. 在从服务器上登录，并修改change master to, 不需要指位置和文件名

    [root@centos7 ~]#mysql -pcentosmysql> CHANGE MASTER TO    ->   MASTER_HOST='192.168.205.37',    ->   MASTER_USER='repluser',    ->   MASTER_PASSWORD='centos',    ->   MASTER_PORT=3306,    ->   MASTER_AUTO_POSITION=1;Query OK, 0 rows affected, 2 warnings (0.01 sec)

16. 查看复制状态

    mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.205.37                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: centos7-bin.000005          Read_Master_Log_Pos: 453               Relay_Log_File: centos7-relay-bin.000002                Relay_Log_Pos: 670        Relay_Master_Log_File: centos7-bin.000005             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

17. 测试数据库的复制

    mysql> create database db1;Query OK, 1 row affected (0.01 sec)

18. 在从服务器上查看复制状态，复制成功

    mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db1                || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec

19. 看了以上关于MySQL5.7实现GTID功能的实验流程及结果，如果大家还有什么地方需要了解的可以在行业资讯里查找自己感兴趣的或者找我们的专业技术工程师解答的，技术工程师在行业内拥有十几年的经验了。