Shulou(Shulou.com)06/01 Report--

1. The principle explains that replication has the following three steps:

(a) the primary server records data changes in the binary log. (this is called a binary log event (binary log events). )

(B) copy the binary log events from the master server to its own relay log (relay log) from the slave server.

(C) replay the events in the relay log from the server and apply the changes to your own data.

Step 1: record a binary log on the primary server. Before each transaction that updates the data is completed, the primary server records the data changes in the binary log. Even if the transaction is interlaced during execution, mysql writes the transaction to the binary log serially. After the event is written to the binary log, the primary server tells the storage engine to commit the transaction.

Step 2: copy the binary log of the master server to your hard disk from the server and enter the so-called "relay log (relay log)."

It first starts a worker thread called the I / O thread. This Ithumb O thread opens a normal client connection and then starts a special binary log dump process. This dump process reads events from the binary log of the primary server. It does not poll events. If it keeps up with the primary server, it goes to sleep and waits for a signal from the primary server when a new event occurs. The Ithumb O thread writes events from the server's relay log.

Step 3: SQL handles the last part of the process from the thread. The thread reads the relay log, replays the events in it, and then updates the data from the server. Because this thread can keep up with the Ithumb O thread, the relay log is generally in the operating system's cache, so the overhead of the relay log is very low. At the same time, events executed by SQL threads can also be written to the server's own binary log.

Second, the structure description of the main server

192.168.0.103

Mysqlserver

From the server

192.168.0.104

Mysqlslave

L2 server mysql version is consistent, initialize the table, and start mysql in the background

L premise: turn off the firewall and selinux

L can achieve instance-level, database-level, table-level replication, this experiment is the instance-level master-slave replication

Replication advantage: no need to share storage, transfer changed data over the network to slave library

Modify the master server master:#vi / etc/my.cnf / / and add the following parameters

[mysqld]

Log-bin=mysql-bin / / [must] enable binary logging

Server-id=103 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

4. Modify the slave server slave:#vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [not required] enable binary logging

Server-id=104 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

Restart the mysql/etc/init.d/mysql restart of the two servers

Establish an account on the master server and authorize slave:create user 'rep1'@'192.168.0.%' identified by' rep1234'

GRANT REPLICATION SLAVE ON *. * TO 'rep1'@'192.168.0.%'

/ / generally, no root account is used. "%" means that all clients may be connected. As long as the account number and password are correct, you can use specific client IP here, such as 192.168.145.226, to enhance security.

The user information will not be copied to the slave library

7. Log in to the mysql of the master server and query the status of master > show master status

Note: do not modify the primary server table after performing this step to prevent the status value of the primary server from changing

8. Configure Slave from the server: > change master to master_host='192.168.0.103',master_port=3306,master_user='rep1',master_password='rep1234',master_log_file='mysql-bin.000001',master_log_pos=350; / / be careful not to disconnect. There are no single quotation marks around the 350th digit.

Mysql > start slave; / / start the copy from server function

9. Check the status of replication from the server:

Mysql > show slave status\ G

Note: the Slave_IO and Slave_SQL processes must be running normally, that is, the YES state, otherwise they are all in the wrong state (for example, one of the NO is wrong).

View on Master:

10. Master-slave server test:

The master server Mysql, set up the database, and insert a piece of data into the library by creating a table:

Mysql > create database hi_db

Query OK, 1 row affected (0.00 sec)

Mysql > use hi_db

Database changed

Mysql > create table hi_tb (id int (3), name char (10))

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into hi_tb values (001)

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | hi_db |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Query from server Mysql:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | hi_db | / / Itemm here, you see? |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > use hi_db

Database changed

Mysql > select * from hi_tb; / / View the specific data added on the master server

+-+ +

| | id | name |

+-+ +

| | 1 | bobu |

+-+ +

1 row in set (0.00 sec)

11. Master-slave manual switch 1) stop the IO_THREAD thread from the slave library

Mysql > stop slave IO_THREAD

2) activate the slave library

Root@localhost:francs > stop slave

Root@localhost:francs > reset master

Root@localhost:francs > reset slave

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 106 | |

+-+ +

1 row in set (0.00 sec)

3) restart the slave library

[root@mysqlslave ~] # / etc/init.d/mysqld restart

Mysql > show slave status\ G

Empty set (0.00 sec)

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000002

Position: 360

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4) create a replication user from the library

Create user 'rep2'@'192.168.0.%' identified by' rep1234'

GRANT REPLICATION SLAVE ON *. * TO 'rep2'@'192.168.0.%'

/ / generally, no root account is used. "%" means that all clients may be connected. As long as the account number and password are correct, you can use specific client IP here, such as 192.168.145.226, to enhance security.

5) Operation on the original main library 103

Change the original master library into a slave library

CHANGE MASTER TO

MASTER_HOST='192.168.0.104'

MASTER_PORT=3306

MASTER_USER='rep2'

MASTER_PASSWORD='rep1234'

MASTER_LOG_FILE='mysql-bin.000002'

MASTER_LOG_POS=360

Mysql > start slave

Mysql > show slave status\ G

6) copy data testing

Insert data on 104 original slave database master database

Query on 103 original master database and now slave database

Master-slave failback purpose: to switch back the previous manual switch and then cut back

103 failback back to the master

104 failback from

1) 103 stop the IO thread from the library

Mysql > stop slave IO_THREAD

Mysql > show slave status\ G

2) activate 103 slave library

Root@localhost:francs > stop slave

Root@localhost:francs > reset master

Root@localhost:francs > reset slave

Mysql > show binary logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mysql-bin.000001 | 106 | |

+-+ +

1 row in set (0.00 sec)

3) restart 103 slave libraries and queries

[root@mysqlserver ~] # / etc/init.d/mysqld restart

Mysql > show slave status\ G

Empty set (0.00 sec)

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000002

Position: 106

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4) Operation on 104

Change the original master library into a slave library

CHANGE MASTER TO

MASTER_HOST='192.168.0.103'

MASTER_PORT=3306

MASTER_USER='rep1'

MASTER_PASSWORD='rep1234'

MASTER_LOG_FILE='mysql-bin.000002'

MASTER_LOG_POS=106

Mysql > start slave

Mysql > show slave status\ G

5) Test

Update records on 103

Mysql > insert into hi_tb values ('5m')

Query OK, 1 row affected (0.00 sec)

Query on 104

13. Master-slave downtime test 1) simulate master downtime

103 downtime

104 View

2) 103 restore

Replication recovery

3) 104 downtime

103 the main library will not be affected

4) 104 recovery

Start 104 library, copy and restore

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.