配置 MySQL 主从复制

sqlercn | | 访问(100)

  主DB服务器,必须开启二进制日志

  一、主数据库master修改:

  1、配置主数据库服务器

vim /etc/my.cnf
server_id = 1 # 设置server-id,做主从同步的时候需要将server-id号码设置不一样才行
# 保存my.cnf
show variables like 'server_id';# server_id的查看
service mysqld restart # 重启MySQL

  2、在主DB服务器上建立复制账号

  建立复制账号

  创建用户并授权:用户:repl 密码:A36@1#2x3A

CREATE USER 'repl'@'47.104.104.54' WITH mysql_native_password IDENTIFIED BY 'A36@1#2x3A';# 创建用户(IP为可访问该master的IP,任意IP就写'%')
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'47.104.104.54';# 分配权限(IP为可访问该 master的IP,任意IP就写'%')
flush privileges;# 刷新权限
SHOW MASTER STATUS;# 查看master状态,记录二进制文件名(File = binlog.000009)和位置(Position = 1749):

  二、从服务器slave修改:

  1、配置从数据库服务器

vim /etc/my.cnf
server_id = 2# 设置server-id,做主从同步的时候需要将server-id号码设置不一样才行
# 保存my.cnf
show variables like 'server_id';# server_id的查看
service mysqld restart # 重启MySQL

  2、重启mysql,打开mysql会话,执行同步SQL语句(需要主服务器主机名,登陆凭据,二进制文件的名称和位置):

CHANGE MASTER TO
 MASTER_HOST = '140.143.240.18',# 主服务器IP
 MASTER_USER = 'repl',# 主服务器MySQL账号
 MASTER_PASSWORD = 'A36@1#2x3A',# 主服务器MySQL密码
 MASTER_PORT = 3306,
 MASTER_LOG_FILE = 'binlog.000009',# (File = binlog.000009)
 MASTER_LOG_POS = 1749;# 位置(Position = 1749)
 
start slave; # 启动连路
stop slave; # 关闭连路
show slave status\G;# 查看slave状态

  当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。

  接下来就可以进行一些验证了,比如在主master数据库的test数据库的一张表中插入一条数据,在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效,还可以关闭slave,然后再修改master,看slave是否也相应修改(停止slave后,master的修改不会同步到slave),就可以完成主从复制功能的验证了。

  Slave_IO_Running: Connecting 的原因

  1、网络不通

  2、密码不对

  3、pos不正确

  4、ID问题

  ID的问题,在安装完mysql数据库的时候默认他们的server-id=1 但是在做主从同步的时候需要将ID 号码设置不一样才行。

  注意事项:

CREATE USER 'repl'@'%' IDENTIFIED BY '密码'; # MySQL 8.0中,caching_sha2_password 默认的密码加密方式是sha2

  改成

CREATE USER 'repl'@'%' WITH mysql_native_password IDENTIFIED BY '密码';# 这里我们需要 使用mysql_native_password方式