Atlas implements MySQL master-slave separation

Directory.... 1

Document Format: .... 1

I. Introduction to ATLAS and architecture diagram .... 2

Two. Experimental environment .... 3

Three. Implementation cases .... 3

1. Directory structure ... 3

2. Configure MySQL master-slave ... 4

3. Install the configuration atlas. 8

4. Launch Atlas. 12

5. View read-write separation effect ... 13

I. Introduction to ATLAS and architecture diagram

The Atlas source code is written in C, which is equivalent to a DB for Web server, equivalent to a client in db, and if the logic of Atlas is placed in a Web server program, it will greatly increase the complexity of the Web server program, while the Web The coupling between server and DB is also quite high, because as long as the DB increases/decreases service, the WEB server is likely to have code changes, if the code does not change, you have to use the intermediate relational table and heartbeat mechanism to maintain the relationship between the server, which will lead to performance loss, While Atlas is a middleware that is built between Web server and DB, the coupling between Web server and DB is placed in Atlas to handle both flexibility and performance, which is also the value of Atlas.

Atlas supports horizontal slicing of tables, supports read and write separations, and projects that require higher real-time data to increase/*master*/forced read in front of the SELECT statement

Two. Experimental environment

MySQL master:192.168.200.101
MySQL slave:192.168.200.102
atlas:192.168.200.103

Atlas has two ports, respectively

Work Port: Proxy-address item configuration, for example proxy-address = 0.0.0.0:1234 on behalf of the client should use the 1234 port to connect to Atlas to send SQL requests.

Management port: Admin-address item configuration, such as Admin-address = 0.0.0.0:2345, means that the DBA should use the 2345 port to connect to Atlas to perform operational management operations.

Two. Install Atlas

Note: Only install on 64-bit Linux operating system, CentOS official recommended RPM installation method

Three. Implementation case 1. Directory structure

Bin: This directory holds the Atlas launcher, as well as the encryption script

Conf: This directory holds the atlas.conf configuration file and can have multiple instances

Lib: This directory holds some library files and dependent files for the program

LOG: This directory holds the logs generated by the instance

Installation location:

/usr/local/mysql-proxy

Configuration file:

/usr/local/mysql-proxy/conf/test.cnf

2. Configure MySQL master and slave

all hosts shut down the firewall and SElinux

[[Email protected]~]#/etc/init.d/iptables Stop

Iptables:setting chains to Policy Accept:filter [OK]

iptables:flushing firewall rules: [OK]

iptables:unloading modules: [OK]

[[Email protected]~]# setenforce 0

Setenforce:selinux is disabled

Modify the host name ( 200.101 )

[[Email protected]~]# hostname Master

[Email protected]~]# Bash

[[Email protected]~]# vim/etc/sysconfig/network

Networking=yes

Hostname=master

( 200.102 )

[Email protected]~]# hostname Slave

[Email protected]~]# Bash

[[Email protected]~]# vim/etc/sysconfig/network

( 200.103 )

[Email protected]~]# hostname Atlas

[Email protected]~]# Bash

[[Email protected]~]# vim/etc/sysconfig/network

Networking=yes

Hostname=zha

The master and slave respectively use Yum installation MySQL

[[email protected]~]# yum-y install MySQL mysql-server

[[Email protected]~]# yum-y install MySQL Mysql-server

Modify the configuration file and start the service separately

[[Email protected]~]# vim/etc/my.cnf

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

#Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-error=/var/lib/mysql/mysql.err

Log=/var/lib/mysql/mysql_log.log

Log-slow-queries=/var/lib/mysql/mysql_slow_queris.log

Default-character-set=utf8

Log-bin=mysql-bin

Server-id=1

Log-slave-updates

Sync_binlog=1

auto_increment_increment=2

Auto_increment_offset=1

[Client]

Default-character-set=utf8

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

# Note Server-id cannot be the same

[[Email protected]~]#/etc/init.d/mysqld Start

Configuring Master-slave replication

Master Operation:

View file and the POS value

Mysql>show Master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|mysql-bin.000003 |              106 |                  | |

+------------------+----------+--------------+------------------+

1row in Set (0.00 sec)

mysql> grant replication Slave on * * to ' replication ' @ ' 192.168.200.% ' identified by ' 123456 ';

Queryok, 0 rows affected (0.04 sec)

Mysql>flush privileges;

slave Operation:

Mysql>change Master tomaster_host= ' 192.168.200.101 ', master_user= ' replication ', master_password= ' 123456 ', master _log_file= ' mysql-bin.000003 ', master_log_pos=106;

Queryok, 0 rows affected (0.09 sec)

Mysql>slave start;

Queryok, 0 rows Affected (0.00 sec)

Mysql>show slave Status\g

Slave_io_running:yes

Slave_sql_running:yes

(make sure that these two threads are YES )

Master-slave configuration complete!

3. Installation Configuration Atlas

: https://github.com/Qihoo360/Atlas/releases

[Email protected]~]# rpm-ivh atlas-2.2.1.el5.x86_64.rpm

Preparing ... ########################################### [100%]

1:atlas ###########################################[100%]

[[Email protected]~]# vim/usr/local/mysql-proxy/conf/test.cnf

# user name of the management interface

Admin-username= User

# password for the management interface

admin-password= pwd

#Atlas back-end connections. MySQL of the main library IP and ports, you can set multiple items, separated by commas

proxy-backend-addresses= 192.168.200.101:3306

#Atlas back-end connections. MySQL from the library's IP and Ports, @ the following numbers represent weights, which are used for load balancing, and if omitted, the default is 1 , multiple items can be set, separated by commas

proxy-read-only-backend-addresses= 192.168.200.102:[email protected]

# the user name and its corresponding encrypted MySQL password, password use Prefix/bin encryption programs in the directory Encrypt encryption, downlink User1 and the User2 as an example, replace it with your MySQL user name and encryption password!

pwds= user1:+jksgb3yag8=, user2:gs+tr4tpgqc=

# Set Atlas operating mode, set to true is the daemon mode, set to false when the front-office mode, the general development and debugging is set to false , the on-line runtime is set to true,true there must be no spaces behind.

daemon= true

# Set Atlas operating mode, set to true when Atlas will start two processes, one for Monitor , one for worker , Monitor in the worker automatically restarts after an unexpected exit, set to false when only worker , without Monitor , general development debugging is set to false , the on-line runtime is set to true,true there must be no spaces behind.

keepalive= true

# number of worker threads, for Atlas have a significant impact on the performance, and can be set appropriately according to the situation

Event-threads= 8

# log level, divided into message , Warning , Critical , Error , Debug Five levels

Log-level= message

# Path of log storage

Log-path=/usr/local/mysql-proxy/log

#SQL log switch, can be set to OFF , on , REALTIME , OFF representative does not record SQL logs, on Representative Records SQL logs, REALTIME Representative Records SQL log and write to disk in real time, the default is OFF

#sql-log= OFF

# slow log output settings. When this parameter is set, the log only outputs log records with a execution time exceeding Sql-log-slow(inms) . If you do not set this parameter, all logs are output.

#sql-log-slow= 10

# instance name for multiple on the same machine Atlas the distinction between instances

#instance = Test

#Atlas working interface for listening IP and Port

proxy-address= 0.0.0.0:1234

#Atlas management interface for monitoring IP and Port

admin-address= 0.0.0.0:2345

# table setting, in this example Person for the library name, MT is the table name, ID is a table field, 3 as the number of child tables, you can set multiple items, separated by commas, and you do not need to set this if you do not divide the table

#tables = Person.mt.id.3

# The default character set, after which the client no longer needs to execute Setnames Statement

charset= UTF8

# Allow connection Atlas the client's IP , can be a precise IP , or it can be IP segment, separated by commas, if not set, allows all IP Connect, otherwise only allow the list of IP Connection

#client-ips= 127.0.0.1, 192.168.1

#Atlas the front hook LVS the physical NIC of the IP ( Note not virtual IP) , if there is LVS and set the client-ips This key must be set, otherwise you may not set the

#lvs-ips= 192.168.1.1

Change the work port and the management port IP to be Masterip

4. Launch Atlas

[[Email protected]~]#/usr/local/mysql-proxy/bin/mysql-proxyd Test start

Ok:mysql-proxy of Test is started

If error:

/usr/local/mysql-proxy/bin/mysql-proxy:error while loading shared libraries:libcrypto.so.6:cannot open shared Objectfile:no such file or directory

Error:failed to start mysql-proxy of test

so [email protected] ~]# Yum provides*/libcrypto.so.6 # Looking for inclusions libcrypto.so.6 the installation package

Found missing this

Openssl098e-0.9.8e-17.el6.centos.2.x86_64:a

...: Compatibility version of a general

...: Cryptography and TLS Library

Repo:c6-media

Matchedfrom:

Filename:/usr/lib64/libcrypto.so.6

installation [email protected] ~]# Yum-yinstall openssl098e-0.9.8e-17.el6.centos.2.x86_64

[email protected]~]# ln-s libcrypto.so.0.9.8elibcrypto.so.6 # Create a connection file

Give Atlas Default User Authorization

Master and the slave Operation:

mysql> grant replication Slave on * * to ' user ' @ ' 192.168.200.% ' identified by ' PWD ';

Queryok, 0 rows affected (0.04 sec)

Mysql>flush privileges;

Queryok, 0 rows affected (0.12 sec)

5. View read-Write separation effect

[[Email protected]~]# cat/usr/local/mysql-proxy/log/test.log

2018-05-0416:40:53: (message) proxy listening on port 0.0.0.0:1234

2018-05-0416:40:53: (Message) added Read/write backend:192.168.200.101:3306

2018-05-0416:40:53: (Message) added Read-only backend:192.168.200.102:3305

Atlas implements MySQL master-slave separation