目录
一、为什么要使用MySQL的主从复制和读写分离
在实际环境中,如果数据库的读和写都在一个数据库服务器中,并发负载是无法满足实际要求的
故而引出了通过主从复制和读写分离来提高数据库的并发负载能力
MySQL主从复制原理(利用二进制日志进行备份数据)
二、实验环境
主机名 | ip地址 | 扮演角色 |
---|---|---|
master | 192.168.100.1 | mysql主服务器 |
slave | 192.168.100.2 | mysql从服务器 |
amoeba | 192.168.100.3 | 代理服务器 |
app | 192.168.100.4 | 应用服务器 |
三、实验步骤
(1)搭建时间服务器,为了防止时间错误无法备份
mysql主服务器
******(1)进行基础配置
[root@mysql ~]# hostnamectl set-hostname master
[root@mysql ~]# su
[root@master ~]# setenforce 0
setenforce: SELinux is disabled
[root@master ~]# systemctl stop firewalld
[root@master ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护,将以只读方式挂载
******(2)使用yum安装ntp时间服务
[root@master ~]# yum -y install ntp
。。。。。。
完毕!
******(3)配置ntp时间服务,并且启动
[root@master ~]# cat <<a>> /etc/ntp.conf
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> a
[root@master ~]# systemctl restart ntpd
[root@master ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@master ~]# netstat -utpln | grep ntp (确认已经成功启动)
udp 0 0 192.168.100.1:123 0.0.0.0:* 1728/ntpd
udp 0 0 127.0.0.1:123 0.0.0.0:* 1728/ntpd
udp 0 0 0.0.0.0:123 0.0.0.0:* 1728/ntpd
udp6 0 0 fe80::7762:f351:dbf:123 :::* 1728/ntpd
udp6 0 0 ::1:123 :::* 1728/ntpd
udp6 0 0 :::123 :::* 1728/ntpd
mysql从服务器
******(1)做基础配置
[root@centos7-011 ~]# hostnamectl set-hostname slave
[root@centos7-011 ~]# su
[root@slave ~]# systemctl stop firewalld
[root@slave ~]# setenforce 0
setenforce: SELinux is disabled
[root@slave ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护,将以只读方式挂载
******(2)使用yum安装ntp的客户端
[root@slave ~]# yum -y install ntpdate
。。。。。。
完毕!
[root@slave ~]# /usr/sbin/ntpdate 192.168.100.1 (与主服务器同步时间)
(2)在主服务器和从服务器上安装mysql
具体步骤请点击链接:mysql安装
(3)开启mysql,创建密码
主服务器和从服务器的操作相同
******(1)开启mysql
[root@master ~]# systemctl start mysqld
******(2)创建密码
[root@master ~]# mysqladmin -u root -p password 123.com
(4)配置MySQL主从复制
mysql主服务器
******(1)编辑mysql主配置文件
[root@master ~]# vim /etc/my.cnf
。。。。。。
server-id = 11 (这个是唯一的)
log-bin = master-bin (开启二进制日志)
log-slave-updates = true
保存退出
[root@master ~]# systemctl restart mysqld (重启mysql)
******(2)进入数据库配置
[root@master ~]# mysql -u root -p123.com
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'myslave'@'%' identified by '123'; (赋权一个用户)
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; (刷新权限)
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; (查看主服务器的二进制日志,这里要记住file和position的值)
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 199 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> create database aaa; (创建测试数据库)
Query OK, 1 row affected (0.00 sec)
mysql> show databases; (查看是否创建成功)
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.39 sec)
mysql> quit (退出数据库)
Bye
mysql从服务器
******(1)配置mysql配置文件
[root@slave ~]# vim /etc/my.cnf
。。。。。。
server-id = 22
relay-log = relay-log-bin (开启中继日志)
relay-log-index = slave-relay-bin.index
保存退出
[root@slave ~]# systemctl restart mysqld
******(2)进入数据库配置
[root@slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host = '192.168.100.1',master_user = 'myslave',master_password = '123',master_log_file= 'master-bin.000001',master_log_pos = 199; (199不能加引号)
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; (查看无error即可)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.1
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 491
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 576
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes (这项必须为yes)
Slave_SQL_Running: Yes (这项必须为yes)
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 491
Relay_Log_Space: 747
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: e65c9adc-3489-11eb-841f-000c2944addb
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show databases; (查看是否同步成功)
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.11 sec)
(5)配置代理服务器
******(1)做基础配置
[root@centos7-009 ~]# hostnamectl set-hostname amoeba
[root@centos7-009 ~]# su
[root@amoeba ~]# systemctl stop firewalld
[root@amoeba ~]# setenforce 0
setenforce: SELinux is disabled
[root@amoeba ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护,将以只读方式挂载
******(2)搭建java环境
[root@amoeba ~]# yum -y remove java (先检查是否安装了java,安装了的话得先删除)
已加载插件:fastestmirror
参数 java 没有匹配
不删除任何软件包
[root@amoeba ~]# ll (上传java脚本)
总用量 79936
-rw-------. 1 root root 1262 9月 3 2020 anaconda-ks.cfg
-rw-r--r-- 1 root root 81849212 3月 23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin (添加可执行权限)
[root@amoeba ~]# bash jdk-6u14-linux-x64.bin (执行脚本)
。。。。。。 过程中输入一次 yes , 最后输入回车
[root@amoeba ~]# ll
总用量 79936
-rw-------. 1 root root 1262 9月 3 2020 anaconda-ks.cfg
drwxr-xr-x 10 root root 321 3月 23 16:47 jdk1.6.0_14
-rwxr-xr-x 1 root root 81849212 3月 23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba ~]# cat <<a>> /etc/profile (在全局变量的文件下添加,优化环境变量,使linux内核可以识别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
> a
[root@amoeba ~]# source /etc/profile (使刚写入的全局变量生效)
[root@amoeba ~]# java -version (查看java版本)
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
******(3)编译安装amoeba
[root@amoeba ~]# mkdir /usr/local/amoeba (创建目录)
[root@amoeba ~]# ll (上传amoeba的源码包)
总用量 83024
-rw-r--r-- 1 root root 3161433 3月 23 16:57 amoeba-mysql-binary-2.2.0.tar.gz
-rw-------. 1 root root 1262 9月 3 2020 anaconda-ks.cfg
-rwxr-xr-x 1 root root 81849212 3月 23 16:46 jdk-6u14-linux-x64.bin
[root@amoeba ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ (解压)
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba (验证)
amoeba start|stop
(6)在mysql主服务器和从服务器赋权一个用户
******(1)在mysql主服务器上赋权一个用户
[root@master ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to aaa@'192.168.100.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
******(2)查看mysql从服务器是否同步
[root@slave ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants for aaa@'192.168.100.%'; (发现已经成功同步)
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for aaa@192.168.100.% |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'aaa'@'192.168.100.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(7)修改amoeba的配置文件
[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml
。。。。。。
30 <property name="user">amoeba</property>
31
32 <property name="password">123456</property>
。。。。。。
115 <property name="defaultPool">master</property>
116
117 <property name="writePool">master</property> (注意删除<!-- -->的注释)
118 <property name="readPool">slaves</property>
。。。。。。
保存退出
[root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml
。。。。。。
25 <!-- mysql user -->
26 <property name="user">aaa</property> (数据库赋权的用户)
27
28 <!-- mysql password --> (把下面的--》注释移到这里)
29 <property name="password">123</property> (赋权用户的密码)
。。。。。。
44
45 <dbServer name="master" parent="abstractServer"> (修改为master)
46 <factoryConfig>
47 <!-- mysql ip -->
48 <property name="ipAddress">192.168.100.1</property> (修改为mysql主服务器的ip)
。。。。。。
51 (51——57为服务器的配置,如果有多个,可以由此格式写多台从服务器)
52 <dbServer name="slave1" parent="abstractServer"> (修改为slave1)
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">192.168.100.2</property> (修改为mysql从服务器的地址)
56 </factoryConfig>
57 </dbServer>
。。。。。。
59 <dbServer name="slaves" virtual="true"> (修改为slaves)
。。。。。。
65 <property name="poolNames">slave1</property> (修改为slave1,如果有多个使用,隔开)
保存退出
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba start & (启动amoeba在后台运行)
[3] 1954
[root@amoeba ~]# netstat -utpln | grep 8066 (检查端口确认已经启动)
tcp6 0 0 :::8066 :::* LISTEN 1954/java
(8)验证
******(1)打开第四台机器,先做基础配置
[root@centos7-010 ~]# hostnamectl set-hostname app
[root@centos7-010 ~]# su
[root@app ~]# systemctl stop firewalld
[root@app ~]# setenforce 0
setenforce: SELinux is disabled
[root@app ~]# mount /dev/cdrom /media/cdrom/
mount: /dev/sr0 写保护,将以只读方式挂载
******(2)使用yum安装mysql
[root@app ~]# yum -y install mysql (使用yum安装mysql)
。。。。。。
完毕!
******(3)远程连接amoeba代理服务器,创建测试数据库和表
[root@app ~]# mysql -u amoeba -p123456 -h 192.168.100.3 -P 8066 (代理服务器的用户使amoeba,但是代理服务器使用的是mysql主服务器赋权的用户aaa登录的)
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 906872446
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.02 sec)
MySQL [(none)]> create database bbb;
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> create table bbb.aaa(id int(10),name char(10));
Query OK, 0 rows affected (0.25 sec)
******(4)在mysql从服务器上查看结果,发现已经成功同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| bbb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use bbb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_bbb |
+---------------+
| aaa |
+---------------+
1 row in set (0.00 sec)
mysql>
******(5)关闭从服务器的slave功能,分别在主服务器和从服务器上创建不同的数据
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主服务器:
mysql> insert into bbb.aaa values(1,'aaa');
Query OK, 1 row affected (0.00 sec)
从服务器:
mysql> insert into bbb.aaa values(2,'bbb');
Query OK, 1 row affected (0.00 sec)
******(6)使用应用客户端查看
MySQL [(none)]> select * from bbb.aaa; (发现只有从服务器创建的数据)
+------+------+
| id | name |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.05 sec)
******(7)使用应用客户端写入数据
MySQL [(none)]> insert into bbb.aaa values(3,'ccc');
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from bbb.aaa; (查询的时候发现没有刚刚创建的数据)
+------+------+
| id | name |
+------+------+
| 2 | bbb |
+------+------+
1 row in set (0.00 sec)
******(8)使用mysql主服务器查询
mysql> select * from bbb.aaa; (发现没有从服务器创建的数据)
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 3 | ccc |
+------+------+
2 rows in set (0.00 sec)
总结
当应用客户端写入数据时,amoeba代理服务器会到mysql主服务器上进行存储,当应用客户端读取数据时,amoeba代理服务器回到mysql从服务器上查询数据 (从服务器可以有多台)
,实现了读写分离,当mysql从服务器开启slave功能时,主服务器和从服务器可以保证数据的一致性