Shulou(Shulou.com)05/31 Report--

This article mainly talks about "the implementation of Mysql replication Replication", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Mysql replication Replication implementation method" it!

Implementation of Mysql replication (Replication)

Individuals need to do a small project, need to synchronize the data of three Mysql servers, check the information on the Internet, and complete the basic idea of practical operation.

First of all, three Centos6.2 sets are prepared, with basic Webserver configurations such as Apache Mysql PHP.

The three machines are Master Avatar 192.168.1.49 www.2cto.com.

Slave B1:192.168.1.50

B2:192.168.1.51

The environment I need is one-way replication, from replicating A's data to B1 and B2. Make the data of the three computers consistent.

Configuration logic:

From a high-level perspective, replication is divided into three steps:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

Start configuration: www.2cto.com

Step 1: create a replication account

Each slave connects to the master using a standard MySQL username and password. The user performing the replication operation is granted REPLICATIONSLAVE permission.

The password for the user name is stored in the text file master.info. Suppose you want to create a repl user, as follows:

one

Mysql > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. *

two

-> TO repl@'192.168.1.%' IDENTIFIED BY '123456'

Step 2: configure My.cnf

Configure the My.cnf of Master, the default location of which is / etc/my.cnf

Next, configure master, including opening the binary log and specifying a unique servr ID. For example, add the following values to the configuration file:

[mysqld]

Log-bin=mysql-bin

Server-id=10

Restart master and run SHOW MASTER STATUS. The output is as follows:

Configure the My.cnf of Slave, the default location of which is / etc/my.cnf

The configuration of Slave is similar to that of master, and you also need to restart MySQL for slave. As follows:

Log_bin = mysql-bin

Server_id = 2 www.2cto.com

Relay_log = mysql-relay-bin

Log_slave_updates = 1

Read_only = 1

Server_id is necessary and unique. It is not necessary for slave to turn on binary logging, but in some cases, it must be set, for example, if slave is the master of another slave, bin_log must be set. Here, we turn on the binary log and display the name (the default name is hostname, but there will be a problem if the hostname changes).

Relay_log configures relay logs, and log_slave_updates indicates that slave writes replication events to its own binary log (you'll see its usefulness later).

Some people open the binary log of slave, but do not set log_slave_updates, and then check to see if the data of slave has changed, which is a misconfiguration. Therefore, try to use read_only, which prevents data from being changed (except for special threads). However, read_only is not very useful, especially for applications that need to create tables on slave.

Step 3: start slave

The next step is to have slave connect to master and start redoing 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 replaces changes to the configuration file, and it can specify a different master for the slave without stopping the server. As follows:

one

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.49'

two

-> MASTER_USER='repl'

three

-> MASTER_PASSWORD='123456'

four

-> MASTER_LOG_FILE='mysql-bin.000001'

five

-> MASTER_LOG_POS=0

The value of MASTER_LOG_POS is 0 because it is the starting position of the log. Then, you can use the SHOW SLAVE STATUS statement to see if the slave is set correctly:

01

Mysql > SHOW SLAVE STATUS\ G

02

* * 1. Row *

03 www.2cto.com

Slave_IO_State:

04

Master_Host: server1

05

Master_User: repl

06

Master_Port: 3306

07

Connect_Retry: 60

08

Master_Log_File: mysql-bin.000001

09

Read_Master_Log_Pos: 4

ten

Relay_Log_File: mysql-relay-bin.000001

eleven

Relay_Log_Pos: 4

twelve

Relay_Master_Log_File: mysql-bin.000001

thirteen

Slave_IO_Running: No

fourteen

Slave_SQL_Running: No

fifteen

... omitted...

sixteen

Seconds_Behind_Master: NULL

Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running indicate that slave has not yet started the replication process. The location of the log is 4 instead of 0, because 0 is the starting location of the log file, not the log location. In fact, the location of the first event that MySQL knows is 4.

To start copying, you can run:

01

Mysql > START SLAVE

02

Mysql > SHOW SLAVE STATUS\ G

03

Run SHOW SLAVE STATUS to view the output:

04

* * 1. Row *

05

Slave_IO_State: Waiting for master to send event

06 www.2cto.com

Master_Host: server1

07

Master_User: repl

08

Master_Port: 3306

09

Connect_Retry: 60

ten

Master_Log_File: mysql-bin.000001

eleven

Read_Master_Log_Pos: 164

twelve

Relay_Log_File: mysql-relay-bin.000001

thirteen

Relay_Log_Pos: 164

fourteen

Relay_Master_Log_File: mysql-bin.000001

fifteen

Slave_IO_Running: Yes

sixteen

Slave_SQL_Running: Yes

seventeen

... omitted...

eighteen

Seconds_Behind_Master: 0

Notice that both the slave O and SQL threads are already running, and that Seconds_Behind_Master is no longer NULL. The location of the log has increased, which means that some events have been obtained and executed. If you make changes on master, you can see changes in the location of various log files on slave, as well as changes in data.

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

01

> show processlist\ G

02

* * 1. Row *

03

Id: 1

04

User: root

05

Host: localhost:2096

06

Db: test www.2cto.com

07

Command: Query

08

Time: 0

09

State: NULL

ten

Info: show processlist

eleven

* 2. Row * *

twelve

Id: 2

thirteen

User: repl

fourteen

Host: localhost:2144

fifteen

Db: NULL

sixteen

Command: Binlog Dump

seventeen

Time: 1838 www.2cto.com

eighteen

State: Has sent all binlog to slave; waiting for binlog to be updated

nineteen

Info: NULL

twenty

2 rows in set (0.00 sec)

Basically finished here, as for the later enhanced operation, I will add separately.

The main problems in the testing process are:

1. Please configure Mysql first

two。 Please copy the Mysql library and the frame of the table before enabling replication. (test again if you can copy the table! )

At this point, I believe that everyone on the "Mysql replication Replication implementation method" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.