mysql 5.7 gtid_mysql5.7使用gtid模式搭建主从复制架构

一、架构

两台mysql服务器做一主一从，172.28.18.69(主) 172.28.18.78(从)

二、分别编译安装mysql5.7

1、下载mysql5.7.26源码包

[root@server-1 /]# mkdir /usr/local/src/mysql-5.7.26-src

[root@server-1]# cd /usr/local/src/mysql-5.7.26-src/[root@server-1 mysql-5.7.26-src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz

2、添加mysql用户和组

[root@server-1 mysql-5.7.26-src]# useradd mysql

[root@server-1 mysql-5.7.26-src]# idmysql

uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)

3、设置mysql用户密码

[root@server-1 mysql-5.7.26-src]# passwdmysql

更改用户 mysql 的密码 。

新的 密码：

重新输入新的 密码：passwd：所有的身份验证令牌已经成功更新。

4、新建mysql相关目录，并赋权限给mysql用户

[root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26[root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26/data

[root@server-1 mysql-5.7.26-src]# mkdir /home/mysql-5.7.26/log

[root@server-1 mysql-5.7.26-src]# mkdir -p /home/mysql-5.7.26/run[root@server-1 home]# chown -R mysql:mysql mysql-5.7.26/[root@server-1 home]# cd mysql-5.7.26/[root@server-1 mysql-5.7.26]# ll

总用量0drwxr-xr-x 2 mysql mysql 6 5月 31 14:25data

drwxr-xr-x 2 mysql mysql 6 5月 31 14:26log

drwxr-xr-x 2 mysql mysql 6 5月 31 14:26run

5、安装依赖包

[root@server-1]# yum install cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl automake autoconf make libtool bison-devel libaio-devel -y

6、安装boost

MySQL5.7.24要求boost的版本是1.59，更高版本的不适用MySQL5.7.24

[root@server-1 /]# cd /usr/local/src/

[root@server-1 src]#wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

[root@server-1 src]# tar -zxvf boost_1_59_0.tar.gz

[root@server-1 src]# mv boost_1_59_0 /usr/local/

7、编译安装

[root@server-1 /]# cd /usr/local/src/mysql-5.7.26-src/[root@server-1 mysql-5.7.26-src]# tar -zxvf mysql-5.7.26.tar.gz

[root@server-1 mysql-5.7.26]# cd mysql-5.7.26

[root@server-1 mysql-5.7.26]# cmake -DCMAKE_INSTALL_PREFIX=/home/mysql-5.7.26 -DINSTALL_DATADIR=/home/mysql-5.7.26/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_SSL=yes -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/home/mysql-5.7.26/run/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DSYSCONFDIR=/etc -DWITH_READLINE=on -DWITH_BOOST=/usr/local/boost_1_59_0

如果中途出现错误，则根据错误信息去查找问题，再次编译的时候，一定需要删除mysql-5.7.26下的CMakeCache.txt这个文件，清空缓存才能再次编译，否则还是跟上次一样出现错误。

最后出现

CMake Warning:

Manually-specified variables were not used by the project:

INSTALL_DATADIR

WITH_READLINE-- Build files have been written to: /usr/local/src/mysql-5.7.26-src/mysql-5.7.26

编译成功，再执行

[root@server-1 mysql-5.7.26]# make && make install

最后出现

-- Installing: /home/mysql-5.7.26/mysql-test/lib/My/SafeProcess/Base.pm-- Installing: /home/mysql-5.7.26/support-files/mysqld_multi.server-- Installing: /home/mysql-5.7.26/support-files/mysql-log-rotate-- Installing: /home/mysql-5.7.26/support-files/magic-- Installing: /home/mysql-5.7.26/share/aclocal/mysql.m4-- Installing: /home/mysql-5.7.26/support-files/mysql.server

8、编写配置文件my.cnf

[root@server-1 mysql-5.7.26]# vim /etc/my.cnf

[mysqld]

bsedir=/home/mysql-5.7.26datadir=/home/mysql-5.7.26/data

socket=/home/mysql-5.7.26/run/mysql.sock

user=mysql

symbolic-links=0[mysqld_safe]

log-error=/home/mysql-5.7.26/log/mysqld.log

pid-file=/home/mysql-5.7.26/run/mysqld.pid

9、配置环境变量

[root@server-1 mysql-5.7.26]# vim /etc/profile

在文件末尾加上

PATH=$PATH:/home/mysql-5.7.26/bin/[root@server-1 mysql-5.7.26]# source /etc/profile

10、初始化系统数据库

[root@server-1 /]# mysqld --initialize --user=mysql --basedir=/home/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/[root@server-1 /]#

没有报错，查看日志中的初始密码

[root@server-1 /]# cat /home/mysql-5.7.26/log/mysqld.log |greppassword2019-06-04T01:56:14.224507Z 1 [Note] A temporary password is generated for root@localhost: dyB8iUq

11、启动数据库

[root@server-1 /]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &2019-06-04T02:00:01.566664Z mysqld_safe Logging to '/home/mysql-5.7.26/log/mysqld.log'.2019-06-04T02:00:01.629556Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data

12、启动客户端,输入初始密码登录数据库

[root@server-1 ~]# mysql -uroot -p

13、修改密码

mysql> set PASSWORD=PASSWORD('xxxxxxx');

mysql> alter user 'root'@'localhost'PASSWORD EXPIRE NEVER;

Query OK,0 rows affected (0.00sec)

mysql>flush privileges;

Query OK,0 rows affected (0.00 sec)

退出，重新登录

[root@server-1 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 3Server version:5.7.26Source distribution

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql>

三、配置主从配置

1、主库172.28.18.69配置文件

[mysqld]

basedir=/home/mysql-5.7.26datadir=/home/mysql-5.7.26/data

socket=/home/mysql-5.7.26/run/mysql.sock

user=mysql

symbolic-links=0server_id=69gtid_mode=on #开启gtid模式

enforce_gtid_consistency=on #强制gtid一致性

log-bin=master #开启二进制日志

binlog_format=row

[mysqld_safe]

log-error=/home/mysql-5.7.26/log/mysqld.log

pid-file=/home/mysql-5.7.26/run/mysqld.pid

重启mysql，并登录mysql

[root@server-1 /]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &[1] 29747[root@server-1 /]# 2019-06-04T02:34:07.501598Z mysqld_safe Logging to '/home/mysql-5.7.26/log/mysqld.log'.2019-06-04T02:34:07.554499Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data^C

[root@server-1 /]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.26-log Source distribution

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql>

查询主库状态

mysql>show master status;+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| master.000001 | 154 | | | |

+---------------+----------+--------------+------------------+-------------------+

多了一个Executed_Gtid_Set

查询gitd相关变量

mysql> show variables like '%gtid%';+----------------------------------+-----------+

| Variable_name | Value |

+----------------------------------+-----------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | |

| session_track_gtids | OFF |

+----------------------------------+-----------+

8 rows in set (0.00 sec)

2、主库上创建repl账号

mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'xxxxxx';

Query OK,0 rows affected, 1 warning (0.02 sec)

3从库配置文件

[mysqld]

basedir=/home/mysql-5.7.26datadir=/home/mysql-5.7.26/data

socket=/home/mysql-5.7.26/run/mysql.sock

user=mysql

symbolic-links=0server_id=103gtid_mode=on #开启gtid模式

enforce_gtid_consistency=on

log-bin=salve

binlog_format=row

[mysqld_safe]

log-error=/home/mysql-5.7.26/log/mysqld.log

pid-file=/home/mysql-5.7.26/run/mysqld.pid

重启mysql

[root@server-2 log]# mysqld_safe --defaults-file=/etc/mysql/my.cnf &[1] 1037[root@server-2 log]# 2019-06-04T02:49:07.867996Z mysqld_safe Logging to '/home/mysql-5.7.26/log/mysqld.log'.2019-06-04T02:49:07.899183Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data

登录mysql，从库执行以下命令

mysql> change master to master_host='172.28.18.69' ,master_user='repl',master_password='xxxxxx',master_auto_position=1;

Query OK,0 rows affected, 2 warnings (0.30sec)

mysql>start slave;

Query OK,0 rows affected (0.02 sec)

查看从库状态

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:172.28.18.69Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: master.000001Read_Master_Log_Pos:457Relay_Log_File: server-2-relay-bin.000002Relay_Log_Pos:664Relay_Master_Log_File: master.000001Slave_IO_Running: Yes

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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:457Relay_Log_Space:874Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:0Last_IO_Error:

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:69Master_UUID: ee3e292b-866b-11e9-9df8-14feb5dc2c77

Master_Info_File:/home/mysql-5.7.26/data/master.infoSQL_Delay:0SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waitingfor moreupdates

Master_Retry_Count:86400Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: ee3e292b-866b-11e9-9df8-14feb5dc2c77:1Executed_Gtid_Set: 520b8b86-8675-11e9-8283-90b11c15be09:1-3,

ee3e292b-866b-11e9-9df8-14feb5dc2c77:1Auto_Position:1Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:1 row in set (0.00 sec)

成功

mysql> mysql> show variables like '%gtid%';+----------------------------------+----------------------------------------+

| Variable_name | Value |

+----------------------------------+----------------------------------------+

| binlog_gtid_simple_recovery | ON |

| enforce_gtid_consistency | ON |

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | ee3e292b-866b-11e9-9df8-14feb5dc2c77:1 |

| session_track_gtids | OFF |

+----------------------------------+----------------------------------------+

测试

主库新建test库和test表，插入一条记录

mysql>create database test;

Query OK,1 row affected (0.02sec)

mysql>use test;

Database changed

mysql> create table test(id int ,name varchar(20));

Query OK,0 rows affected (0.14sec)

mysql> insert into test values(1,'aaaaa');

Query OK,1 row affected (0.05sec)

mysql> select *from test;+------+-------+

| id | name |

+------+-------+

| 1 | aaaaa |

+------+-------+

1 row in set (0.00 sec)

在从库上查看

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

5 rows in set (0.00sec)

mysql>use test;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from test;+------+-------+

| id | name |

+------+-------+

| 1 | aaaaa |

+------+-------+

1 row in set (0.00 sec)

数据同步成功。