Mysql之多实例GTID主从复制

前引：在一台主机上部署一主两从mysql主从复制

Server version: 5.6.32-log MySQL Community Server (GPL)

该节点IP为192.168.0.77

一、先在本机安装mysql

1、mysql默认是3306端口，安装脚本

cat >> mysql_install.sh << 'EOF'

[root@centos74-ren-m ~]# cat mysql_install.sh 
#! /bin/bash
id mysql
[ $? -eq 0 ] || useradd -M -s /sbin/nologin mysql
yum -y install autoconf libaio* &> /dev/null
[ -d /root/tools ] || mkdir -p /root/tools
[ -d /appliaction ] || mkdir -p /application
if [ -d /root/tools ] ;then
    wget -P /root/tools https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.32-linux-glibc2.5-x86_64.tar.gz
else
    mkdir -P /root/tools
    exit 1
fi
###Mysql Config####
Mysql_Config() {
cd  /root/tools && tar xf mysql-5.6.32-linux-glibc2.5-x86_64.tar.gz
mv /root/tools/mysql-5.6.32-linux-glibc2.5-x86_64 /application/mysql-5.6.32
ln -s /application/mysql-5.6.32 /application/mysql
cd /application/mysql
chown -R mysql.mysql /application/mysql/data/
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data/ --user=mysql
cp -rf /application/mysql/support-files/mysql.server /etc/init.d/mysqld
sed -ri "s#/usr/local#/application#g" /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
cp -rf /application/mysql/support-files/my-default.cnf /etc/my.cnf 
/etc/init.d/mysqld start 
/application/mysql/bin/mysqladmin -uroot password "Rcs551552?"
}

Mysql_Config

EOF

2、导入环境变量

echo 'PATH=/application/mysql/bin/:$PATH' >>/etc/profile
source /etc/profile
/etc/init.d/mysqld {start|restart}

3、配置文件编写

cat >> /etc/my.cnf << EOF
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data/
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log-bin=/application/mysql/data/mysql-bin
binlog_format=row
skip-name-resolve
server-id=3306
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=1
EOF

/etc/init.d/mysqld restart

4、登录

mysql -uroot -pRcs551552?

二、创建多实例

1、创建多个目录

mkdir -p /data/330{7,8}
chown -R mysql:mysql /data

2、配置多个配置文件

cat >> /data/3307/my.cnf << EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin
binlog_format=row
socket=/data/3307/mysql.sock
skip-name-resolve
log-error=/data/3307/mysql.log
gtid_mode=on
enforce-gtid-consistency=on
log-slave-updates=1
EOF

cp /data/3307/my.cnf /data/3308/my.cnf
sed -i 's/3307/3308/g' /data/3308/my.cnf

chown -R mysql:mysql /data/3307/my.cnf
chown -R mysql:mysql /data/3308/my.cnf

3、初始化

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307 --user=mysql

/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308 --user=mysql

4、开启mysql--3307

mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3307/my.cnf &

5、登录mysql--3307

mysql -S /data/3307/mysql.sock

mysql -S /data/3308/mysql.sock

三、验证结果

1、在主进行用户授权

[root@centos74-ren-gtid ~]# mysql -uroot -pRcs551552?

mysql> grant replication slave on *.* to repl@'192.168.0.%' identified by '123';

2、从进行连接主数据库

3307实例：

[root@centos74-ren-gtid ~]# mysql -S /data/3307/mysql.sock

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.77',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

3308实例：

[root@centos74-ren-gtid ~]# mysql -S /data/3308/mysql.sock

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.77',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)