526互联

MySQL5.7双主安装

发布时间 2023-11-16 17:31:20作者: julian_chang

214、215 两台服务器

1.下载MySQL安装包

https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz

2.上传至服务器并解压

./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

3.配置文件 /etc/my.cnf

##214
[mysql]
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock

[mysqld]
skip-name-resolve
port=3306
socket=/var/lib/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql/data
max_connections=200
character-set-server=utf8mb4
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M

#集群
server-id=214
log-bin=master-bin
log-slave-updates=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 1
##215
[mysql]
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock

[mysqld]
skip-name-resolve
port=3306
socket=/var/lib/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql/data
max_connections=200
character-set-server=utf8mb4
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M

#集群
server-id=215
log-bin=master-bin
log-slave-updates=true
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 2

4.分别启动MySQL

service mysql start

日志中可看到root的密码 如：root@localhost: #Vh0+qwD9-dp ,登录后修改密码

set password for root@localhost = password('xxx');

5.两台服务器创建同步用户

grant replication slave on *.* to 'slave'@'172.10.0.%' identified by 'xxx';

flush privileges;

6.分别查看两台MySQL的binlog, File和Postion

show master status;

 7.分别指定对方master

change master to master_host='172.10.0.215',master_user='slave',master_password='xxx',master_log_file='master-bin.000007',master_log_pos=236676703;

change master to master_host='172.10.0.214',master_user='slave',master_password='xxx',master_log_file='master-bin.000007',master_log_pos=236676703;

8.分别开启主从并查看状态

start slave;
 -- stop slave;

show slave status\G;

 出现 ：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes   即完成。

9.创建函数时，另外一台报错

-- 函数报错的问题
SET GLOBAL log_bin_trust_function_creators=TRUE;

 

其它：

处理错误时，重启MySQL后需要开启主从

#重启MySQL
service mysql restart
#登录MySQL后
start slave;

服务器之间网络不通时，需要开通对应端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

导入数据报错时，导致数据不同步（可重复操作处理）

-- 数据报错，先停主从
stop slave;
-- 设置跳过这个冲突语句
set global sql_slave_skip_counter=1;
-- 再启动
start slave;

 

 

参考：

 https://blog.csdn.net/wuds_158/article/details/132895513