PROXYSQL,MARIADB master-slave copy and read/write separation

Get ready:

4 VMS (CentOS 7.3):

   192.168.1.120   proxysql   192.168.1.121   node1   master    192.168.1.122   Node2   slave   192.168.1.123   node3   Slave

I. Installing MARIADB in NODE1,NODE2,NODE3, Mariadb-server

(installed as root user)

1. Yum installation

Yum Install mariadb mariadb-server-y

2. Start the service

# Systemctl Start Mariadb.service

Boot up

3. Configure the Administrator password

# mysql_secure_installation

4. Configure remote access (MySQL prompt is represented by mysql>)

Mysql>Use MySQL; MySQL> Grant all privileges on * * to'Root'@'IP'Identified by'123456'; This allows only the specified IP access to MySQL> Grant all privileges on * * to'Root'@'%'Identified by'123456'; This allows arbitrary IP access to MySQL> Flush Privileges;

Two. Configuring Master-slave replication

Node1:/etc/my.cnf.d/server.cnf

[Mysqld]skip_name_resolve=oninnodb_file_per_table=onserver_id=1Log_bin =master-bin

Restart Service

# systemctl Restart Mariadb.service

Create user Mauser (user name arbitrary, here is Mauser): Node2 and Node3 replicate data via user Mauser

' Mauser ' @'192.168.1.%'123456'; MySQL > Flush Privileges;

And then:

Mysql> Show master status;

The number above will need to be used.

Node2:/etc/my.cnf.d/server.cnf

and Node1 not the same is more read_only this line, with this line, in Proxysql mysql_servers table will have only one row hostgroup_id=1, if you remove this line, Mysql_ There are two rows in the servers table from each server:

Hostgroup_id=0, hostgroup_id=1

[Mysqld]skip_name_resolve=oninnodb_file_per_table=onserver_id=2Relay_ Log=relay-logread_only=on

Restart Service

# systemctl Restart Mariadb.service

Set to from the database, Master_log_pos to set the number in the image above (756)

mysql> Change Master to master_host='192.168.1.121', master_user='Mauser  ', master_password='123456', master_log_file='  master-bin.000001', master_log_pos=756;mysql> start slave;
Mysql> show slave status\g;    ...  
    Slave_io_running:yes         These two lines must be Yes    Slave_sql_running:yes
    ...

Node3:/etc/my.cnf.d/server.cnf

[Mysqld]skip_name_resolve=oninnodb_file_per_table=onserver_id=3Relay_ Log=relay-logread_only=on

Restart Service

# systemctl Restart Mariadb.service

Set to from database

mysql> Change Master to master_host='192.168.1.121', master_user='Mauser', Master_ password='123456', master_log_file='master-bin.000001', master_log_pos=756; mysql> start slave;

Go back to Node1, build a library, build a table, insert data:

mysql> CREATE DATABASE Mybatisdb;
mysql> CREATE TABLE person (IDintnull primary key, name varchar (int) ;
Mysql> INSERT into the person values (1, ' Tom ', +), (2, ' Mary ', 30)

Back to Node2, Node3 view

mysql> use  mybatisdb;mysql>  Select * from person;

You can see the person table data.

Master-slave replication is complete.

Three. Read and write separation

Download proxysql:http://www.proxysql.com/here is 1.4.7

On the 192.168.1.120 VM

# yum Install Mariadb-y
Yum Install proxysql-1.4. 7-1-centos7.x86_64.rpm

After the installation is complete, modify the/ETC/PROXYSQL.CNF

Datadir="/var/lib/proxysql"Admin_variables={admin_credentials="Admin:admin"mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"}mysql_variables={Threads=4max_connections=2048Default_query_delay=0Default_query_timeout=36000000have_compress=truePoll_timeout= -Interfaces="0.0.0.0:3306;/tmp/mysql.sock"Default_schema="Information_schema"stacksize=1048576server_version="5.5.56"Connect_timeout_server= themonitor_history=600000Monitor_connect_interval=60000Monitor_ping_interval=10000Monitor_read_only_interval= theMonitor_read_only_timeout= -Ping_interval_server=120000Ping_timeout_server= -Commands_stats=trueSessions_sort=trueconnect_retries_on_failure=Ten}mysql_servers=({address="192.168.1.121"Port=3306HostGroup=0max_connections= -}, {address="192.168.1.122"Port=3306HostGroup=1max_connections= -}, {address="192.168.1.123"Port=3306HostGroup=1max_connections=100
}
)
Mysql_users:
(
{
Username = "root"
Password = "123456"
Default_hostgroup = 0
max_connections=1000
Default_schema= "MySQL"
Active = 1
}
)
Mysql_query_rules:
(
{
Rule_id=1
Active=1
Match_pattern= "^select. * for update$"
Destination_hostgroup=0
Apply=1
},
{
rule_id=2
Active=1
Match_pattern= "^select"
Destination_hostgroup=1
Apply=1
}
)
mysql_replication_hostgroups=
(
{
Writer_hostgroup=0
Reader_hostgroup=1
}
)

Before starting Proxysql: To add a monitor user to the primary database (Node1) and give the usage and replcation client permissions, Proxysql Use this user connection node1,node2,node3.

Of course, you can modify this monitor to change to another user name and password, not described here.

Proxysql default configuration monitor 's password is also monitor (in the Global_variables table of the main database)

' Monitor '@'192.168.1.120'monitor'; MySQL> flush privileges, which allows the proxysql to use monitor/monitor access, which is also synchronized to NODE2,NODE3, so the other two VMS do not have to do so.

Start Proxysql:

# Systemctl Start Proxysql

Connection Proxysql:

# mysql-h127. 0.0. 1 -uadmin-padmin-p 6032

four. Test :

On other VMS (not on Proxysql VMs, like Node1), connect Proxysql

192.168. 1.120 -uroot-p123456
...
mysql> use Mybatisdb;
Mysql> INSERT into the person values (3, ' Mike ', (4, ' Jack ', 39)

To stop the Node1 Primary database service:

Systemctl Stop Mariadb.service

When you connect Proxysql again, you can still perform select, but insert Insert error:

ERROR 9001 (HY000): Max Connect timeout reached while reaching HostGroup 0 after 10000ms

Description, insert cannot be executed because the primary database is dead. However, select is still operational, indicating that read-write separation is working.

Five. What's important

The above is implemented from scratch, in fact, in general, there is a database first, due to performance problems, to achieve read-write separation and master-slave replication.

In this case, the original data to a full backup (mysqldump), import the server, and then the original database show master status that Pos, write into the server (change master, start Slave),

So as to achieve master-slave replication.

Primary database backup:
# mysqldump--all-databases--master-data=2 --routine--trigger--event  --lock-all-tables > X.sql

In the new Add database import:
# mysql-u ...-P ... < x.sq

Change the new database from the database and start
mysql> Change Master ...;
mysql> start slave;

PROXYSQL,MARIADB master-slave copy and read/write separation