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

The Advanced part of MySQL Database-- Master-slave synchronization and read-write Separation Master-Slave synchronization experiment preparation

A total of four virtual machines, one application side, one amoeba, one master server, one slave server

​ application: centos7-2 (MySQL installed before the experiment)

​ amoeba: centos7-1 (no MySQL)

​ master server: centos7-3 (MySQL installed before the lab)

​ slave server: centos7-4 (MySQL installed before the lab)

Master server settin

Install time synchronization service

[root@master ~] # yum install ntp-y # modify the ntp configuration file [root@master ~] # vim / etc/ntp.conf server 127.127.195.0 / / Local is the clock source, 195 paragraphs / / fudge 127.127.195.0 stratum 8 systemctl start ntpd / set the time level to 8pm / enable the service [root@master ~] # systemctl start ntpd

Modify the MySQL main configuration file

[root@master ~] # vim / etc/my.cnf server-id 10 log-bin=zhu-bin log-slave-updates=ture # # enable master-slave synchronization [root@master ~] # systemctl restart mysqld [root@master ~] # systemctl stop firewalld

Enter the database to modify

[root@master ~] # mysql-u root-pgrant replication slave on *. * to 'myslave'@'192.168.142.%' identified by' asd123'; # allows the slave server to copy on the master server using the myslave account show master status; # to view the master server information mysql > mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000005 | 868 | +- -+ 1 row in set (0.00 sec) from the server settings

Install the synchronization service client ntpd

[root@slave ~] # yum install ntp ntpdate-y # enable service off firewall [root@slave ~] # systemctl start ntpd [root@slave ~] # systemctl stop firewalld## for time synchronization [root@slave ~] # / usr/sbin/ntpdate master server address

Modify the MySQL main configuration file

[root@slave ~] # vim / etc/ntp.conf server-id = 20 relay-log = relay-bin # synchronize log files to the local relay-log-index = slave-bin.index # # define the log file type and name [root@slave ~] # systemctl restart mysqld

Enter the database to modify

[root@slave ~] # mysql- u root-p123123change master to master_host='192.168.142.135',master_user='myslave',master_password='asd123',master_log_file='mysql-bin.000005',master_log_pos=865; # # Grant permission start slave # query whether synchronization is successful show slave status\ gateway * 1. Row * * Slave_IO_State: Reconnecting after a failed master event read Master_Host: 192.168.142.132 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 1633 Relay_Log_File: master1-relay-bin.000011 Relay_Log_Pos: 1126 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_ Running: Yes # # IO connection Slave_SQL_Running: Yes # # SQL connection.

PS:

​ sometimes encounters the problem of Slave_SQL_Running: NO when synchronizing between master and slave. Here are the solutions:

# Slave_SQL_Running: No problem solution MariaDB [(none)] > stop slave; MariaDB [(none)] > SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;MariaDB [(none)] > start slave; MariaDB [(none)] > show slave status\ G so far, MySQL master-slave synchronization has been completed. Here is the preparation for MySQL read-write separation experiment with dynamic and static separation.

The following experiments are all based on master-slave synchronization.

# A total of four virtual machines, one application side, one amoeba, one master server and one slave server

​ application: centos7-2 (with MySQL)

​ amoeba: centos7-1 (no MySQL)

​ master server: centos7-3 (with MySQL)

​ slave server: centos7-4 (with MySQL)

Set up the amoeba server

Install the jdk environment (amoeba is developed by java)

[root@amoeba ~] # systemctl stop firewalld.service # turn off firewall [root@amoeba ~] # setenforce 0 [root@amoeba mnt] # cp-p jdk-6u14-linux-x64.bin / usr/local/ # Mobile pre-prepared jdk package [root@amoeba mnt] # cd / usr/local/ [root@amoeba local] #. / jdk-6u14-linux-x64.bin # install jdk environment [root@amoeba local] # mv jdk1.6.0_14 / jdk1.6## configure jdk environment variable [root@amoeba local] # vim / 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/ [root @ amoeba local] # source / etc/profile

Extract and install the amoeba toolkit

[root@amoeba local] # mkdir amoeba [root@amoeba local] # tar zxf / mnt/amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba # decompress the prepared toolkit [root@amoeba local] # chmod-R 755 amoeba/ # Grant permissions # # check whether the installation is successful [root@amoeba local] # / usr/local/amoeba/bin/amoebaamoeba start | stop # # the following command line is successful

Modify the main configuration file amoeba.xml

[root@amoeba local] # vim amoeba/conf/amoeba.xml## modify as shown below # (increase the account and password for client to access amoeba) amoeba123123---# (enable default pool, read pool, write pool) mastermasterslaves

Modify amoeba's configuration file dbserver.xml about the database

[root@amoeba local] # vim amoeba/conf/dbServers.xml## modify as shown below # (change the account and password of the amoeba access node server) test asd123---# (specify the master, From the server node address) 192.168.142.132 192.168.142.136 Murmuri # (put the server into the read address pool) slave

Return to the master and slave server database

# # Open access on master and slave servers [root@slave] # mysql-u root-p123123 # access to master server database (slave server operates the same as master server) grant all on *. * to test@'192.168.142.%'identified by 'asd123'; # Open test user access

Start the amoeba server

[root@amoeba conf] # / usr/local/amoeba/bin/amoeba start & # call into the background to run the application client continuously

​ on the premise that MySQL is installed, enter mysql-u amoeba-p123123-h 192.168.142.142-P8066 to successfully access the amoeba server.

​ at this point, the master server master is used for writing and the slave server slave is used for reading and writing operations.

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.