接着上边一篇
如下为我的环境参数
| 主机 | Master1 | Master2 |
| 系统 | Win7系统 | Win10系统 |
| 数据库id | Server_id = 1 | Server_id = 2 |
| ip地址 | 192.168.2.193 | 192.168.2.132 |
Master1机器和Master2机器各自的配置文件my.ini中的server_id参数一定不能一样。
这里设置Master1的server_id=1。
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\MySQL
datadir = D:\MySQL\data
port = 3306
server_id = 1
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES配置后重新启动mysql服务。
这里设置Master2的server_id=2。
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\MySQL
datadir = D:\MySQL\data
port = 3306
server_id = 2
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES配置后重新启动mysql服务。
在搭建之前一定要确保需要同步的数据库在master1和master2两台机器上内容、大小是一致的。
这里需要配置两次，即互为主从。
第一次主从配置master1是主机，master2是从机。
第二次主从配置master2是从机，master1是从机。
#1、连接master1数据库，查看日志记录位置相关信息
# 刷新日志
flush logs;
# 查看主机状态信息
show master status;show master status命令执行以后信息表格参数：
- File :当前正在记录的二进制日志文件
- Position ：记录偏移量，日志 mysql-bin.000008 所记录到的位置。
- Binlog_Do_DB ：要记录日志的数据库
- Binlog_Ignore_DB ：不记录日志的数据库
- Executed_Gtid_Set ：已执行的事务ID
#2、查看二进制日志情况
show variables like '%log_bin%';#3、连接master1数据库 为master2机器创建slave账户。
# 创建用户
mysql> create user "repl_salve"@"192.168.2.193" identified by "d514d514";
Query OK, 0 rows affected (0.02 sec)
# 授权
mysql> grant replication slave on *.* to 'repl_salve'@'192.168.2.193';
Query OK, 0 rows affected (0.02 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#4、登陆master1获取数据日志记录位置相关信息
表格里的参数后边要用到。
#5、连接master2数据库，用在master1创建的同步账号连接到master1主库。
# 命令1，连接主库。
# 说明：
# MASTER_HOST 主节点ip
# MASTER_USER 用户名
# MASTER_PASSWORD 密码
# MASTER_LOG_FILE 143服务正在记录的二进制日志文件（实时获取）
# MASTER_LOG_POS 143服务记录二进制日志的偏移量（实时获取）
CHANGE MASTER TO
MASTER_HOST='192.168.2.132',
MASTER_USER='repl_user',
MASTER_PASSWORD='d514d514',
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=1708;
# 启动从机
start slave;一定要start slave；
#6、 查看master2（从库）的同步信息
mysql 服务端输入：show slave status /G
show slave status /G说明：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这两个参数的值都等于yes时，主从同步正常
这里如果报错：Authentication plugin ‘caching_sha2_password‘ cannot be loaded
修改后，需重新用 show matser status;来查看日志信息。
流程和4.1一样。查看master2的master状态。在master1启动slave。
#1、连接master2数据库，查看日志记录位置相关信息
# 刷新日志
flush logs;
# 查看主机状态信息
show master status;#2、查看二进制日志情况
show variables like '%log_bin%';#3、连接master2数据库 为master1机器创建slave账户。
# 创建用户
mysql> create user "repl_user"@"192.168.2.132" identified by "d514d514";
Query OK, 0 rows affected (0.02 sec)
# 授权
mysql> grant replication slave on *.* to 'repl_user'@'192.168.2.132';
Query OK, 0 rows affected (0.02 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#4、登陆master2获取数据日志记录位置相关信息
表格里的参数后边要用到。
#5、连接master1数据库，用在master2创建的同步账号连接到master2主库。
# 命令1，连接主库。
# 说明：
# MASTER_HOST 主节点ip
# MASTER_USER 用户名
# MASTER_PASSWORD 密码
# MASTER_LOG_FILE 143服务正在记录的二进制日志文件（实时获取）
# MASTER_LOG_POS 143服务记录二进制日志的偏移量（实时获取）
CHANGE MASTER TO
MASTER_HOST='192.168.2.193',
MASTER_USER='repl_user',
MASTER_PASSWORD='d514d514',
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=1624;
# 启动从机
start slave;#6、 查看master1（从库）的同步信息
show salve status \G;这里如果报错：Authentication plugin ‘caching_sha2_password‘ cannot be loaded
解决后，stop slave。重新配置。
我这里重新解决了，再配置一次。
# 停止主机
stop salve；
# 命令1，连接主库。
# 说明：
# MASTER_HOST 主节点ip
# MASTER_USER 用户名
# MASTER_PASSWORD 密码
# MASTER_LOG_FILE 143服务正在记录的二进制日志文件（实时获取）
# MASTER_LOG_POS 143服务记录二进制日志的偏移量（实时获取）
CHANGE MASTER TO
MASTER_HOST='192.168.2.193',
MASTER_USER='repl_user',
MASTER_PASSWORD='d514d514',
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=2441;
# 启动从机
start slave;再次，show slave status \G;都是yes。