MYSQL主从复制

主从复制原理 : 通过在主库记录二进制日志, 在从库重放日志.

复制如何工作

1.在主库更改记录到二进制日志

2.备库将主库的日志复制到自己的中继日志

3.备库读取中继日志的事件, 将其重放到备库数据之上.

实际操作

主库是阿里云 : 112.74.20.150 ,

备库是腾讯云 : 119.29.108.230

复制是大部分是向后兼容的, 也就是说新版本的mysql可以作为老版本mysql的备库, 反过来就不行了.

主服务器开启日志记录: 在配置文件 my.cnf 中 [mysqld] 添加下面两行 :

log_bin = mysql-bin
server_id = 10

server_id 必须唯一, 默认指定是 1. 我这里改成了 10.

重启之后, 用 show variables like '%log_bin%'; 查看, 发现log_bin已经开启.

备库上的修改大致相同

log_bin = mysql-bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

relay_log 指定中继日志位置和名字;

log_slave_updates = 1 允许备库将其重放到事件也记录在自身到二进制日志中 , 用与环形复制;

read_only 阻止没有特权到线程修改数据.

在主库上创建一个专门用于复制的测试帐号

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'%' IDENTIFIED BY '1234';

show master status; 记录下 FIle 和 Position 的值

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      489 |              |                  |
+------------------+----------+--------------+------------------+

在从库上开始基本的复制命令

CHANGE MASTER TO MASTER_HOST = '112.74.20.150',
MASTER_USER = 'slave',
MASTER_PASSWORD = '1234',
MASTER_LOG_FILE = 'mysql-bin.000007',
MASTER_LOG_POS = 489;

先关闭复制 stop slave; 再执行复制操作 start slave; 这样就可以实现复制操作.

这里有两点注意:

mysql 默认只能12.7.0.0.0访问. 需要修改配置文件

#bind-address           = 127.0.0.1       这一行注释掉

如果复制失败, 主库上可以使用 flush logs; 刷新日志.

在主库上可以看到由备库I/O线程向主库发起的连接

mysql> show processlistG
*************************** 1. row ***************************
     Id: 51
   User: slave
   Host: 119.29.108.230:39265
     db: NULL
Command: Binlog Dump
   Time: 1477
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

在备库上也可以看到两个线程, 一个I/O线程, 一个SQL线程.

mysql> show processlistG
*************************** 1. row ***************************
     Id: 41
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1548
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 42
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 1483
  State: Slave has read all relay log; waiting for the slave I/O thread to update it