MySQL (master/slave) master copy principle and configuration graphics and text detailed _mysql

1 Replication Overview

MySQL's built-in replication functionality is the foundation for building large, high-performance applications. To distribute MySQL data to multiple systems, this distribution mechanism is done by replicating data from one of MySQL's hosts to other hosts (slaves) and performing it again. One server acts as the primary server during the replication process, and one or more other servers act as a server. The master server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates sent to the server. When a primary server is connected from a server, it notifies the primary server of the location of the last successful update that was read from the server in the log. Receive any updates from the server that have occurred since then, and then block and wait for the primary server to notify the new updates.

Please note that when you replicate, all updates to the tables in the replication must be done on the primary server. Otherwise, you must be careful to avoid conflicts between updates made to tables on the primary server and updates made to tables on the server.

1.1 The type of replication supported by MySQL:

(1): statement-based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL defaults to the use of statement-based replication, high efficiency.
Once you find that you cannot replicate exactly, you automatically select a row-based copy.
(2): Based on the copy of the row: change the content of the past, rather than the command on the server from the execution. Starting from mysql5.0 support
(3): Mixed type of replication: The default is based on the statement of replication, once found based on the statement can not be accurate replication, will be based on the row of replication.

1.2. Replication-Resolved issues

MySQL replication technology has the following features:
(1) Data distribution (distribution)
(2) Load balancing (loading balancing)
(3) Backup (Backups)
(4) High availability and fault-tolerant line availability and failover

1.3 How replication works

On the whole, replication has 3 steps:

(1) Master changes the record to the binary log (binary log) (these records are called binary logs, binary log events);
(2) Slave will copy Master's binary log events to its trunk logs (relay log);
(3) Slave the event in the relay log to change the data that reflects it.

The following figure describes the process of replication:

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is completed. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are executed in a cross execution. After the event writes to the binary log, master notifies the storage engine to commit the transaction.
The next step is slave to copy Master's binary log to its own relay log. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and begins binlog the dump process. The Binlog dump process reads events from Master's binary log and, if it has been followed by Master, sleeps and waits for master to generate new events. The I/O thread writes these events to the relay log.
SQL slave thread (SQL from a thread) handles the last step of the process. The SQL thread logs the event from the log and replays the events in it to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the relay log is typically located in the OS cache, so the overhead of the relay log is very small.
In addition, there is also a worker thread in master: As with other MySQL connections, slave opening a connection in master also makes master start a thread. The replication process has an important limitation--replication is serialized on the slave, meaning that parallel update operations on master cannot be run concurrently on slave.

2. Replication Configuration

There are two MySQL database servers master and Slave,master primary servers, slave from the server, the initial state, master and slave the same data information, when the data in master changed, Slave also followed the corresponding changes, so that master and slave data information synchronization, to achieve the purpose of backup.

Points:
The medium responsible for transmitting various modifications to the master and from the server is the master server's binary change log, which records the various modifications that need to be transmitted to the server. Therefore, the primary server must activate the binary logging feature. The server must have sufficient permissions to connect it to the primary server and request the primary server to transmit the binary change log to it.

Environment:
The MySQL database version of Master and slave is the same as 5.0.18
Operating system: Unbuntu 11.10
IP Address: 10.100.0.100

2.1, create a replication account

1. Create a backup account in Master's database: Each slave uses a standard MySQL username and password to connect to master. The user who made the copy will grant replication slave permissions. The password for the username will be stored in the text file Master.info

The order is as follows:
MySQL > GRANT REPLICATION slave,reload,super on *.*
To backup@ ' 10.100.0.200 '
Identified by ' 1234 ';

Set up an account backup and only allow landing from 10.100.0.200 this address, the password is 1234.

(if the MySQL version is different from the old and new password algorithm, you can set: Set password for ' backup ' @ ' 10.100.0.200 ' =old_password (' 1234 '))

2.2. Copy data

(If you are completely new to install MySQL master server, this step is not required.) Because the newly installed master and slave have the same data)

Shut down the master server, copy the data from master to the B server, synchronize the data in master and slave, and ensure that no write operations are done on the master and slave servers until all settings are complete. Make sure that the data in both databases must be the same!

2.3, Configuration Master

Next, configure Master to include opening the binary log and specifying a unique SERVR ID. For example, add the following values to the configuration file:

Server-id=1
Log-bin=mysql-bin

Server-id: ID value for primary server A
Log-bin: Binary Change Day value

Restart Master to run show Master STATUS with the following output:

2.4, Configuration Slaveslave Configuration and master similar, you also need to restart slave mysql. As follows:
Log_bin = Mysql-bin
server_id = 2
Relay_log = Mysql-relay-bin
Log_slave_updates = 1
READ_ONLY = 1
SERVER_ID is a must, and the only. Slave it is not necessary to open the binary log, but in some cases it must be set, for example, if Slave is the master of the other slave, the Bin_log must be set. Here we turn on the binary log, and the name that is displayed (the default is hostname, but the problem occurs if the hostname changes).
Relay_log configures the relay log, log_slave_updates indicates that slave writes the copy event into its own binary log (which is useful later).
Some people turn on the binary log of slave, but do not set log_slave_updates, and then see if the slave data changes, which is an incorrect configuration. So, try to use READ_ONLY, which prevents data from being changed (except for special threads). However, Read_Only is also useful, especially for those applications that need to create tables on slave.

2.5. Start slave The next step is to have slave connect to master and begin to redo events in the master binary log. Instead of using a configuration file to do this, you should use the change MASTER to statement, which replaces the configuration file completely, and it can specify a different MASTER for slave without stopping the server. As follows:

mysql> change MASTER to master_host= ' Server1 ',

-> master_user= ' Repl ',

-> master_password= ' P4ssword ',

-> master_log_file= ' mysql-bin.000001 ',

-> master_log_pos=0;

The value of Master_log_pos is 0 because it is the starting position of the log.

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:

Master_host:server1

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

Read_master_log_pos:4

relay_log_file:mysql-relay-bin.000001

Relay_log_pos:4

relay_master_log_file:mysql-bin.000001

Slave_io_running:no

Slave_sql_running:no

... omitted ...

Seconds_behind_master:null

Slave_io_state, slave_io_running, and slave_sql_running are no.

Indicates that slave has not started the replication process yet. The position of the log is 4 instead of 0, because 0 is the starting position of the log file, not the log location. In fact, the first event that MySQL knows is in the position of 4.

To start copying, you can run:

Mysql> START SLAVE;

Run Show SLAVE Status View output:

Mysql> Show SLAVE Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:server1

Master_user:repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:164

relay_log_file:mysql-relay-bin.000001

relay_log_pos:164

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

... omitted ...

seconds_behind_master:0

Here is mainly 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 position of the log has increased, meaning that some events have been fetched and executed. If you make changes to master, you can see changes in the location of the various log files on the slave, and you can also see the changes in the data in the database.

You can view the status of the master and slave threads. On master, you can see the connections created by the slave I/O thread:

Enter show Processlist\g on master;

Mysql> Show Processlist \g 1. Row *************************** Id:1 User:root host:localhost:2096 Db:test Command:query time:0 State:null Info:show processlist 2. Row *************************** Id:2 User:repl host:localhost:2144 Db:null Command:binlog Dump time:1838 State:has sent all binlog to slave; Waiting for Binlog to is updated Info:null 2 rows in Set (0.00 sec)

Row 2 is a connection to handle the slave I/O thread.

Run the statement on the slave server:

Mysql> Show Processlist \g 1. Row *************************** Id:1 User:system User Host: Db:null Command:connect time:2291 State:waiting for Master to send event Info:null 2. Row *************************** Id:2 User:system User Host: Db:null Command:connect time:1852 State:has read all relay log; Waiting for the slave I/O thread to update it Info:null 3. Row *************************** Id:5 User:root host:localhost:2152 Db:test Command:query time:0 State:null Info:show processlist 3 Rows in Set (0.00 sec)

Row 1 is the I/O thread state, and row 2 is the SQL thread state.

2.5, add new slave server

If master has been running for a long time, he wants to sync the newly installed slave, even if it doesn't have master data.
At this point, there are several ways to get slave to start with another service, such as copying data from Master, cloning from another slave, and starting a slave from the most recent backup. When slave is synchronized with master, you need three things:
(1) A snapshot of the data at some point in master;
(2) Master's current log file, and the byte offset at the time the snapshot was generated. These two values can be called log file coordinates (coordinate) because they determine the location of a binary log, and you can use the show MASTER Status command to locate the log file coordinates;
(3) Master's binary log file.

You can clone a slave in the following ways:
(1) Cold copy (chilly copy)
Stop master, copy Master's files to slave, and then restart Master. The disadvantages are 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 have not locked the 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> use mysqldump to create a dump of the database you want to replicate on another connection:
shell> mysqldump--all-databases--lock-all-tables >dbdump.db
<3> release lock on table.
Mysql> UNLOCK TABLES;

3, in-depth understanding of replication has discussed some of the basics of replication, the following in-depth discussion of replication.

3.1, statement-based replication (statement-based Replication)     &NBSP;MYSQL 5.0 and previous versions support only statement based replication (also known as logical replication, logical Replication), which is not common in the database. Master records the query that changes the data, and then slave reads the event from the secondary log and executes it, the same SQL statements as Master executes.
The advantage of this approach is that it is simple to implement. In addition, the binary log of a statement-based replication is well compressed, and the log has a smaller amount of data and less bandwidth--for example, a query that updates gigabytes of data requires only a dozens of-byte binary log. Mysqlbinlog is very convenient for log processing based on statements.
 
      However, statement-based replication is not as simple as it seems, because some query statements depend on the specific conditions of master, for example, master and slave may have different times. Therefore, the MySQL binary log format is not just a query statement, but also includes some metadata information, such as the current timestamp. Even so, there are statements, such as the current user function, that do not replicate correctly. In addition, stored procedures and triggers are also a problem.
      Another problem is that statement-based replication must be serialized. This requires a lot of special code, configuration, such as InnoDB next-key locks. Not all storage engines support statement-based replication.

3.2, record-based replication (row-based Replication) MySQL adds a record-based replication and records changes to the actual data in the binary log, similar to how other DBMS implementations are implemented. There are advantages and disadvantages in this way. The advantage is that you can work correctly on any statement, and some statements are more efficient. The main drawback is that binary logs can be large and not intuitive, so you can't use Mysqlbinlog to view binary logs.
For some statements, a record-based replication can work more efficiently, such as:
Mysql> INSERT into Summary_table (col1, col2, Sum_col3)
-> SELECT col1, col2, sum (col3)
-> from Enormous_table
-> GROUP by col1, col2;
Suppose there are only three unique combinations of col1 and col2, but the query scans many rows of the original table and returns only three records. At this point, record-based replication is more efficient.
The following statement, on the other hand, is more efficient based on the statement's replication:
mysql> UPDATE enormous_table SET col1 = 0;
The cost of using a record-based replication at this time can be very high. Since both methods cannot be handled well in all cases, MySQL 5.1 supports dynamic swapping prior to statement-based replication and record-based replication. You can control by setting the session variable Binlog_format.

3.3, copy related files in addition to the binary log and relay log files, there are some other copy-related files. As follows:

(1) Mysql-bin.index Server once the binary log is turned on, a file with the same name as the 2nd log file, but ending with. Index is generated. It is used to track which binary log files exist on the disk. MySQL uses it to locate binary log files. Its contents are as follows (on my machine):

(2) Mysql-relay-bin.index the function of the file is similar to the Mysql-bin.index, but it is for the relay log, not the binary log. The contents are as follows:
. \mysql-02-relay-bin.000017
. \mysql-02-relay-bin.000018

(3) Master.info to save master information. Do not delete it, otherwise, slave can not connect master after reboot. The contents are as follows (on my machine):

The I/O thread updates the Master.info file as follows (on my machine):

. \mysql-02-relay-bin.000019 254 mysql-01-bin.000010 286 0 52813

(4) Relay-log.info contains information about the current binary and relay logs in Slave.

3.4, send replication events to other slave when you set up log_slave_updates, you can let slave play the other slave master. At this point, slave writes the events executed by the SQL thread into its own binary log (binary log), and then its slave can get these events and execute it. As follows:

3.5, Replication filter (Replication Filters) replication filter allows you to copy only a subset of the data in the server, there are two kinds of replication filtering: in master to filter the events in the binary log; on the slave, filter the events in the relay log. As follows:

4, replication of the common structure of replication architecture has the following basic principles:
(1) Each slave can only have 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 the Log_slave_updates,slave can be master of other slave, thereby diffusing the update of master.

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

4.1, single master and multiple slave The replication system consists of a master and a slave is the simplest case. Slave do not communicate with each other and can only communicate with master. As follows:

This structure can be used if the write operation is less and the read operation is very good. You can distribute the read operation to other slave to reduce the pressure on master. However, when the slave is increased to a certain number, slave's load on master and network bandwidth can 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 standby master for replication only;
(3) The use of a remote slave for disaster recovery;

4.2, the active mode of Master-master (Master-master in active-active mode) Master-master replicated two servers, both Master and another server slave. As shown in figure:

Active Master-master replication has some special uses. For example, two parts of a geographic distribution require their own writable copy of the data. The biggest problem with this structure is the update conflict. Suppose a table has only one row (one column) of data, the value is 1, and if two servers execute the following statement at the same time:
Execute on the first server:
mysql> UPDATE tbl SET col=col + 1;
Executing on the second server:
mysql> UPDATE tbl SET col=col * 2;
So how much is the result? One server is 4 and the other server is 3, but this does not create errors.
In fact, MySQL does not support multiple master server replication (multimaster Replication) supported by other DBMS, which is a significant limitation of MySQL's replication capabilities (the difficulty of a multiple-master server is to resolve update conflicts), but if you really have this 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 simulate the replication of this multiple-master server.

4.3, active-Passive mode master-master (Master-master in active-passive mode) This is Master-master structural change, it avoids the m-m disadvantage, in fact, This is a system with fault tolerance and high availability. The difference is that one of the services can only perform read-only operations. As shown in figure:

4.4, with the master-master structure from the server (Master-master with slaves) The advantage of this structure is the provision of redundancy. A geographically distributed replication structure that does not have a single node failure problem and can also place read-intensive requests on slave.