MySQL Master-slave replication

The implementation principle of database replication replication:

1: The primary server where the statement is run produces a binary log binlog

2: Continuously read the binlog of the master server from the server

3: Binlog translated from the server to its own executable relaylog

4: Execution Relaylog

Implementation steps:

1. First ensure that the primary server opens the binary logging feature so that the primary server immediately generates a binary log once there is data change

2. From the server also need to turn on the binary log and relay logging function, so that you can read binlog from the primary server and generate Relaylog

3. Set up an account from the server on the master server and grant read Binlog permissions

4. Specify the primary server that corresponds to the service, turn on the slave server

Specific implementation:

Now there are 2 Linux,ip under the virtual machine, respectively 192.168.153.158,192.168.153.159

1.158 as the primary server

2.159 as Slave server

3: Ensure the master-slave 3306 Port Interoperability

4: Configure the primary server to open Binlog

#给服务器起一个唯一的id

Server-id=1

#开启二进制日志

Log -Bin=MySQL-bin

#日志格式

Binlog-format=mixed/row/statement

Statement:2 record execution statements, such as update ....

The Row:2 record is a change in disk

Long statement and less disk changes, it is advisable to use row, short statement, but the impact of tens of thousands of rows, disk changes, it is advisable to use statement

Mixed, mixed, determined by the system according to the statement.

-- give the server a unique IDserver-id=158-- declares that the 2 binary log file is mysql-bin.xxxx  Log-bin=mysql-bin- binary log format mixed/row/statement Binlog_format=Mixed

Restart MySQL

5. Configure the slave server

Server-id=159Log-bin=mysql-binrelay - Log =MySQL-relaybinlog-format=Mixed

Restart MySQL

Primary server Authorized User

Grant replication Client,replicationon*. *  to [Email protected] ' 192.168.153.% '  by ' Repl '  Privileges;

Specify the primary server to replicate from the server through the statement (note that you can master multiple from, not one from multi-master)

Change Master toMaster_host='192.168.153.158', Master_user='Repl', Master_password='Repl', Master_log_file='mysql-bin.000001', Master_log_pos=98;

Start

Start slave

At this point, the simplest MySQL master-slave replication is configured to complete.

This is only the implementation of master-slave replication, read and write separation has not been implemented, can be implemented from the application layer, you can also use cluster middleware such as

The official mysql_proxy, the other is the domestic middleware amoeba

MySQL Master-slave replication