MySQL Master-slave replication

In this section we want to talk about MySQL master-slave replication, the reason for master-slave replication, mainly can read and write separation, reduce the pressure of MySQL server. So let's take a look at how to implement master-slave replication.

The function of copying:

Load Balancing

Data distribution

Backup

High Availability

MySQL Upgrade test

Disadvantages:

Single point

Write operation not balanced

How MySQL replication works:

Place the binary log of the master node on the slave node and re-execute it again

Three steps to replicate:

1. Enable binary logging on the main library

2. Copy the binary log from the main library, and save it to the local relay log;

3. The standby library reads events from the log and executes them locally.

Master-Slave replication framework

650) this.width=650; "style=" border-bottom:0px;border-left:0px;border-top:0px;border-right:0px; "title=" clipboard [5] "border=" 0 "alt=" clipboard[5] "src=" Http://img1.51cto.com/attachment/201409/22/8733640_1411406947omeW.png " height= "391"/>

master:172.16.6.1

slave:172.16.6.2

Note: The slave version must be the same as master, or higher, since the slave node must be compatible with the master node. If you are deploying a standby server on a primary server that already has data, back up the data from the primary server, make a backup of the binary log location record, then restore to the slave server, and specify the replication location from the server's binary log from the primary server, starting at the point in time.

Master side

The following entries are included in the configuration file:

[Mysqld]

Log-bin=/var/log/master-bin Defining binary Logs
BINLOG_FORMAT=MIXD format
Sync_binlog=1 Synchronizing binary logs
server-id=10 master and Slave nodes must be inconsistent

Start MySQL and create a user

mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO ‘[email protected]‘172.16.6.%‘ IDENTIFIED BY ‘123456‘;

mysql> FLUSH PRIVILEGES;

View Binary

mysql> Show master status;

+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
|      master-bin.000001 |              486 |                  | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.02 sec)

SLA ve End

The following entries are included in the configuration file:

[Mysqld]

# log-bin=/mydata/bin-log/master-bin  # 从节点中禁用二进制日志

# binlog_format=mixed

relay-log=/var/log/relay-bin #启用中继日志

sync_binlog = 1

server-id = 20

READ_ONLY = 1

连接主服务器：

mysql> CHANGE MASTER TO MASTER_HOST=‘172.16.6.1‘,MASTER_USER=‘repl‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘master-bin.00001‘,MASTER_LOG_POS=486;

mysql> START SLAVE;

Start thread Io_thread and Sql_thread:

  mysql> Start slave io_thread;
Query OK, 0 rows Affected (0.00 sec)
mysql> start slave sql_thread;
Query OK, 0 rows Affected (0.00 sec)
mysql> Show Slave Status\g

650) this.width=650; "style=" border-bottom:0px;border-left:0px;border-top:0px;border-right:0px; "title=" image " Border= "0" alt= "image" Src= "Http://img1.51cto.com/attachment/201409/22/8733640_1411406947Wz1e.png" height= "236"/ >

Okay, here's the test.

Master

To create a database in the master node

mysql> Create Database MWJ;
Query OK, 1 row Affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| MWJ |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.03 sec)

Slave

To see if the synchronization is in the From node

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| MWJ |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.02SEC)

OK, copy success, then this section of the experiment is here, thank you!

This article is from the "Linux rookie it Road," blog, please be sure to keep this source http://wiggin.blog.51cto.com/8733640/1557158

MySQL Master-slave replication