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

Experimental purpose:

Set up the basic environment of atlas-mysql and realize the separation of reading and writing. And create a user table, split horizontally with the 'id' field as a condition, and split into three child tables.

The steps of the experiment:

1. Deploy two mysql-master nodes for two-way synchronization. And configure keepalived to ensure high availability by connecting to VIP.

two。 Deploy two mysql-slave nodes to synchronize data from mysql-master through VIP

3. Deploy one atlas node, connect to mysql-master through VIP, and add 2 mysql-slave nodes

Experimental environment:

Mysql-master1 Centos 6.8

Serverid 11

192.168.1.102

192.168.1.110 (VIP)

Mysql-master2 Centos 6.8

Serverid 12

192.168.1.103

192.168.1.110 (VIP)

Mysql-slave1 Centos 7.2

Serverid 21

192.168.1.200

Mysql-slave2 Centos 7.2

Serverid 22

192.168.1.202

Atlas Centos 6.8

192.168.1.104

1. Install mysql:

Operate on all mysql nodes

1. Prepare mysql groups and users

Groupadd-r-g 306 mysql

Useradd-r-g mysql-u 306-s / sbin/nologin mysql

two。 Prepare the package

Tar-xf mariadb-5.5.46-linux-x86_64.tar.gz-C / usr/local/

Ln-s / usr/local/mariadb-5.5.46-linux-x86_64//usr/local/mysql

3. Modify file permissions

Chown-R root:mysql / usr/local/mysql/*

4. Initialize the database

Mkdir-p / mydata/dbdata

Chown mysql:mysql / mydata/dbdata

/ / create a database storage directory and modify permissions

/ usr/local/mysql/scripts/mysql_install_db--user=mysql-basedir=/usr/local/mysql-datadir=/mydata/dbdata

/ / initialize the database

5. Prepare the basic configuration file

Rm-f / etc/my.cnf

/ / Delete the configuration file that comes with the system

Mkdir / etc/mysql

Cp / usr/local/mysql/support-files/my-large.cnf/etc/mysql/my.cnf

/ / copy the configuration template

6. Add environment variables, etc.

Echo 'export PATH=/usr/local/mysql/bin:$PATH' > / etc/profile.d/mysqld.sh

Source / etc/profile.d/mysqld.sh

Echo'/ usr/local/mysql/lib/' > / etc/ld.so.conf.d/mysqld.conf

Ldconfig

Ln-s / usr/local/mysql/include/mysql/ / usr/include/mysqld

Echo 'MANPATH / usr/local/mysql/man/' > > / etc/man.config

7. Configure startup script

Cp / usr/local/mysql/support-files/mysql.server/etc/rc.d/init.d/mysqld

Chmod + x / etc/rc.d/init.d/mysqld

Chkconfig-add mysqld

/ / Centos 6 can directly copy the scripts provided in the package.

Vim / usr/lib/systemd/system/mariadb.service

/ / Centos 7 manually configure the startup script

[Unit] Description=MariaDBdatabase serverAfter=syslog.targetAfter=network.target [Service] Type=simpleUser=mysqlGroup=mysql ExecStart=/usr/local/mysql/bin/mysqld_safe--basedir=/usr/local/mysql TimeoutSec=300 [Install] WantedBy=multi-user.target

Systemctldaemon-reload

/ / reload systemctl

Second, further configure two mysql-master to achieve two-way synchronization:

Operate on two mysql-master nodes

Vim/etc/mysql/my.cnf

Servicemysqld start

/ / start the service

Mysql

/ / Log in to mysql for further operations on the current node

INSTALL PLUGIN rpl_semi_sync_masterSONAME 'semisync_master.so'

INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so'

/ / install the semi-synchronous plug-in, with both master nodes installed

GRANTREPLICATION SLAVE, REPLICATION CLIENT ON *. * TO repl@'192.168.1.%' IDENTIFIEDBY '12345'

FLUSHPRIVILEGES

/ / create a synchronization account on each master node and keep it consistent

CHANGE MASTER TOMASTER_HOST='192.168.1.103',MASTER_USER='repl',MASTER_PASSWORD='12345'

/ / execute on master1 and point to the ip address of master2

CHANGE MASTER TOMASTER_HOST='192.168.1.102',MASTER_USER='repl',MASTER_PASSWORD='12345'

/ / execute on master2 and point to the ip address of master1

START SLAVE

/ / all master nodes start the SLAVE function

Exit

Vim/etc/mysql/my.cnf

/ / modify the configuration file again to enable the semi-synchronous plug-in to start automatically

Servicemysqld restart

/ / restart the service

Verification

Mysql

/ / Log in to two master nodes for verification

SHOW SLAVESTATUS\ G

/ / check whether the synchronization is normal

SHOW STATUS LIKE'rpl_%'

/ / View semi-synchronous status

3. Configure keepalived to achieve high availability of mysql-master:

Operate on two mysql-master nodes, master1 is primary and configured in non-preemptive mode, and master2 is standby

Yuminstall-y keepalived

/ / install

Vim/etc/keepalived/keepalived.conf

/ / configure keepalived

Configuration of the master1 node:

! ConfigurationFile for keepalived global_defs {notification_email {acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc} notification_email_fromAlexandre.Cassen@firewall.loc smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL} vrrp_scriptchk_mysqld {script "/ usr/sbin/ss-tanl | grep 3306 & > / dev/null" interval 1 weight-2} vrrp_instancemysqld {state BACKUP Nopreempt interface eth0 virtual_router_id 100 priority 100 advert_int 1 authentication {auth_type PASS auth_pass 12345} virtual_ipaddress {192.168.1.110 dev eth0 label eth0:0} track_interface {eth0} track_script {chk_mysqld}}

Configuration of the master2 node:

! ConfigurationFile for keepalived global_defs {notification_email {acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc} notification_email_fromAlexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL} vrrp_scriptchk_mysqld {script "/ etc/keepalived/chk_mysqld.sh" interval 1 weight-2} vrrp_instancemysqld {state BACKUP interface eth0 virtual_router_ Id 100 priority 99 advert_int 1 authentication {auth_type PASS auth_pass 12345} virtual_ipaddress {192.168.1.110 dev eth0 label eth0:0} track_interface {eth0} track_script {chk_mysqld}}

/ etc/keepalived/chk_mysqld.sh

/ / configure the monitoring script on the master2 node

Chmod+x / etc/keepalived/chk_mysqld.sh

Vim/etc/rc.d/init.d/mysqld

/ / modify mysql startup script on master2 node

Chkconfigkeepalived on

Servicekeepalived start

/ / start keepalived

Verification

1. Initial state

two。 Turn off the mysql service on master1

3. Restart the mysql service on master1

Master1 nodes will not retake VIP resources

4. Turn off the mysql service on master2

5. Restart the mysql service on master2

VIP is still on the master1 node

4. Configure mysql-slave nodes:

Operate on two mysql-slave nodes

Vim/etc/mysql/my.cnf

Systemctlrestart mariadb

/ / start the mysql service on the slave node

Mysql

/ / Log in to the mysql service of the current slave node for further configuration

INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so'

/ / install semi-synchronous plug-in

CHANGE MASTER TOMASTER_HOST='192.168.1.110',MASTER_USER='repl',MASTER_PASSWORD='12345'

/ / point the master to the VIP of mysql-master

START SLAVE

Exit

Vim/etc/mysql/my.cnf

/ / modify the configuration file again to enable the semi-synchronous plug-in to start automatically

Systemctlrestart mariadb

/ / restart the mysql service

Verification

Log in to the mysql service on the slave node to view

VIP is currently on the mysql-master1 node. Log in to the mysql service on this node to view

Test data synchronization

Log in to the mysql service of the mysql-master1 node to operate

GRANT ALL ON *. * TO 'dbadmin'@'%' IDENTIFIED BY' 12345'

GRANT ALL ON *. * TO 'dbadmin'@'localhost' IDENTIFIED BY' 12345'

FLUSHPRIVILEGES

/ / create two accounts and check them on other mysql nodes

Install and configure atlas:

Operate on the atlas node

1. Install atlas

Software access website: https://github.com/Qihoo360/Atlas/releases

Rpm-ivh Atlas-2.2.1.el6.x86_64.rpm

two。 Configure atlas

/ usr/local/mysql-proxy/bin/encrypt12345

/ / use the program provided by atlas to encrypt the database password. Ciphertext must be filled in the configuration file.

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

/ / copy the configuration template to create an instance named mymysql

Vim / usr/local/mysql-proxy/conf/mymysql.cnf

[mysql-proxy] # the user name of the management interface admin-username = user # the password of the management interface admin-password = pwd # the IP and port of the MySQL master library connected to the Atlas backend can be set, separated by a comma # the VIPproxy-backend-addresses= 192.168.1.110user 3306 # of the backend connection of the mysql-master is separated by a comma, the IP and port of the MySQL slave library of the backend connection of the Atlas, and the digital weight after @ is used for load balancing. If omitted, the default is 1, and multiple entries can be set. Separate # ip of two mysql-slave with commas, and separate proxy-read-only-backend-addresses= 192.168.1.200 proxy-read-only-backend-addresses= 3306 "1192.168.1.202 proxy-read-only-backend-addresses= 3306" 1 # the encrypted MySQL password corresponding to the user name. The password is encrypted using the encryption program encrypt under the PREFIX/bin directory, and the downlink user1 and user2 are examples. Replace it with your MySQL username and encrypted password! # to use the previously created database account, the password must use the ciphertext pwds = dbadmin:tO5B+RteaNo= # to set 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 = true # sets the running mode of Atlas. When set to true, Atlas will start two processes, one is monitor, and the other is worker,monitor will restart it automatically after worker exits unexpectedly. When set to false, there is only worker and no monitor. Generally, it is set to false when developing and debugging, and after true,true when running online. > No spaces. Keepalive = the number of worker threads of true #, which has a great impact on the performance of Atlas. You can set event-threads = 2 # log level appropriately according to the situation, which is divided into five levels: message, warning, critical, error, and debug. The switch of log-level = warning # log storage path log-path = / var/log/mysql-proxy/ # 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 OFF#sql-log = OFF# 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 = 10 # instance name, used to distinguish between multiple Atlas instances on the same machine instance = mymysql # Atlas snooping work interface IP and port proxy-address = 0.0.0.0instance 1234 # Atlas snooping management interface IP and port admin-address = 0.0.0.0instance 2345 # subtable setting, in this case testdb is the library name, user is the table name, id is the subtable field, 3 is the number of child tables (if set to even number) It seems that one of the subtables does not work). You can set multiple items separated by commas. If you do not divide the table, you do not need to set the tables = testdb.user.id.3 # default character set. After setting this item, the client no longer needs to execute the SET NAMES statement # charset = utf8 # the IP of the client that allows you to connect to Atlas, which can be precise IP or IP segments separated by commas. If you do not set this item, all IP connections are allowed. Otherwise, only the IP connection in the list is allowed: # client-ips = 127.0.0.1, 192.168.1 # the IP of the physical Nic of the LVS attached in front of Atlas (note that it is not a virtual IP). This must be set if there is LVS and client-ips is set, otherwise # lvs-ips = 192.168.1.1 can not be set.

Mkdir/var/log/mysql-proxy/

/ / create a log storage path

Vim/etc/rc.d/init.d/mymysql-proxy

/ / configure startup script

#! / bin/sh## atlas init file for starting up the atlasdaemon## chkconfig:-20.8 million description:Starts and stops the redis daemon. # Sourcefunction library../etc/rc.d/init.d/functions instance=mymysqlstart () {/ usr/local/mysql-proxy/bin/mysql-proxyd$instance start} stop () {/ usr/local/mysql-proxy/bin/mysql-proxyd$instance stop} restart () {stop start} status () {/ usr/local/mysql-proxy/bin/mysql-proxyd$instance status} case "$1" in start) $1;; stop) $1 Restart) $1;; status) $1;; *) echo $"Usage: $0 {start | stop | status | restart}" exit 2esac

Chmod+x / etc/rc.d/init.d/mymysql-proxy

Chkconfig--add mymysql-proxy

Chkconfigmymysql-proxy on

Servicemymysql-proxy start

/ / start the service

Test:

Mysql-h 192.168.1.104-port 1234-udbadmin-p12345

/ / the proxy address that connects to the atlas

CREATE DATABASEtestdb

Use testdb

CREATE TABLE user_0 (id int UNSIGNED NOT NULLPRIMARY KEY,name VARCHAR (20) NOT NULL,age tinyint UNSIGNED)

CREATE TABLE user_1 (id int UNSIGNED NOT NULLPRIMARY KEY,name VARCHAR (20) NOT NULL,age tinyint UNSIGNED)

CREATE TABLE user_2 (id int UNSIGNED NOT NULLPRIMARY KEY,name VARCHAR (20) NOT NULL,age tinyint UNSIGNED)

/ / create 3 child tables of user according to the format, with the sequence number starting from 0

INSERT testdb.userVALUES (1), (2)), (2), (2), (2,), (2)

INSERT testdb.userVALUES (3), (4)), (4), (4)

INSERT testdb.userVALUES (5 for user05), (16 for user06)

INSERT testdb.userVALUES (7), (8), (8), (18)

INSERT testdb.userVALUES (9 for user09), (10 for user10)

/ / execute 5 insert statements, each inserting two pieces of data

Mysql-h 192.168.1.104-port 2345-uuser-ppwd

/ / Connect to the management address of atlas to view

Note:

If you want to test the effect of read-write separation, you can manually insert different data into two mysql-slave nodes to see the effect

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.