读写分离
读写分离其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。使用读写分离可以大大降低数据库服务器的负载。
环境搭建
rhel7.3
server1：172.25.61.1（读写）
server2：172.25.61.2（只读）
server3：172.25.61.3（调度）
server1和server2上已经安装了mysql
- 配置server1和server2主从复制
server1修改配置文件,初始化
vim /etc/my.cnf
添加以下内容
log-bin=mysql-bin
server_id=1
gtid_mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
log_bin=binlog
cd /var/lib/mysql ##进入到此目录中，如果有文件就删除，初始化
systemctl start mysqld
cat /var/log/mysqld.log | grep password ##获取密码
mysql_secure_installation ##安全初始化
[root@server1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: ##输入获取的密码
The existing password for the user account root has expired. Please set a new password.
New password: ##设置密码
Re-enter new password:
登陆server1授权
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.61.%' identified by 'Redhat+001';
Query OK, 0 rows affected, 1 warning (0.29 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 1003 | | | ab212727-7725-11e9-aee1-525400bacb9b:1-4 |
+---------------+----------+--------------+------------------+------------------------------------------+
server2设置与servar1同步
server2数据库初始化同server1一样
[root@server2 ~]# mysql -p
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.01 sec)
mysql> change master to master_host=‘172.25.61.1’, master_user=‘repl’, master_password=‘Redhat+001’,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.96 sec)
mysql> start slave;
Query OK, 0 rows affected (0.48 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都为yes同步连接成功
-
server2安装调度器，编写调度规则
[root@server3 ~]# ls mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ [root@server3 ~]# cd /usr/local/ [root@server3 local]# ls bin games lib libexec sbin src etc include lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit share [root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy [root@server3 local]# cd mysql-proxy [root@server3 mysql-proxy]# mkdir conf [root@server3 mysql-proxy]# cd conf/ [root@server3 ~]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf 1 [mysql-proxy] 2 proxy-address=0.0.0.0:3306 3 proxy-read-only-backend-addresses=172.25.61.2:3306 ##指定后端从slave读取数据 4 proxy-backend-addresses=172.25.61.1:3306 ##指定后端从master写入数据 5 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 6 pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid 7 log-file=/usr/local/mysql-proxy/log/mysql-proxy.log 8 plugins=proxy 9 log-level=debug 10 keepalive=true 11 daemon=true vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
##表示大于两个就启动读写分离
-
启动mysql-proxy
[root@server3 ~]# mkdir /usr/local/mysql-proxy/log [root@server3 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
查看日志连接情况
[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 ~]# cat /usr/local/mysql-proxy/log/mysql-proxy.log
2019-07-13 00:17:11: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=10413 alive
2019-07-13 00:17:11: (debug) chassis-unix-daemon.c:157: waiting for 10413
2019-07-13 00:17:11: (debug) chassis-unix-daemon.c:121: we are the child: 10413
2019-07-13 00:17:11: (critical) plugin proxy 0.8.5 started
2019-07-13 00:17:11: (debug) max open file-descriptors = 1024
2019-07-13 00:17:11: (message) proxy listening on port 0.0.0.0:3306
2019-07-13 00:17:11: (message) added read/write backend: 172.25.61.1:3306
2019-07-13 00:17:11: (message) added read-only backend: 172.25.61.2:330
-
server1（master）写入数据，配置用户权限
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> grant insert,update,select on *.* to xin@'%' identified by 'Redhat+001'; Query OK, 0 rows affected, 1 warning (0.29 sec) mysql> flush privileges; Query OK, 0 rows affected (0.40 sec) mysql> create database redhat; Query OK, 1 row affected (0.14 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | redhat | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use redhat; Database changed mysql> create table usertb ( -> username varchar(10) not null, -> password varchar(15) not null); Query OK, 0 rows affected (0.78 sec) mysql> desc usertb; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(10) | NO | | NULL | | | password | varchar(15) | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from usertb; +----------+----------+ | username | password | +----------+----------+ | user1 | 123 | +----------+----------+ 1 row in set (0.00 sec) -
远程登陆mysql-proxy服务端
[root@foundation61 yum.repos.d]# mysql -h 172.25.61.3 -uxin -pRedhat+001 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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 | | mysql | | performance_schema | | redhat | | sys | +--------------------+ 5 rows in set (0.00 sec) MySQL [(none)]> select * from usertab; ERROR 1046 (3D000): No database selected MySQL [(none)]> select * from usertb; ERROR 1046 (3D000): No database selected MySQL [(none)]> use redhat; 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 [redhat]> select * from usertb; +----------+----------+ | username | password | +----------+----------+ | user1 | 123 | +----------+----------+ 1 row in set (0.00 sec)
-
测试读写分离
[root@foundation61 ~]# mysql -h 172.25.61.3 -uxin -pRedhat+001 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright © 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. MySQL [(none)]>
关闭server2主从复制，添加数据
添加数据
数据添加成功却查不到，因为主从复制关闭后，数据写在了server1上，读的时候读server2的数据，server2没有同步server1的数据
查看server1和server2的数据
数据添加到了server1上
server2上没有数据
实现读写分离
扫一扫
5647
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
