Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief introduction to the idea of designing the MySQL dual-master architecture. You can check the relevant professional terms on the Internet or find some related books to supplement it. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help. I hope that what is the idea of designing the dual-main architecture of MySQL.

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 records changes in the binary log (binary log) (these records are called binary log events, binary log events); 2. Slave copies the binary log events of master to its relay log (relay log); and 3. Slave redoes the events in the relay log and changes the data that reflects itself.

The following figure depicts this process:

The first part of the process is that master records binary logs. Before each transaction updates the data, master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

SQL slave thread handles the last step of the process. The SQL thread reads events from the relay log and updates the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread.

There is an important limitation in the replication process of previous versions of MySQL5.6-replication is serialized on slave, which means that parallel update operations on master cannot be operated in parallel on slave. The MySQL5.6 version parameter slave-parallel-workers=1 indicates that the multithreading feature is enabled.

At the beginning of MySQL5.6, a new feature has been added, which is the addition of global transaction ID (GTID) to strengthen the primary and standby consistency, fault recovery and fault tolerance of the database.

Official document: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html

The idea of MySQL double-master (master) architecture is: 1. Both mysql can read and write to each other. By default, only one (masterA) is used to write data, and the other (masterB) is used as a backup. 2.masterA is the main library of masterB, and masterB is the master library of masterA. For high availability between the two main libraries, you can use keepalived and other schemes (using VIP to provide services); 4. All slave CVMs provide services for master-slave synchronization with masterB (double master and multi-slave); 5. It is recommended that when a high availability policy is adopted, neither masterA nor masterB will preempt VIP after downtime recovery (non-preemptive mode)

This can ensure the high availability of the main library to a certain extent. After the down of one master library is dropped, it can be switched to another master library in a very short time (to minimize the impact on the business caused by the downtime of the master library), thus reducing the pressure of master-slave synchronization on the online master library.

But there are several inadequacies:

1.masterB may be idle all the time (you can use it as a slave library, responsible for partial queries); 2. The slave library that provides services behind the master library can only go to masterB to synchronize data after masterB has synchronized data first, which may cause a certain degree of synchronization delay.

The simple diagram of the architecture is as follows:

Master master environment (only the configuration scheme of 2 masters is introduced here):

1.CentOS 6.8 64-bit 2: masterA (192.168.10.11), masterB (192.168.10.12)

two。 Official Mysql5.6 version

Construction process: 1. Install MySQL service (recommended source code installation) 1.1 yum install dependency package yum-y install make gcc gcc-c++ ncurses-devel bison openssl-devel1.2 users and groups required to add MySQL groupadd-g 27 mysqladduser-u 27-g mysql-s / sbin/nologin mysql1.3 download MySQL source code 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.gz1.4 create mysql data directory mkdir-p / usr/local/mysql/data1.5 decompress, compile and install cmake, MySQLcd / 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 install1.6 add boot script cp support-files/mysql.server / etc/rc.d/init.d/mysqld1.7 add MasterA configuration file / 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 # Odd IDlog-bin = mysql-bin # turn on binary function The MASTER master server must open this 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 single file maximum replicate-ignore-db = mysql # ignore non-synchronized master-slave database replicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = zabbixmax_connections = 3000max_connect_errors = 30skip-character-set-client-handshake # ignore other character sets init- that the application wants to set Connect='SET NAMES utf8' # maximum connection time interactive_timeout=1800 requested by SQLcharacter-set-server=utf8 # server default character set interactive_timeout=1800 # when connecting # and the previous parameter are modified at the same time to take effect sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES # sql mode max_allowed_packet = 10Mbulk_insert_buffer_size = 8Mquery_cache_type = 1query_cache_size = 128Mquery_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 parameters indicate that log-slave-updates = true # writes replication events to binlog A CVM acts as both master and slave libraries. This option must be enabled for # masterA self-growing IDauto_increment_offset = 1auto_increment_increment = 2 # odd ID#masterB self-increasing IDauto_increment_offset = 2auto_increment_increment = 2 # even ID1.9 to add masterB configuration file / 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 # even IDlog- Bin = mysql-bin # turn on the binary function The MASTER master server must open this 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 single file maximum replicate-ignore-db = mysql # ignore non-synchronized master-slave database replicate-ignore-db = information_schemareplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = zabbixmax_connections = 3000max_connect_errors = 30skip-character-set-client-handshake # ignore other character sets init- that the application wants to set Connect='SET NAMES utf8' # maximum connection time interactive_timeout=1800 requested by SQLcharacter-set-server=utf8 # server default character set interactive_timeout=1800 # when connecting # and the previous parameter are modified at the same time to take effect sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES # sql mode max_allowed_packet = 10Mbulk_insert_buffer_size = 8Mquery_cache_type = 1query_cache_size = 128Mquery_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 initialize MySQLcd / usr/local/mysqlscripts/mysql_install_db-- user=mysql1.11 gives executable permissions to the startup script and starts MySQLchmod + x / etc/rc.d/init.d/mysqld/etc/init.d/mysqld start2. 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 unknown MySQL > flush privileges

On masterB:

Mysql > grant replication slave on *. * to 'repl'@'192.168.10.11' identified by' 123456 query MySQL > flush privileges;2.2 to check 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 | 120 | +- -+ 1 row in set (0.00 sec)

On masterB

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

It is normal if the following status is displayed:

Slave_IO_Running: YesSlave_SQL_Running: Yes

On masterB:

# I am the test environment, so no data can be written. Otherwise, the necessary steps are: first masterA lock table-> masterA backup data-> masterA unlock table-> masterB import data-> masterB settings master-slave-> view master-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

It is normal if the following status is displayed:

Slave_IO_Running: YesSlave_SQL_Running: Yes3. Test Master-Slave synchronization 3.1 create a database on masterA Test synchronization effect 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 [root@masterA data ] # 3.2 check whether the database mysql > show databases has been created synchronously in masterB +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | | test01 | +-+ 5 rows in set (0.00 sec) mysql > quitBye [root@masterB data] # 4. Enable 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 kind of master and subordinate misreport bothered me for a long time:

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file'

Later, modify the parameters related to master-slave synchronization to confirm that the following parameters have been added to my.cnf:

Log-bin = mysql-bin

Relay-log = mysql-bin

The binary log file synchronized from the normal master master shows that there are 2 sets of binary logs. Therefore, it is inferred that the above two parameters lead to the failure to produce two sets of binaries, which leads to the confusion and loss of binaries.

What is the idea of designing the dual main architecture of MySQL? let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.