MySQL主从复制和读写分离
主从复制（一主双备，这边mysql已经都是装好的）
1、设置时间同步
主服务器主机名设置为1
[root@localhost ~]# yum -y install ntp
[root@localhost ~]# hostnamectl set-hostname 1
[root@localhost ~]# su
[root@1 ~]# vim /etc/ntp.conf ##在文本最后加上下面两句代码
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@1 ~]# service ntpd restart
Redirecting to /bin/systemctl restart ntpd.service
[root@1 ~]# systemctl restart ntpd
[root@1 ~]# systemctl enable ntpd两台从服务器分别设置主机名为2，3
[root@localhost ~]# hostnamectl set-hostname 2
[root@localhost ~]# su
[root@2 ~]#
[root@2 ~]# yum -y install ntpdate
Loaded plugins: fastestmirror
Determining fastest mirrors
centos | 3.6 kB 00:00:00
Package ntpdate-4.2.6p5-28.el7.centos.x86_64 already installed and latest version
Nothing to do
[root@2 ~]#
##这边是我已经有这个时间同步服务了
[root@2 ~]# ntpdate 20.0.0.60
21 Oct 02:17:22 ntpdate[37995]: adjust time server 20.0.0.60 offset -0.008243 sec
[root@2 ~]# crontab -e ##创建周期性计划任务
no crontab for root - using an empty one
*/2 * * * * /usr/sbin/ntpdate 20.0.0.60 >>/var/log/ntpdate.log
crontab: installing new crontab
##刷新计划任务并设为开机自启
[root@2 ~]# systemctl restart crond
[root@2 ~]# systemctl enable crond第二台从服务器跟第一台从服务操作一样
[root@localhost ~]# hostnamectl set-hostname 3
[root@localhost ~]# su
[root@3 ~]# yum -y install ntpdate
Loaded plugins: fastestmirror
Determining fastest mirrors
centos | 3.6 kB 00:00:00
Package ntpdate-4.2.6p5-28.el7.centos.x86_64 already installed and latest version
Nothing to do
[root@3 ~]# ntpdate 20.0.0.60
21 Oct 02:17:39 ntpdate[48587]: adjust time server 20.0.0.60 offset 0.006296 sec
[root@3 ~]# crontab -e
no crontab for root - using an empty one
*/2 * * * * /usr/sbin/ntpdate 20.0.0.60 >>/var/log/ntpdate.log
crontab: installing new crontab
[root@3 ~]# systemctl restart crond
[root@3 ~]# systemctl enable crond2、配置主从复制
主服务器
[root@1 ~]# vim /etc/my.cnf
##修改配置文件[mysqld]模块下面的server-id然后加入下面两行
##启用二进制文件和允许从服务器同步
server-id = 11
log_bin = master-bin
log-slave-updates = ture
[root@1 ~]# systemctl restart mysqld ##重启数据库并进入数据库授权
[root@1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'As123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; ###看主服务器的状态，待会从服务器要用到
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 599 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)第一台从服务器
[root@2 ~]# vim /etc/my.cnf ##修改[mysqld]模块下的server id 并添加两行代码，开启中继日志和日志索引
server-id = 22
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
##重启mysql服务并进入数据库授权
[root@2 ~]# systemctl restart mysqld
[root@2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> change master to master_host='20.0.0.60',master_user='myslave',master_password='As123',master_log_file='master-bin.0000001',master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges; ##刷新数据库，有时候会不立即生效
Query OK, 0 rows affected (0.00 sec)
mysql> start slave; ##开启从服务器
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G ##查看从服务器状态，\G是竖着看，横着看有点烦。只需要看到IO和SQL两个都是yes状态就说明OK了
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.60
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
第二台从服务器
和第一台从服务器基本一样的
[root@localhost ~]# vi /etc/my.cnf
server-id = 33
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> change master to master_host='20.0.0.60',master_user='myslave',master_password='As123',master_log_file='master-bin.0000001
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.60
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试主从复制
先去所有mysql看一下库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)在主服务器创建一个库
mysql> create database zhucong;
Query OK, 1 row affected (0.00 sec)
去从服务器看一下
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhucong |
+--------------------+
5 rows in set (0.00 sec)
说明主从复制已经完成了
读写分离
准备工作jdk和amoeba压缩包
[root@amoeba opt]# ll
total 83024
-rw-r--r-- 1 root root 3161433 Oct 20 19:59 amoeba-mysql-binary-2.2.0.tar.gz
-rwxr-xr-x 1 root root 81849212 Oct 20 20:00 jdk-6u14-linux-x64.bin
##这边我的jdk权限已经给了直接./执行了
配置jdk
[root@amoeba ~]# mv /opt/jdk1.6.0_14 /usr/local/jdk1.6
[root@amoeba ~]# vim /etc/profile ## 设置Java环境变量
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/bin
##1、java的工作目录
##2、jre里面放了lib（类库）里面都是类文件
##3、指定的环境变量，便于识别
##4、amoeba工作目录
##5、指定环境变量
[root@amoeba ~]# source /etc/profile ## 刷新文件
[root@amoeba ~]# echo $PATH
/usr/local/jdk1.6/lib:/usr/local/jdk1.6/jre/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/usr/local/amoeba/bin
##Jdk和amoeba都在里面，说明环境变量生效了
[root@amoeba opt]# ll
drwxr-xr-x 10 root root 321 Oct 21 17:20 jdk1.6.0_14
-rw-r--r-- 1 root root 3161433 Oct 20 19:59 amoeba-mysql-binary-2.2.0.tar.gz
-rwxr-xr-x 1 root root 81849212 Oct 20 20:00 jdk-6u14-linux-x64.bin
配置阿米巴
[root@amoeba ~]# mkdir /usr/local/amoeba
[root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
##说明已经安装成功了
###去三台数据库给amoeba授权
mysql> grant all on *.* to test@'20.0.0.%' identified by 'As123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; ## 刷新一下
Query OK, 0 rows affected (0.00 sec)
修改阿米巴配置文件
[root@amoeba ~]# cd /usr/local/amoeba/conf/
[root@amoeba conf]# vim amoeba.xml
## amoeba申明名称的配置文件
[root@amoeba conf]# vi dbServers.xml
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &
#开启amoeba服务并放到后台执行
[root@amoeba conf]# yum -y install mariadb*
# 安装mysql客户端，用于做测试用
[root@amoeba conf]# systemctl start mariadb.service
## 开启数据库这边有时候会有提示按下面的顺序做就行了
回车 （提示输入密码，我们没有密码直接回车）
yes 然后输入两次密码 （输入新密码）
n （删除里面匿名用户）
n （禁止用户远程登录）
n （删除测试数据库）
y （重载里面的表）
[root@amoeba conf]# netstat -anpt |grep 8066
tcp6 0 0 :::8066 :::* LISTEN 33973/java
##端口已经开启，说明服务已经开了
[root@amoeba ~]# mysql -u amoeba -p -h 127.0.0.1 -P8066
##因为是amoeba本机当测试机用的所以-h指向的是127.0.0.1测试
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| szy |
+--------------------+
5 rows in set (0.01 sec)
MySQL [(none)]> create database ig;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> use ig;
Database changed
MySQL [ig]> create table nb (id int(4));
Query OK, 0 rows affected (0.01 sec)然后去两台从服务器关闭主从复制
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 20.0.0.60
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 1212
Relay_Log_File: relay-log-bin.000017
Relay_Log_Pos: 1379
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No ##这两个关闭就行了
Slave_SQL_Running: No ##这两个关闭就行了
mysql> use ig
Database changed
mysql> show tables;
+--------------+
| Tables_in_ig |
+--------------+
| nb |
+--------------+
1 row in set (0.00 sec)
mysql> insert into nb values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from nb;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 20.0.0.60
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 1212
Relay_Log_File: relay-log-bin.000017
Relay_Log_Pos: 1379
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No ##这两个关闭就行了
Slave_SQL_Running: No ##这两个关闭就行了
mysql> use ig
Database changed
mysql> show tables;
+--------------+
| Tables_in_ig |
+--------------+
| nb |
+--------------+
1 row in set (0.00 sec)
mysql> insert into nb values (2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from nb;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
最后去客户机测试
MySQL [ig]> select * from nb;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
MySQL [ig]> select * from nb;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
MySQL [ig]> select * from nb;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
MySQL [ig]> select * from nb;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.01 sec)结论：发现每一次查询都会轮询在slave1和slave2上查询数据，如果开启主从复制，则数据都会相同
读写分离试验成功
扫一扫
145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
