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

Next, let's learn about the specific steps of MySQL to achieve the separation of master-slave replication and reading and writing. I believe you will benefit a lot after reading. I hope that MySQL will achieve the specific steps of master-slave replication and reading and writing separation. This short article is what you want.

Environment introduction:

Primary CVM (master): 192.168.100.155

From CVM (slave1,slave2): 192.168.100.153-154

Proxy CVM (amoeba): 192.168.100.156

Application client (app): 192.168.100.157

1. Set up a time CVM:

Install the ntp time service on the primary node: 192.168.100.155

Yum-y install ntp

Sed-I'/ ^ server/s/ ^ / # / G' / etc/ntp.conf

Cat / etc/ntp.conf

Server 127.127.1.0

Fudge 127.127.1.0 stratum 8

END

/ etc/init.d/ntpd restart

Netstat-utpln | grep ntp

Slave node synchronization time: 192.168.100.153-154

Yum-y install ntpdate

/ usr/sbin/ntpdate 192.168.100.155

two。 Install mysql:192.168.100.153-155

Wget ftp://192.168.100.100/tools/lamp_install_publis-app-2015-07-16.tar.xz

Tar Jxvf lamp_install_publis-app-2015-07-16.tar.xz

Mysql_install.sh

Mysql_config.sh

Reboot

Tip:

[root@localhost ~] # cd bin/

[root@localhost bin] # cat mysql_install.sh

#! / bin/bash

# # first configure yum and install ncurses dependency package

Yum-y install ncurses-*

# decompress cmake and install the basic environment

Tar zxvf / root/cmake-2.8.6.tar.gz-C / usr/src/

Cd / usr/src/cmake-2.8.6

# configure, compile and install cmake

. / configure & & gmake & & gmake install

# # decompressing mysql

Tar zxvf / root/mysql-5.5.22.tar.gz-C / usr/src/

Cd / usr/src/mysql-5.5.22/

# cmake to configure mysql

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql # specify the installation directory\

-DDEFAULT_CHARSET=utf8 # specifies the character set as utf8\

-DDEFAULT_COLLATION=utf8_general_ci # # specify character check\

-DWITH_EXTRA_CHARSETS=all # # support additional character sets\

-DSYSCONFDIR=/etc/ # # specify the configuration file location

Make & & make install # compilation and installation

If [- e / usr/local/mysql]; then

Echo "mysql install successfully."

Fi

[root@localhost bin] #

[root@localhost bin] # cat mysql_config.sh

#! / bin/bash

# 1. Copy Profil

Cp / usr/src/mysql-5.5.22/support-files/my-medium.cnf / etc/my.cnf

# 2. Add system services

Cp / usr/src/mysql-5.5.22/support-files/mysql.server / etc/init.d/mysqld

Chmod + x / etc/init.d/mysqld

Chkconfig-add mysqld

Chkconfig mysqld on

# 3. Optimize the PATH path to facilitate command execution, both single quotation marks and double quotation marks

Grep mysql / etc/profile

If [$?-eq 0]; then

Echo "PATH is set."

Else

Echo "export PATH=$PATH:/usr/local/mysql/bin" > > / etc/profile

Source / etc/profile # # execution file

Fi

# 4. Initialize mysql, create users, and empower

Useradd-M-s / sbin/nologin mysql

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

/ usr/local/mysql/scripts/mysql_install_db\

-- basedir=/usr/local/mysql\

-datadir=/usr/local/mysql/data-user=mysql

# 5. Start mysql and set it to boot

If [- e / tmp/mysql.sock]; then

/ etc/init.d/mysqld restart

Else

/ etc/init.d/mysqld start

Fi

Chkconfig mysqld on

# 6. Change the password and prompt for the password

Mysqladmin-u root password '123123' & & echo mysql root password is 123123

3. Configure MySQL master-slave replication:

1) configuration of primary CVM: 192.168.100.155

Sed-I's / ^ log-bin=.*/log-bin=master-bin\ nlog-slave-updates=ture/g' / etc/my.cnf

Sed-I'/ ^ server-id/s/1/11/g' / etc/my.cnf

/ etc/init.d/mysqld restart

Mysql-uroot-p123123

Mysql > grant replication slave on *. * to 'myslave'@'192.168.100.%' identified by' 123123'

Mysql > flush privileges

Mysql > show master status; # # remember the values of File and Position, here are master-bin.000001 and 337

Mysql > create database db_test; # # create a test database

Mysql > quit

2) configure slave CVM 1: 192.168.100.153

Sed-I'/ ^ server-id/s/1/22/g' / etc/my.cnf

Sed-I'/ ^ server-id/arelay-log=relay-log-bin\ nrelay-log-index=slave-relay-bin.index' / etc/my.cnf

/ etc/init.d/mysqld restart

Mysql-uroot-p123123

Mysql > change master to master_host='192.168.100.155',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=337

Mysql > start slave

Mysql > show slave status\ G; # # View without error

Mysql > show databases; # # verify that the database is synchronized

Mysql > quit

3) configure slave CVM 2VR 192.168.100.154

Sed-I'/ ^ server-id/s/1/33/g' / etc/my.cnf

Sed-I'/ ^ server-id/arelay-log=relay-log-bin\ nrelay-log-index=slave-relay-bin.index' / etc/my.cnf

/ etc/init.d/mysqld restart

Mysql-uroot-p123123

Mysql > change master to master_host='192.168.100.155',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=337

Mysql > start slave

Mysql > show slave status\ G; # # View without error

Mysql > show databases; # # verify that the database is synchronized

Mysql > quit

4. Set up MySQL read-write separation:

1) install the software: 192.168.100.156

Lftp 192.168.100.100

> cd tools/

> get amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin

> bye

Yum-y remove java

Chmod + x jdk-6u14-linux-x64.bin

. / jdk-6u14-linux-x64.bin

Mv jdk1.6.0_14/ / usr/local/jdk1.6

Vi / etc/profile

Export JAVA_HOME=/usr/local/jdk1.6

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

: wq

Source / etc/profile

Java-version

Mkdir / usr/local/amoeba

Tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/

Chmod-R 755 / usr/local/amoeba/

/ usr/local/amoeba/bin/amoeba # # Verification

2) data is granted read and write permissions to amoeba:

Mysql > grant all on *. * to linuxyy@'192.168.100.%' identified by '123123; # # completed on 192.168.100.155

Mysql > show grants for linuxyy@'192.168.100.%'; # # check whether permissions are synchronized on 192.168.100.153-154,

3) modify the configuration file: 192.168.100.156

Vim / usr/local/amoeba/conf/amoeba.xml

30 amoeba

thirty-one

32 123456

115 master

one hundred and sixteen

117 master # # Note deleted comments

118 slaves

: set nu # # displays the line number

: wq

Vim / usr/local/amoeba/conf/dbServers.xml

twenty-five

26 linuxyy # # this user must be the user authorized in the previous step

twenty-seven

28 # # modify

29 123123 # # delete "-->" on the next line

44 # # modify to master

forty-five

forty-six

47 192.168.100.155 # # specify the correct ip for master

51 # # modified to slave1

fifty-two

fifty-three

54 192.168.100.153 # # specify the ip address of slave1

fifty-five

fifty-six

57 # # add the following 6 lines to specify the ip of slave2

fifty-eight

fifty-nine

60 192.168.100.154

sixty-one

sixty-two

64 # # modify to slaves

70 slave1,slave2 # # modify cluster member names separated by commas

: wq

/ usr/local/amoeba/bin/amoeba start& # # start the proxy service

Netstat-utpln | grep 8066 # # Verification

5. Test read-write separation

1) verify master-slave replication: 192.168.100.157

Yum-y install mysql

Mysql-uamoeba-p123456-h 192.168.100.156-P 8066 # Log in to db cluster 192.168.100.157

Mysql > show databases

Mysql > use db_test

Mysql > create table linuxyy (id int (10), name varchar (10), address varchar (20))

View the results on 192.168.100.153-155:

Mysql-uroot-p123123

Mysql > use db_test

Mysql > show tables; # # has been synchronized

2) disable the replication function of slave1,slave2: 192.168.100.153-154

Mysql > stop slave

3) create different data on master,slave1,slave2:

Master:

Mysql > insert into linuxyy values (1) the is master' of this is master' is the same as that of others.

Slave1:

Mysql > insert into linuxyy values. (2) this is slave1' is the same as the last one.

Slave2:

Mysql > insert into linuxyy values. (3) this is slave2' is the same as the last one.

4) Application client verification read: 192.168.100.157

Mysql > select * from linuxyy; # # first query

+-+

| | id | name | address | |

+-+

| | 2 | hehe | this is slave1 |

+-+

1 row in set (0.02 sec)

Mysql > select * from linuxyy; # # second query

+-+

| | id | name | address | |

+-+

| | 3 | hehe | this is slave2 |

+-+

1 row in set (0.01 sec)

Mysql > select * from linuxyy; # # third query

+-+

| | id | name | address | |

+-+

| | 2 | hehe | this is slave1 |

+-+

1 row in set (0.00 sec)

5) verify the write on the application client:

Mysql > insert into linuxyy values; # # write data

Query OK, 1 row affected (0.02 sec)

Mysql > select * from linuxyy; # # cannot find the data just written

+-+

| | id | name | address | |

+-+

| | 3 | hehe | this is slave2 |

+-+

1 row in set (0.01 sec)

Verify on master:

Mysql > select * from linuxyy; # # find data

+-+

| | id | name | address | |

+-+

| | 1 | hehe | this is master |

| | 4 | hehe | app write test |

+-+

2 rows in set (0.00 sec)

Summary:

When app writes data, amoeba will route the data to master for storage. When app reads data, amoeba will send the read request-polling method to the slaves group (slave1+slave2) to achieve read-write separation.

Master-slave replication is configured between master and slaves to ensure the consistency of data.

After reading this article on the specific steps of MySQL to achieve the separation of master-slave replication and reading and writing, many readers will certainly want to know more about it. For more industry information, you can follow our industry information section.

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.