The concrete steps of separating master-slave replication from reading and writing by MySQL
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.
