Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the construction of MYSQL master-slave and ATLAS read-write separation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Environmental preparation

Operating system CENTOS 7.5

Database MYSQL5.6.40

ATLAS2.2.1

IP address

Main library 192.168.56.118

Preparation of 192.168.56.119

ATLAS middleware 192.168.56.117

What I write here is only the ordinary read-write separation building, which is not as complicated as those share sub-tables. I will do it later when I have time.

MYSQL master-slave building, this, in fact, you can read an article I wrote about MYSQL master-slave and master.

Http://blog.itpub.net/28371090/viewspace-2154828/

It's already very detailed. I'll write it again, just to be more concise.

MYSQL5.6.40 download address (source code package)

Https://downloads.mysql.com/archives/get/file/mysql-5.6.40.tar.gz

ATLAS2.2.1 download address

Https://github.com/Qihoo360/Atlas/releases/tag/2.2.1

Two servers are installed in the same way

II. MYSQL source code installation

Use a local yum source

[520]

Name=520

Baseurl= file:///mnt/cdrom

Gpgcheck=0

Enabled=1

1. Installation dependency

Yum-y install make gcc-c++ cmake bison-devel ncurses-devel perl

2. Create users and groups

Groupadd mysql

Useradd mysql-g mysql-M-s / sbin/nologin

3. Decompress tar-zxvf mysql-5.6.40.tar.gz

Cd / opt/

Tar-zxvf mysql-5.6.40.tar.gz-C / opt/

Cd / opt/mysql-5.6.40

Mkdir / data/mysql5.6.40/

4. Compile and install

Cmake-DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.40/-DMYSQL_DATADIR=/data/mysql5.6.40/-DSYSCONFDIR=/etc-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_READLINE=1-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock-DMYSQL_TCP_PORT=3306-DENABLED_LOCAL_INFILE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DEXTRA_CHARSETS=all-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

5. Compilation

Make & & make install

6. Directory authorization

Chown mysql.mysql / data/mysql5.6.40/

Mkdir / data/mysql5.6.40/log

Chown mysql.mysql / data/mysql5.6.40/log

7. Database initialization

Cd / opt/mysql-5.6.40/

. / scripts/mysql_install_db-user=mysql-datadir=/data/mysql5.6.40/

8. Configure environment variables

Vi / root/.bash_profile is added on the last line

PATH=$PATH:$HOME/bin:/data/mysql5.6.40/bin:/data/mysql5.6.40/lib

Third, MYSQL5.6.40 master-slave building

1. Master-side configuration deployment a, add the following configuration under the [mysqld] node in the my.cnf configuration file on the main server

Vi / etc/my.cnf

[mysqld]

Server-id=101

Default-storage-engine=InnoDB

Lower_case_table_names=1

Log-bin=/data/mysql5.6.40/log/mysql-bin.log

Log-bin-index=/data/mysql5.6.40/log/mysql-bin.index

Expire_logs_days=30

Datadir=/data/mysql5.6.40/

Socket=/tmp/mysql.sock

User=mysql

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

Symbolic-links=0

[mysqld_safe]

# log-error=/data/mysql5.6.40/mysqld.log

# pid-file=/data/mysql5.6.40/mysqld.pid

If there is a situation where it cannot be started, please comment it out.

Start the database

Traditional startup mode

/ usr/local/mysql/bin/mysqld_safe-user=mysql &

Make it into service startup

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

Check to see if the startup is successful

Netstat-tnl | grep 3306

Ps-ef | grep mysql

Related command

Service mysql start

Stop the mysql service

Service mysql stop

Restart the mysql service

Service mysql restart

Add to boot startup item

Chkconfig-add mysql

B. Create a user and grant permissions:

Login to the database Times error

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysqld.sock' (2)

Ln-s / tmp/mysql.sock / tmp/mysqld.sock

Landing

Mysql-uroot

Create user repl_user

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%' IDENTIFIED BY PASSWORD' *'

You will encounter an error when setting the password:

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Solution: use select password ('password you want to enter'); query the string corresponding to your password

Select password ('123456')

Found out is * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

2. Configure deployment on the Slave side

Vi / etc/my.cnf

[mysqld]

Server-id=102

Default-storage-engine=InnoDB

Lower_case_table_names=1

Log-bin=/data/mysql5.6.40/log/mysql-bin.log

Log-bin-index=/data/mysql5.6.40/log/mysql-bin.index

Expire_logs_days=30

Datadir=/data/mysql5.6.40/

Socket=/tmp/mysql.sock

User=mysql

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

Symbolic-links=0

[mysqld_safe]

# log-error=/data/mysql5.6.40/mysqld.log

# pid-file=/data/mysql5.6.40/mysqld.pid

3. Establish master-slave synchronization

Since I have a newly built library here, you can create it directly, and you don't need to do any backup and import.

Main library:

Log in to mysql

Show master status

Remember file and position.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 120 | |

+-+

1 row in set (0.00 sec)

From the library:

Log in to mysql

Change master to

Master_host='192.168.56.118'

Master_port=3306

Master_user='repl_user'

Master_password='123456'

Master_log_file='mysql-bin.000002'

Master_log_pos=120

After the configuration is successful, start slave

Start slave

Verify that the master and slave are successfully built and executed in the slave library.

Show slave status\ G

OK when you see two YES

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Create a table in the main library

Use test

Create table aa (name char (10))

Insert into aa values ('Tom')

Querying from the library

Use test

Select * from aa

When you find the data that has just been inserted, it will be O.

Mysql > use mysql;mysql > desc user;mysql > GRANT ALL PRIVILEGES ON *. * TO root@ "%" IDENTIFIED BY "root"; / / authorize remote connection mysql > update user set Password = password ('123456') where User='root'; / / set root user password mysql > select Host,User,Password from user where User='root';mysql > flush privileges;mysql > exit

Fourth, dealing with the problem of reporting errors:

MYSQL startup error report

(1)

Error reporting phenomenon:

[root@node1 mysql] # service mysql start

Starting MySQL.. ERROR! The server quit without updating PID file (/ var/lib/mysql/node1.pid).

View the error log:

[root@node1 mysql] # tail / var/log/mysqld.log

190512 19:59:10 InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

InnoDB: Apply batch completed

190512 19:59:10 InnoDB: Waiting for the background threads to start

190512 19:59:11 InnoDB: 5.5.40 started; log sequence number 1595675

190512 19:59:11 [Note] Server hostname (bind-address): '0.0.0.0; port: 3306

190512 19:59:11 [Note]-'0.0.0.0' resolves to' 0.0.0.0'

190512 19:59:11 [Note] Server socket created on IP: '0.0.0.09.

190512 19:59:11 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

190512 19:59:11 mysqld_safe mysqld from pid file / var/lib/mysql/node1.pid ended

Solution:

Check the configuration file and find that the datadir directory in the configuration file is default and needs to be modified to your own / usr/local/mysql/data/.

[root@node1 mysql] # cat / 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

[mysqld_safe]

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

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

[root@node1 mysql] # vi / etc/my.cnf

Datadir=/usr/local/mysql/data/

Restart MYSQL successfully after modification is completed

[root@node1 mysql] # service mysql start

Starting MySQL.. SUCCESS!

(2)

Error reporting phenomenon:

[root@node1 mysql] # / usr/local/mysql/bin/mysql-uroot

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

Check the configuration file and find that the socket file is in / var/lib/mysql/mysql.sock, not in / tmp/mysql.sock

[root@node1 mysql] # cat / etc/my.cnf

[mysqld]

Datadir=/usr/local/mysql/data/

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

User=mysql

# 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

Solution:

Set up soft links

Ln-s / var/lib/mysql/mysql.sock / tmp/mysql.sock

Alternatively, modify the configuration file

Pay attention to a problem, in the production environment, we should pay attention to the time zone.

Vi / etc/my.cnf

[mysqld]

Default-time_zone ='+ 8VR 00'

Use the time zone of Beijing time

Note:

In the case of abnormal mysql downtime, if sync_binlog=1 or innodb_flush_log_at_trx_commit=1 is not set, it is very likely that the binlog or relaylog file will be corrupted, resulting in inconsistency of the master.

5. ATLAS read-write separation configuration

ATLAS2.2.1 download address

Https://github.com/Qihoo360/Atlas/releases/tag/2.2.1

Atlas-2.2.1.el6.x86_64.rpm

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

After the installation is complete, it will generate 4 folders by default in the "/ usr/local/mysql-proxy" directory, as well as the files that need to be configured, as follows:

[root@localhost ~] # ll / usr/local/mysql-proxy/

Total 4

Drwxr-xr-x. 2 root root 75 Jul 30 14:27 bin

Drwxr-xr-x. 2 root root 22 Jul 30 16:35 conf

Drwxr-xr-x. 3 root root 4096 Jul 30 14:27 lib

Drwxr-xr-x. 2 root root 38 Jul 30 16:35 log

The executable files are all distributed in the bin directory.

1. "encrypt" is used to generate MySQL password encryption, which is used in configuration.

2. "mysql-proxy" is MySQL's own read-write separation agent.

3. "mysql-proxyd" is created by 360. there is a "d" behind it to start, restart and stop the service. It's all carried out by him.

Under the conf directory is the configuration file.

1. "test.cnf" has only one file, which is used to configure the agent and can be edited using vim.

Under the lib directory are some packages, as well as Atlas dependencies

In the log directory, there are logs, such as error messages and other records.

Enter the bin directory and use encrypt to encrypt the password of the database. The user name of my MySQL data is buck and the password is hello. I need to encrypt the password.

[root@localhost bin] #. / encrypt hello

RePBqJ+5gI4=

Configure Atlas, use vim for editing

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

This is the account and password of the administrator who logs in to Atlas, corresponding to "IP and port of # Atlas monitoring", that is, you need to set the port for administrator login to enter the administrator interface. The default port is 2345, or you can specify IP login. After you specify IP, other IP cannot access the administrator's command interface. For ease of testing, I do not specify IP and port login here.

# user name of the management interface

Admin-username = user

# password of the administrative interface

Admin-password = pwd

The user name and password of the management interface here are also used by default

This is the address used to configure the master data and the slave database. The master database configured here is 118 and the slave database is 119

# IP and port of MySQL master library connected to Atlas backend. Multiple entries can be set, separated by commas.

Proxy-backend-addresses = 192.168.56.118purl 3306

# the MySQL connected to the Atlas backend is from the IP and port of the library, and the number after @ represents the weight, which is used for load balancing. If omitted, it defaults to 1. Multiple entries can be set, separated by commas.

Proxy-read- >

This is used to configure the account and password of MySQL. The user of my MySQL is buck and the password is hello. I have just used the tool provided by Atlas to generate the corresponding encrypted password.

# the user name corresponds to its encrypted MySQL password, which is encrypted using the encryption program encrypt in the PREFIX/bin directory. Take the downlink user1 and user2 as examples, and replace it with your MySQL username and encrypted password! Pwds = buck:RePBqJ+5gI4=

(RePBqJ+5gI4= this is the password encrypted by yourself, fill in it.)

This is to set the work interface and management interface. If the "0.0.0.0" set by ip means that any IP can access this interface, of course, you can also specify IP and port, which is not specified by me. The user name and password of the work interface corresponds to the account of MySQL, and the user password of the administrator corresponds to the user password of the administrator configured above.

# working interface IP and port proxy-address for Atlas snooping = management interface IP and port admin-address = 0.0.0.0proxy-address 2345 for 0.0.0.0:1234#Atlas snooping

Start Atlas

[root@localhost bin] #. / mysql-proxyd test startOK: MySQL-Proxy of test is started

I don't have MYSQL installed on the ATLAS machine.

/ etc/init.d/mysqld status test is fine, but it doesn't mean much to me. I didn't install it at all.

Ps-ef | grep mysql

Use the following command to enter the Atlas management mode "mysql-h227.0.0.1-P2345-uuser-ppwd", which shows that Atlas is running normally, because it treats itself as a MySQL database, so you can enter the MySQL database mode without the database environment.

[root@localhost bin] # mysql-h227.0.0.1-P2345-uuser-ppwd

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 >

You can visit the "help" table to see what MySQL administrator mode can do. You can use SQL statements to access

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 example 3306",... |

| | ADD SLAVE $backend | example: "add slave 127.0.0.1 example 3306",... |

| | REMOVE BACKEND $backend_id | example: "remove backend 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 |

+-+

16 rows in set (0.00 sec)

Mysql >

It can also be accessed using the work interface, using the command "mysql-h227.0.0.1-P1234-ubuck-phello"

[root@localhost bin] # mysql-h227.0.0.1-P1234-ubuck-phelloWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.0.81-logCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

If the work interface can be accessed, you can use Navicat to connect to the database under the Windows platform, and fill in the corresponding host,Port, user name and password.

4. Read-write separation test

Here you need to use Jmeter to test read-write separation, which is the first open source stress testing tool for Java to write, because it's convenient. He has a module dedicated to testing MySQL, and needs to use MySQL's JDBC driver jar package. The configuration is very simple, and the things are very good, powerful and easy to use.

Download address of Jmeter:

Https://mirrors.tuna.tsinghua.edu.cn/apache//jmeter/binaries/apache-jmeter-5.1.1.zip

JDBC package for MySQL:

Https://cdn.mysql.com//archives/mysql-connector-java-5.1/mysql-connector-java-5.1.44.zip

JAVA8

Https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

After downloading, unpack them separately and open Jmeter (there is a jmeter.bat in the bin directory. Before you double-click it, your machine needs to install JAVA8. Only in this way can you run it. In the test plan, import JDBC's jar package.

Configure the driver for JDBC, as shown below

The name can be filled in at will. URL and account secret can be set as before, as shown in the following figure.

Jdbc:mysql://192.168.56.117:1234/test buck hello

After the configuration is complete, test

Create a group first

Add JDBC REQUEST for the test

The test of the whole query first.

After clicking execute, go to the main and standby database on the server to observe the data transmission.

Sar-n DEV 1 10000 use this command

Or iostat can.

Main library: (there is obviously no data transfer (almost no read or write))

Standby library: (obvious red box to see data transfer)

Thus it can be seen that the data reading is read from the standby database.

It's the same with writing data.

In the main library

Create database test

Create table sbtest (name varchar (9))

Insert into sbtest values ('Tom')

Insert into sbtest as select * from sbtest

Keep executing the loop insertion of the following statement

Insert into sbtest as select * from sbtest

There are millions of lines, you can start testing.

This is the end of the content of "the separation of MYSQL master and slave and ATLAS reading and writing". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.