mysql 多实例主从_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 /applicationif [ -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

elsemkdir-P /root/tools

exit1fi

###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.32ln-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)

3、在主数据库创建数据库，也会同步到3307与3308实例

主：

mysql> create database rencs;

3307与3308实例：