mysql8.0单机多实例主从搭建

1.单机配置mysql8.0

配置文档：mysql8单机多实例安装教程

2.配置单机多实例主库与从库的my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[client]
default-character-set=utf8mb4

[mysqld]
user=mysql
basedir=/usr/app/mysql/mysql-8.0
lower_case_table_names=1

[mysqld_multi]
mysqld=/usr/app/mysql/mysql-8.0/bin/mysqld_safe
mysqladmin=/usr/app/mysql/mysql-8.0/bin/mysqladmin
log=/usr/app/mysql/mysql-8.0/mysqld_multi.log

# 3306 数据库实例
[mysqld3306]
port=3306
server_id=1
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/app/mysql/mysql-8.0/3306/data
socket=/tmp/mysql_3306.sock
log-error=/usr/app/mysql/mysql-8.0/3306/log/error.log
pid-file=/usr/app/mysql/mysql-8.0/3306/mysql3306.pid

## skip-grant-tables #用于跳过密码登录
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true

# 主从复制之主库配置
## 启动MySQ二进制日志系统
log-bin=/usr/app/mysql/mysql-8.0/3306/mysql-bin
## 选择row模式 
binlog-format=ROW
## 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
binlog-do-db=gupao
## 不同步mysql系统数据库
binlog-ignore-db=mysql

# 3307 数据库实例
[mysqld3307]
port=3307
server_id=2
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/app/mysql/mysql-8.0/3307/data
socket=/tmp/mysql_3307.sock
log-error=/usr/app/mysql/mysql-8.0/3307/log/error.log
pid-file=/usr/app/mysql/mysql-8.0/3307/mysql3307.pid
# lc_messages_dir=/usr/local/mysql/share/english

## 默认最大连接数设置
# max_connections=300
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true

# 3308 数据库实例
[mysqld3308]
port=3308
server_id=3
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/app/mysql/mysql-8.0/3308/data
socket=/tmp/mysql_3308.sock
log-error=/usr/app/mysql/mysql-8.0/3308/log/error.log
pid-file=/usr/app/mysql/mysql-8.0/3308/mysql3308.pid
# lc_messages_dir=/usr/local/mysql/share/english

character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true

# 3309 主从复制之从数据库
[mysqld3309]
port=3309
server_id=4
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/usr/app/mysql/mysql-8.0/3309/data
socket=/tmp/mysql_3309.sock
log-error=/usr/app/mysql/mysql-8.0/3309/log/error.log
pid-file=/usr/app/mysql/mysql-8.0/3309/mysql3309.pid
# lc_messages_dir=/usr/local/mysql/share/english

character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'
lower_case_table_names=1
explicit_defaults_for_timestamp=true

## 从库配置
log-bin=/usr/app/mysql/mysql-8.0/3309/mysql-bin
## 选择row模式 
binlog-format=ROW
## 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行
replicate-do-db=gupao
## 不同步mysql系统数据库
replicate-ignore-db=mysql

3.初始化从库

# 1.创建从库所需目录和文件并赋权
mkdir /usr/app/mysql/mysql-8.0/3309/{data,log}
touch /usr/app/mysql/mysql-8.0/3309/log/error.log
## 赋权
chown -R mysql:mysql /usr/app/mysql

# 2.初始化数据库
/usr/app/mysql/mysql-8.0/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/usr/app/mysql/mysql-8.0/3309/data/ --initialize
## 登录并启动mysql
mysqld_multi --defaults-file=/etc/my.cnf start 3309
## 服务器登录输入密码（密码为初始化数据时的临时密码）
mysql -uroot -p -S /tmp/mysql_3309.sock

## 3.修改本地和远程访问密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql@123';
use mysql;
update user set host='%' where user='root';
flush privileges;
GRANT ALL ON *.* TO 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Mysql@123';
flush privileges;

# 连接主库配置从库账号访问权限
## 1.主库设置
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'repl'@'%' PASSWORD EXPIRE NEVER;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

## 2.从库设置
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.235.120',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=156;
start slave;

## 3.查看同步状态（mysql服务器连接时使用）
SHOW SLAVE STATUS \G

注意：同步状态信息重点内容：Slave_IO_Running 和 Slave SQL Running 都为 yes 表示主从设置成功。

4.查看主从复制binlog相关

-- 查看主mysql服务 binlog属性
-- 查看mysql log_bin
show variables like 'log_bin%';

-- 查看 binlog 格式
show global variables like '%binlog_format%';

-- 查看 binlog 列表
show binary logs;

-- 单个binlog文件最大字节数
show variables like 'max_binlog_size';

-- 查看binlog文件内容
show binlog events in 'mysql-bin.000001';

5.测试

-- 创建测试数据库和相应的表结构（例如本次主从同步数据库为：gupao）
-- 建表（主库和从库都要创建）
CREATE TABLE `dict` (
  `id` int DEFAULT NULL,
  `param_code` varchar(255) DEFAULT NULL,
  `param_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 在主库中插入测试数据（主库执行）
INSERT INTO `dict`(`id`, `param_code`, `param_name`) VALUES (330, '330', '330');
INSERT INTO `dict`(`id`, `param_code`, `param_name`) VALUES (331, '331', '331');

测验结果：连接从库执行查询时，可以看到插入的数据表示成功。

其他事项1：主库插入完毕数据后，发现从库中没有数据，此时需要检查从库中的数据库和同步的表是否创建成功，再检查同步状态（从库无对应表会导致同步服务关闭，SHOW SLAVE STATUS;（外部MySQL可视化工具执行）查看时，Slave_IO_Running：Yes，Slave_SQL_Running：NO），之后就是启动同步服务：start slave;

其他事项2：防火墙端口开放

firewall-cmd --zone=public --add-port=3309/tcp --permanent
## 使配置生效
firewall-cmd --reload
## 查看开放端口列表
firewall-cmd --list-port

以上就是mysql8单机多实例主从同步采坑记录。