Shulou(Shulou.com)06/01 Report--

Implement MySQL read-write separation deployment cluster basic environment

1 to achieve the separation of MySQL read and write

1.1 question

This case requires the configuration of 2 MySQL servers and 1 proxy server to achieve read-write separation of MySQL proxies:

Users only need to access the MySQL proxy server, and the actual SQL query and write operations are handed over to the two MySQL servers in the background to complete which the Master server allows SQL query and write, and the Slave server only allows SQL query.

1.2 scenario

Use 4 RHEL 7.2 virtual machines, as shown in figure-1. Among them, 192.168.4.10 and 192.168.4.20 are the MySQL master and slave servers respectively, which are the back end of the whole service; the other 192.168.4.100 is the MySQL proxy server, which is the front end of the customer-oriented service; and the client 192.168.4.120 is used as the access test.

Figure-1

Compare the effects of reading and writing separation between the two methods--

MySQL master-slave replication: the client accesses the Master server to write the database, and the client accesses the Slave server to read the database. In this case, the client needs to distinguish where to write and where to read. MySQL master-slave replication + agent: the client accesses the Proxy server, and the read and write requests are sent to Proxy for identification. If it is a write database operation, it is handed over to Master. If it is a read database operation, it is handed over to Slave, which is controlled by the allocation policy. In this case, there is no need for the client to distinguish between read and write targets, but by the Proxy server, thus reducing the complexity of the client program.

Among them, the construction of MySQL master and slave replication structures refer to the previous lessons, which will not be repeated here.

1.3 steps

To implement this case, you need to follow these steps.

Step 1: deploy the mysql-proxy proxy server

1) install the maxscale package officially provided by mariadb

[root@bogon] # rpm-ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

Modify the configuration file:

[root@pxysvr pub] # [root@bogon ~] # grep-E-v'^ # / etc/ maxscale.cnf [maxscale] threads= 1 [server1] # specify the name corresponding to the ip address type=serveraddress=192.168.4.10 # master database server ip address port=3306protocol= MySQL Monitor [server2] # specify the name type=serveraddress=192.168.4.20 # from the database server ip address port=3306protocol= MySQLBackendMySQL Monitor] # specify the host to be monitored and the user type=monitormodule=mysqlmonservers=server1 connected during monitoring Server2 # hostname user=scalemon # username passwd=111111 # password monitor_interval=10000# [Read-Only Service] # type=service#router=readconnroute#servers=server1#user=myuser#passwd=mypwd#router_options=slave [Read-Write Service] # define server list type=servicerouter=readwritesplitservers=server1 Hostname user=maxscale # username 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 [root@bogon ~] # defined before server2

Add authorized users to the master and slave database servers respectively (only if the master server is authorized, the slave server will automatically synchronize):

[root@pxysvr pub] # mysql > grant replication slave, replication client on *. * to scalemon@'%' identified by "111111"; / / create monitoring user mysql > grant select on mysql.* to maxscale@'%' identified by "111111"; / / create routing user mysql > grant all on *. * to student@'%' identified by "111111"; / / create client access user

2) start the maxscale service

[root@bogon ~] # maxscale-- config=/etc/maxscale.cnf [root@bogon ~] # netstat-utnalp | grep maxscaletcp 00 192.168.4.100 grep maxscaletcp 58960 192.168.4.10 root@bogon 3306 ESTABLISHED 19081/maxscale tcp6 00 192.168.4.100 config=/etc/maxscale.cnf 43508 192.168.4.203306 :: * LISTEN 19081/maxscale [root@bogon ~] # kill-9 19081 / / stop the service by killing the process

Step 2: test the configuration

1) use the above authorized user student to connect to the proxy server 192.168.4.100 on the client 192.168.4.120:

[root@bogon ~] # mysql-h292.168.4.100-P4006-ustudent-p111111MySQL [(none)] > select @ @ hostname / / display the currently visited host +-+ | @ @ hostname | +-+ | slave20 | / / display the hostname of the slave server +-+ Query OK, 0 rows affected (0.00 sec) MySQL [(none)] > insert into bbsdb.a values (111); / / insert a new record

The client currently accesses from the database server and can still insert records. It means success.

2 deploy the basic cluster environment

2.1 question

This case requires the preparation of the basic environment for the MySQL cluster to complete the following tasks:

Database authorization deployment MySQL dual-master multi-slave configuration native hosts parsing record

2.2 scenario

Use four RHEL 6 virtual machines, as shown in figure-1. Among them, 192.168.4.10 and 192.168.4.11 are used as MySQL double master servers, and 192.168.4.12 and 192.168.4.13 are used as slave servers of the master server.

Figure-1

2.3 steps

To implement this case, you need to follow these steps.

Step 1: prepare the environment

[root@master1 ~] # 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 [root@master1 ~] # 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 packet loss 0.387 packet loss 0.396 ms [root@master1 ~] #

Step 2: deploy the database host

1) install the startup database (4 database hosts master1,master2,slave1,slave2 do the following)

[root@master1] # tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar / / decompression package.. . [root@master1 ~] # rpm-Uvh MySQL-*.rpm / / install MySQL.. . [root@master1] # service mysql startStarting MySQL. [OK]

2) initialize the configuration database (4 database hosts master1,master2,slave1,slave2 do the following)

[root@master1 ~] # cat / root/.mysql_secret / / View the randomly generated password # The random password set for the root user at Thu May 7 22:15:47 2015 (local time): wW1BNAjD [root@master1 ~] # mysql-uroot-pwW1BNAjD / / Log in to Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor using the randomly generated password. 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"); / / modify database root password Query OK, 0 rows affected (0.49 sec) mysql > exitBye [root@master1 ~] #

Step 3: deploy the dual-master and multi-slave structure

1) Database authorization (4 database host master1,master2,slave1,slave2 do the following)

To deploy master-slave synchronization, you only need to authorize a master-slave synchronization user, but we need to deploy the MySQL-MMM architecture, so here we will set up the authorization of the users required by MySQL-MMM. Then authorize a test user to test for use when the architecture is built.

[root@master1] # 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.

In order to facilitate the experiment, we directly allow all addresses to access the database authorization part, so we need to be careful in the real environment.

Mysql > grant replication slave on *. * to slaveuser@ "%" identified by "pwd123"; / / Master / Slave synchronous license Query OK, 0 rows affected (0.00 sec) mysql > grant replication client on *. * to monitor@ "%" identified by "monitor" / / MMM required architecture user authorization Query OK, 0 rows affected (0.06 sec) mysql > grant replication client,process,super on *. * to agent@ "%" identified by "agent" / / MMM required architecture user authorization Query OK, 0 rows affected (0.00 sec) mysql > grant all on *. * to root@ "%" identified by "pwd123"; / / Test user authorization Query OK, 0 rows affected (0.00 sec) mysql >

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

Master1 settings:

[root@master1 ~] # cat / etc/my.cnf [mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver_id=10 / / set server_id You cannot repeat log-bin / / enable bin-log 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 [root@master1 ~] # service mysql restart / / restart MySQL service Shutting down MySQL.. in the cluster with this value [OK] Starting MySQL.. [OK] [root@master1 ~] # ls / var/lib/mysql/master1-bin* / / check whether the binlog log is generated / var/lib/mysql/master1-bin.000001 / var/lib/mysql/master1-bin.index [root@master1 ~] #

Master2 settings:

[root@master2 ~] # 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 [root@master2 ~] # service mysql restartShutting down MySQL.. [confirm] Starting MySQL. [OK] [root@master2 ~] # ls / var/lib/mysql/master2-bin.*/var/lib/mysql/master2-bin.000001 / var/lib/mysql/master2-bin.index

3) set server_id from the library

Slave1 settings:

[root@slave1 ~] # 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 [root@slave1 ~] # service mysql restartShutting down MySQL.. [OK] Starting MySQL.. [OK] [root@slave1 ~] #

Slave2 settings:

[root@slave2 ~] # 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 [root@slave2 ~] # service mysql restartShutting down MySQL.. [confirm] Starting MySQL. [OK] [root@slave2 ~] #

4) configure the master-slave relationship

Configure master2, slave1, slave2 to become the slave server of master1

View the master1 server binlong log usage node information:

[root@master1] # mysql-uroot-ppwd123.. .. MySQL > show master status\ gateway * 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:

[root@master2] # mysql-uroot-ppwd123.. .. MySQL > change master to / / set master server information-> master_host= "192.168.4.10", / / set master server IP address-> master_user= "slaveuser", / / set master server synchronization user-> master_password= "pwd123" / / set master / slave synchronization password-> master_log_file= "master1-bin.000001", / / set master library binlog log name-> master_log_pos=120 / / set master-slave binlog log to use node Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql > start slave; / / start synchronization process Query OK, 0 rows affected (0.00 sec) mysql > show slave status\ G / / check whether the master / slave is successful.. ..

After starting the synchronization process, check whether the IO node and the SQL node are Yes. If both are Yes, the master and slave are normal.

Slave_IO_Running: Yes / / IO node is normal Slave_SQL_Running: Yes / / SQL node is normal.. .. MySQL >

Set slave1 to master1 from:

[root@slave1] # 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 node is normal Slave_SQL_Running: Yes / / SQL node is normal.. .. MySQL >

Set slave2 to master1 from:

[root@slave2] # 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 node is normal Slave_SQL_Running: Yes / / SQL node is normal.. .. MySQL >

5) configure the master-slave relationship, and configure master1 as the slave of master2

View binlog usage information for master2:

[root@master2] # mysql-uroot-ppwd123.. .. MySQL > show master status\ gateway * 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 the slave of master2:

[root@master1] # 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 node is normal Slave_SQL_Running: Yes / / SQL node is normal.. .. MySQL >

6) Test whether the master-slave architecture is successful

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

[root@master1] # 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:

[root@master2 ~] # 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 | +- -+ [root@master2 ~] #

Slave1 host view:

[root@slave1 ~] # 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 | +- -+ [root@slave1 ~] #

Slave2 host view:

[root@slave2 ~] # 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 | +- -+ [root@slave2 ~] #

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.