MySQL master-slave server copy operation

Master host ip:192.168.1.19 user name Sunzy password 123456
Slave host ip:192.168.1.20

1. Configure Master
1) Next Configure master, which includes opening the binary log, specifying a unique SERVR ID. For example, add the following values to the configuration file:
[MySQL]
Log-bin=mysql-bin #二进制变更日值, [must] enable binary logging
Server-id=1 #为主服务master的ID值
2) Restart the Master service, run show Master STATUS, and view the configuration.

2. Create a copy Account
Establish a backup account in the master database: Each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions. The password for the user name is stored in the text file Master.info.
The command is as follows:
MySQL > GRANT REPLICATION slave,reload,super on * * ' to backup ' @ ' 192.168.1.20 ' identified by ' 1234 ';
Create an account backup, and only allow to log in from the 192.168.1.20 address, the password is 1234.

3. Configure slave
Slave configuration is similar to master, you also need to restart slave mysql. As follows:
Log_bin = Mysql-bin
server_id = 2 #server_id是必须的, and the only
Relay_log = Mysql-relay-bin
Log_slave_updates = 1
READ_ONLY = 1
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 Configuring the Relay log
Log_slave_updates means 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 useful, especially for applications that need to create tables on slave.

4. Configure slave from the server
1) 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:
mysql> change MASTER to master_host= ' 192.168.1.19 ',
Master_user= ' Sunzy ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=0; #MASTER_LOG_POS的值为0 because it is the starting position of the log.
2) Start the Copy from Server feature
Mysql>start slave;
3) You can use the show SLAVE status statement to see if the SLAVE settings are correct.
mysql> SHOW SLAVE Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.19
Master_user:sunzy
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:164
relay_master_log_file:mysql-bin.000001
relay_log_pos:164
relay_log_file:mysql-relay-bin.000001
Slave_io_running:yes
Slave_sql_running:yes
..... omitted ...
seconds_behind_master:0

Here the main is to see:
Slave_io_running=yes
Slave_sql_running=yes
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.

4) You can view the status of Master and slave on-line.
Enter show Processlist\g on master; #在master上, you can see the connection created by the slave I/O thread:
Enter show processlist\g on the slave; #SQL线程状态

5. 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, create a dump of the database you want to replicate with mysqldump:
shell> mysqldump--all-databases--lock-all-tables >dbdump.db
<3> release lock on table.
Mysql> UNLOCK TABLES;

Attached: 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 (multimaster Replication)-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, single master and multi-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.
In real-world scenarios, MySQL replication over 90% is a master copy to one or more slave schema patterns, primarily for low-cost, scalable solutions to database-side applications with large read-stress ratios. Because as long as the pressure of master and slave is not too great (especially the slave pressure), the delay of asynchronous replication is generally very small. Especially since the slave end of the replication mode changed to two thread processing, but also reduced the slave end of the delay problem. The benefit is that the real-time requirements of the data is not particularly critical application, only through the cheap pcserver to expand the number of slave, the reading pressure spread to multiple slave machines, Can solve the reading performance bottleneck of the database by dispersing the reading pressure of the single database server, after all, the reading pressure in most database application system is much higher than the writing pressure. This largely solves the current database pressure bottleneck of many small and medium-sized websites, and even some large websites are using similar schemes to solve the database bottleneck.
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;

2. Active mode Master-master (Master-master in active-active mode)
Master-master replicates two servers, both master and slave of the other server. In this way, changes made by either party are applied to the other party's database through replication.

Perhaps some readers will have a concern, so that after the replication environment, will not cause two mysql between the replication? In fact, MySQL has long thought of this, so in MySQL Binarylog recorded the current MySQL Server-id, and this parameter is also the time we build mysqlreplication must be explicitly specified, Moreover, the Server-id parameter values of master and slave are more inconsistent than necessary to make the mysqlreplication build successfully. Once you have the value of Server-id, it is easy for MySQL to tell which mysqlserver the change originated from, so it is easy to avoid a recurring replication situation. Also, if we do not open the slave binarylog option (--log-slave-update), MySQL will not record changes in the replication process to Binarylog, not to mention the possibility of a recurring replication situation.

As follows:

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 (multimaster Replication), 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 that need, 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.

3. 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.

Multi-level replication architecture master–slaves-slaves
In some applications, it may be that the pressure difference between reading and writing is large, the reading pressure is particularly large, and a master may need 10 or more slave to support the pressure of the reading. At this time, master will be more difficult, because only the Slaveio thread is more, so write a little bit more pressure, the master side because replication will consume more resources, it is easy to cause replication delay.
How to solve this problem? At this point, we can use MySQL to record the binarylog information on the slave side of the change that was generated, that is, to open the-log-slave-update option. Then, a two-level (or more-level) copy is required to reduce the stress on the master side as a result of replication. That is, we first replicate from master through a handful of MySQL machines, which we would call the first level slave cluster, and then the other slave to replicate from the first level slave cluster. From the first level slave to replicate the slave, I call it the second level slave cluster. If necessary, we can continue to add more layers of replication. This makes it easy for us to control the number of slave attached to each mysql. This architecture I call master-slaves-slaves architecture. This multi-tiered replication architecture makes it easy to address the risk of the master side becoming a bottleneck because of the fact that the secondary slave is too many. Shows the replication architecture for multi-level replication.

Of course, if the conditions allow, I would prefer to suggest that you split into multiple replication clusters to solve.
The above bottleneck problem. After all, slave did not reduce the amount of write, all slave actually still applied all the data change operations and did not reduce any write Io. Conversely, the more slave, the greater the total amount of write Io for the entire cluster, the more we don't have a very obvious feeling, simply because it's scattered over multiple machines, so it's not easy to show.
In addition, increasing the cascade level of replication, the same change to the bottom of the slave need to pass the MySQL will also be more, the same may cause a longer delay risk.
And if we were to solve it by splitting the cluster, it would probably be much better, and of course, a split cluster would require more complex technologies and more complex application architectures.

MySQL master-slave server copy operation