MySQL master slave replication, master master master replication

Wake up from a dream_ write 2020-11-10 15:20:35
mysql master slave replication master


  • See 《 High performance MySql》 Third edition No 10 Chapter copy
  • Replication is not a backup , It can't replace backup , Please do the database backup work regularly
  • Suppose two servers ,192.168.0.1( Lord ) and 192.168.0.2( from )

Master slave copy

1.1. Create a copy account

Execute on Main Library :

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

explain :

  • 'repl' To copy the account ;192.168.0.%' It means that the account permission is restricted to the local network to improve the security
  • replication slave For permission to copy ,replication client Rights to monitor and manage replication , Authorize two permissions to the same account to facilitate management

1.2. Configure master database and slave database

1.2.1. Main library my.cnf Add or modify the following configuration

 

log_bin=mysql-bin # Specify the binary log name 
server_id=10 # The server id
binlog_format=row # Binary log format changed to line row Pattern 

 

1.2.2. Slave Library my.cnf Add or modify the following configuration

 

log_bin=mysql-bin # Specify the binary log name 
server_id=20 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=1 # read-only 

explain :

  • The server id A unique must be specified id, Server default id Usually it is 1, In order to avoid conflict, it's best to use meaningful conventions and follow ( Such as ip Address );
  • *_do_db( Such as :binlog_do_db) and *_ignore_db( Such as :replicate_ignore_db) Statements that may cause errors to be executed or ignored , It is not recommended to use .

1.3. After modifying the configuration, restart the master-slave server

1.3.1. restart

systemctl restart mysqld

1.3.2. Check whether the restart is normal

View all log configuration commands :

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

log_error Record start 、 Problems in running or stopping , Warning messages are also recorded .

View error log location :

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)

Check out the last 100 Line logs :

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

If there is an error in the log , After fixing the error, continue with the next steps .

1.4. Synchronize database initial data

Before starting master-slave replication, it is necessary to ensure that the data of the two databases are consistent , If the database is newly installed or the data is already consistent, you can skip this step .

MyISAM The engine does not support hot standby ,InnoDB The engine supports hot standby .

1.4.1. MyISAM Engine backup

Read lock first :

 

mysql> flush tables with read lock;

 

Then copy the physical files to the slave library , Finally unlock :

mysql> unlock tables;

 

1.4.2. InnoDB Engine backup

 

Hot standby , export sql file :

 

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

 

Or export the compressed file directly :

 

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

 

explain :

  • --single-transaction: Based on this option, hot standby can be realized InnoDB surface ; therefore , You don't need to use it at the same time --lock-all-tables;
  • --master-data=2   Record the location of the binary log at the time of backup , And comment out ,1 It is not annotated ; This is useful for
  • --databases dbname Specify the database to back up
  • --all-databases,-A Dump all tables in all databases
  • --routines,-R Export stored procedures and custom functions
  • The general database will MyISAM The engine and InnoDB Engines work together , At this time MyISAM The engine is mostly used in tables that read more data and write less data ; If you can be sure MyISAM The engine's table can be used directly without modification InnoDB Backup by hot standby , conversely , You can do it first 'flush tables with read lock' The command is locked and exported .
  • Directly export compressed sql Files are smaller and easier to transfer , We will introduce the direct import of compressed sql Method of file ; It is said that when the amount of data is large, it is directly imported into the compressed sql Documents are much faster ; Personal test 500MBsql The original document , Compressed size 50MB, Import sql The speed of the original file and the compressed file imported directly is almost the same , about 2 branch 30 second ; In the future, if you encounter a large amount of data, you can try it first , In order to save time in production operation .

 

Remote copy backup files to slave library :

 

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

 

Import sql File to Library :

 

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

 

Or log in mysql After using source Command import :

 

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

 

If you import compressed sql file , The order is :

 

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

 

1.5. View master server status and binary log information

 

Execute on Main Library :

 

mysql> show master status;

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

 

Current log file name (File=mysql-bin.000001) And the current binary log location (Position=120), We'll use that later ; If you use the above InnoDB Hot standby way , You can also see the annotated binary file name and log location information in front of the exported file , Similar to the following :

 

--
-- 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. Start replication

 

Use the binary log file name recorded above (File=mysql-bin.000001) And the current binary log location (Position=120)

Execute from the library :

 

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;

 

View the status of the slave server :

Execute from the library :

 

mysql> show slave status\G

 

You will see the following information :

 

*************************** 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
 ...

 

If you see 'Slave_IO_Running: Yes  Slave_SQL_Running: Yes' The master-slave copy operation is successful .

 

You can also view the master-slave server thread status :

Lord / Execute from the library :

 

mysql> show processlist\G

 

You'll see from the library :

 

*************************** 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

 

You'll see :

 

*************************** 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

Two Master master copy ( Take the initiative - Active in passive mode - Master copy )

 

Doing the master-slave replication in reverse is master master replication , There's no repetition here , Just talk about the things to pay attention to

 

2.1. Here's a list of primary and secondary replication configurations

 

Main library my.cnf Add or modify the following configuration

 

log_bin=mysql-bin # Specify the binary log name 
server_id=10 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=0 # read-write 

 

another “ Lord ” library my.cnf Add or modify the following configuration

 

log_bin=mysql-bin # Specify the binary log name 
server_id=20 # The server id
binlog_format=row # Binary log format changed to line row Pattern 
relay_log=/var/lib/mysql/mysql-relay-bin # Specify the location and naming of the relay log 
log_slave_updates=1 # The standby database is allowed to record its replay events in its own binary log 
read_only=1 # read-only 

 

explain :

  • The mode in which both the master and the master can read and write is called “ Take the initiative - The master in active mode - Master copy ”, It is not recommended to use ; The mode in which one can read and write and the other can only read is called “ Take the initiative - Active in passive mode - Master copy ”, Recommended , It is convenient to switch when the primary server fails , perform 'mysql-> set global read_only=0' The command can set the library to read-write mode ;
  • Take the initiative - The master in active mode - There is a problem with master replication , Such as : Database fields a=1, Now execute two sql, One is set a=a+1, One is set a=a*2; a+1 First in db1 The result of execution on the server is 1+1=2,a*2 On the server db2 The result is 1*2=2; then db1 Sync db2 Of sql perform *2 The operation of , That is to say 2*2=4,db2 Also synchronized db1 Of sql perform +1 The operation of , That is to say 2+1=3; The end result of the two machines is different , One is 4 One is 3. Because replication is asynchronous , So there's a delay , If both the master and slave can write data, the consistency of the data may be broken .

 

Be careful :

Set up read_only=1 Read only mode , It can limit ordinary users to modify data , But not limited to having super Data modification operations of users with permissions ; It is a good habit to strictly control the permissions of database users .

 

General application connection database account creation example ( useless super jurisdiction ):

 

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

3、 ... and Routine maintenance

 

3.1. Turn on 、 stop it 、 Clear copy

 

Turn on the master-slave , Execute... From the server :

 

mysql> start slave;

 

Master and slave stop , Execute... From the server :

 

mysql> stop slave;

 

Clear the master and slave , Execute... From the server :

 

mysql> stop slave;
mysql> change master to master_host=' '; # There is a space between the quotation marks , No space will report an error 
mysql> reset slave;

 

It can be used show slave status\G The command is in the slave server to see if the master-slave replication state is stopped

 

3.2. Measuring replication latency

 

Use on the slave library "show slave status\G" Command output “Seconds_Behind_Master” The delay of slave library is shown theoretically .

Percona Toolkit In the kit pt-heartbeat Is one of the most popular implementations of measuring replication latency .

 

3.3. Copy data consistency check

 

use Percona Toolkit In the kit pt-table-checksum Check consistency

 

3.4. Fix inconsistent data

 

use Percona Toolkit In the kit pt-table-sync Fix inconsistent data

 

3.5. There is an error , Need to skip error reporting

 

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

Four appendix

 

4.1. Percona Toolkit

 

Official documents :https://www.percona.com/doc/percona-toolkit/LATEST/index.html

Blog reference :http://bbs.chinaunix.net/thread-3751657-1-1.html

Blog reference :https://segmentfault.com/a/1190000004309169

Blog reference :http://blog.csdn.net/wulantian/article/details/12062005

版权声明
本文为[Wake up from a dream_ write]所创,转载请带上原文链接,感谢

  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课程百度云