MySQL dual-master (master-master) architecture configuration solution

MySQL dual-master (master-master) architecture configuration solution

In enterprises, database high availability has always been a top priority. Many small and medium-sized enterprises use the MySQL master-slave solution, one master and multiple slaves, read-write separation, etc. However, a single master has a single point of failure, and changes need to be made when switching from a slave database to a master database. Therefore, if it is dual-master or multi-master, the MySQL entry will be added to increase high availability. However, for multiple masters, the self-increment ID problem needs to be considered. This requires special configuration files. For example, for dual masters, parity can be used. In short, the self-increment ID conflict problem can be perfectly solved by setting self-increment IDs between masters without conflicts.

Master-slave synchronous replication principle

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

Copying is divided into three steps:

1. The master records the changes in the binary log (these records are called binary log events).
2. The slave copies the master's binary log events to its relay log;
3. The slave redoes the events in the relay log, changing the data to reflect its own.

The following diagram describes this process:

The first part of the process is for the master to record binary logs. Before each transaction completes updating data, the master records these changes in the secondary log. MySQL writes transactions serially to the binary log, even if statements in the transaction are interleaved. After the event is written to the binary log, the master notifies the storage engine to commit the transaction.

The next step is for the slave to copy the master's binary log to its own relay log. First, the slave starts a worker thread - the I/O thread. The I/O thread opens a normal connection on the master and then starts the binlog dump process. The Binlog dump process reads events from the master's binary log. If it has caught up with the master, it will sleep and wait for the master to generate new events. 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 relay log and updates the slave's data to make it consistent with the data in the master. As long as the thread stays consistent with the I/O thread, the relay log will usually be in the OS's cache, so the overhead of the relay log is small.

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

The replication process of versions prior to MySQL 5.6 has a very important limitation - replication is serialized on the slave, which means that parallel update operations on the master cannot be performed in parallel on the slave. The MySQL 5.6 version parameter slave-parallel-workers=1 means enabling the multi-threading function.

Starting from MySQL 5.6, a new feature has been added, which is the addition of global transaction ID (GTID) to enhance the master-slave consistency, fault recovery, and fault tolerance of the database.

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

The idea of ​​MySQL dual-master (master-master) architecture is:

1. Both MySQL servers are readable and writable, and they are mutually master and backup. By default, only one server (masterA) is used to write data, and the other server (masterB) is used as backup.
2. masterA is the master database of masterB, and masterB is the master database of masterA. They are master and slave to each other;
3. To achieve high availability between the two master databases, you can use solutions such as keepalived (using VIP to provide external services);
4. All slave servers providing services perform master-slave synchronization with masterB (dual-master and multiple-slave);
5. When adopting the high availability strategy, it is recommended that neither masterA nor masterB preempt VIP after recovery due to downtime (non-preemptive mode);
Doing so can ensure the high availability of the master database to a certain extent. When a master database goes down, it can be switched to another master database in a very short time (minimizing the impact of the master database downtime on the business as much as possible), reducing the pressure of master-slave synchronization on the online master database.

But there are also several shortcomings:

1. masterB may be idle all the time (it can be used as a slave to be responsible for some queries);
2. The slave database that provides services behind the master database must wait until masterB has synchronized data before it can synchronize data with masterB. This may cause a certain degree of synchronization delay;
A simplified diagram of the architecture is as follows:

Master-master environment (here we only introduce the configuration of 2 masters):

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

2. Official MySQL 5.6 version

Building process:

1. Install MySQL service (source code installation is recommended)

1.1 yum installation dependency package

yum -y install make gcc gcc-c++ ncurses-devel bison openssl-devel

1.2 Add users and groups required by MySQL

groupadd -g 27 mysql
adduser -u 27 -g mysql -s /sbin/nologin mysql

1.3 Download MySQL source code package

mkdir -p /data/packages/src
cd /data/packages/
wget http://distfiles.macports.org/cmake/cmake-3.2.3.tar.gz
wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.34.tar.gz

1.4 Create MySQL data directory

mkdir -p /usr/local/mysql/data

1.5 Unzip, compile and install cmake and MySQL

cd /data/packages/src
tar -zxvf ../cmake-3.2.3.tar.gz
cd cmake-3.2.3/
./bootstrap
gmake
make install
cd ../
tar xf mysql-5.6.34.tar.gz
cd mysql-5.6.34
cmake . -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/data
make && make install

1.6 Add a startup script

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

1.7 Add masterA configuration file /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
basedir = /usr/local/mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err

server-id = 1
auto_increment_offset = 1
auto_increment_increment = 2 #odd number ID

log-bin = mysql-bin #Turn on the binary function. The MASTER server must turn on this option binlog-format=ROW
binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M #Maximum value of a single binlog file replicate-ignore-db = mysql #Ignore databases that are not synchronized between master and slave replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake #Ignore other character sets that the application wants to set init-connect='SET NAMES utf8' #SQL executed when connecting
character-set-server=utf8 #Default character set on the server wait_timeout=1800 #Maximum connection time for a request interactive_timeout=1800 #This will only take effect if modified at the same time as the previous parameter sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql mode max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]

1.8 Special Parameter Description

log-slave-updates = true #Write replication events to binlog. A server can be both a master and a slave. This option must be enabled. #MasterA self-incrementing ID
auto_increment_offset = 1
auto_increment_increment = 2 #odd number ID
#masterB self-increases ID
auto_increment_offset = 2
auto_increment_increment = 2 #even ID

1.9 Add masterB configuration file /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
basedir = /usr/local/mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/data
pid-file = /usr/local/mysql/data/mysql.pid
log-error = /usr/local/mysql/data/mysql.err

server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2 #even ID

log-bin = mysql-bin #Turn on the binary function. The MASTER server must turn on this option binlog-format=ROW
binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#expire_logs_days=5
max_binlog_size=1024M #Maximum value of a single binlog file replicate-ignore-db = mysql #Ignore databases that are not synchronized between master and slave replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = zabbix

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake #Ignore other character sets that the application wants to set init-connect='SET NAMES utf8' #SQL executed when connecting
character-set-server=utf8 #Default character set on the server wait_timeout=1800 #Maximum connection time for a request interactive_timeout=1800 #This will only take effect if modified at the same time as the previous parameter sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql mode max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M

[mysqld_safe]

1.10 Initialize MySQL

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

1.11 Grant executable permissions to the startup script and start MySQL

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

2. Configure master-slave synchronization

2.1 Adding a 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 Check the status of the main database
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 Configuration 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;

If the following status is displayed, it is normal:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

On masterB:

#I am in a test environment and can guarantee that no data is written. Otherwise, the steps required are: first masterA locks the table --> masterA backs up data --> masterA unlocks the table --> masterB imports data --> masterB sets up 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;

If the following status is displayed, it is normal:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3. Test master-slave synchronization

3.1 Create a database on masterA to test the 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> quit

Bye

[root@masterA data]#

3.2 Go to masterB to check whether 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> quit

Bye

[root@masterB data]#

4. Enable the GTID function of MySQL 5.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 reported by 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, the master-slave synchronization related parameters were modified to confirm that the reason was that my.cnf added the following parameters:

log-bin = mysql-bin

relay-log = mysql-bin

The binary log files during normal master-master synchronization show that there are two sets of binary logs. Therefore, it can be inferred that the above two parameters result in the inability to generate two sets of binary files, which leads to the confusion and loss of binary files.

This is the end of this article about the MySQL dual-master (master-master) architecture configuration solution. For more relevant MySQL dual-master content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • In-depth understanding of Mysql logical architecture
  • Introduction to MySQL overall architecture
  • MySQL 20 high-performance architecture design principles (worth collecting)
  • MySQL 4 common master-slave replication architectures
  • MySQL learning summary: a preliminary understanding of the architectural design of the InnoDB storage engine
  • Summary of MySQL Architecture Knowledge Points
  • Detailed explanation of Mysql logical architecture
  • MySQL database architecture details

<<:  Detailed tutorial for installing ElasticSearch:7.8.0 cluster with docker

>>:  Detailed explanation of the use of grid properties in CSS

Recommend

Nodejs uses readline to prompt for content input example code

Table of contents Preface 1. bat executes js 2. T...

How to automatically backup the script for Linux servers (mysql, attachment backup)

1. Create the backup.sh script file #!/bin/sh SOU...

Disable autocomplete in html so it doesn't show history

The input box always displays the input history wh...

Using Nginx to implement grayscale release

Grayscale release refers to a release method that...

A brief discussion on the solution to excessive data in ElementUI el-select

Table of contents 1. Scenario Description 2. Solu...

Share CSS writing standards and order [recommended for everyone to use]

CSS writing order 1. Position attributes (positio...

MySQL SQL statement analysis and query optimization detailed explanation

How to obtain SQL statements with performance iss...

How to use axios request in Vue project

Table of contents 1. Installation 2. There is no ...

DHTML objects (common properties of various HTML objects)

!DOCTYPE Specifies the Document Type Definition (...

MySQL infobright installation steps

Table of contents 1. Use the "rpm -ivh insta...

Detailed explanation of referential integrity in SQL (one-to-one, one-to-many, many-to-many)

1. Referential Integrity Referential integrity re...

How to view and configure password expiration on Linux

With the right settings, you can force Linux user...

Basic usage of UNION and UNION ALL in MySQL

In the database, both UNION and UNION ALL keyword...

MySql common query command operation list

MYSQL commonly used query commands: mysql> sel...

MySQL 5.7.17 free installation version configuration method graphic tutorial (windows10)

1. Overview I searched a lot online and found tha...