Mysql master-slave synchronous read and write separation

Https://pan.baidu.com/s/1tm_FQ4C8heQqzx01URr85A / / Software connects to Baidu network disk

Three mysql databases: master database server: 192.168.80.100 slave database server 192.168.80.101 slave database server 192.168.80.102 one amoeba scheduler: 192.168.80.103 a mariadb client: 192.168.80.104 Note: The first step of all five virtual machines is to write the following two lines: systemctl stop firewalld / / turn off firewall setenforce 0 / close monitoring configuration MySQL master-slave environment ready to configure NTP server-time synchronization [root@NGINX ~] # yum install-y ntp / / install time synchronization program [root@NGINX ~] # service ntpd start# Note: if you make a mistake, service ntpd stop [root@NGINX ~] # ntpdate-u cn. Pool.ntp.org / / synchronize network time

[root@NGINX ~] # vi / etc/ntp.conf / / add the following three lines to the appropriate location for time synchronization: restrict 192.168.80.0 mask 255.255.255.0 nomodifyserver 127.127.1.0fudge 127.127.1.0 stratum 8 on the slave server: [root@localhost ~] # yum install-y ntp [root@localhost ~] # ntpdate 192.168.80.100

[root@localhost ~] # echo'* / 30 * / usr/sbin/ntpdate 192.168.80.100' > / var/spool/cron/root / / set scheduled tasks, synchronize [root@localhost ~] # crontab-l every 30 minutes, and then configure mysql server master / slave replication on the master mysql server: 192.168.80.100 [root@NGINX ~] # vi / etc/my.cnf insert under socket: server-id=11log_bin=master_binlog_slave_updates=true

[root@NGINX ~] # service mysqld restart if the root password is not set, use the following line of code mysqladmin-u root-p password "abc123" [root@NGINX ~] # mysql-u root-p / / enter the database mysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.80.%' IDENTIFIED BY' 123456 password / authorize all databases for all slave servers mysql > FLUSH PRIVILEGES; / / refresh permissions mysql > show master status\ G

Set on slave server 1: 192.168.80.101 [root@localhost ~] # vi / etc/my.cnf modify and add the following: server-id = 22relay_log=relay-log-binrelay_log_index=slave-relay-bin.index

[root@localhost ~] # systemctl restart mysqld if the root password is not set, use the following line of code mysqladmin-u root-p password "abc123" [root@localhost ~] # mysql-u root-pmysql > change master to master_host='192.168.80.100',master_user='myslave',master_password='123456',master_log_file='master_bin.000007',master_log_pos=154;mysql > start slave;mysql > show slave status\ G

Set on slave server 2: [root@test ~] # vi / etc/my.cnf modify and add the following: server-id = 33relay_log=relay-log-binrelay_log_index=slave-relay-bin.index [root@test ~] # service mysqld restart if the root password is not set, use the following line of code mysqladmin-u root-p password "abc123" [root@test ~] # mysql-u root-pmysql > change master to master_host='192.168.80.100',master_user='myslave' Master_password='123456',master_log_file='master_bin.000007',master_log_pos=154 Mysql > start slave;mysql > show slave status\ G

Test, create an empty library on the master server and see if there is any slave server

Mysql > create database ab on the main service

On slave server 1: mysql > show databases

On server 2: mysql > show databases

On the front-end server: 192.168.80.103 [root@amoeba ~] # yum install-y ntpdate [root@amoeba ~] # ntpdate 192.168.80.100

[root@amoeba ~] # echo'* / 30 * / usr/sbin/ntpdate 192.168.80.100' > > / var/spool/cron/root [root@amoeba ~] # crontab-l [root@amoeba ~] # tar xf jdk-8u144-linux-x64.tar.gz-C / opt/ [root@amoeba ~] # cd / opt/ [root@amoeba opt] # cp-rv jdk1.8.0_144/ / usr/local/java [root@amoeba opt] # vi / etc/profileexport JAVA_HOME=/usr/local/javaexport JRE_HOME=/usr/local/java/jreexport PATH=$PATH:/usr/local/java/binexport CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib

[root@amoeba opt] # source / etc/profile [root@amoeba opt] # java-version

Now the Java environment is set up.

Yum-y install zip unzip [root@amoeba ~] # unzip amoeba-mysql-3.0.5-RC-distribution.zip-d / opt/ [root@amoeba ~] # cd / opt/ [root@amoeba opt] # mv amoeba-mysql-3.0.5-RC/ / usr/local/amoeba [root@amoeba opt] # chmod-R 755 / usr/local/amoeba/ [root@amoeba opt] # vi / usr/local/amoeba/jvm.properties added: JVM_OPTIONS= "- server-Xms1024m-Xmx1024m-Xss256k"

[root@amoeba opt] # vi / etc/init.d/amoeba / / Edit system service script #! / bin/bash#chkconfig: 35 62 62#export JAVA_HOME=/usr/local/javaexport PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATHNAME=AmoebaAMOEBA_BIN=/usr/local/amoeba/bin/launcherSHUTDOWN_BIN=/usr/local/amoeba/bin/shutdownPIDFILE=/usr/local/amoeba/Amoeba-MySQL.pidSCRIPTNAME=/etc/init.d/amoebacase "$1" instart) echo-n "Starting $NAME... "$AMOEBA_BINecho" done ";; stop) echo-n" Stoping $NAME... "$SHUTDOWN_BINecho" done ";; restart) $SHUTDOWN_BINsleep 1 $AMOEBA_BIN;;*) echo" Usage: $SCRIPTNAME {start | stop | restart} "exit 1 leading esac [root@amoeba opt] # chmod + x / etc/init.d/amoeba / / give execution permission [root@amoeba opt] # chkconfig-add amoeba / / add to system management [root@amoeba opt] # service amoeba start / / enable amoeba service

To show that this is equal to this, there is no problem.

-configure read-write separation-

-authorize amoeba in three mysql databases-

Mysql > GRANT ALL ON *. * TO test@'192.168.80.%' IDENTIFIED BY '123123 unknown MySQL > FLUSH PRIVILEGES

-on the amoeba front-end server-[root@amoeba amoeba] # cd / usr/local/amoeba [root@amoeba amoeba] # vi conf/amoeba.xml- sets the username and password that the client uses when connecting to the amoeba front-end server-28 lines amoeba 123456

-at the same time, change the default server1 to master and the default servers to slaves-83 line slaves master slaves

Cd conf/ [root@amoeba conf] # vi dbServers.xml 26 line test 123123 / / specify the login username and password for the database

Modify the following 192.168.80.100192.168.80.101192.168.80.102/property > slave1,slave2

[root@amoeba amoeba] # service amoeba restart [root@amoeba amoeba] # netstat-anpt | grep java

On the client server: 192.168.80.104

[root@MYSQL ~] # yum install-y mysql

[root@MYSQL] # mysql-u amoeba-p123456-h 192.168.80.103-P8066

Use the command on the client to create a table in test:

MySQL [(none)] > use test

MySQL [test] > create table zhang (id int (10), name varchar (10), address varchar (20))

MySQL [test] > show tables

80.100

80.104