The Advanced part of MySQL Database-- Master-Slave synchronization and read-write Separation
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.
