CNDBA社区
Mysql8.0基于GTID主从复制
一、环境
软件 版本
Centos Centos 6 & Centos 7
Mysql 8.0.x
二、安装
2.1、环境配置
1、关闭防火墙以及selinux
Centos 6:
➜ ~ service iptables stop
➜ ~ chkconfig iptables off
➜ ~ vi /etc/selinux/config
修改
SELINUX=enforcing
为
SELINUX=disabled
Centos 7:
➜ ~ systemctl stop firewalld.service
➜ ~ systemctl disabled firewalld.service
➜ ~ vi /etc/sysconfig/selinux
修改
SELINUX=enforcing
为
SELINUX=disabled
2、依赖包安装
➜ ~ yum install gcc gcc-c++ libaio
2.2、下载解压重命令
1、创建目录
自定义目录:
软件目录：
所有下载的软件放至目录 /opt/soft/
所有软件安装目录(主程序目录)：/opt/soft-base-dir/
软件和安装目录新建目录Mysql:
➜ ~ mkdir -p /opt/soft/mysql
➜ ~ mkdir -p /opt/soft-base-dir/
数据目录：
➜ ~ mkdir -p /data/mysql/mysql-data
日志文件目录：
➜ ~ mkdir -p /data/mysql/log
PID目录：
➜ ~ mkdir -p /data/mysql/pid
2、下载并解压
2.1、下载：
➜ ~ cd /opt/soft/mysql
➜ mysql wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar
--2018-09-05 13:16:04-- https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar
Resolving dev.mysql.com... 137.254.60.11
Connecting to dev.mysql.com|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar [following]
--2018-09-05 13:16:06-- https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar
Resolving cdn.mysql.com... 23.199.129.242
Connecting to cdn.mysql.com|23.199.129.242|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 396308480 (378M) [application/x-tar]
Saving to: “mysql-8.0.12-linux-glibc2.12-x86_64.tar”
100%[================================================================================================================>] 396,308,480 1.76M/s in 3m 18s
2018-09-05 13:19:26 (1.91 MB/s) - “mysql-8.0.12-linux-glibc2.12-x86_64.tar” saved [396308480/396308480]
2.2、解压到当前目录/opt/soft/mysql
➜ ~ cd /opt/soft/mysql
➜ mysql tar -xvf mysql-8.0.12-linux-glibc2.12-x86_64.tar
mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
mysql-test-8.0.12-linux-glibc2.12-x86_64.tar.xz
➜ ~ ll /opt/soft/mysql
total 756M
-rw-r--r--. 1 root root 378M Jun 29 16:37 mysql-8.0.12-linux-glibc2.12-x86_64.tar
-rw-r--r--. 1 7155 31415 339M Jun 29 15:39 mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
-rw-r--r--. 1 7155 31415 40M Jun 29 16:35 mysql-test-8.0.12-linux-glibc2.12-x86_64.tar.xz
2.3、选中mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz安装包解压到软件安装主目录，而非mysql-test安装包
➜ ~ tar -xvf /opt/soft/mysql/mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz -C /opt/soft-base-dir/
➜ ~ ll /opt/soft-base-dir/
total 4.0K
drwxr-xr-x. 9 root root 4.0K Sep 5 13:45 mysql-8.0.12-linux-glibc2.12-x86_64
2.4、更改Mysql主目录名
➜ ~ mv /opt/soft-base-dir/mysql-8.0.12-linux-glibc2.12-x86_64 /opt/soft-base-dir/mysql-base
➜ ~ ll /opt/soft-base-dir/
total 4.0K
drwxr-xr-x. 9 root root 4.0K Sep 5 13:45 mysql-base
---------------------------------------------------------------
解压目录内容
bin mysqld服务器，客户端和实用程序
data 日志文件，数据库
docs MySQL手册信息格式
man Unix手册页
include 包含（标题）文件
lib 库
share 其他支持文件，包括错误消息，示例配置文件，用于数据库安装的SQL
----------------------------------------------------------------
3、安装Mysql
切换到主目录
➜ ~ cd /opt/soft-base-dir/mysql-base
创建用户以及组
➜ ~ mysql groupadd mysql
➜ ~ mysql useradd -g mysql mysql
➜ ~ echo "marvin" | passwd --stdin mysql
4、授予主目录以及数据目录mysql权限
➜ ~ chmod 750 /opt/soft-base-dir/mysql-base
➜ ~ chown -Rf mysql.mysql /opt/soft-base-dir/mysql
➜ ~ /data chmod 750 /data/
➜ ~ /data chown -Rf mysql:mysql /data
2.3、初始化Mysql
在初始化之前，将新的my.cnf配置完成，以及将系统自动重启文件进行修改
1、配置my.cnf
➜ ~ vi /etc/my.cnf
部分配置显示，其他请按照自身情况配置
[mysqld]
user=mysql
port = 3306
socket = /data/mysql/mysql.sock
datadir = /data/mysql/mysql-data
character-set-server=utf8
collation-server=utf8_general_ci
slow_query_log_file = /data/mysql/log/slow.log
[mysqld_safe]
open-files-limit = 65535
log-error=/data/mysql/log/mysqld.log
pid-file=/data/mysql/pid/mysqld.pid
2、自启动文件
复制自启动文件到系统init.d目录：
➜ ~ cp /opt/soft-base-dir/mysql-base/support-files/mysql.server /etc/init.d/mysql.server
修改启动文件
➜ ~ vi /etc/init.d/mysql.server
将原来的：
````
basedir=
datadir=
````
修改为
````
basedir=/opt/soft-base-dir/mysql-base
datadir=/data/mysql/mysql-data
````
将原来的
```
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test -z "$datadir"
then
datadir=/usr/local/mysql/data
fi
sbindir=/usr/local/mysql/bin
libexecdir=/usr/local/mysql/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
```
修改为(修改部分为basedir、bindir、datadir、sbindir、libexecdir)
```
mysqld_pid_file_path=
if test -z "$basedir"
then
basedir=/opt/soft-base-dir/mysql-base
bindir=/opt/soft-base-dir/mysql-base/bin
if test -z "$datadir"
then
datadir=/data/mysql/mysql-data
fi
sbindir=/opt/soft-base-dir/mysql-base/bin
libexecdir=/opt/soft-base-dir/mysql-base/bin
else
bindir="$basedir/bin"
if test -z "$datadir"
then
datadir="$basedir/data"
fi
sbindir="$basedir/sbin"
libexecdir="$basedir/libexec"
fi
```
3、环境变量配置
echo "export PATH=$PATH:/opt/soft-base-dir/mysql-base/bin/" >> /etc/profile
source /etc/profile
4、初始化Mysql
➜ ~ mysqld --initialize --user=mysql --basedir=/opt/soft-base-dir/mysql-base --datadir=/data/mysql/mysql-data
以下启动Mysql8.0被废弃参数，报错如下：
默认Mysql8.0是已经禁用无需手动禁用symbolic-links=0，参数被废弃
默认Mysql8.0 bin_log日志过期时间参数精确到秒，原参数expire-logs-days被废弃，现在参数为binlog_expire_logs_seconds
因为参数innodb_ft_aux_table="test/articles"设置带了值"test/articles"，导致插件InnoDB失败，开启全文扫描，只需要配置文件中写入参数innodb_ft_aux_table即可，不需要值
查询缓存参数Mysql8.0被废弃，需要去掉参数配置文件中的 query_cache_size = 1024M 和 query_cache_limit = 16M参数即可
该参数log_warnings已被Mysql8.0废弃,取而代之为log_error_verbosity，修改为log_error_verbosity=3
重启初始化
➜ ~ mysqld --initialize --user=mysql --basedir=/opt/soft-base-dir/mysql-base --datadir=/data/mysql/mysql-data
2018-09-05T07:21:26.338560Z 0 [System] [MY-013169] [Server] mysqld (mysqld 8.0.12) initializing of server in progress as process 13081
2018-09-05T07:21:26.393929Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2018-09-05T07:21:26.394479Z 0 [ERROR] [MY-010119] [Server] Aborting
因为之前做初始化，有部分文件已经生成在data目录下，所以只需要删除data目录下，重新初始化即可
2018-09-05T07:21:26.394843Z 0 [System] [MY-010910] [Server] mysqld: Shutdown complete (mysqld 8.0.12) MySQL Community Server - GPL
➜ ~ cd /data/mysql/mysql-data
➜ mysql-data ls
mysql-bin.index
➜ mysql-data rm -rf mysql-bin.index
➜ mysql-data cd ~
解决上述废弃参数，之后重启初始化成功：
➜ ~ mysqld --initialize --user=mysql --basedir=/opt/soft-base-dir/mysql-base --datadir=/data/mysql/mysql-data
部分输出显示：
2018-09-05T09:10:53.402761Z 5 [Note] [MY-011061] [Server] Creating the system database.
2018-09-05T09:10:53.403001Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: C3kWpS3Jtb?r
2018-09-05T09:10:53.403211Z 5 [Note] [MY-011061] [Server] Creating the system tables.
2018-09-05T09:10:59.538020Z 5 [Note] [MY-011061] [Server] Filling in the system tables, part 1.
2018-09-05T09:10:59.637169Z 5 [Note] [MY-011061] [Server] Filling in the system tables, part 2.
2018-09-05T09:11:02.211888Z 5 [Note] [MY-011061] [Server] Filling in the mysql.help table.
2018-09-05T09:11:03.620456Z 5 [Note] [MY-011061] [Server] Creating the system users for internal usage.
root用户临时密码： C3kWpS3Jtb?r
2.4、root账号密码修改
-------------------------------------------------------------------------------------------------------------------------
注意： Mysql8.0默认使用caching_sha2_password密码验证
因为当前有很多数据库工具和链接包都不支持“caching_sha2_password”，为了方便，生产环境可暂时改回“mysql_native_password”认证插件。
修改用户密码，在MySQL中执行命令：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
若想修改密码验证插件，同时修改密码，永久生效。
如果想默认使用“mysql_native_password”插件认证，可以在配置文件中配置default_authentication_plugin项
[mysqld]
default_authentication_plugin=mysql_native_password
-------------------------------------------------------------------------------------------------------------------------
当前测试环境就，使用原生默认支持的caching_sha2_password密码验证
#1、生成RSA私钥，可以跳过此步骤
mysql_ssl_rsa_setup需要openssl支持，用于启用数据量ssl连接
➜ ~ /opt/soft-base-dir/mysql-base/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/mysql-data
#2、启动Mysql服务
➜ mysql service mysql.server start
Starting MySQL.2018-09-05T09:18:27.532720Z mysqld_safe error: log-error set to '/data/mysql/log/mysqld.log', however file don't exists. Create writable for user 'mysql'.
The server quit without updating PID file (/data/mysql/mysq[FAILED]bmaster.pid). --这个报错是目录权限不足，无法启动数据库
手工创建日志文件：
➜ ~ touch /data/mysql/log/mysqld.log
授权：
➜ ~ chown mysql:mysql /data/mysql/log/mysqld.log
➜ ~ chown -R mysql:mysql /data
再次重启启动服务
➜ /opt service mysql.server start
Starting MySQL.... [ OK ]
➜ /opt
#3、进入MySQL shell
----------------------------------------------------------------------------------------------------------------------
➜ /tmp mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
报错原因是因为：MYSQL客户端还是从默认目录/tmp/目录下，寻找sock连接串，但是这个下面不存在，所以到参数配置文件my.cnf手动设置客户端连接串位置
vi /etc/my.cnf
配置文件添加如下参数即可：
[client]
socket=/data/mysql/mysql.sock
-------------------------------------------------------------------------------------------------------------------------
➜ ~ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 8
Server version: 8.0.12
Copyright (c) 2000, 2018, 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>
#3、修改密码
mysql> alter user 'root'@'localhost' identified by 'marvin';
Query OK, 0 rows affected (0.39 sec)
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
该错误是因为Mysql加强了密码强度验证插件，5.7版本及以上默认使用密码强度插件validate_password.密码不能过于简单，需要大小写特殊字符以及数字
mysql> alter user 'root'@'localhost' identified by 'Marvin@123';
Query OK, 0 rows affected (2.41 sec)
三、其他主机安装配置
注意：其他主机安装配置如上所示类似…….注意: 主机Mysql初始化Root临时密码
2018-09-06T02:09:39.293916Z 5 Note [Server] Creating the system database.
2018-09-06T02:09:39.294382Z 5 Note [Server] A temporary password is generated for root@localhost: q!lGI>>oe2T7
2018-09-06T02:09:39.323999Z 5 Note [Server] Creating the system tables.
2018-09-06T02:09:39.481800Z 0 Note [InnoDB] InnoDB: Page cleaner took 5421ms to flush 153 and evict 0 pages
3.1、查找配置文件位置
➜ ~ whereis my.cnf
my: /etc/my.cnf
➜ ~
3.2、修改配置文件(所有节点服务器配置文件一致)
复制主机的参数my.cnf配置文件到其他远程主机目录/etc下，并修改server-id
复制主机的配置文件/etc/init.d/mysql.server到其他远程主机目录
3.3、重启mysql服务
#重启后配置即可生效
Centos 7:
systemctl restart mysqld
Centos 6:
➜ ~ service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
➜ ~
四、配置MySQL基于GTID的复制
GTID主从复制的配置思路
4.1、Mysql主服务器建库
➜ ~ mysql -uroot -p
创建具有复制权限的用户（主服务器上创建）
基于 GTID 的复制会自动地将没有在从库执行过的事务重放，所以不要在其它从库上建立相同的账号。 如果建立了相同的账户，有可能造成复制链路的错误
mysql> CREATE USER 'repl-marvin'@'172.16.10.%' IDENTIFIED BY 'marvin';
Query OK, 0 rows affected (0.54 sec)
mysql> grant replication slave on *.* to 'repl-marvin'@'172.16.10.%';
Query OK, 0 rows affected (0.35 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)
4.2、Mysql配置文件修改
配置Mysql基于GTID复制，需要在Mysql在服务器的主配置文件[mysqld]段中添加如下内容：
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
MySQL 5.6 版本时，基于 GTID 的复制中 log-slave-updates 选项是必须的,但是其增大了从服务器的IO负载, 而在 MySQL 5.7 中该选项已经不是必须项
4.2.1、主服务器配置文件
➜ ~ vi /etc/my.cnf
部分Mysql配置文件片段...其他请按照自身情况配置
[mysqld]
####GTID Copy Start####
server-id = 1
gtid-mode = ON
enforce-gtid-consistency = ON
log-slave-updates = ON
master-verify-checksum = 1
log-bin=mysql-bin
log_bin_index = mysql-bin.index
binlog_format= mixed
max_binlog_size = 100M
binlog-checksum = CRC32
4.2.2、从服务器配置文件
➜ ~ vi /etc/my.cnf
部分Mysql配置文件片段...其他请按照自身情况配置
server-id = 3
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = ON
skip-slave-start = true
read_only = ON
slave-sql-verify-checksum = 1
relay-log = /data/mysql/log/relay-log
relay-log-index = /data/mysql/log/relay-log-index
relay-log-info-file = /data/mysql/log/relay-log.info
master-info-repository = table
relay-log-info-repository = table
relay-log-recovery = ON
report-port = 3306
report-host = 172.16.10.242
replicate-do-db = marvin
replicate_wild_do_table= marvin.%
注：server-id 每台必须配置为不一样，dbmaster 为1，dbslave1 为3。这里没有给出全部配置，其它请根据实际情况自行配置。
4.2.3、重启Mysql服务器
主从服务器都需要重启
➜ ~ service mysql.server restart
Shutting down MySQL..... [ OK ]
Starting MySQL.... [ OK ]
4.3、查看主库与从库的GTID是否开启
mysql> show variables like "%gtid%";
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
9 rows in set (0.15 sec)
mysql> show variables like '%gtid_next%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| gtid_next | AUTOMATIC |
+---------------+-----------+
1 row in set (0.00 sec)
-------------------------------------------------------------------------------------------------------------------------
常用参数的作用：
a) gtid_executed
在当前实例上执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。设置 set sql_log_bin=0 后执行的事务不会生成 binlog 事件，也不会被记录到 gtid_executed 中。执行 RESET MASTER 可以将该变量置空。
b) gtid_purged
binlog 不可能永远驻留在服务上，需要定期进行清理(通过版本5.7 expire_logs_days ，Mysql8.0 参数binlog_expire_logs_seconds 可以控制定期清理间隔)，否则迟早它会把磁盘用尽。
gtid_purged 用于记录本机上已经执行过，但是已经被清除了的 binlog 事务集合。它是 gtid_executed 的子集。只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为和 gtid_purged 相同的值。
gtid_executed 为空意味着要么之前没有启动过基于 GTID 的复制，要么执行过 RESET MASTER。执行 RESET MASTER 时同样也会把 gtid_purged 置空，即始终保持 gtid_purged 是 gtid_executed 的子集。
c) gtid_next
会话级变量，指示如何产生下一个GTID。可能的取值如下:
第一个：AUTOMATIC
自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。
第二个：ANONYMOUS
设置后执行事务不会产生GTID。
第三个：显式指定的GTID
可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则下次执行事务时会报错。
4.4、服务器相关状态配置及查看(主从)
1、查看服务器server_uuid
主：
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 96d0b837-b0eb-11e8-9c13-5254006d562e |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
从：
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | e72eabd8-b179-11e8-9450-5254008f6f5c |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
2、查看主服务器状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3、主从GTID复制开启(从库执行)
mysql> CHANGE MASTER TO MASTER_HOST='172.16.10.243',MASTER_USER='replmarvin',MASTER_PASSWORD='marvin',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.42 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
4、启动成功后查看SLAVE的状态
mysql> show slave status/G;
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: marvin
查看验证slave_io_running以及slave_sql_running状态是否yes，是表示正常
-------------------------------------------------------------------------------------------------------------------------
之前查看slave状态发现报错：
Last_Errno: 1008
Last_Error: Error 'Can't drop database 'marvin'; database doesn't exist' on query. Default database: 'marvin'. Query: 'drop database marvin'
查看mysqd.log日志：
2018-09-06T02:49:20.788931Z 12 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Can't drop database 'marvin'; database doesn't exist' on query. Default database: 'marvin'. Query: 'drop database marvin', Error_code: MY-001008
2018-09-06T02:49:20.789189Z 12 [Warning] [MY-010584] [Repl] Slave: Can't drop database 'marvin'; database doesn't exist Error_code: MY-001008
2018-09-06T02:49:20.789288Z 12 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000004' position 155
这是因为主库我创建了marvin数据库，之前开始配置GTID复制失败，我手工在主库删出marvin数据库，因为GTID复制具有强一致性，全局事务唯一性，现在重新开始slave成功，但是应用SQL drop database发现从库根本没有该数据库解决方法：
从库上创建marvin数据库，然后它自己会进行drop 数据库，slave状态则会显示正常
创建数据库：
mysql> create database marvin;
Query OK, 1 row affected (0.23 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看数据库，实际该数据库不存在了，被slave删除了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------------------------------
5、在主服务器查看从库连接的主机信息
主库：该功能实现是因为我从库配置文件中加了report-host参数
mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+---------------+------+-----------+--------------------------------------+
| 3 | 172.16.10.242 | 3306 | 1 | e72eabd8-b179-11e8-9450-5254008f6f5c |
+-----------+---------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
4.5、测试GTID主从复制
4.5.1、在主库实例创建测试数据
mysql> create database marvin;
mysql> use marvin;
mysql> CREATE TABLE `test` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
mysql> insert into test values(1,1);
4.5.2、在从库实例检查数据是否成功复制
mysql> select * from marvin.test;
+------+-------+
| id | count |
+------+-------+
| 1 | 1 |
+------+-------+
1 row in set (0.00 sec)
4.5.3、检查从服务器状态
mysql> show slave status/G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.243
Master_User: repl-marvin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1042
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 1256
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: marvin
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: marvin.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1042
Relay_Log_Space: 1458
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: 96d0b837-b0eb-11e8-9c13-5254006d562e
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: 96d0b837-b0eb-11e8-9c13-5254006d562e:1-4
Executed_Gtid_Set: 96d0b837-b0eb-11e8-9c13-5254006d562e:1-4,
e72eabd8-b179-11e8-9450-5254008f6f5c:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
可以看到 IO 和 SQL 线程都为 YES ，另外 retrieved_Gtid_Set 接收了4个事务，Executed_Gtid_Set 执行了4个事务
至此，Mysql8.0基于GTID主从复制搭建完毕…..
版权声明：本文为博主原创文章，未经博主允许不得转载。
