3、MySQL 8.0.20在Linux（centos 8）上搭建主从复制

文章目录

1 Master上配置1.1 配置Master库的/etc/my.cnf1.2 主要配置 2 Slave上配置2.1 配置Slave库的/etc/my.cnf2.2 主要配置 3 Master上创建用于主从复置的账号4 Slave上设置从库向主库同步5 创建数据库数据表

1 Master上配置

创建2个Mysql 数据库服务，并启动两个Mysql服务

1.1 配置Master库的/etc/my.cnf

[mysqld] bind-address = 0.0.0.0 basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql port=3306 # 修改加密方式（8.0+关键配置） default_authentication_plugin = mysql_native_password # Master # 开启日志 log-bin = mysql-bin # 日志格式按行同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 1 # 设置需要同步的数据库 binlog-do-db = testdb # 设置忽略系统库同步 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #character config

1.2 主要配置

# Master # 开启日志 log-bin = mysql-bin # 日志格式按行同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 1 # 设置需要同步的数据库 binlog-do-db = testdb # 设置忽略系统库同步 binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys

2 Slave上配置

2.1 配置Slave库的/etc/my.cnf

[mysqld] bind-address = 0.0.0.0 basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock user=mysql port=3306 # 修改加密方式（8.0+关键配置） default_authentication_plugin = mysql_native_password # Slave # 开启日志 log-bin = mysql-bin # 日志按照行进行数据同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 2 # 设置需要同步的数据库 replicate-do-db = testdb # 设置忽略系统库 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = sys # 设置忽略系统库 # binlog-ignore-db = mysql # binlog-ignore-db = information_schema # binlog-ignore-db = performance_schema # binlog-ignore-db = sys # 设置忽略系统库所有表 #replicate_wild_ignore_table = mysql.% #replicate_wild_ignore_table = information_schema.% #replicate_wild_ignore_table = performance_schema.% #replicate_wild_ignore_table = sys.% # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # #character config character_set_server=utf8mb4 # [mysqld_safe] log-error=/usr/local/mysql/data/mysql.log pid-file=/usr/local/mysql/data/mysql.pid # MySQL兼容问题，需要修改数据库的认证方式 # MySQL8.0版本默认的认证方式是caching_sha2_password # MySQL5.7版本则为mysql_native_password # default_authentication_plugin=mysql_native_password # include all files from the config directory !includedir /etc/my.cnf.d

2.2 主要配置

# Slave # 开启日志 log-bin = mysql-bin # 日志按照行进行数据同步 binlog-format = ROW # 设置服务id，主从不能一致 server-id = 2 # 设置需要同步的数据库 replicate-do-db = testdb # 设置忽略系统库 replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema replicate-ignore-db = sys

3 Master上创建用于主从复置的账号

mysql -uroot -p123456 -h192.168.124.12 CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; # 授权主备复制专用账号 GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%'; #显示账户的权限 show grants for sync; # 刷新权限 flush privileges; # 确认位点，记录下文件名及位点 show master status;

4 Slave上设置从库向主库同步

# 首先切换到从库登录 mysql -uroot -p123456 -h192.168.124.11 -P3306 # 先停止同步 stop slave; # 修改从库指向到主库，使用上一步记录的文件名以及位点 CHANGE MASTER TO master_host='192.168.124.12', master_port=3306, master_user='sync', master_password='123456', master_log_file='mysql-bin.000011', master_log_pos=3214; # 启动同步 start slave; # 查看slave 的状态 show slave status;

需要注意的是上方的两个字段都为YES才算成功。

5 创建数据库数据表

CREATE SCHEMA `testdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ; CREATE TABLE `testdb`.`t_user` ( `id` BIGINT NOT NULL, `name` VARCHAR(45) NOT NULL, `age` INT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

需要注意的是两边的数据库和表要使用相同的字符集编码