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

In the actual production environment, if the read and write of the database are all operated in the same database server, no matter in security, high availability, high concurrency and other aspects, it can not meet the actual demand at all. Therefore, it is generally through master-slave replication (Master- Slave) to synchronize data, and then through read separation to improve the concurrent load capacity of the database.

Principle of MySQL master-slave replication

There is a close relationship between the master-slave replication of MySQL and the read-write separation of MySQL. First of all, master-slave replication must be deployed. Only when the master-slave replication is completed can the read-write separation be carried out.

Replication types supported by MySQL

1) statement-based replication. The SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.

2) Row-based replication. Copy the changes instead of executing the command on the slave server.

3) mixed type of replication. Statement-based replication is used by default, and row-based replication is used when it is found that statement-based replication cannot be accurately replicated.

MySQL principle of separation of reading and writing

Read-write separation is based on writing on the master server and reading only on the slave server. The basic principle is to let the database handle transactional queries and select queries from the database. The database is replicated to synchronize transactional changes to the database in the cluster.

Based on the implementation of the intermediate agent, the agent is generally located between the client and the server, and the proxy server receives the client request and forwards it to the back-end database.

Experimental environment

This case is simulated with five servers. The specific topology diagram is as follows:

Host operating system IP address main software

Master Centos 7 x86_64 192.168.213.170 mysql-5.5.24.tar.gz

Slave 1 Centos 7 x86_64 192.168.213.168 mysql-5.5.24.tar.gz

Slave 2 Centos 7 x86_64 192.168.213.171 mysql-5.5.24.tar.gz

Amoeba Centos 7 x86_64 192.168.213.166 jdk-6u14-linux-x64.bin

Amoeba-mysql-binary-2.2.0.tar.gz

Client Centos 7 x8634 192.168.213.172

So the required software packages can be downloaded to Baidu cloud disk through this link:

Link: https://pan.baidu.com/s/1Wv65-ZmAIasN3EPAzB8J7Q password: 5q4b

Build MySQL master-slave replication

one。 Establish time synchronization and build a time synchronization server on the master node.

Check to see if NTP is installed.

[root@master] # rpm-Q ntp

Ntp-4.2.6p5-25.el7.centos.2.x86_64

Configure NTP (insert at the last line)

[root@master ~] # vim / etc/ntp.conf

Server 127.127.213.0 / / Local clock source

Fudge 127.127.100.0 stratum 8 / / sets the time level to 8

Start NTP, turn off firewall and security enhancements

[root@master ~] # service ntpd start

Redirecting to / bin/systemctl start ntpd.service

[root@master ~] # systemctl stop firewalld.service

[root@master ~] # setenforce 0

two。 Time synchronization on the slave node server (node server slave01 and slave02 configuration are the same)

Check to see if NTP is installed.

[root@slave01] # rpm-Q ntp

Ntp-4.2.6p5-25.el7.centos.2.x86_64

Start NTP, turn off firewall and security enhancements

[root@slave01 ~] # service ntpd start

Redirecting to / bin/systemctl start ntpd.service

[root@slave01 ~] # systemctl stop firewalld.service

[root@slave01 ~] # setenforce 0

Time synchronization with the master server

[root@slave01 ~] # / usr/sbin/ntpdate 192.168.213.170 / / time synchronization with the master server

7 Sep 10:10:30 ntpdate [4940]: the NTP socket is in use, exiting

three。 Install MySQL database, three servers master, slave01, slave 02 must be installed

Install the compilation environment

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

Add mysql users and join the mysql group

[root@master LNMP] # useradd-s / sbin/nologin mysql / / create a mysql user

[root@master LNMP] # mkdir / usr/local/mysql / / create a mysql directory

Extract the MySQL package and configure and install mysql

[root@master LNMP] # tar zxvf mysql-5.5.24.tar.gz-C / opt/

[root@master mysql-5.5.24] # cmake\

>-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\ / / mysql software installation location

>-DDEFAULT_CHARSET=utf8\ / / default character set

>-DDEFAULT_COLLATION=utf8_general_ci\ / / default character check

>-DWITH_EXTRA_CHARSETS=all\ / / for additional coding, please use all

>-DSYSCONFDIR=/etc\

>-DMYSQL_DATADIR=/home/mysql/\

>-DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock\

>-DWITH_MYISAM_STORAGE_ENGINE=1\ / / Storage engine

>-DWITH_INNOBASE_STORAGE_ENGINE=1\

>-DWITH_ARCHIVE_STORAGE_ENGINE=1\

>-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

>-DENABLED_LOCAL_INFILE=1\ / / enable local loading of data

>-DWITH_SSL=system\

>-the port of DMYSQL_TCP_PORT=3306\ / / mysql defaults to 3306

>-DENABLE_DOWNLOADS=1\

>-DWITH_SSL=bundled

Compile and compile installation (this process takes longer)

[root@master mysql-5.5.24] # make & & make install

Modify the owner and group of the mysql installation directory

[root@master mysql-5.5.24] # chown-R mysql.mysql / usr/local/mysql

Modify environment variabl

[root@master mysql-5.5.24] # echo "PATH=$PATH:/usr/local/mysql/bin/" > > / etc/profile

[root@master mysql] # source / etc/profile / / refresh environment variables

Create and modify my.cnf configuration file

[root@master mysql-5.5.24] # cd / usr/local/mysql/

[root@master mysql] # ls

Bin data include lib mysql-test scripts sql-bench

COPYING docs INSTALL-BINARY man README share support-files

[root@master mysql] # cp support-files/my-medium.cnf / etc/my.cnf

Cp: overwrite "/ etc/my.cnf"? Yes

[root@master mysql] # cp support-files/mysql.server / etc/init.d/mysqld

[root@master mysql] # chmod 755 / etc/init.d/mysqld / / add execution permission

[root@master mysql] # chkconfig-- add / etc/init.d/mysqld / / add mysql for system use

[root@master mysql] # chkconfig mysqld-- level 35 on

Initialize the database

[root@master mysql] # / usr/local/mysql/scripts/mysql_install_db\

>-- user=mysql\ / / designate database administrator

>-- ldata=/var/lib/mysql\

>-- basedir=/usr/local/mysql\ / / specify the installation location of the mysql software

>-- datadir=/home/mysql / / specify the installation location of the mysql database

Specify the installation location in the configuration file

[root@master mysql] # vim / etc/init.d/mysqld

Basedir=/usr/local/mysql

Datadir=/home/mysql

Create a soft connection

[root@master mysql] # ln-s / var/lib/mysql/mysql.sock / home/mysql/mysql.sock

Start the database

[root@master mysql] # service mysql start

Redirecting to / bin/systemctl start mysql.service

Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.

[root@master mysql] # systemctl daemon-reload

[root@master mysql] # service mysql start

Redirecting to / bin/systemctl start mysql.service

[root@master mysql] # netstat-ntap | grep 3306 / / View port 3306

Tcp 0 0 0.0.0.0 3306 0.0.0.015 * LISTEN 56229/mysqld

Set the login password for the database root user and log in to the mysql database

[root@master mysql] # mysqladmin-u root password 'abc123'

[root@master mysql] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

four。 After the mysql installation of the three servers is complete, configure them separately.

(1) mysql master server configuration

[root@master mysql] # vim / etc/my.cnf

Server-id = 11 / / unique ID. The server-id of three servers cannot be the same.

Log-bin=mysql-bin / / Log file of the master server

Log-slave-updates=true / / Slave server binaries

Restart the mysql service

[root@master mysql] # systemctl restart mysqld.service

Authorization from the server

[root@master mysql] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.213.%' IDENTIFIED BY' 123456'

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec) / / authorizes all replication permissions from the server for binary log rights management

Mysql > FLUSH PRIVILEGES; / / Refresh

Query OK, 0 rows affected (0.01 sec)

Mysql > show master status; / / View the status of the master server

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | master-bin.000001 | 1086 | / / two parameters of file name and location are recorded, which will be used when configuring synchronization |

+-+ Note: if you edit databases, tables, and data, the file name and location will change.

1 row in set (0.00 sec)

(2) mysql slave server slave 01 configuration

[root@slave01 mysql] # vim / etc/my.cnf

Server-id = 22

The relay-log=relay-log-bin / / IO thread reads the log to put into the relay-log

Relay-log-index=slave-relay-bin.index / / Index log location

Restart the mysql service

[root@slave01 mysql] # systemctl restart mysqld.service

Mysql > change master to master_host='192.168.213.170',master_user='myslave',master_password=''123456',master_log_file='master-bin.000001',master_log_pos=1086; / / change the parameters of master_log_file and master_log_pos in the command based on the result of the master server

Query OK, 0 rows affected (0.01 sec)

Mysql > start slave; / / start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G; / / View slave status

Slave_IO_Running: the two parameters of Yes / / read and playback log must be Yes

Slave_SQL_Running: Yes

(3) mysql slave server slave 02 configuration

[root@slave01 mysql] # vim / etc/my.cnf

Server-id = 22

Relay-log=relay-log-bin

Relay-log-index=slave-relay-bin.index

Restart the mysql service

[root@slave01 mysql] # systemctl restart mysqld.service

Mysql > change master to master_host='192.168.213.170',master_user='myslave',master_password=''123456',master_log_file='master-bin.000001',master_log_pos=1086

Query OK, 0 rows affected (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

five。 Verify master-slave synchronization

Create a library on the master server to see if it can be synchronized on the slave server

Mysql > create database school

Query OK, 1 row affected (0.00 sec)

Viewing the database from the server

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | # mysql50#.mozilla |

| | mysql |

| | performance_schema |

| | schoo |

| | school |

| | test |

+-+

Set up MySQL read-write separation

The Amoeba (amoeba) project open source framework released an Amoeba for MySQL software in 2008. This software is dedicated to the proxy layer of the distributed database of MySQL, which mainly acts as SQL routing for the application layer to access MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing to related target databases, and concurrent requests for multiple databases. Through Amoeba, we can achieve the functions of high availability, load balancing and data slicing of multiple data sources.

1. Install the Java environment on Amoeba.

Because Amoeba is based on jdk 1.5, jdk version 1.5 or 1.6 is officially recommended

[root@bogon Y2C] # systemctl stop firewalld.service # close the protective wall

[root@bogon Y2C] # setenforce 0

[root@bogon Y2C] # cp jdk-6u14-linux-x64.bin / usr/local/ # copy the package to the / usr/local/ directory

[root@bogon Y2C] #. / jdk-6u14-linux-x64.bin # enter yes according to the prompt and press enter to complete it.

Do you agree to the above license terms? [yes or no]

Press Enter to continue.

[root@bogon Y2C] # mv jdk1.6.0_14/ / usr/local/jdk1.6 # cut the compressed package to / usr/local/

[root@bogon Y2C] # vim / etc/profile # Editing system environment variables

Export JAVA_HOME=/usr/local/jdk1.6 (Last Line insert)

Export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

Export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin

Export AMOEBA_HOME=/usr/local/amoeba

Export PATH=$PATH:$AMOEBA_HOME/bin

[root@bogon Y2C] # source / etc/profile # reread system environment variables

2. Install and configure Amoeba softwar

[root@bogon Y2C] # mkdir / usr/local/amoeba # create amoeba installation directory

[root@bogon Y2C] # tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ # decompress amoeba package

[root@bogon Y2C] # chmod-R 755 / usr/local/amoeba/ # Recursive modify permission

[root@bogon Y2C] # / usr/local/amoeba/bin/amoeba # View amoeba installation status

Amoeba start | stop / / shows that Amoeba is installed successfully

3. Configure Amoeba read-write separation and two Slave read load balancers

(1) Open permissions to Amoeba access in Master, slave1 and slave2 databases

Mysql > grant all on *. * to test@'192.168.213.%' identified by '123.com'

Query OK, 0 rows affected (0.00 sec)

(2) Edit amoeba.xml configuration file

[root@bogon Y2C] # cd / usr/local/amoeba/conf/

[root@bogon Y2C] # vim amoeba.xml

30 amoeba # acts on line 30 to set the account where the client connects to anoeba

thirty-one

32 123456 # set the password for the client to connect to amoeba

115 master # Edit the default address pool to the name master

one hundred and sixteen

one hundred and seventeen

118 master # close the comment and write the pool name as master

119 slaves # close the comments and read the pool name as slaves

(3) Edit dbServers.xml configuration file

[root@bogon Y2C] # cd / usr/local/amoeba/conf/

[root@bogon Y2C] # vim dbServers.xml

26 test # Edit connection database user

twenty-seven

twenty-eight

29 123.com # Editing the password of the connection database account

45 # specify master server

forty-six

forty-seven

48 192.168.213.170 # specify the IP address of the primary server

# specify slave 1 server

fifty-three

fifty-four

55 192.168.213.168 # specify the IP of the slave 1 server

59 # specify slave 2 server

sixty

sixty-one

62 192.168.213.171 # specify the IP address of the slave 1 server

sixty-three

sixty-four

# set the cluster name slaves

sixty-six

sixty-seven

68 1

sixty-nine

seventy

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.