MySQL Master-Slave synchronization configuration

1. MySQL master-Slave synchronization configuration 1.1. Basic information

Database master server ip:127.0.0.1
Database from server ip:192.168.1.31

1.2. Primary server configuration file My.ini configuration (Master)

[Mysqld]
#打开日志 (the host needs to open) indicates the binary change day value
Log-bin=mysql-bin
#服务器唯一id
Server-id=1
#给从机同步的数据库名称
Binlog-do-db=shenxianyun_console
Binlog-do-db=shenxianyun_portal
Binlog-do-db=test
#自动清理1天前的log文件
Expire_logs_days=1

1.3. Restart the main MySQL service

Service Mysqldrestart

1.4. The primary database authorizes the user to synchronize the database

• Authorized

GRANT Replicationslave on * * to ' user ' @ '% ' identified by ' password ';

• Refresh Permissions

FLUSH privileges;

• View Primary Service Status

SHOW MASTER STATUS;

1.5. My.ini configuration from server configuration file (slave)

[Mysqld]
#服务器id
server-id=2
#要从主机同步的库
Replicate-do-db=shenxianyun_console
Replicate-do-db=shenxianyun_portal
Replicate-do-db=test

# Configure the trunk log (usually not available)
#relay_log =mysql-relay-bin

# slave write the copy event into your own binary log (you'll see what it's for later).
#log_slave_updates = 1

# It prevents data changes (except for special threads) from being used with caution
#read_only = 1
SERVER_ID is a must, and the only. Slave there is no need to open the binary log, but in some cases it must be set, for example, if Slave is the master of the other slave, you must set the Bin_log. Here we have the binary log turned on, and the display is named (the default name is hostname, but if the hostname changes there will be a problem).
Relay_log configures the trunk log, log_slave_updates indicates that slave writes the copy event into its own binary log (which you will see later).
Some people turn on the slave binary log, but do not set log_slave_updates, and then see if slave's data changes, which is a bad configuration. So, try to use READ_ONLY, which prevents the data from changing (except for special threads). However, read_only is not very practical, especially for applications that need to create tables on slave.

1.6. Restart the service from MySQL

Service Mysqldrestart

1.7. Start slave (for data synchronization)

The next step is to have slave connect to master and begin to redo the events in the master binary log. Instead of doing this with a configuration file, you should use the change MASTER to statement, which completely supersedes the modification of the configuration file, and it can specify a different MASTER for slave, without having to stop the server. As follows:

• Configure the parameters that the slave server uses to connect to the master server

Note: master_log_file= ' mysql-bin.000001 ', master_log_pos=594 is through the front main database show Master STATUS;

Change MASTER tomaster_host= ' 1.1.1.1 ', master_user= ' root ', master_password= ' PASSWORD ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=594;

• Turn on slave sync

Start slave;

• View the slave status

show slave status;

When both slave_io_running and slave_sql_running are yes, the master-slave replication succeeds

Slave I/O and SQL threads have started running, and seconds_behind_master is no longer null. The location of the log is increased, which means that some events have been acquired and executed. If you make changes on master, you can see the changes in the location of the various log files on the slave, as well as you can see the changes in the data in the database.

You can view the status of Master and slave on-line. On master, you can see the connection created by the slave I/O thread:

Enter showprocesslist\g on master;

Line 2 to handle slave of the I/O the connection to the thread.

Run the statement on the slave server: showprocesslist;

Line 1 to be I/O thread state, line 2 to be SQL The thread state.

1.8. Add a new slave server

If master has been running for a long time, you want to synchronize data with the newly installed slave, even if it does not have master data.
At this point, there are several ways to get slave to start from another service, such as copying data from Master, cloning from another slave, and starting a slave from the most recent backup. When synchronizing with master, you need three things: Slave
(1) Data snapshot at a certain time of master;
(2) The master current log file, and the byte offset at the time the snapshot was generated. These two values can be called log file coordinate, because they determine the location of a binary log, and you can use the show MASTER Status command to find the coordinates of the log file;
(3) The binary log file of master.

You can clone a slave in the following ways:
(1) Cool copy (Cold copy)
Stop master, copy Master's files to slave, and restart master. The disadvantage is obvious.
(2) Hot copy (warm copy)
If you use only the MyISAM table, you can use the mysqlhotcopy copy even if the server is running.
(3) using mysqldump
Using mysqldump to get a snapshot of a data can be divided into the following steps:
<1> Lock Table: If you do not have the lock table, you should lock the table, prevent other connections to modify the database, otherwise, you can get the data is inconsistent. As follows:
Mysql> FLUSH TABLES with READ LOCK;
< 2> in another connection use mysqldump to create a dump of the database that you want to replicate:
shell> mysqldump--all-databases--lock-all-tables >dbdump.db
< 3> release lock on table.
Mysql> UNLOCK TABLES;

2. Common topologies for replication

The architecture of replication has the following basic principles:
(1) Each slave can have only one master;
(2) Each slave can only have a unique server ID;
(3) Each master can have a lot of slave;
(4) If you set Log_slave_updates,slave to be the master of other slave, it will spread the update of master.

MySQL does not support multi-master server replication (multimasterreplication)-that is, a slave can have more than one master. However, with a few simple combinations, we can build a flexible and powerful replication architecture.

1.9. Single Master and multiple slave

It is easiest to make a replication system from a master and a slave. The slave does not communicate with each other and only communicates with master. As follows:

This structure can be taken if the write operation is small and the read operation is very good. You can distribute the read operations to other slave, thereby reducing the pressure on the master. However, when slave is increased to a certain number, the slave load on master and the bandwidth of the network become a serious problem.
This structure is simple, but it is flexible enough to meet the needs of most applications. Some suggestions:
(1) Different slave play different roles (e.g. using different indexes, or different storage engines);
(2) using a Slave as the backup master, copy only;
(3) Use a remote slave for disaster recovery;

1.10. Active mode Master-master (Master-master in active-active mode)

Master-master replicates two servers, both master and slave of the other server.

Active Master-master replication has some special uses. For example, two parts that are geographically distributed require a copy of their own writable data. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) of data, with a value of 1, and if two servers execute the following statements, respectively:
Execute on the first server:
mysql> UPDATE tbl SET col=col + 1;
Execute on the second server:
mysql> UPDATE tbl SET col=col * 2;
So what's the result? One server is 4 and the other server is 3, but this does not produce an error.
In fact, MySQL does not support a number of other DBMS-supported multi-master server replication (multimasterreplication), which is a significant limitation of the replication functionality of MySQL (the difficulty of a multi-master server is to resolve the update conflict), but if you really have this requirement, You can use MySQL Cluster and combine Cluster and replication to build a powerful, high-performance database platform. However, there are other ways to emulate this multi-master server replication.

1.11. Active-Passive mode master-master (Master-master in active-passive mode)

This is a master-master structure change, it avoids the disadvantage of m-m, in fact, this is a fault-tolerant and high-availability system. The difference is that one of the services can only be read-only.

1.12. Master-master structure with Slave server (master-master with slaves)

The advantage of this structure is that it provides redundancy. Geographically distributed replication structure, it does not have a single node failure problem, and can also put read-intensive requests on the slave.

MySQL Master-Slave synchronization configuration