1 background
Nowadays, MySQL is widely used. With the increase of users and the increase of data volume, high concurrency will follow.
However, there are many ways to ease the pressure on the database. Distributed databases, load balancing, read/write separation, adding cache servers, and so on.
Here we are going to take the pressure off the database by using the read-write separation technology.
The write efficiency of the database is lower than that of the read efficiency. Generally, the frequency of reading data is higher than that of writing data in the system. Therefore, the read performance of a single database instance is affected when it is written.
2 Basic Environment
| system | IP | configuration |
|---|---|---|
| CentOS 6.5 | 192.168.2.40 | Atlas Proxy Service |
| CentOS 6.5 | 192.168.2.41 | Master MySQL database |
| CentOS 6.5 | 192.168.2.42 | From MySQL database |
| CentOS 6.5 | 192.168.2.43 | From MySQL database |
3 Configure the database
You need to enter the database 192.168.2.41, 192.168.2.42, and 192.168.2.43 to configure the user name and password. The user must be a remotely accessible user. The configuration method is as follows:
Enter the MySQL database 192.168.2.41, create user repl, set password to hello, the following red is the user and password.
mysql> grant all on *.* to repl@'192.168.2. %' identified by "hello";
Query OK, 0 rows affected (0.00 sec)
Copy the codemysql> select user, host from user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| repl | % |
| root | 192.1682..% |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
Copy the codeUpdate the database information. If the database information is not updated, the changes will not take effect immediately, and you need to restart the database. Update database information directly here to avoid restart.
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Copy the codeBoth master and slave MySQL need to create a database. The database I created here is test, in order to facilitate the test of read and write separation
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
Copy the codeNote: The 192.168.2.42/43 database is configured the same as the 192.168.2.41 database, so remember to create the same database
4 Connection to the primary and secondary databases
You need to write the MySQL configuration file to configure the primary and secondary servers. The detailed configuration steps are as follows:
The primary server (192.168.2.41), configured using Vim
[mysqld] datadir = / data/mysql socket = / var/lib/mysql/mysql. The sock user = # mysql master-slave replication configuration innodb_flush_log_at_trx_commit = 1 Sync_binlog =1 # backup database binlog-do-db=test # Backup database binlog-ignore-db=mysql # start binary log-bin=mysql-bin # server ID server-id=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pidCopy the codeNote: If binlog-do-db and binlog_ignore_db are not configured, all databases are backed up. 】
Restart the mysqld service
[root@localhost bin]# /etc/init.d/mysqld restart
Copy the codeAccess the database and configure the primary/secondary replication permission
mysql> grant replication slave on *.* to 'repl'@'192.168.2. %' identified by 'hello';
Query OK, 0 rows affected (0.00 sec)
Copy the codeLocking a database
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Copy the codeTo view the master database information, remember the following File and Position information, which are the key information used to configure the slave database.
If the data in the test database is different from that in the primary database, you need to manually synchronize the data
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin000002. | 17620976 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Copy the code[mysqld] server id=2 and [mysqld] server id=3; [mysqld] server id=3;
192.168.2.42
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Copy the code192.168.2.43
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=3
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Copy the codeEnter the “File” and “Position” information recorded here, and execute it on the slave server:
| Mysql > change master to master_host= ‘192.168.2.41’ | IP address of the primary server |
|---|---|
| Repl master_user = ‘ ‘ | Configure the user name of the primary server |
| Master_password = ‘hello’ | Specifies the password of the user |
| master_port=3306 | Mysql port of the primary server |
| Master_log_file = ‘mysql – bin. 000002’ | The name of the log file must correspond to that of the primary server |
| master_log_pos=17620976 | Log location, corresponding to the primary server |
| master_connect_retry=10 | Reconnection number |
mysql> change master to master_host='192.168.2.41'.-> master_user='repl'.-> master_password='hello'.-> master_port=3306.-> master_log_file='mysql-bin.000002'.-> master_log_pos=17620976.-> master_connect_retry=10;
Query OK, 0 rows affected (0.01 sec)
Copy the codeStart the process
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Copy the codeCheck the status of the master/slave replication. The connection between the master and slave is correct only when both of the following information marked in red are Yes. If one of the following information is No, confirm the log information recorded just now and stop stop slave to reconfigure the master/slave connection.
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.1682.. 41
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin000002.
Read_Master_Log_Pos: 17620976
Relay_Log_File: mysqld-relay-bin000002.
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin000002.
<span style="color: #ff0000;">Slave_IO_Running: Yes</span>
<span style="color: #ff0000;">Slave_SQL_Running: Yes</span>
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 17620976
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
Copy the code5 the Atlas configuration
There will be two versions of Atlas downloaded, one of which is a sub-table version, but this needs other dependencies. I do not need such a requirement as sub-table, so install the normal version
Atlas (normal) : atlas-2.2.1.el6.x86_64.rpm
Atlas (sub-table) : Atlas-sharding_1.0.1-el6.x86_64.rpm
First, go to the Linux Home directory and download the non-table installation package
[root@localhost ~]# cd /home/
[root@localhost home]Wget # https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
Copy the codeOnce the download is complete, install it
[root@localhost home]# RPM - the ivh Atlas - 2.2.1. El6. X86_64. RPM
Preparing... # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]
1:Atlas # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]
Copy the code/usr/local/mysql-proxy = /usr/local/mysql-proxy = /usr/local/mysql-proxy
[root@localhost home]# ll /usr/local/mysql-proxy/total 16 drwxr-xr-x. 2 root root 4096 Dec 28 10:47 bin drwxr-xr-x. 2 root root 4096 Dec 28 10:47 conf drwxr-xr-x. 3 root root 4096 Dec 28 10:47 lib drwxr-xr-x. 2 root root 4096 Dec 17 2014log
Copy the codeAll executable files are delegated to the bin directory
“Encrypt” is used to generate the MySQL password encryption, which will be used during configuration
“Mysql-proxy” is mysql’s own read-write proxy
“Mysql-proxyd” is created by 360 and has a “d” behind it. It is used to start, restart, stop the service. It’s all done by him
The conf directory assigns configuration files
“Test.cnf” has only one file, used to configure the agent, which can be edited using Vim
The lib directory places packages, as well as Atlas dependencies
The log directory contains logs, such as error information
Go to the bin directory and use encrypt to encrypt the password of the database. The user name of my MySQL data is repl and the password is Hello. I need to encrypt the password
[root@localhost bin]# ./encrypt hello
RePBqJ+5gI4=
Copy the codeConfigure Atlas and use Vim for editing
[root@localhost conf]# cd /usr/local/mysql-proxy/conf/
[root@localhost conf]# vim test.cnf
Copy the codeAfter entering, you can configure in Atlas. The Chinese annotations written by 360 are very detailed, and the information can be configured according to the annotations. Among them, the important configurations need to be explained are as follows
This is the account and password of the administrator used to log in to Atlas, corresponding to which are “IP address and port of the management interface monitored by Atlas”. That is to say, you need to set the login port of the administrator to enter the administrator interface. The default port is 2345, and you can also specify the IP address for login. Other IP addresses cannot access the administrator’s command interface. For testing purposes, I did not specify IP and port login here.
Admin-username = admin # Admin-password = admin.comCopy the codeThis is used to configure the address of the master database and the address of the slave database. In this case, the master database is configured to be 41 and the slave database to be 42/43
# Atlas backend addresses = 192.168.2.41:3306 # Atlas backend addresses = 192.168.2.41:3306 The default value is 1 if omitted. Multiple objects can be set and separated by commas (,) proxy-read-only-backend-addresses = 192.168.2.42:3306@1,192.168.2.43:3306@1Copy the codeMy MySQL user is repl and password is hello. I just used the tool provided by Atlas to generate the corresponding encrypted password
# User name and its corresponding encrypted MySQL password, which is encrypted using the encrypt program in the PREFIX/bin directory PWDS = repl:RePBqJ+5gI4=Copy the codeThis is to set the working interface and the management interface. If the IP address is set to 0.0.0.0, it means that any IP address can access this interface. Of course, you can also specify the IP address and the port, which is convenient for test. The administrator password corresponds to the password configured above.
# Proxy-address = 0.0.0.0:1234 # Proxy-address = 0.0.0.0:2345Copy the code
Start the Atlas
[root@localhost bin]# ./mysql-proxyd test start
OK: MySQL-Proxy of test is started
Copy the codeRun the following command to enter the management mode of Atlas
[root@localhost bin]# mysql -h127.0.0.1 -P2345 -uadmin -padmin.com
Copy the codeBeing able to go in means that Atlas is working properly, because it will treat itself as a MySQL database, so you can go into MySQL database mode without needing a database environment
[root@localhost bin]# mysql -h127.0.0.1 -P2345 -uadmin -padmin.com
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Copy the codeYou can access the “help” table to see what the MySQL administrator mode can do. This can be accessed using SQL statements
mysql> select * from help;
+----------------------------+---------------------------------------------------------+
| command | description |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id | | SET ONLINE $backend_id | online backend server, ... | | ADD MASTER $backend | example: "ADD MASTER 127.0.0.1:3306",... | | ADD SLAVE $backend | example: "ADD SLAVE 127.0.0.1:3306",... | | REMOVE BACKEND $backend_id | example: "remove backend 1", ... | | SELECT * FROM clients | lists the clients | | the ADD CLIENT $CLIENT | example: "the ADD CLIENT 192.168.1.2 instead",... | | REMOVE CLIENT $CLIENT | example: "REMOVE CLIENT 192.168.1.2 instead",... | | SELECT * FROM pwds | lists the pwds | | ADD PWD $pwd | example: "add pwd user:raw_password", ... | | ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... | | REMOVE PWD $pwd | example: "remove pwd user", ... | | SAVE CONFIG | save the backends to config file | | SELECT VERSION | display the version of Atlas | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 16 rows in the set (0.00 SEC) mysql>Copy the codeIt can also be accessed using the working interface, using the mysql -h127.0.0.1 -p1234-urepl -phello command
[root@localhost bin]# mysql -h127.0.0.1 -p1234-urepl-phello
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.81-log
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select * from backends;
+-------------+-------------------+-------+------+
| backend_ndx | address | state | type| + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- + | 1 | 192.168.2.41:3306 | up | rw | | 2 | 192.168.2.42:3306 | | of the up Ro | | 3 | 192.168.2.43:3306 | up | ro | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rowsin set (0.01 sec)
Copy the code6 Separate read and write tests
Jmeter is the first open source stress testing tool for Java writing, because it is convenient. He has a special test MySQL module, need to use the MySQL JDBC driver JAR package, configuration is very simple, the thing is very good, very powerful and easy to use.
6.1 Testing write Operations
As you can see, both the master database and the slave database have high traffic. The master database is writing and the slave database is synchronizing data.
6.2 Testing read Operations
You can see that when the read operation is performed, the primary database has little traffic and the secondary database has a lot of traffic, which confirms that the data is being read from the database. Read/write separation has been implemented.