MySQL Dual master mode scenario

In enterprises, the high availability of databases has always been the top priority of enterprises, many small and medium-sized enterprises are using MySQL master-slave scheme, a master and many from, read and write separation, but single-master single point of failure, switch from the library to the main library needs to be changed. Therefore, if it is a dual-master or multi-master, it will increase the MySQL portal and increase the high availability. However, a multi-master needs to consider the problem of self-growth ID, this need to set a special configuration file, such as dual-master, you can use parity, in short, the host between the setting of the self-growth ID can not conflict with each other to perfectly solve the problem of self-growth ID conflict.

Principle of Master-slave synchronous replication

Before we begin, let's look at the principle of master-slave synchronous replication.

Replication is divided into three steps:

1. Master will change the record to binary log (these are called binary log events, binary logs event); 2. Slave copies the binary log events of master to its trunk log (relay log); 3. Slave redo the events in the trunk log and change the data to reflect its own.

Describes this process:

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

The SQL slave thread handles the final step of the process. The SQL thread reads events from the log, updating the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread.

MySQL5.6 the previous version replication process has an important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave. The MySQL5.6 version parameter, Slave-parallel-workers=1, indicates the multithreading feature is enabled.

MySQL5.6 begins with a new feature that adds a global transaction ID (GTID) to harden the database's primary and standby consistency, failback, and fault tolerance.

Official Document: Http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

MySQL dual master (Master) architecture Scheme idea is: 1. Two MySQL can read and write, the main preparation, by default only one (Mastera) responsible for data write, another (Masterb) standby 2.masterA is the main library of Masterb, and Masterb is the main library of Mastera, and they are mainly from each other; 3. Two main libraries are highly available, can use keepalived and other programs (using VIP to provide services) 4. All services from the server and the Masterb for master-slave synchronization (dual master multi-slave); 5. When a highly available strategy is recommended, Mastera or masterb do not preempt VIP (non-preemptive mode) when downtime is restored;

This will ensure that the main library is highly available to a certain extent, and after a main library is down, it can be switched to another main library in a very short time (minimizing the impact of the main library outage on the business), reducing the pressure on the master-slave synchronization to the main library;

But there are also a few deficiencies:

1.masterB may always be idle (it can be used when from the library, responsible for part of the query); 2. After the main library to provide services from the library to wait for the Masterb to synchronize the data before going to masterb up to synchronize data, which may cause a certain degree of synchronization delay;

A simplified diagram of the architecture is as follows:

Primary master Environment (only 2 master configuration scenarios are described here):

1.CentOS 6.8 64-bit 2 units: Mastera (192.168.10.11), Masterb (192.168.10.12)

2. Official Mysql5.6 version

Build process: 1. Install MySQL service (recommended source installation) 1.1 Yum install Dependency pack

Yum-y install make gcc gcc-c++  ncurses-devel Bison openssl-devel

1.2 Users and groups required to add MySQL

Groupadd-g mysqladduser-u 27-g mysql-s/sbin/nologin MySQL

1.3 Download MySQL Source package

Mkdir-p/data/packages/srccd/data/packages/wget Http://distfiles.macports.org/cmake/cmake-3.2.3.tar.gzwget http:/ /dev.mysql.com/get/downloads/mysql-5.6/mysql-5.6.34.tar.gz

1.4 Creating MySQL Data Catalog

Mkdir-p/usr/local/mysql/data

1.5 Decompression Compile install CMake, MySQL

CD/DATA/PACKAGES/SRCTAR-ZXVF. /CMAKE-3.2.3.TAR.GZCD Cmake-3.2.3/./bootstrapgmakemake Install

Cd.. /tar XF  mysql-5.6.34.tar.gzcd mysql-5.6.34cmake.-DCMAKE_INSTALL_PREFIX=/USR/LOCAL/MYSQL-DSYSCONFDIR=/ETC- Dwith_ssl=bundled-ddefault_charset=utf8-ddefault_collation=utf8_general_ci-dwith_innobase_storage_engine=1- dwith_myisam_storage_engine=1-dmysql_tcp_port=3306-dmysql_unix_addr=/tmp/mysql.sock-dmysql_datadir=/usr/local/ Mysql/datamake && make Install

1.6 Adding a Boot startup script

CP Support-files/mysql.server/etc/rc.d/init.d/mysqld

1.7 Adding Mastera configuration Files/etc/my.cnf

[Client]port = 3306socket =/tmp/mysql.sock[mysqld]basedir =/usr/local/mysqlport = 3306socket =/tmp/mysql.sockdatadir = /usr/local/mysql/datapid-file =/usr/local/mysql/data/mysql.pidlog-error =/usr/local/mysql/data/ Mysql.errserver-id = 1auto_increment_offset = 1auto_increment_increment = 2 #奇数 Idlog-bin = Mysql-bin #打开二进制功能, the Master master server must have this entry open Binlog-format=rowbinlog -row-p_w_picpath=minimallog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository= tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=0sync_binlog=0binlog-checksum= Crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1#expire_logs_days=5max_                                             binlog_size=1024m #binlog单文件最大值replicate-ignore-db = MySQL #忽略不同步主从的数据库replicate-ignore-db = Information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = Zabbixmax_connections = 3000max_ Connect_errors = 30skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集init-connect= '                                               SET NAMES UTF8 ' #连接时执行的SQLcharacter-set-server=utf8 #服务端默认字符集wait_timeout =1800 #请求的最大连接时间interactive_t imeout=1800 #和上一参数同时修改才会生效sql_mode =no_engine_substitution,strict_trans_tabl ES #sql模式max_allowed_packet = 10mbulk_insert_buffer_size = 8mquery_cache_type = 1query_cache_size = 12 8mquery_cache_limit = 4mkey_buffer_size = 256mread_buffer_size = 16kskip-name-resolveslow_query_log=1long_query_time = 6slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 16M[mysql] No-auto-rehash[myisamchk]key_buffer_size = 20msort_buFfer_size = 20mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout[mysqldump]quickmax_allowed_packet = 16m[mysqld_safe]

1.8 Special Parameter Description

Log-slave-updates = True     #将复制事件写入binlog, a server is both the master library and the slave library. This option must be turned on

#masterA自增长IDauto_increment_offset = 1auto_increment_increment = 2                                            #奇数ID

#masterB自增加IDauto_increment_offset = 2auto_increment_increment = 2                                            #偶数ID

1.9 Adding Masterb configuration Files/etc/my.cnf

[Client]port = 3306socket =/tmp/mysql.sock[mysqld]basedir =/usr/local/mysqlport = 3306socket =/tmp/mysql.sockdatadir = /usr/local/mysql/datapid-file =/usr/local/mysql/data/mysql.pidlog-error =/usr/local/mysql/data/ Mysql.errserver-id = 2auto_increment_offset = 2auto_increment_increment = 2 #偶数 Idlog-bin = Mysql-bin #打开二进制功能, the Master master server must have this entry open Binlog-format=rowbinlog -row-p_w_picpath=minimallog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository= tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=0sync_binlog=0binlog-checksum= Crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1#expire_logs_days=5max_                                             binlog_size=1024m #binlog单文件最大值replicate-ignore-db = MySQL #忽略不同步主从的数据库replicate-ignore-db = Information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = Zabbixmax_connections = 3000max_ Connect_errors = 30skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集init-connect= '                                               SET NAMES UTF8 ' #连接时执行的SQLcharacter-set-server=utf8 #服务端默认字符集wait_timeout =1800 #请求的最大连接时间interactive_t imeout=1800 #和上一参数同时修改才会生效sql_mode =no_engine_substitution,strict_trans_tabl ES #sql模式max_allowed_packet = 10mbulk_insert_buffer_size = 8mquery_cache_type = 1query_cache_size = 12 8mquery_cache_limit = 4mkey_buffer_size = 256mread_buffer_size = 16kskip-name-resolveslow_query_log=1long_query_time = 6slow_query_log_file=slow-query.loginnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 16M[mysql] No-auto-rehash[myisamchk]key_buffer_size = 20msort_buFfer_size = 20mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout[mysqldump]quickmax_allowed_packet = 16m[mysqld_safe]

1.10 Initializing MySQL

cd/usr/local/mysqlscripts/mysql_install_db--user=mysql

1.11 Give the startup script executable permissions and start MySQL

chmod +x/etc/rc.d/init.d/mysqld/etc/init.d/mysqld Start

2. Configure master-Slave Synchronization 2.1 Add master-Slave synchronization account

On Mastera:

mysql> grant replication Slave on * * to ' repl ' @ ' 192.168.10.12 ' identified by ' 123456 ';mysql> flush privileges;

On Masterb:

mysql> grant replication Slave on * * to ' repl ' @ ' 192.168.10.11 ' identified by ' 123456 ';mysql> flush privileges;

2.2 Viewing the status of the main library

On Mastera:

Mysql> Show Master status;+------------------+----------+--------------+------------------+------------------- +| File             | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |      | | | |                   +------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

Masterb on

Mysql> Show Master status;+------------------+----------+--------------+------------------+------------------- +| File             | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |      437 | | | |                   +------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)

2.3 Configuring synchronization information:

On Mastera:

mysql> Change Master to master_host= ' 192.168.10.12 ', master_port=3306,master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000003 ',master_log_pos=437;mysql> start slave;mysql> show slave Status\G;

Displays the following status as normal:

Slave_IO_Running:YesSlave_SQL_Running:Yes

On Masterb:

#本人是测试环境, you can guarantee no data to write, otherwise the required steps are: Mastera lock table-->mastera backup data-->mastera unlock table-->masterb import data-->masterb set master-slave-- > View master and slave

mysql> Change Master to master_host= ' 192.168.10.11 ', master_port=3306,master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000003 ', Master_log_pos=120;start slave;mysql> show slave status\g;

Displays the following status as normal:

Slave_IO_Running:YesSlave_SQL_Running:Yes

3. Test master-Slave synchronization 3.1 Create a Database test sync effect on Mastera

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               |+---------- ----------+4 rows in Set (0.00 sec) mysql> CREATE database test01; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               | | test01             |+--------------------+5 rows in Set (0.00 sec) mysql> Quitbye[[email protected] data]#

3.2 to Masterb to see if the database has been created synchronously

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               | | test01             |+--------------------+5 rows in Set (0.00 sec) mysql> Quitbye[[email protected] data]#

4. Turn on the Gtid function of MySQL5.6

Mastera and Masterb execute the following commands, respectively:

mysql> stop Slave; Query OK, 0 rows Affected (0.00 sec) mysql> Change master to Master_auto_position=1; Query OK, 0 rows affected (0.01 sec) mysql> start slave; Query OK, 0 rows Affected (0.00 sec)

5. Problems encountered

A master-slave error toss me half a day:

last_io_errno:1236

Last_io_error:got fatal Error 1236 from master if reading data from binary log: ' Could not open log file '

After modifying the master-slave synchronization related parameters, confirm that the reason is my.cnf added the following parameters:

Log-bin = Mysql-bin

Relay-log = Mysql-bin

The binary log files from the normal master synchronization are displayed with 2 sets of binary logs. Therefore, it is inferred that the above 2 parameters result in the inability to produce 2 sets of binary files, resulting in binary file confusion and loss.

MySQL Dual master mode scenario