yum -y install ntpvim /etc/ntp.conf ###添加两行 server 127.127.1.0 fudge 127.127.1.0 stratum 8 systemctl restart ntpd systemctl enable ntpd
yum -y install ntpdate ntpdate 20.0.0.100 添加计划任务，自动同步时间 crontab -e */2 * * * * /usr/sbin/ntpdate 20.0.0.100 >> /var/log/ntpdate.log
主mysql服务器20.0.0.100 vim /etc/my.cnf server-id = 11 #修改为11 log-bin = master-bin #添加开启二进制日志 log-slave-updates = true #添加允许从服务器二进制日志同步
systemctl restart mysqld
登陆数据库授权从服务器 mysql -u root -p mysql> grant replication slave on . to ‘myslave’@‘20.0.0.%’ identified by ‘abc123’; #授予复制权限 mysql> flush privileges; #刷新 mysql> show master status; ±------------------±---------±-------------±-----------------±------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ±------------------±---------±-------------±-----------------±------------------+ | master-bin.000001 | 604 | | | | ±------------------±---------±-------------±-----------------±------------------+ 记住这里的Position值，后面配置从服务器需要用到
vim /etc/my.cnf server-id = 22 #多从台服务器，只需要保证server-id 不同即可，其它配置都一样 relay-log = relay-log-bin #开启中继日志，从主服务器上复制二进制日志文件到本地 relay-log-index = slave-relay-bin.index #定义relay-log的未知和文件名 systemctl restart mysqld
登陆数据库配置Slave同步 mysql -u root -p mysql> change master to master_host=‘20.0.0.100’,master_user=‘myslave’,master_password=‘abc123’,master_log_file=‘master-bin.000001’,master_log_pos=604; #此处配置信息与主服务器授权时应一致#此处用到主服务器的Position值 mysql> start slave; #当再次开启同步时，必须要先停止stop slave;否则会发送错误 mysql> show slave status\G #\G以竖的格式显示
cd /opt tar xzvf jdk-8u144-linux-x64.tar.gz cp -rv jdk1.8.0_144/ /usr/local/java vim /etc/profile ###在后面加入 export JAVA_HOME=/usr/local/java #指定java目录 export JRE_HOME=/usr/local/java/jre #java虚拟机，简称JVM export PATH=$PATH:/usr/local/java/bin export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib #类文件库
source /etc/profile java -version
unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/ mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba chmod -R 755 /usr/local/amoeba/ vim /usr/local/amoeba/jvm.properties JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" ###修改
vi /etc/init.d/amoeba #!/bin/bash #chkconfig: 35 62 62
export JAVA_HOME=/usr/local/java export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH NAME=Amoeba AMOEBA_BIN=/usr/local/amoeba/bin/launcher SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid SCRIPTNAME=/etc/init.d/amoeba
case “$1” in start) echo -n “Starting $NAME… " $AMOEBA_BIN echo " done” ;; stop) echo -n “Stoping $NAME… " $SHUTDOWN_BIN echo " done” ;; restart) $SHUTDOWN_BIN sleep 1 $AMOEBA_BIN ;; *) echo “Usage: $SCRIPTNAME {start|stop|restart}” exit 1 ;; esac
chmod +x /etc/init.d/amoeba chkconfig --add amoeba service amoeba start
mysql> create database test; mysql> GRANT ALL ON *.* TO test@‘20.0.0.%’ IDENTIFIED BY ‘abc123’; mysql> FLUSH PRIVILEGES;
vi conf/amoeba.xml <property name=“readPool”>slaves #85 读取池名称 <property name=“needParse”>true #86 vim conf/dbServers.xml #26-29的注释去掉 <property name=“schema”>test #####数据库中要有此处定义的数据库，否则客户端连接后会报错，默认我用test数据库，创建是test数据库不要弄错了 <property name=“user”>test #设置授权账号，与三台mysql服务器amoeba授权对应 <property name=“password”>abc123 #设置授权密码，与三台mysql服务器amoeba授权对应 <dbServer name=“master” parent=“abstractServer”> #####name后面改成 master <factoryConfig> <!-- mysql ip --> <property name=“ipAddress”>192.168.100.42 ####ipAddress加上主mysql的IP地址 </factoryConfig> </dbServer>
<dbServer name=“slave1” parent=“abstractServer”> #####name后面改成 slave1 <factoryConfig> <!-- mysql ip --> <property name=“ipAddress”>192.168.100.43 ###ipAddress加上从1mysql的IP地址 </factoryConfig> </dbServer>
<dbServer name=“slave2” parent=“abstractServer”> #####name后面改成 slave2 <factoryConfig> <!-- mysql ip --> <property name=“ipAddress”>192.168.100.44 ###ipAddress加上从2mysql的IP地址 </factoryConfig> </dbServer>
<dbServer name=“slaves” virtual=“true”> #####name后面改成 slaves，与amoeba.xml中读取池的名字对应 <poolConfig class=“com.meidusa.amoeba.server.MultipleServerPool”> <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA–> <property name=“loadbalance”>1
<!-- Separated by commas,such as: server1,server2,server1 --> <property name=“poolNames”>slave1,slave2 #####poolNames后面改成 slave1,slave2 </poolConfig> </dbServer>
service amoeba restart netstat -anpt | grep java
service firewalld stop setenforce 0 yum install -y mysql mysql -u amoeba -pabc123 -h 20.0.0.33 -P8066 ##### 登录20.0.0.33 amoeba服务端
[root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123 mysql> use test; create table zhang1 (id int(10),name varchar(10),address varchar(20)); mysql> use test; Database changed mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20)); Query OK, 0 rows affected (0.05 sec）
###在从1 mysql关闭同步 20.0.0.31### [root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123 mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> use test; Database changed mysql> insert into zhang1 values(‘2’,‘zhang’,‘this_is_slave1’); Query OK, 1 row affected (0.00 sec)
###在从2 mysql关闭同步 20.0.0.32### [root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123 mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> use test; Database changed mysql> insert into zhang1 values(‘3’,‘zhang’,‘this_is_slave2’); Query OK, 1 row affected (0.00 sec)
###在主mysql上插入数据 20.0.0.31 ### [root@localhost ~]# mysql -u root -p ###输入mysql密码 abc123 mysql> use test; Database changed mysql> insert into zhang1 values(‘1’,‘zhang’,‘this_is_master’); Query OK, 0 rows affected (0.05 sec）
######登录客户端 20.0.099##### [root@localhost ~]# mysql -u amoeba -pabc123 -h 20.0.0.33 -P8066 MySQL [(none)]> show databases; ±-------------------+ | Database | ±-------------------+ | information_schema | | bdqn | | db_test | | mysql | | performance_schema | | sys | | test | #####发现数据库test ±-------------------+ 7 rows in set (0.01 sec)
MySQL [(none)]> use test; ####进入数据库test Database changed MySQL [test]> select * from zhang1; ###查看zhang信息 发现进入slave1 数据中的数据 | id | name | address | ±-----±------±---------------+ | 2 | zhang | this_is_slave1 | ±-----±------±---------------+ 1 row in set (0.00 sec)
MySQL [test]> select * from zhang1; ###查看zhang信息 发现进入slave2 数据中的数据 ±-----±------±---------------+ | id | name | address | ±-----±------±---------------+ | 3 | zhang | this_is_slave2 | ±-----±------±---------------+ 1 row in set (0.01 sec)
#########上述实验，实现数据读取负载均衡#######
###在客户机上 20.0.0.99 继续写输数据，按照正常的规则应该是slave1 和slave2是看不见的 MySQL [(none)]> use test; Database changed MySQL [test]> insert into zhang1 values(‘4’,‘zhang’,‘write_test’); Query OK, 1 row affected (0.01 sec)
######登录mastar 20.0.0.100 ### [root@localhost ~]# mysql -u root -p mysql> use test; Database changed mysql> select * from zhang1; ±-----±------±---------------+ | id | name | address | ±-----±------±---------------+ | 1 | zhang | this_is_master | | 4 | zhang | write_test | ±-----±------±---------------+ 2 rows in set (0.00 sec)
######登录slave1 20.0.0.31### [root@localhost ~]# mysql -u root -p mysql> use test; Database changed mysql> select * from zhang1; ±-----±------±---------------+ | id | name | address | ±-----±------±---------------+ | 2 | zhang | this_is_slave1 | ±-----±------±---------------+ 1 row in set (0.00 sec)
######登录slave2 20.0.0.32 ### [root@localhost ~]# mysql -u root -p mysql> use test; Database changed mysql> select * from zhang1; ±-----±------±---------------+ | id | name | address | ±-----±------±---------------+ | 3 | zhang | this_is_slave2 | ±-----±------±---------------+ 1 row in set (0.00 sec)
