MySQL master-master replication in Linux

Why is there a master-master replication of mysql. In some highly available environments, mysql Master/Slave cannot meet some practical needs. For example, if there is a bottleneck for access to some websites with high traffic, two or more mysql servers will be used for load balancing, and the database data of these mysql servers must be consistent, master-master replication is used.
In the mysql master-slave architecture, a master is actually working, and it is equivalent to a backup machine. It backs up data in the master database through log monitoring to ensure data security in the master database. In this architecture, if the preceding data is changed, the primary data will not be changed. Because the mysql master-slave architecture is mainly used for synchronization from monitoring the Log Changes of the mysql master, on the contrary, the master does not monitor the Log Changes in this architecture. Therefore, the master node does not change as the mysql DATA changes.
Through the above description, we can see that if you want to achieve master-master replication, it is nothing more than letting the mysql master monitor the Log Changes in the mysql master-slave architecture, so that the two machines can synchronize with each other. (There is a blog post before the master-slave architecture)
Lab environment: two servers:
Host Name: HA1, HA2 (haha, this host name is short for High availability, High availability)
Ip: 192.168.1.231
192.168.1.20.
Host system: CentOS6.4
Mysql 5.5.22
First, check the mysql configuration file of HA1 (192.168.1.231 ).
Vim/etc/my. cnf
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set
# Locations which depend on the deployment platform.
# You can copy this option file to one of those
# Locations. For information about these locations, see:
# Http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# With the "-- help" option.
# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306
Socket =/usr/local/mysql/tmp/mysql. sock
# Here follows entries for some specific programs
# The MySQL server
[Mysqld]
Port = 3306
Socket =/usr/local/mysql/tmp/mysql. sock
Skip-external-locking
Key_buffer_size = 384 M
Max_allowed_packet = 1 M
Table_open_cache = 512
Sort_buffer_size = 2 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 8 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 32 M
# Try number of CPU's * 2 for thread_concurrency
Thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# If all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (Via the "enable-named-pipe" option) will render mysqld useless!
#
# Skip-networking
# Replication Master Server (default)
# Binary logging is required for replication
Log-bin = mysql-bin
Log-slave-updates
# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 1

In this configuration file, pay special attention to the following three points:
Log-bin = mysql-bin: This option is usually open by default. If it is not enabled, You can manually open it.
Log-slave-updates: This option is particularly important to enable slave to act as a master and to better serve the m-m + s environment, ensure that the slave will receive the write information from the other master when it is attached to any master. Of course, it is not limited to this architecture. The cascade replication architecture also needs the support of log-slave-updates.
Server-id = 1: This ID is the server ID. If the configuration is the same, a conflict will occur, and the server id cannot be copied.
Next, let's look at the mysql configuration file of HA2 (192.168.1.20 .).
Vim/etc/my. cnf
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set
# Locations which depend on the deployment platform.
# You can copy this option file to one of those
# Locations. For information about these locations, see:
# Http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# With the "-- help" option.
# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306
Socket =/usr/local/mysql/tmp/mysql. sock
# Here follows entries for some specific programs
# The MySQL server
[Mysqld]
Port = 3306
Socket =/usr/local/mysql/tmp/mysql. sock
Skip-external-locking
Key_buffer_size = 384 M
Max_allowed_packet = 1 M
Table_open_cache = 512
Sort_buffer_size = 2 M
Read_buffer_size = 2 M
Read_rnd_buffer_size = 8 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 8
Query_cache_size = 32 M
# Try number of CPU's * 2 for thread_concurrency
Thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# If all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (Via the "enable-named-pipe" option) will render mysqld useless!
#
# Skip-networking
# Replication Master Server (default)
# Binary logging is required for replication
Log-bin = mysql-bin
Log-slave-updates
# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 10
# Replication Slave (comment out master section to use this)

In the mysql configuration file of HA2, except for the server-id, the others are almost identical. After the configuration file is written, we start the mysql servers on the two servers.

For more details, please continue to read the highlights on the next page:

Recommended reading:

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL + KeepAlived + LVS single-point write master/Master synchronization high-availability architecture Experiment

MySQL master/Master synchronization Configuration

MySQL master-slave copy notes in CentOS 6.3

1 2 Next Page