Implementing MySQL read-Write separation Deployment cluster infrastructure (with diagram)

实现MySQL读写分离部署集群基础环境

1 realizing MySQL read-write separation
1.1 Questions

This case requires the configuration of 2 MySQL server +1 proxy Server, to achieve the MySQL agent read and write separation:

用户只需要访问MySQL代理服务器，而实际的SQL查询、写入操作交给后台的2台MySQL服务器来完成其中Master服务器允许SQL查询、写入，Slave服务器只允许SQL查询

1.2 Solutions

Using 4 Rhel 7.2 virtual machines,-1 is shown. 192.168.4.10, 192.168.4.20, respectively, as MySQL master, slave server, is the back end of the entire service Another 192.168.4.100, as a MySQL proxy server, is a direct customer-facing front end of the service; client 192.168.4.120 is used as an access test.

Figure-1

Comparison of two ways of reading and writing separation effect--

MySQL主从复制：客户机访问Master服务器来写数据库，客户机访问Slave服务器来读数据库。这种情况下，需要客户端自行区分向何处写、从何处读。MySQL主从复制+代理：客户机访问Proxy服务器，读、写请求交给Proxy识别，如果是写数据库操作则交给Master，如果是读数据库操作则交给Slave处理，具体由分配策略控制。这种情况下，无需客户端区分读、写目标，而是由Proxy服务器代劳了，从而降低了客户端程序的复杂度。

The MySQL master, from the construction of the copy structure reference to the previous course, here no longer repeat.
1.3 Steps

The implementation of this case needs to follow the steps below.

Step one: Deploy the Mysql-proxy proxy server

1) Install the MARIADB official Maxscale package

To modify a configuration file:

[[email protected] pub]# [[email protected] ~]# grep-e-V ' ^# '/etc/maxscale.cnf[maxscale]threads=1[server1 ] #指定ip地址对应的名字type =serveraddress=192.168.4.10 #主数据库服务器ip地址port =3306protocol=mysqlbackend[server2] # Specify the IP address corresponding to the name type=serveraddress=192.168.4.20 #从数据库服务器ip地址port =3306protocol=mysqlbackend[mysql Monitor] #指定要监控的主机 and monitoring when connected users type=monitormodule=mysqlmonservers=server1, Server2 #前边定义的主机名user =scalemon # user name passwd=111111 # password Monitor_i Nterval=10000#[read-only Service] #type =service#router=readconnroute#servers=server1#user=myuser#passwd=mypwd# Router_options=slave[read-write Service] #定义服务器列表type =servicerouter=readwritesplitservers=server1, Server2 # The hostname defined in the front User=maxscale # user name passwd=111111 # password Max_slave_connections=100%[maxadmin service]type=servicerouter=cli#[ Read-only Listener] #type =listener#service=read-only service#protocol=mysqlclient#port=4008[read-write Listener] Type=listenerservice=read-write Serviceprotocol=mysqlclientport=4006[maxadmin Listener]type=listenersErvice=maxadmin serviceprotocol=maxscaledsocket=default[[email protected] ~]#  

Add authorized users to the primary, from the database server (only the primary server is authorized to automatically synchronize from the server):

[[email protected] pub]# mysql> grant replication slave, replication client on *.* to [email protected]‘%‘  identified by “111111”;  //创建监控用户mysql> grant select on mysql.* to [email protected]‘%‘ identified by “111111”;  //创建路由用户mysql> grant all  on *.*  to  [email protected]‘%‘ identified by “111111”;//创建客户端访问用户    

2) Start Maxscale Service

[[email protected] ~]# maxscale --config=/etc/maxscale.cnf[[email protected] ~]# netstat -utnalp | grep maxscaletcp        0      0 192.168.4.100:58960     192.168.4.10:3306      ESTABLISHED 19081/maxscale      tcp        0      0 192.168.4.100:43508     192.168.4.20:3306      ESTABLISHED 19081/maxscale      tcp6       0      0 :::4006                 :::*                    LISTEN      19081/maxscale      [[email protected] ~]# kill -9 19081        //通过杀进程的方式停止服务

Step Two: Test the configuration

1) on the client 192.168.4.120, use the upper authorized user student connect the proxy server 192.168.4.100:

[[email protected] ~]# mysql -h192.168.4.100 -P4006 -ustudent  -p111111MySQL [(none)]> select  @@hostname; //显示当前访问的主机+----------------+| @@hostname      |+----------------+| slave20         |   //显示的是从服务器的主机名+----------------+Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> insert  into bbsdb.a values(111);//插入新纪录

The client is currently accessing the record from the database server and is still able to insert it. Indicates success.
2 Deploying a Clustered infrastructure environment
2.1 Questions

This case requires preparing the basic environment for the MySQL cluster and completing the following tasks:

数据库授权部署MySQL双主多从结构配置本机hosts解析记录

2.2 Solutions

Using 4 Rhel 6 virtual machines,-1 is shown. where 192.168.4.10, 192.168.4.11 as the MySQL dual master server, 192.168.4.12, 192.168.4.13 as the primary server from the server.

Figure-1
2.3 Steps

The implementation of this case needs to follow the steps below.

Step one: Prepare the environment

[[email protected] ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.4.10     master1         master1.tarena.com192.168.4.11     master2         master2.tarena.com192.168.4.12     slave1            slave1.tarena.com192.168.4.13     slave2            slave2.tarena.com192.168.4.100   master1         master1.tarena.com[[email protected] ~]# ping -c 2 master1PING master1 (192.168.4.10) 56(84) bytes of data.64 bytes from master1 (192.168.4.10): icmp_seq=1 ttl=64 time=0.378 ms64 bytes from master1 (192.168.4.10): icmp_seq=2 ttl=64 time=0.396 ms--- master1 ping statistics ---2 packets transmitted, 2 received, 0% packet loss, time 1001msrtt min/avg/max/mdev = 0.378/0.387/0.396/0.009 ms[[email protected] ~]#

Step two: Deploy the database host

1) Install the startup database (4 database hosts master1,master2,slave1,slave2 perform the following actions)

[[email protected] ~]# tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar    //解压软件包.. ..[[email protected] ~]# rpm -Uvh MySQL-*.rpm                                //安装MySQL.. ..[[email protected] ~]# service mysql startStarting MySQL.                                            [确定]

2) Initialize the configuration database (4 database hosts master1,master2,slave1,slave2 perform the following actions)

[[email protected] ~]# cat /root/.mysql_secret         //查看随机生成密码# The random password set for the root user at Thu May  7 22:15:47 2015 (local time): wW1BNAjD[[email protected] ~]# mysql -uroot -pwW1BNAjD        //使用随机生成密码登陆Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.15Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> set password=password("pwd123");          //修改数据库root密码Query OK, 0 rows affected (0.49 sec)mysql> exitBye[[email protected] ~]#

Step three: Deploy a dual master multi-slave structure

1) database Authorization (4 database hosts master1,master2,slave1,slave2 perform the following actions)

Deploying master-Slave synchronization requires only the authorization of a master-slave synchronization user, but we want to deploy the MYSQL-MMM architecture, so here we will mysql-mmm the required users together for authorization settings. Authorize a test user to test the application when the architecture is built.

[[email protected] ~]# mysql -uroot -ppwd123Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.15 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

Database authorization section to facilitate testing we directly allow all addresses to access, the real environment should be cautious

mysql> grant   replication  slave  on  *.*  to  [email protected]"%" identified by  "pwd123";                                //主从同步授权Query OK, 0 rows affected (0.00 sec)mysql> grant  replication  client  on *.*  to  [email protected]"%" identified by "monitor";                                         //MMM所需架构用户授权Query OK, 0 rows affected (0.06 sec)        mysql> grant  replication client,process,super   on *.*  to  [email protected]"%" identified by "agent";                                 //MMM所需架构用户授权Query OK, 0 rows affected (0.00 sec)mysql> grant  all  on *.*  to  [email protected]"%" identified by "pwd123";  //测试用户授权Query OK, 0 rows affected (0.00 sec)mysql>

2) Open the main database binlog log, set server_id (Master1,master2)

Master1 settings:

[[email protected] ~]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver_id=10                        //设置server_id,该值集群中不可以重复log-bin                            //开启bin-log日志# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[[email protected] ~]# service mysql restart                //重启MySQL服务Shutting down MySQL..                                      [确定]Starting MySQL..                                           [确定][[email protected] ~]# ls /var/lib/mysql/master1-bin*        //查看binlog日志是否生成/var/lib/mysql/master1-bin.000001  /var/lib/mysql/master1-bin.index[[email protected] ~]#

Master2 settings:

[[email protected] ~]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver_id=11log-bin# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[[email protected] ~]# service mysql restartShutting down MySQL..                                      [确定]Starting MySQL.                                            [确定][[email protected] ~]# ls /var/lib/mysql/master2-bin.*/var/lib/mysql/master2-bin.000001  /var/lib/mysql/master2-bin.index

3) set server_id from library

Slave1 settings:

[[email protected] ~]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver_id=12# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[[email protected] ~]# service mysql restartShutting down MySQL..                                      [确定]Starting MySQL..                                           [确定][[email protected] ~]#

Slave2 settings:

[[email protected] ~]# cat /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver_id=13# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[[email protected] ~]# service mysql restartShutting down MySQL..                                      [确定]Starting MySQL.                                            [确定][[email protected] ~]#

4) Configure Master slave from relationship

Configure Master2, slave1, slave2 to become master1 from the server

View Master1 server Binlong Logs using node information:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> show master status\G*************************** 1. row ***************************             File: master1-bin.000001         Position: 120     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)mysql>

Set Master2 to Master1 from:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> change  master  to                         //设置主服务器信息    -> master_host="192.168.4.10",                //设置主服务器IP地址    -> master_user="slaveuser",                //设置主从同步用户    -> master_password="pwd123",                //设置主从同步密码    -> master_log_file="master1-bin.000001",    //设置主库binlog日志名称    -> master_log_pos=120;                        //设置主从binlog日志使用节点Query OK, 0 rows affected, 2 warnings (0.06 sec)mysql> start slave;                            //启动同步进程Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G                        //查看主从是否成功.. ..

Check that the IO node and the SQL node are yes if all yes indicates that the master-slave is normal after initiating the synchronization process.

             Slave_IO_Running: Yes                //IO节点正常            Slave_SQL_Running: Yes                //SQL节点正常.. ..mysql>

Set Slave1 to Master1 from:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> change  master  to     -> master_host="192.168.4.10",    -> master_user="slaveuser",    -> master_password="pwd123",    -> master_log_file="master1-bin.000001",                        -> master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.12 sec)mysql> start slave;Query OK, 0 rows affected (0.16 sec)mysql> show slave status\G.. ..             Slave_IO_Running: Yes                //IO节点正常            Slave_SQL_Running: Yes                //SQL节点正常.. ..mysql>

Set Slave2 to Master1 from:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> change  master  to     -> master_host="192.168.4.10",    -> master_user="slaveuser",    -> master_password="pwd123",    -> master_log_file="master1-bin.000001",                        -> master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.13 sec)mysql> start slave;Query OK, 0 rows affected (0.27 sec)mysql> show slave status\G.. ..             Slave_IO_Running: Yes                //IO节点正常            Slave_SQL_Running: Yes                //SQL节点正常.. ..mysql>

5) Configure the master-slave relationship to configure the Master1 to Master2 from

View Master2 's Binlog usage information:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> show master status\G*************************** 1. row ***************************             File: master2-bin.000001         Position: 120     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)mysql>

Set Master1 to become master2 from:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> change  master  to     -> master_host="192.168.4.11",    -> master_user="slaveuser",    -> master_password="pwd123",    -> master_log_file="master2-bin.000001",                        -> master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.31 sec)mysql> start slave;Query OK, 0 rows affected (0.27 sec)mysql> show slave status\G.. ..             Slave_IO_Running: Yes                //IO节点正常            Slave_SQL_Running: Yes                //SQL节点正常.. ..mysql>

6) test whether the master-slave architecture is successful

Master1 update the data to see if the other hosts are synchronized:

[[email protected] ~]# mysql -uroot -ppwd123.. ..mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)mysql> create database tarena;Query OK, 1 row affected (0.06 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || tarena             || test               |+--------------------+5 rows in set (0.00 sec)mysql>

Master2 Host View:

[[email protected] ~]# mysql -uroot -ppwd123 -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || tarena             || test               |+--------------------+[[email protected] ~]#

Slave1 Host View:

[[email protected] ~]# mysql -uroot -ppwd123 -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || tarena             || test               |+--------------------+[[email protected] ~]#

Slave2 Host View:

[[email protected] ~]# mysql -uroot -ppwd123 -e "show databases"Warning: Using a password on the command line interface can be insecure.+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || tarena             || test               |+--------------------+[[email protected] ~]#

Implementing MySQL read-Write separation Deployment cluster infrastructure (with diagram)