本文共 3419 字，大约阅读时间需要 11 分钟。

1 Master上配置

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

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

[mysqld]bind-address = 0.0.0.0basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlport=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 = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemabinlog-ignore-db = sys# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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 = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemabinlog-ignore-db = sys

2 Slave上配置

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

[mysqld]bind-address = 0.0.0.0basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlport=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 = mysqlreplicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-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 riskssymbolic-links=0# #character configcharacter_set_server=utf8mb4# [mysqld_safe]log-error=/usr/local/mysql/data/mysql.logpid-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 = mysqlreplicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-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;

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 = InnoDBDEFAULT CHARACTER SET = utf8mb4COLLATE = utf8mb4_unicode_ci;

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