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

The following mainly brings you how to use Altas software to achieve mysql master-slave replication read-write separation. I hope that how to use Altas software to achieve mysql master-slave replication read-write separation can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

Mysql principle of read-write separation:

In the case of high concurrency in the database layer, iUnix will cause bottlenecks. In fact, the request for reading is much greater than the request for writing.

Using the proxy service as the front end of the database, different requests are assigned to different back-end data according to the rules, such as assigning write requests to master databases and reading requests to slave databases. Master and slave can be one or more load balancers. Master database synchronizes data to slave through master-slave replication.

Environment introduction:

HostNameOSIP role mastercentos6.5192.168.100.150 serves as mysql master cloud server salvecentos6.5192.168.100.151 serves as mysql slave server Altascentos6.5192.168.100.152 acts as mysql proxy ftpcentos6.5192.168.100.100 acts as ftp master slave provides yum source, software support (public network yum source can be used instead of this host)

1: install mysql from master to slave:

[root@master ~] # yum-y install mysql-server [root@slave ~] # yum-y install msyql-server

2: modify the master-slave configuration file to support bin_log logging

[root@master ~] # vi / etc/my.cnf 7 log-bin=mysql-bin # # supports bin-log logging. The bin-log log file name starts with mysql-bin and is the unique identifier of 8 server-id=150 # # service. The default is 1. It is easy to remember here. I used the last paragraph of ip [root@slave ~] # vi / etc/my.cnf 7 server-id=151 [root@master ~] # / etc/init.d/mysqld start # # restart the service [root@slave ~] # / etc/init.d/mysqld start

3: grant slave replication permissions on the master database:

Log in to CVM for authorization

[root@master ~] # mysqladmin-uroot password 123123 [root@master ~] # mysql-uroot-p123123mysql > grant replication slave on *. * to 'slave'@ "192.168.100.%" identified by' 123123 transactions query OK, 0 rows affected (0.00 sec) mysql > flush privileges; # # Refresh permission Query OK, 0 rows affected (0.00 sec) mysql >

View the bin-log log file information of the main service:

Need to record the contents of file and position: whichever is found shall prevail.

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 476 | +-+ 1 row in set (0.00 sec)

4: modify your master database from the CVM

Log into the database

[root@slave ~] # mysqladmin-uroot password 123123 [root@slave ~] # mysql-uroot-p123123

Set the information about reading master bin-log from CVM

Mysql > change master to-> master_host='192.168.100.150', # # master ip-> master_user='slave', # # user name authorized to allow replication-> master_password='123123', # # authorized allow replication password-> master_log_file='mysql-bin.000003', # # bin-log file name The information found on master in the previous step-> master_log_pos=476 # # offset, the information found on master Query OK, 0 rows affected (0.07 sec)

Start slave

Mysql > start slave;Query OK, 0 rows affected (0.00 sec)

Card slave status:

# # the two checked statuses are yes. If there is no error error below, it will be normal Slave_IO_Running: Yes Slave_SQL_Running: Yes

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.150 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 706 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 481 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 706 Relay_Log_Space: 637 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: 0Master_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 specifiedmysql >

5: test:

Create a new library on the master database and view the library

Mysql > mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > create database test_databases;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | test | | test_databases | +-+ 4 rows in set (0.00 sec)

View the library from the database:

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | | test_databases | +-+ 4 rows in set (0.00 sec)

Authorization on the masterside:

Mysql > grant all on *. * to root@ "192.168.100.%" identified by '123123 query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql >

Download and install software on Atlas CVM

[root@Atlas ~] # wget-O. / altas https://github.com/Qihoo360/Atlas/releases/download/sharding-1.0.1/Atlas-sharding_1.0.1-el6.x86_64.rpm[root@Atlas ~] # lsaltas anaconda-ks.cfg install.log install.log.syslog [root@Atlas ~] # file altas altas: RPM v3.0 bin i386/x86_64 Atlas-sharding_1.0.1-el6 [root@Atlas ~] # rpm-ivh altas Preparing... # [100%] 1:Atlas #

Modify the configuration file:

[root@Atlas ~] # vim / usr/local/mysql-proxy/conf/test.cnf

What needs to be changed:

Proxy-backend-addresses = 192.168.100.150:3306proxy-read-only-backend-addresses = 192.168.100.151:3306pwds = root:++gAN07C/Q0= # # here use / usr/local/mysql-proxy/bin/encrypt plus the password authorized by the database to generate the ciphertext password and fill in it here

Generate ciphertext password

[root@Atlas bin] # pwd/usr/local/mysql-proxy/bin [root@Atlas bin] #. / encrypt 123123++gAN07C/Q0=daemon = truesql-log = REALTIMEcharset = utf8

All configuration items, along with comments

# password of the management interface admin-password = IP and port of the MySQL master library connected to the pwd#Atlas backend. Multiple entries can be set. Separate the IP and port of the MySQL slave library of the proxy-backend-addresses = 192.168.100.150:3306#Atlas backend connection with a comma, and the number after @ represents the weight, which is used for load balancing. If omitted, it defaults to 1, and multiple entries can be set. Separate the encrypted MySQL password from the user name of # proxy-read-only-backend-addresses = 127.0.0.1:3305@1proxy-read-only-backend-addresses = 192.168.100.151MySQL with a comma. The password is encrypted using the encryption program encrypt in the PREFIX/bin directory, and the downlink user1 and user2 are examples and replace it with your MySQL username and encrypted password! Pwds = root:++gAN07C/Q0=# sets the running mode of Atlas, daemon when true, foreground when false, false when developing and debugging, and no spaces after true,true when running online. Daemon = truekeepalive = number of true# worker threads, which has a great impact on the performance of Atlas. According to the situation, the event-threads = log level can be set appropriately, which can be divided into five levels: message, warning, critical, error and debug. # with # is the non-essential configuration item # the user name of the management interface admin-username = the password of the user# management interface admin-password = the IP and port of the MySQL main library connected to the pwd#Atlas backend. Multiple items can be set. Use a comma to separate the IP and port of the MySQL connected to the proxy-backend-addresses = 192.168.100.150:3306#Atlas backend, and the number after @ represents the weight, which is used for load balancing. If omitted, it defaults to 1, and multiple items can be set. Separate the encrypted MySQL password corresponding to the user name # proxy-read-only-backend-addresses = 127.0.0.1:3305@1proxy-read-only-backend-addresses = 192.168.100.151IP 330" with a comma. The password is encrypted using the encryption program encrypt in the PREFIX/bin directory. Take the following user1 and user2 as examples and replace them with your MySQL username and encrypted password! Pwds = root:++gAN07C/Q0=# sets the running mode of Atlas, daemon when true, foreground when false, false when developing and debugging, and no spaces after true,true when running online. Daemon = truekeepalive = number of true# worker threads, which has a great impact on the performance of Atlas. You can set the event-threads = log level appropriately according to the situation, which can be divided into five levels: message, warning, critical, error, and debug. The switch of log-level = message# log storage path log-path = / usr/local/mysql-proxy/log#SQL log can be set to OFF, ON, REALTIME,OFF for not recording SQL log, and ON for recording SQL log. REALTIME stands for recording SQL logs and writing them to disk in real time. The default is OFFsql-log = REALTIME# slow log output setting. When this parameter is set, the log only outputs log records whose execution time exceeds sql-log-slow (in ms). If this parameter is not set, all logs are output. # sql-log-slow = 1 instance name, used to distinguish between multiple Atlas instances on the same machine # instance = test#Atlas snooping work interface IP and port proxy-address = 0.0.0.0:1234#Atlas snooping management interface IP and port admin-address = 0.0.0.0test#Atlas snooping 234mm subtable setting, in this case person is the library name, mt is the table name, id is the sub-table field, 3 is the number of child tables, multiple entries can be set, separated by commas If you do not divide the table, you do not need to set the default character set of the item # tables = person.mt.id.3#. After setting this item, the client no longer needs to execute the SET NAMES statement charset = utf8# to allow the IP of clients connecting to Atlas, which can be exact IP or IP segments separated by commas. If this item is not set, all IP connections are allowed. Otherwise, only the IP connection # client-ips = 127.0.0.1 in the list is allowed, and the IP of the physical Nic of the LVS attached in front of the 192.168.1#Atlas (note that it is not a virtual IP). If there is a LVS and client-ips is set, this must be set, otherwise # lvs-ips = 192.168.1.1 can not be set.

Start the shutdown agent service:

[root@Atlas bin] # lsencrypt mysql-binlog-dump mysql-myisam-dump mysql-proxy mysql-proxyd VERSION [root@Atlas bin] #. / mysql-proxyd test startOK: MySQL-Proxy of test is started [root@Atlas bin] #. / mysql-proxyd test stopOK: MySQL-Proxy of test is stopped [root@Atlas bin] #. / mysql-proxyd test startOK: MySQL-Proxy of test is started [root@Atlas bin] #. / mysql-proxyd test restartOK: MySQL-Proxy of test is stoppedOK: MySQL-Proxy of test is started

View the process:

[root@Atlas ~] # ps aux | grep mysql-proxyroot 1266 0.00.2 67156 1452? S 19:24 0:00 / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/test.cnfroot 1267 0.00.6 161460 3352? Sl 19:24 0:01 / usr/local/mysql-proxy/bin/mysql-proxy-- defaults-file=/usr/local/mysql-proxy/conf/test.cnfroot 16756 0.00.1 103248 876 pts/0 S + 20:55 0:00 grep mysql-proxy

Install mysql and install only the client.

[root@Atlas ~] # yum-y install mysql [root@Atlas ~] # mysql-uroot-p123123-h 192.168.100.152-P1234mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > quitBye

View log information:

[root@Atlas] # tail-f / usr/local/mysql-proxy/log/test.log

2017-08-22 19:24:32: (message) proxy listening on port 0.0.0.0 purl 1234

2017-08-22 19:24:32: (message) added read/write backend: 192.168.100.150purl 3306

2017-08-22 19:24:32: (message) added read-only backend: 192.168.100.151 purl 3306

2017-08-22 19:24:32: (message) chassis-event-thread.c:235: starting 8 threads

2017-08-22 19:24:34: (message) chassis-unix-daemon.c:138: [angel] we try to keep PID=1267 alive

2017-08-22 19:24:34: (message) mysql-proxy 0.8.2 started-instance: test

2017-08-22 19:24:34: (message) proxy listening on port 0.0.0.0 purl 1234

2017-08-22 19:24:34: (message) added read/write backend: 192.168.100.150purl 3306

2017-08-22 19:24:34: (message) added read-only backend: 192.168.100.151 purl 3306

2017-08-22 19:24:34: (message) chassis-event-thread.c:235: starting 8 threads

# # you can see that all read operations are handed over to the slave database, and master can read and write operations, usually write-only.

Log in to the management port: (you can manage the back-end mysql database)

[root@Atlas] # mysql-uuser-ppwd-h292.168.100.152-P2345mysql > select * from help # # View Management 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 ADD MASTER 3306",... | | ADD SLAVE $backend | example: "add slave 127.0.0.1 online backend server 3306" | | ADD GMASTER $group_id $backend | example: "add gmaster 1 127.0.0.1 add gmaster 3306",... | | ADD GSLAVE $group_id $backend | example: "add gslave 1 127.0.1 backend 3306",... | | REMOVE BACKEND $backend_id | example: "remove backend 1". | | REMOVE GBACKEND $group_id $backend_id | example: "remove gbackend 1 1",... | | SELECT * FROM clients | lists the clients | | ADD CLIENT $client | example: "add client 192.168.1.2" | | REMOVE CLIENT $client | example: "remove client 192.168.1.2",... | | 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 | +- -+-+ 19 rows in set (0.00 sec) mysql > select * from backends # # View the status of backend mysql Work type +-+-+ | group_id | address | state | type | backend_ndx | +-+- -+ |-1 | 192.168.100.150 up 3306 | up | rw | 1 |-1 | 192.168.100.151 range 3306 | up | ro | 2 | +-+- -- + 2 rows in set (0.00 sec)

For the above about how to use Altas software to achieve mysql master-slave replication read-write separation, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.