MySql主从复制
1.复制的基本原理
slave会从master读取binlog来进行数据同步
三步骤+原理图
MySQL复制三步骤:
- master将写操作记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events;
- slave将master的binary log events拷贝到它的中继日志(relay log); 从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后SQL线程会读取relay-log日志的内容并应用到从服务器,从而使从服务器和主服务器的数据保持一致!
- slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的,而且重启后从接入点开始复制。
复制的最大问题(延时)
2.复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个salve
3.一主一从常见配置
3.1.主机配置文件
建议mysql版本一致且后台以服务运行
logbin格式:
binlog_format=STATEMENT(默认):数据操作的时间,同步时不一致
binlog_format=ROW:批量数据操作时,效率低
binlog_format=MIXED:是以上两种level的混合使用,有函数用ROW,没函数用STATEMENT,但是无法识别系统变量
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
binlog-do-db=mytestdb
binlog_format=STATEMENT
先不要创建mytestdb数据库。主从搭建好了再创建
3.2.从机配置文件
主从所有配置项都配置在[mysqld]节点下,且都是小写字母
[必须]从服务器唯一ID:server-id=2
[可选]启用中继日志:relay-log=mysql-relay
server-id=2
relay-log=mysql-relay
3.3.主从机重启配置生效
systemctl restart mysqld
3.4.主从机都关闭防火墙
systemctl stop firewalld
3.5.在主机上建立帐户并授权slave
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
flush privileges;
grant all privileges on *.* to root@'%' identified by 'root';
flush privileges;
给root 用户授予远程访问权限
查询master的状态: show master status;
记下File和Position的值
执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
3.6.在Linux从机上配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='主机ip地址',
MASTER_USER='X',MASTER_PASSWORD='X',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
启动从服务器复制功能
start slave;
show slave status\G;
下面两个参数都是Yes,则说明主从配置成功!
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果出现:Slave_IO_Running:No
则可能的解决办法是:
1)停止stop slave; 再启动start slave;看是否能正常运行 2)两个服务器的防火墙是否关闭,是否互相能ping通
3)配置文件是否正确、是否重启了服务器
4)连接主机的语句是否正确
5) 可能是uuid 一致(master,slave uuid) vim
/var/lib/mysql/auto.cnf 下更改uuid,重启服务
3.7.主机新建库、新建表、insert记录,从机复制
CREATE DATABASE mytestdb;
CREATE TABLE mytbl(id INT,NAME VARCHAR(16));
INSERT INTO mytbl VALUES(1, 'zhang3');
INSERT INTO mytbl VALUES(2, 'li4');
3.8.如果停止从服务复制功能,重新配置主从
在从机上执行。功能说明:停止I/O 线程和SQL线程的操作。
mysql> stop slave;
在从机上执行。功能说明:用于删除SLAVE数据库的relaylog日志文件,并重新启用新的relaylog文件。
mysql> reset slave;
在主机上执行。功能说明:删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件。
用于第一次进行搭建主从库时,进行主库binlog初始化工作;
mysql> reset master;