mysql5.7双相主从复制，读写分离

一、部署mysql双相主从复制

服务器mysql-master：192.168.52.213

服务器mysql-slave：192.168.52.212

my.cnf参考配置，两台服务器一样

[root@mysql-master ~]# vim /etc/my.cnf
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld]
port=3306
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#开启查询缓存
explicit_defaults_for_timestamp=true

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
validate_password_policy=0
validate_password=off

1、检查uuid

查看datadir目录下的auto.cnf文件，两台服务器的uuid必须不一样

[root@mysql-master ~]# vim /var/lib/mysql/auto.cnf 

[auto]
server-uuid=669100dd-45fd-11e9-bcad-000c29a7ad71

[root@mysql-slave ~]# vim /var/lib/mysql/auto.cnf 

[auto]
server-uuid=669100dd-45fd-11e9-bcad-000c29a7ad72

2、配置mysql-master

在/etc/my.cnf的[mysqld]下追加配置

# 同步配置
server-id=213 #服务器id，两台服务器必须不一样
log-bin=mysql-bin-213 #打开日志
binlog-do-db=demo #要给从机同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改

重启mysql服务

systemctl restart mysqld

3、mysql-master为mysql-slave设置一个连接账户并授予REPLICATION SLAVE权限

grant replication slave on *.* to 账号@'mysql-slave的ip' identified by '密码';

mysql> grant replication slave on *.* to slave@'192.168.52.212' identified by 'slave';

刷新授权表

mysql> flush privileges;

4、导出mysql-master需要同步的database的数据，导入到mysql-slave

 导出数据

mysqldump -uroot -p demo > demo.sql

 发送给mysql-slave

scp demo.sql root@192.168.52.212:/root/

登陆mysql-slave

mysql -uroot -proot

导入数据

mysql> create database demo;
mysql> use demo;
mysql> source /root/demo.sql;

5、配置mysql-slave

在/etc/my.cnf的[mysqld]下追加配置

# 同步设置
server-id=212 #服务器id
log-bin=mysql-bin-212 #打开日志
binlog-do-db=demo #要给从机同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改

测试从账号是否能正常登陆

mysql -uslave -pslave -h192.168.52.213

停止从库，并指定从库的主库，打开从库

#登陆mysql-slave服务器的root账号
mysql -uroot -proot

mysql> stop slave;
mysql> change master to master_host='192.168.52.213',master_user='slave',master_password='slave';
mysql> start slave;
mysql> show slave status\G

至此主（mysql-master）------从（mysql-slave）搭建完成，反过来搭建方法一样

6、mysql-slave为mysql-master设置一个连接账户并授予REPLICATION SLAVE权限

mysql> grant replication slave on *.* to slave@'192.168.52.213' identified by 'slave';

刷新授权表

mysql> flush privileges;

7、mysql-master从设置

测试从账号是否能正常登陆

mysql -uslave -pslave -h192.168.52.212

停止从库，并指定从库的主库，打开从库

#登陆mysql-slave服务器的root账号
mysql -uroot -proot

mysql> stop slave;
mysql> change master to master_host='192.168.52.212',master_user='slave',master_password='slave';
mysql> start slave;
mysql> show slave status\G

至此主（mysql-slave）------从（mysql-master）搭建完成

双相主从复制搭建完成

二、读写分离

服务器mysql-router：192.168.52.211

1、下载mysqlrouter

wget https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-8.0.15-linux-glibc2.12-x86_64.tar.xz

2、解压

tar xvf mysql-router-8.0.15-linux-glibc2.12-x86_64.tar.xz

3、移动到/usr/local/mysqlrouter

mv mysql-router-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysqlrouter

4、配置环境变量

vim /etc/profile

 在最后追加

MYSQLROUTER_HOME=/usr/local/mysqlrouter
PATH=$PATH:$MYSQLROUTER_HOME/bin
export MYSQLROUTER_HOME PATH

source /etc/profile

 创建软连接

ln -s /usr/local/mysqlrouter mysqlrouter

5、配置mysqlrouter

在/etc下创建mysqlrouter目录

[root@mysql-router mysqlrouter]# vim /etc/mysqlrouter/mysqlrouter.cnf 

[DEFAULT]
logging_folder=/usr/local/mysqlrouter/log/
plugin_folder=/usr/local/mysqlrouter/lib/mysqlrouter/
runtime_folder=/usr/local/mysqlrouter/run/
config_folder=/etc/mysqlrouter

[logger]
level=info

[routing:slaves]
bind_address=mysql-router
bind_port=7001
destinations=mysql-master:3306,mysql-slave:3306
mode=read-only
max_connections=512
max_connect_errors=100
connect_timeout=1

[routing:masters]
bind_address=mysql-router
bind_port=7002
destinations=mysql-master:3306,mysql-slave:3306
mode=read-write
max_connections=512
max_connect_errors=100
connect_timeout=2

[keepalive]
interval=60

6、启动

[root@mysql-router init.d]# vim /etc/init.d/mysqlrouter

#!/bin/bash

# chkconfig: - 78 30
# Description: Start / Stop MySQL Router

DAEMON=/usr/local/mysqlrouter
proc=$DAEMON/bin/mysqlrouter
DAEMON_OPTIONS="-c /etc/mysqlrouter/mysqlrouter.conf"

. /etc/init.d/functions

start() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        action "MySQL Router is working" /bin/false
    else
        $proc $DAEMON_OPTIONS & &>/dev/null
        retval=$?
        echo
    if [ $retval -eq 0 ]; then
            touch /var/lock/subsys/mysqlrouter
        action "Starting MySQL Router" /bin/true
        else
        echo "Starting MySQL Router Failure"
        fi
    fi
}

stop() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        killall $proc
        retval=$?
        echo
        if [ $retval -eq 0 ]; then
            rm -f /var/lock/subsys/mysqlrouter
            action "Stoping MySQL Router" /bin/true
        fi
    else
        action "MySQL Router is not working" /bin/false
    fi
}

status() {
    if [ -e /var/lock/subsys/mysqlrouter ]; then
        echo "MySQL Router is running"
    else
        echo "MySQL Router is not running"
    fi
}

case "$1" in
    start)
        start
        sleep 1
        ;;
    stop)
        stop
        sleep 1
        ;;
    restart)
        stop
        start
        sleep 1
        ;;
    status)
        status
        ;;
    *)
        echo "Usage: $0 {start|stop|status|restart}"
        retval=1
        ;;
esac

exit $retval

赋予执行权限

chmod +x /etc/init.d/mysqlrouter

启动mysqlrouter

service mysqlrouter start

开机自启动

chkconfig --add mysqlrouter
chkconfig mysqlrouter on

7、备注

MySQL Router只能通过不同的端口来实现简单的读/写分离。

 

参考链接：

http://blog.itpub.net/31485142/viewspace-2157315/

https://www.cnblogs.com/JiangLe/p/6626660.html

https://www.linuxidc.com/Linux/2018-07/153057.htm