CentOs MySQL master-slave replication and read/write separation

MySQL Master-slave Replication (master-slave) and read-write separation (mysql-proxy) practices

As the most widely used free database in the world, MySQL believes that all engineers engaged in system operations must have contacted. However, in the actual production environment, the single MySQL as a separate database is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects.

Therefore, in general, it is through the master-slave Replication (Master-slave) to synchronize the data, and then through the read-write separation (mysql-proxy) to improve the database of the concurrency load of such a scenario for deployment and implementation.

Schematic diagram of the structure

Start building master-slave replication

Server Two units:

Install two MySQL databases separately

1: Installation command

Yum–y Install Mysql-server

2: Configure the login user's password

Demonstrate this action

3: Configure allow third-party machines to access native MySQL

Demonstrate this action

Scenario Description:

Primary database server: 192.168.1.112,mysql is already installed and has no application data.
From the database server: 192.168.1.115,mysql is installed and no application data is applied.

MySQL Configuration

1) vim/etc/my.cnf

2)

3) Next confirm that the server_id on slave and master are correct. You can run SHOW VARIABLES like ' server_id ' on slave and master respectively; To see if the server_id is the same as the one you configured.

4) Restart the Master,slaver two MySQL service separately

5) Login

6) input

7) mysql> SHOW VARIABLES like ' server_id ';

8) To see if the server_id is the same as the one you configured.

9) Master Input

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+

11) record the values of FILE and Position, which need to be used in the subsequent operation from the server.

12) Configure the slave server

Change Master to

Master_host= ' 192.168.0.104 ',

Master_user= ' Root ',

master_password= ' Root ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=106;

13) Start slave synchronization process after correct execution

14) Start Slave

mysql> start slave;

15) View slave status

Mysql> Show Slave Status\g

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

Test Master-slave replication

1: First determine the master, no custom tables from the library

2: Operations on the primary server
Create a database on the primary server itcast_db
mysql> CREATE DATABASE itcast_db;

Create a table on the primary server ITCAST_TB
Mysql> CREATE TABLE ITCAST_TB (ID int (3), name char (10));

Insert a record in table ITCAST_TB on the primary server
mysql> INSERT INTO ITCAST_TB values ("itcast01");

3: See if it is synced from the server?

Summarize:possible causes of failure during the construction process

1:SERVER_ID configuration or configuration is not updated to MySQL data

2:3306 ports Blocked by firewall

3: Incorrect user and password

4:mysql not allow other machines to access

Start building read/write separation preparation:

Server Three:

1: Install two MySQL databases (already installed)

2: Install Mysql-proxy,mysql

Scenario Description:
Database Master master server: 192.168.1.112
Database slave from server: 192.168.1.115
Mysql-proxy Dispatch server: 192.168.1.101

The following operations are performed on 192.168.1.101, the Mysql-proxy dispatch server.

CheckLuais already installed

Mysql-proxy's read-write separation is primarily implemented through the Rw-splitting.lua script, so it is necessary to install LUA.

RPM-QL Lua

InstallationMysql-proxy

Tar xzvf mysql-proxy-0.8.4.tar.gz–c/user/local/src

Cd/user/local/src/mysql-proxy-0.8.4/bin

./mysql-proxy–help-all

Modify read-write detach script Rw-splitting.lua

Modify the default connection, do a quick test, do not change the number of connections to reach 4 o'clock to enable read-write separation
Vim/opt/mysql-proxy/scripts/rw-splitting.lua
=============================
--Connection pool
If not Proxy.global.config.rwsplit then
Proxy.global.config.rwsplit = {
Min_idle_connections = 1,//default is 4
Max_idle_connections = 1,//default is 8
Is_debug = False
}
End

CentOs MySQL master-slave replication and read/write separation