MySql主从复制、主主复制

梦醒_提笔 2020-11-10 15:20:35
Mysql 复制 主从复制 主从


  • 详见《高性能MySql》第三版第10章复制
  • 复制不是备份,也不能代替备份,请定期做好数据库备份工作
  • 假设两台服务器,192.168.0.1(主)和192.168.0.2(从)

主从复制

1.1. 创建复制账号

主库上执行:

mysql> grant replication slave, replication client on *.* to repl@'192.168.0.%' identified by 'password';

说明:

  • 'repl'为复制账号;192.168.0.%'表示把账号权限限制到本地网络提高安全性
  • replication slave为复制的权限,replication client监控和管理复制的权限,授权两个权限给同一个账号方便管理

1.2. 配置主库从库

1.2.1. 主库my.cnf增加或修改如下配置

 

log_bin=mysql-bin #指定二进制日志名称
server_id=10 #服务器id
binlog_format=row #二进制日志格式改为行row模式

 

1.2.2. 从库my.cnf增加或修改如下配置

 

log_bin=mysql-bin #指定二进制日志名称
server_id=20 #服务器id
binlog_format=row #二进制日志格式改为行row模式
relay_log=/var/lib/mysql/mysql-relay-bin #指定中继日志的位置和命名
log_slave_updates=1 #允许备库将其重放的事件也记录到自身的二进制日志中
read_only=1 #只读

说明:

  • 服务器id必须明确指定一个唯一的id,服务器默认id通常为1,为了避免冲突最好采用有意义的约定并遵循(如ip地址);
  • *_do_db(如:binlog_do_db)和*_ignore_db(如:replicate_ignore_db)可能会导致执行或忽略错误的语句,不推荐使用。

1.3. 修改配置后重启主从服务器

1.3.1. 重启

systemctl restart mysqld

1.3.2. 校验重启是否正常

查看所有日志配置命令:

mysql> show global variables like '%log%';

log_error记录启动、运行或停止时出现的问题,一般也会记录警告信息。

查看错误日志位置:

mysql> show global variables like 'log_error';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| log_error | /var/lib/mysql/localhost.localdomain.err |
+---------------+------------------------------------------+
1 row in set (0.00 sec)

查看最后100行日志:

tail -100 /var/lib/mysql/localhost.localdomain.err

如果日志有错误,修复错误后继续后续步骤。

1.4. 同步数据库初始数据

开始主从复制之前需要保证两台数据库数据一致,如果数据库都是新安装的或数据已经是一致的可以跳过此步骤。

MyISAM引擎不支持热备,InnoDB引擎支持热备。

1.4.1. MyISAM引擎备份

先加读锁:

 

mysql> flush tables with read lock;

 

然后拷贝物理文件到从库,最后解锁:

mysql> unlock tables;

 

1.4.2. InnoDB引擎备份

 

热备,导出sql文件:

 

mysqldump -uroot -p --single-transaction --master-data=2 --databases dbname --routines > /backup/mysql/dbname_`date '+%Y-%m-%d'`.sql

 

或者直接导出压缩文件:

 

mysqldump -u root -p --single-transaction --master-data=2 --databases dbname --routines | gzip > /backup/mysql/dbname_`date '+%Y-%m-%d'`.sql.gz

 

说明:

  • --single-transaction: 基于此选项能实现热备InnoDB表;因此,不需要同时使用--lock-all-tables;
  • --master-data=2  记录备份那一时刻的二进制日志的位置,并且注释掉,1是不注释的 ;这个后面有用到
  • --databases dbname 指定备份的数据库
  • --all-databases,-A 转储所有数据库中的所有表
  • --routines,-R 导出存储过程以及自定义函数
  • 一般数据库会MyISAM引擎和InnoDB引擎共同使用,这时MyISAM引擎多用在数据读多写少的表;如果可以确定MyISAM引擎的表不会被修改就可以直接采用InnoDB热备的方法来备份,反之,可以先执行'flush tables with read lock'命令加锁后导出。
  • 直接导出压缩后的sql文件更小更方便传输,后面会介绍直接导入压缩的sql文件的方法;据说当数据量很大时直接导入压缩后的sql文件要快很多;个人测试500MBsql原文件,压缩后大小50MB,导入sql原文件和直接导入压缩后的文件速度差不多,约2分30秒;以后遇到数据量大的情景可以先试验下,以便生产操作时节约时间。

 

远程拷贝备份文件到从库:

 

scp /backup/mysql/dbname_20161213.sql root@192.168.0.2:/backup/mysql/

 

导入sql文件到从库:

 

mysql -u root -p dbname < /backup/mysql/dbname_2016-12-13.sql

 

或者登陆mysql后采用source命令导入:

 

mysql> source /backup/mysql/dbname_2016-12-13.sql;

 

如果直接导入压缩的sql文件,命令为:

 

gunzip -c dbname_2016-12-13.sql.gz | mysql -uroot -p

 

1.5. 查看主服务器状态和二进制日志信息

 

主库上执行:

 

mysql> show master status;

 +------------------+----------+--------------+------------------+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000001 | 120 | | |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

 

记录当前二进制日志文件名(File=mysql-bin.000001)和当前二进制日志位置(Position=120),后面会用到;如果采用的是上面InnoDB热备的方式,可以在导出的文件前面也可以看到注释掉的二进制文件名和日志位置信息,类似下面这样:

 

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

--
-- Current Database: `dbname`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `dbname`;
...

 

1.6. 启动复制

 

用到上面记录的二进制日志文件名(File=mysql-bin.000001)和当前二进制日志位置(Position=120)

从库上执行:

 

mysql> change master to master_host='192.168.0.1', master_user='repl', master_password='password',master_log_file='mysql-bin.000001',master_log_pos=120;
mysql> start slave;

 

查看从服务器状态:

从库上执行:

 

mysql> show slave status\G

 

会看到如下信息:

 

*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.16.1.202
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000005
 Read_Master_Log_Pos: 4284
 Relay_Log_File: mysql-relay-bin.000002
 Relay_Log_Pos: 2447
 Relay_Master_Log_File: mysql-bin.000005
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 ...

 

如果看到'Slave_IO_Running: Yes  Slave_SQL_Running: Yes' 则表示主从复制操作成功。

 

也可以查看主从服务器线程状态:

主/从库上执行:

 

mysql> show processlist\G

 

从库上会看到:

 

*************************** 2. row ***************************
 Id: 3
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 17480
 State: Waiting for master to send event
 Info: NULL
*************************** 3. row ***************************
 Id: 4
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 17099
 State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL

 

主库上会看到:

 

*************************** 1. row ***************************
 Id: 2
 User: repl
 Host:
 db: NULL
Command: Binlog Dump
 Time: 17604
 State: Master has sent all binlog to slave; waiting for binlog to be updated
 Info: NULL

二 主主复制(主动-被动模式下的主-主复制)

 

把主从复制反过来再做一遍就是主主复制了,这里不在重复操作,只说下要注意的地方

 

2.1. 这里列出主主复制配置

 

主库my.cnf增加或修改如下配置

 

log_bin=mysql-bin #指定二进制日志名称
server_id=10 #服务器id
binlog_format=row #二进制日志格式改为行row模式
relay_log=/var/lib/mysql/mysql-relay-bin #指定中继日志的位置和命名
log_slave_updates=1 #允许备库将其重放的事件也记录到自身的二进制日志中
read_only=0 #可读写

 

另一台“主”库my.cnf增加或修改如下配置

 

log_bin=mysql-bin #指定二进制日志名称
server_id=20 #服务器id
binlog_format=row #二进制日志格式改为行row模式
relay_log=/var/lib/mysql/mysql-relay-bin #指定中继日志的位置和命名
log_slave_updates=1 #允许备库将其重放的事件也记录到自身的二进制日志中
read_only=1 #只读

 

说明:

  • 主主复制下两台都可以读写的模式叫“主动-主动模式下的主-主复制”,不推荐使用;一台可以读写另一台只可以读的模式叫“主动-被动模式下的主-主复制”,推荐使用,方便主服务器故障时做切换,执行'mysql-> set global read_only=0'命令可以把库设置为读写模式;
  • 主动-主动模式下的主-主复制存在一个问题,如:数据库字段a=1,现在执行两条sql,一条是set a=a+1,一条是set a=a*2; a+1先在db1服务器上执行结果是1+1=2,a*2在服务器db2执行结果是1*2=2;然后db1同步db2的sql执行*2的操作,也就是2*2=4,db2也同步db1的sql执行+1的操作,也就是 2+1=3;最终导致两台机器的最后结果不一样,一个是4 一个是3。因为复制是异步的,所以会存在延时,如果主从都可以写数据就有可能破坏数据的一致性。

 

注意:

设置read_only=1为只读模式,可以限定普通用户进行数据修改的操作,但不会限定具有super权限的用户的数据修改操作; 严格控制数据库用户的权限是一个很好的习惯.

 

一般应用连接数据库的账号创建示例(没用super权限):

 

mysql-> GRANT SELECT,INSERT,DELETE,UPDATE,CREATE,INDEX,ALTER,CREATE VIEW,SHOW VIEW,EXECUTE ON database.* TO username@'%' IDENTIFIED BY 'password';

三 日常维护

 

3.1. 开启、停止、清除复制

 

开启主从,在从服务器上执行:

 

mysql> start slave;

 

停止主从,在从服务器上执行:

 

mysql> stop slave;

 

清除主从,在从服务器上执行:

 

mysql> stop slave;
mysql> change master to master_host=' '; #引号中间有个空格,没有空格会报错
mysql> reset slave;

 

可以用show slave status\G 命令在从服务器查看主从复制状态是否停止

 

3.2. 测量复制延迟

 

在从库上使用"show slave status\G"命令输出的“Seconds_Behind_Master”理论上显示了从库的延时。

Percona Toolkit工具包里的pt-heartbeat是测量复制延迟最流行的一种实现。

 

3.3. 复制数据一致性校验

 

采用Percona Toolkit工具包里的pt-table-checksum检验一致性

 

3.4. 修复不一致的数据

 

采用Percona Toolkit工具包里的pt-table-sync修复不一致的数据

 

3.5. 出现报错,需要跳过报错

 

mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

四 附录

 

4.1. Percona Toolkit

 

官方文档:https://www.percona.com/doc/percona-toolkit/LATEST/index.html

博文参考:http://bbs.chinaunix.net/thread-3751657-1-1.html

博文参考:https://segmentfault.com/a/1190000004309169

博文参考:http://blog.csdn.net/wulantian/article/details/12062005

版权声明
本文为[梦醒_提笔]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4598896/blog/4711259

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云