MySQL5.7 多主多从架构实现

原创

Jungout 2022-01-23 11:20:10 博主文章分类：DataBase ©著作权

文章标签 mysql 数据库 mysql主从 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者Jungout的原创作品，请联系作者获取转载授权，否则将追究法律责任

【文档】mysql5.7 多主多从架构实现

一、角色划分

1、Mysql数据库规划

主机名	IP地址	角色	Mysql_server_id
master01	172.16.59.21	masterA	21
master02	172.16.59.22	masterB	22
slave01	172.16.59.23	slaveA	23
slave02	172.16.59.24	slaveB	24

二、初始化环境配置

## 1.修改主机名(各主机分别操作)
hostnamectl set-hostname master01
hostnamectl set-hostname master02
hostnamectl set-hostname slave01
hostnamectl set-hostname slave02

## 2.添加主机名的域名解析（所有节点操作）
cat <<EOF > /etc/hosts
172.16.59.21  master01  
172.16.59.22  master02  
172.16.59.23  slave01  
172.16.59.24  slave02  
EOF

三、编译部署数据库

参考链接：https://blog.csdn.net/sinat_33982461/article/details/85887378

## 1.下载mysql.tar.gz安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

## 2.解压安装包至opt目录下并重命名
tar -xzvf  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  -C /opt
mv /opt/mysql-5.7.34-linux-glibc2.12-x86_64 /opt/mysql5.7

## 3.创建mysqlu用户和组
groupadd mysql && useradd -g mysql mysql
mkdir -p /opt/mysql5.7/{data,tmp,logs}

## 4.修改 my.cnf配置文件
cat <<EOF > /etc/my.cnf
[mysqld]
server-id=1
socket=/opt/mysql5.7/tmp/mysql.sock
character-set-server=utf8
datadir=/opt/mysql5.7/data
log_bin=/opt/mysql5.7/logs/mysql-bin
expire_logs_days=10
lower_case_table_names=1

[client]
socket=/opt/mysql5.7/tmp/mysql.sock

EOF

## 5.修改 mysql.server配置文件
vi /opt/mysql5.7/support-files/mysql.server
46 basedir=/opt/mysql5.7
47 datadir=/opt/mysql5.7/data

### 5.1 拷贝启动文件到系统服务目录
cp /opt/mysql5.7/support-files/mysql.server /etc/init.d/mysql

### 5.2 修改目录及文件权限
chown -R mysql:mysql /opt/mysql5.7
chown mysql:mysql /etc/my.cnf
chown mysql:mysql /etc/init.d/mysql

# 6.添加全局环境变量
cat <<EOF >> /etc/profile
MYSQL_HOME=/opt/mysql5.7
export PATH="\$PATH:\$MYSQL_HOME/bin:"
EOF

source /etc/profile

## 7.初始化MySQL数据库
### 7.1初始化数据库
su - mysql
mysqld --initialize --user=mysql --basedir=/opt/mysql5.7 --datadir=/opt/mysql5.7/data --socket=/opt/mysql5.7/tmp/mysql.sock >>/opt/mysql5.7/logs/install.log 2>&1

### 7.2查看数据库密码
cat /opt/mysql5.7/logs/install.log | grep 'root@localhost' | awk -F ':' '{print $4}'

### 7.3启动并进入数据库密码
service mysql start
mysql -uroot -p
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234,abcd');

## 8.开机自启
chkconfig --add mysql
chkconfig --level 345 mysql on

五、修改my.cnf文件进行双主配置

配置文件详情可参阅 MySQL参考手册

[mysqld]
server-id=22
socket=/opt/mysql5.7/tmp/mysql.sock
character-set-server=utf8
datadir=/opt/mysql5.7/data

# MySQL的日志文件的名字
log_bin=/opt/mysql5.7/logs/mysql-bin

expire_logs_days=10
lower_case_table_names=1
binlog_format=MIXED
log-slave-updates=1

auto-increment-increment=2
auto-increment-offset=2                  #起始位置设置为2

# MySQL的日志文件的名字
log-bin=mysql_bin

# 作为从库时 更新操作是否写入日志 on：写入  其他数据库以此数据库做主库时才能进行同步
log-slave-updates=on

# MySQL系统库的数据不需要同步 我们这里写了3个  更加保险
# 同步数据时忽略一下数据库 但是必须在使用use db的情况下才会忽略；如果没有使用use db 比如create user  数据还是会同步的
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 使用通配符忽略MySQL系统库的表  这样在create user时也不会进行同步了
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%

# MySQL系统库的日志不计入binlog 这样更加保险了
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

[client]
socket=/opt/mysql5.7/tmp/mysql.sock

1.Master01配置

[mysqld]
server-id=21
socket=/opt/mysql5.7/tmp/mysql.sock
character-set-server=utf8
datadir=/opt/mysql5.7/data
log_bin=/opt/mysql5.7/logs/mysql-bin
expire_logs_days=10
lower_case_table_names=1
log-bin=mysql-bin
binlog_format=MIXED
log-slave-updates=1
auto-increment-increment=2
auto-increment-offset=1                 #起始位置设置为1

[client]
socket=/opt/mysql5.7/tmp/mysql.sock

2.Master02配置

[mysqld]
server-id=22
socket=/opt/mysql5.7/tmp/mysql.sock
character-set-server=utf8
datadir=/opt/mysql5.7/data
log_bin=/opt/mysql5.7/logs/mysql-bin
expire_logs_days=10
lower_case_table_names=1
log-bin=mysql-bin
binlog_format=MIXED
log-slave-updates=1
auto-increment-increment=2
auto-increment-offset=2                  #起始位置设置为2

[client]
socket=/opt/mysql5.7/tmp/mysql.sock

3.双主配置

## 重启并进入mysql
service mysql restart
mysql -uroot -p

## 授权对方访问数据库
grant replication slave on *.* to 'replm'@'172.16.59.21' identified by 'huangyuefeng';
flush privileges;
SELECT user,host FROM mysql.user;

## 查看对方master主机的binlog和pos
show master status;
    ...
    | mysql-bin.000002 |      154 |
    ...

## 配置对方的binlog和pos
CHANGE MASTER TO
MASTER_HOST='172.16.59.21',
MASTER_USER='replm',
MASTER_PASSWORD='huangyuefeng',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;

## 启动备份并查看状态
start slave;
show slave status\G
    ...
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    ...

六、为每台主节点配置从节点

1.Master01配置

## 授权从机访问连接
grant replication slave on *.* to 'repl'@'172.16.59.23' identified by 'huangyuefeng';
flush privileges;

show master status;
    | mysql-bin.000007 |     1683 |

2.Master02配置

## 授权从机访问连接
grant replication slave on *.* to 'repl'@'172.16.59.24' identified by 'huangyuefeng';
flush privileges;

show master status;
    | mysql-bin.000007 |     1683 |

3.Slave01配置

CHANGE MASTER TO
MASTER_HOST='172.16.59.21',
MASTER_USER='repl',
MASTER_PASSWORD='huangyuefeng',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=1683;

start slave;
show slave status\G

4.Slave02配置

CHANGE MASTER TO
MASTER_HOST='172.16.59.22',
MASTER_USER='repl',
MASTER_PASSWORD='huangyuefeng',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=1683;

start slave;
show slave status\G