MySQL Master server configuration

If a business scenario, after testing, read-write ratio is listed 1:20, according to the ratio of reading and writing, reasonable set optimization scheme.

Read-write ratio column:

Write data/Read data ratio, Insert/update/delete/select

The basic elements are derived from the concept of "read and write separation":

In general, the read server refers to the data mirroring of the write server.

From the server side: to have n units from the server and the primary server to keep the data consistent.

See from the client: for example, there is an INSERT statement and a SELECT statement,

The read/write statements are differentiated and the slave/master server is requested separately.

The specific technology of server-side read and write separation

1: DB cluster technology

Cluster consists of 3 concepts

(1) SQL Nodes SQL node

(2) Data node

(3) Management node NDB managerment

SQL statements send "1SQL nodes", "1sql" nodes to "2 data Nodes", and 3 management nodes to complete synchronization between data nodes.

Cluster technology is relatively complex, there are at least 3 kinds of nodes, 4 servers to complete.

2, database replication

Write/master (0) synchronous read/slave (1)

3, 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 read from the server, converted to its own executable relaylog

(4) Execution Relaylog

4, the implementation steps:

(1) First ensure that the primary server opens the binary logging feature

In this way, once the primary server has data changes, the binary log is generated immediately

(2) from the server also need to open the binary log and relay log function

This allows you to read binlog from the primary server and generate Relaylog

(3) Create an account from the server on the primary server and grant the highest privileges

(4) Specify the primary server corresponding to the server, open the slave server

Specific implementation

If a virtual machine XP, and a Linux

(1) Install MySQL as slave server under virtual machine XP

(2) Compile MySQL under Linux as the primary server

(3) To ensure that XP and Linux 3306 Port interoperability

(4) Configure the master server to open Binlog

Mysql>show Master status;

Empty Set (0.00 sec)

#开启二进制日志

Log-bin=mysql-bin

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

Serve-id=1

#指定日志格式

Binlog-format=mixd/row/statement

Restart MySQL

Mysql>show Master status;

There will be two more files: mysql-bin.000001 and Mysql-bin.index

has been able to act as master server

5, configure the Binlog and Relaylog to open from the server

mysql>show slave status;

Empty Set (0.00 sec)

[MySQL]

#slave Config

Log_bin =mysql-bin

server_id =2

Relay_log =mysql-relay-bin

Log_slave_updates =1

Read_Only =1

Restart from server

6, create the corresponding replication account on the master server

Mysql>grant Replication Slave,replication Client

->on * *

->to [email protected] ' 192.168.2.% ' identified by ' 111111 ';

Query OK, 0 rows affected (0.02 sec)

Mysql>flush privileges;

Query ok,0 Rows Affected (0.00 sec)

7, specify the primary server to replicate from the server through the statement (note that can be a master multiple from, can not be one from the multi-master)

Mysql>change Master to

->master_host= ' 192.168.2.99 ', (primary server IP)

->master_user= ' Repl ',

->master_password= ' 111111 ',

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

->master_log_pos=0;

Query OK, 0 rows affected (0.08 sec)

8, start from server function

->start slave;

9, test

How to use the routing statement when the client is applied

Generally in two ways

(1) directly in PHP's MySQL class to make judgments, the simplest, without additional software

such as the Discuz forum.

Code:

The following MySQL class, not just a MySQL class, also acts as a routing feature for a SQL statement.

Class mysql{

$DBM = primary server;

$dbs 1= from server 1;

$dbs 2= from server 2;

Public Function query () {

Make sentence judgments in query

Connect to different MySQL servers separately

}

}

New MySQL

Mysql->query ();

(2) Using cluster middleware

such as the official Mysql_proxy, as well as the domestic middleware amoeba