MySQL + Atlas to achieve database read and write separation | 8 months more text challenge

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

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 code

mysql> 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 code

Update 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 code

Both 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 code

Note: 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 code

Note: 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 code

Access 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 code

Locking a database

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Copy the code

To 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 code

192.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 code

Enter the “File” and “Position” information recorded here, and execute it on the slave server:

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 code

Start the process

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Copy the code

Check 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 code

5 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 code

Once 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 code

All executable files are delegated to the bin directory

1. “Encrypt” is used to generate the MySQL password encryption, which will be used during configuration

2. “Mysql-proxy” is mysql’s own read-write proxy

3. “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

1. “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 code

Configure Atlas and use Vim for editing

[root@localhost conf]# cd /usr/local/mysql-proxy/conf/
[root@localhost conf]# vim test.cnf
Copy the code

After 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 code

This 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 code

My 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 code

This 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 code

Run the following command to enter the management mode of Atlas

[root@localhost bin]# mysql -h127.0.0.1 -P2345 -uadmin -padmin.com
Copy the code

Being 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 code

You 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 code

It 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 code

6 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.