Summary of MySQL Replication Related Links

Translated from 日本語 • View original

Summary of MySQL Replication Related Information

Hello. This is Komiya.
These days, I am often asked about mysql, so
This is an attempt to compile links that explain what people tend to ask so that they can copy and paste when asked.
Please note that you will not know much just by reading this article.
(It seems to be useful when you completely forget about it.) ）

・Basic
Field-Oriented Replication Detailed Computer Path: 10 Techniques for Safely Using MySQL ReplicationArt of MySQL Replication.
What is written in the binary log is an update query.
As a side note, I/O errors can be physically broken disks, network failure, incorrect replication user IDs and passwords, and duplicate server_id
　It can happen for reasons such as: The main cause of SQL errors is described in the first link above, but I have the impression that there are many duplicate errors.

For reference, the current settings around replication are roughly like this.

[shell]## replication (master/slave) log-bin=mysql-bin log-bin-index=mysql-bin.index binlog_format=mixed server-id = 133 relay-log=mysqld-relay-bin relay-log-index= mysql-relay-bin.index log_slave_updates=1 replicate-ignore-db=mysql,information_schema,performance_schema binlog-ignore-db=mysql,information_schema,performance_ schema skip_slave_start read_only slave_net_timeout=120

## replication (for 5.6) gtid-mode = OFF enforce_gtid_consistency=false master-info-repository=TABLE relay-log-info-repository=TABLE relay_log_recovery=ON #sync-master-info= 1 slave-parallel-workers=0 binlog-checksum=CRC32 #master-verify-checksum=1 #slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 #log_bin_use_v1_row_events=ON #sync_binlog=1 report-port=3306 report-host = 192.168.1.133[/shell]

Well, the master and slave are HA and there is a possibility that the roles will be swapped.
Log_slave_updates and both other parameters (except server_id and report-host) and have the same specifications.
replication is not related, but I have posted some of the parameters, so I will mention it.
If you have multiple disks, you can divide the system area and the data area to distribute the I/O load.
If it is AWS, I think it would be a good idea to specify the ephemeral disk as tmpdir.

・Replication of specific DBs and tables, or not
MySQL :: MySQL 5.1 Reference Manual :: 5.5.6 Server Replication Rule Evaluation
It seems that these cannot be changed online (it seems that they will be able to change from around 5.7).

・About the specifications of the relay log
MySQL :: MySQL 5.1 Reference Manual :: 5.5.5.1 Slave Relay Logs Learn MySQL Relay Log Specifications - OpenGroove
When I verified the MHA, I needed to leave a relay log, so I looked it up.

・Delay countermeasures
The Computer Path of the Han Dynasty: Replication Delay Trends and Countermeasures in MySQL MySQL Replication Delay Cured with ALTER - Takami_hiroki's Diary
Actually, Created_tmp_disk_tables has increased and a temporary table has been created on the disk, so it is delayed.
There were times when I increased tmp_table_size and max_heap_table_size to solve the problem.

・Intentionally delay
Use mk-slave-delay to intentionally delay slaveDB in the development environment for 3 seconds daemontools shell Procedure to reproduce MySQL replication delay locally Part 2 - CFlat Inc.'s Blog MySQL :: MySQL 5.6 Reference Manual :: 16.3.9 Delayed Replication
*From 5.6, CHANGE MASTER TO MASTER_DELAY = N; OK. Prior to 5.5, pt-slave-delay is required.

Duplicate error
Troubleshooting MySQL Replication 1062 Errors - OpenGroove What to Do When a Duplicate Entry Error Appears in a MySQL Slave | DEVLAB
If it seems to overlap too much, it may be better to dump from the master and load it into the slave to match the consistency.

・Integrity check
pt-table-checksum — Percona Toolkit Documentation
[OSS Reference: [MySQL]Replication Integrity with Maatkit] (http://infraref.blogspot.jp/2011/02/mysqlmaatkit.html)
I've never used it, but it seems that there is something like this.

GTID in 5.6
A little bit about MySQL 5.6 - Mamebata Pitfalls to keep in mind when using MySQL-5.6.5's new feature "GTID" - Limitations when using Y-Ken Studio Global Transaction ID for replication configuration - hiroi10's diary
By the way, I have never seen an environment where GTID is enabled and s-in. (As of April 2014)

・5.6 replication in general
Yakst - How to Make Slaves Crash-Safe in MySQL 5.6 MySQL 5.6 Enhancements (Part 3) - Popular Replication Further Enhancements | Think IT Daily Memorandum: Slave _parallel_workers

5.5 and 5.6 replication
Daily Memorandum: Hanging MySQL 5.5 (and earlier) slaves on MySQL 5.6 masters will make you feel annoyed Tell the scary story about "RESET SLAVE" to MySQL-5.5/5.6 replication users - Y-Ken Studio Hateburo Branch Daily Memorandum: MySQL 5.6 and MySQL 5.0 replication I/O threads Error: 2049

・semi-sync
Han's Computer Road: The Strongest MySQL HA Method - Semi-Synchronous Replication MySQL 5.5 GA version is out, so I tried using Semisynchronous Replication - As a Futurist...MySQL Semi-Synchronous Replication. See the Magic. Try the Magic. « Ronen Baram

SSL connection
suz-lab - blog: SSL connection to MySQL suz-lab - blog: MySQL replication with SSL connection
By the way, I don't hear much about wanting to SSL. It may be that you want to partially synchronize and analyze the data.
In the first place, it is rare for the DB to come into contact with the front seg, and I think it is common to feel that it is not global, but through a VPN.

・Hall of Fame N:1 guy (I don't want to do it myself, but there are people like this)
How to Make Master n vs. Slave 1 Replication - do_aki
I think I'll change the datadir, port, and log file names. Well, in other words, n:1 is not possible normally in the same instance.
I have heard that multi-source replication may be available in MySQL 5.7, but it is under development and I am not sure.

・About multi-stage replication configuration
We cannot respond to failures with multi-stage configurations. This is because it complicates dealing with intermediate node failures.
Create a grandson slave that is okay even if it disappears for analysis (or if it is restored during business hours)
If so, I think it's acceptable. I don't think it's possible if it is directly linked to the suspension of service if it is stopped.
If there is such a structure in the company now, it seems that I will check in detail why it was so structured.
If the number of slaves increases too much and the master CPU or NIC bandwidth is insufficient, there is nothing else to do
But fortunately, I have never seen such an environment so far.

If you look at it this way, replication alone is surprisingly informative.
The following is information that is not replication itself, but I will post it.

・MHA
MHA for MySQL and DeNA Open Source Stories mysql-master-ha - MHA for MySQL: Master High Availability Manager and tools for MySQL - Google Project Hosting Try MySQL-MHA - DRY (Diary of an Engineer Working in Japan and the United States) I tried to realize automatic MySQL failover using MySQL-MHA - FAT47 Bottom Infrastructure Minutes I tried adding mysql-mha! Part 2 Deployment Settings for MHA, Murakumo & Me MHA (MasterHigh AvailabilityManager) | DEVLAB MHA Operation Verification and Switchover Verification | DEVLAB

・MySQLFAILOVER
mysqlfailover --exec-before and --exec-after - hiroi10's diary I tried mysqlfailover after becoming a daemon | DEVLAB

Other HA
Recovery plan in the event of a master or slave failure in MySQL that is replicated - (h) Memo About the ha configuration of Mysql

・ALTER TABLE
Han's computer path: Let's master the ALTER TABLE. Talking about locking when adding and deleting columns and indexes in MySQL - (゚∀゚)o彡 sasata299's blog Check the progress of ALTER TABLE statements in MySQL - SH2's diary Notes when creating an INDEX in ALTER TABLE in MySQL | DEVLAB

・Backup
Notes on Backing Up MySQL MyISAM Mixed Environments | mysqldump in an environment using DEVLAB gtid - hiroi10's diary
If you import to a version of MySQL earlier than 5.6, or if you want to dump using mysqldump in 5.6 from a remote 5.5 server, you must add --set-gtid-purged=OFF to mysqldump
If GTID is valid, you will get an error if you don't add --triggers --routines --events to mysqldump
mysqldump --order-by-primary option - SH2 Diary MySQL backup operation

・There was a difference
Absorb master and slave diffs in mysql with pt-table-sync - Qiita

Parameter tuning
5.6 Articles containing similar
MySQL 5.6 Parameter Review - SH2 Diary Daily Memorandum: New Implicit Defaults in MySQL 5.6 InnoDB Performance Tuning and New Features - ORACLE MySQL 5.6 Parameter Review Meeting (slide) Yakst - ( Basics of InnoDB Performance Optimization MySQL unknown option error can be avoided by prefixing options loose-
[[D14] Performance Tuning for MySQL 5.6 Era *db tech showcase 2013 Tokyo] (http://www.slideshare.net/yoyamasaki/20131110-tuning-onmysql56)
MySQL 5.1→5.6 my.cnf diff - (h) note
Other than that
MySQL Tuning (Detecting Bottlenecks) : Figure out! - Drecom Engineer Blog MySQL Tuning DSAS Developer's Room: Tuning MySQL Memory Relationships in 5 Minutes! MySQL Study Notes (Server Settings Item) | Kinurogu
Numa Related
【Note】Swap Occurrence in MySQL and NUMA Architecture - FAT47 Underlying Infrastructure Minutes Tuning the MySQL InnoDB Storage Engine (Part 2) (goo News×EnterpriseZine) - goo News MySQL and NUMA Architecture and Swap Insanity | COLOPL Engineers' Blog Days of Revolution! How to use numactl
innotop reference
Daily memorandum: innotop is very useful innotop displays MySQL status in a top style (in groups!) - mikeda's Diary MySQL - innotop tips - Qiita innotop - A powerful "top" clone for MySQL - Google Project Hosting