GTID复制配置报错解决方法
GTID复制配置过程:
 
(1) 清理环境
pkill mysqld
 \rm -rf /data/mysql/data/*
 \rm -rf /data/binlog/*
 
 (2) 准备配置文件
主库db01:
 
cat > /data/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
 
slave1(db02):
cat > /data/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
 
slave2(db03):
cat > /data/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
 
 
(3) 初始化数据(3个节点多做)
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data 
 
(4) 启动数据库
/etc/init.d/mysqld start
 
(5) 构建主从:
master:51
slave:52,53
 
51:
grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';
 
52\53:
 
change master to 
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
 
mysql> set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)
 
 
 
change master to 
master_host='10.0.0.51',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;
 
 
start slave;
报错:

image.png

image.png

解决方法:
 
GTID复制配置过程:
 
(1) 清理环境
pkill mysqld
 \rm -rf /data/mysql/data/*
 \rm -rf /data/binlog/*
 
 (2) 准备配置文件
主库db01:
原配置

image.png

 mysql默认优先读取/etc/my.cnf 
 
 
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log-bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
socket=/tmp/mysql.sock
prompt=db01 [\\d]>
EOF
 
主库开启日志。
查看log_bin选项:

image.png

image.png

slave1(db02):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log-bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
 
slave2(db03):
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log-bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
 
 
(3) 初始化数据(3个节点多做)
mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data 
 
(4) 启动数据库
/etc/init.d/mysqld start
 
(5) 构建主从:
master:51
slave:52,53
 
51:
grant replication slave  on *.* to repl@'10.0.0.%' identified by '123';
 
52\53:
 
 
 
 
change master to 
master_host='10.0.0.51',
master_user='repl',
master_port=3306,
master_password='123' ,
MASTER_AUTO_POSITION=1;


本文 暂无 评论

Top