mysql主从同步及读写分离配置

mysql主从同步及读写分离配置

一、配置前准备
1.linux服务器（虚拟机），本次使用安装了centos6.5的虚拟机，两台虚拟机ip信息：
master：192.168.72.129
slaver：192.168.72.130
2.安装mysql数据库，需要保证两台服务器所安装的mysql版本一致，本次以mysql-5.6.42为例。mysql安装可以参考：https://blog.csdn.net/m0_37674755/article/details/85869424
3.下载mysqlproxy软件，下载链接：https://downloads.mysql.com/archives/proxy/，选择合适的版本后下载：

下载后的文件名为 mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

二、mysql配置主从同步
1.配置主库
（1）创建主从同步用户
登录master节点数据库: mysql -uroot -p123456
创建用户，用于从库读取二进制日志：

mysql> GRANT REPLICATION SLAVE,FILE ON *.* TO 'testuser'@'192.168.72.130' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
1
2
其中：
testuser：创建的用户名
192.168.72.130：从库ip
123456：testuser的密码

（2）创建数据库，用于同步：

mysql> create database testdb;
Query OK, 1 row affected (0.10 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
5 rows in set (0.40 sec)

mysql> exit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
（3）修改my.cnf配置文件

vim /etc/my.cnf
1
在[myqld]下面加上如下配置：

   #数据库的唯一标示
    server_id = 1
    #二进制日志的记录文件
    log-bin = log-bin
    #需要进行同步的数据库
    binlog-do-db=testdb
    #需要忽略的数据库
    binlog-ignore-db=mysql
1
2
3
4
5
6
7
8
如下图：


配置完之后重启主节点mysql服务：service mysqld restart

（4）登录mysql，查看主库状态：
首先给mysql加个锁，防止写入数据

mysql>flush tables with read lock;
mysql>show master status;
1
2
查看主库状态，记录需要同步的日志名和偏移量


2.配置从库
（1）同步主库数据到从库
在主库/tmp/目录下执行如下命令，备份testdb中的数据：

mysqldump -uroot -p123456 testdb>./testdb.sql
1
使用scp命令将testdb.sql上传到从库/tmp/目录下：

scp testdb.sql root@192.168.72.130:/tmp/
1

也可以使用ftp或者其他工具上传。上传后登录从库,创建testdb数据库：

mysql>create database testdb;
1
使用如下导入命令将testdb.sql中的数据导入到从库中：

mysql -uroot -p123456 testdb</tmp/testdb.sql
1
（2）修改my.cnf文件

vim /etc/my.cnf
1
在[mysqld]中加入配置：

server_id = 2
1


重启从库后登录从库:


停止从库同步状态：

mysql> stop slave;
1
设置主库信息：

mysql>change master to master_host='192.168.72.129',master_port=3308,master_user='testuser',master_password='123456',master_log_file='log-bin.000001',master_log_pos=120; 
1
其中：
192.168.72.129：主库所在节点ip；
3308：master节点数据库的端口号；
testuser：步骤二中在主库创建的用户；
123456：步骤二中在主库创建的用户的密码；
master_log_file：步骤二中使用show master status命令查询到的二进制日志文件的名称；
120：步骤二中使用show master status命令查询到的日志记录的偏移量。

然后，重启主从同步进程：

mysql>start slave; 
1
查看从库同步进程的状态：

mysql>show slave status\G
1
如果回显信息中Slave_IO_Running和Slave_SQL_Running都是Yes，则说明主从同步状态正常。

3.主库解锁，测试同步
（1）登录主库，给主库解锁：

mysql>unlock tables;
1
（2）测试建表
在主库执行如下建表语句：

mysql>use testdb
mysql>create table user( id bigint(20), name varchar(26), address varchar(50));
1
2
在从库查询testdb里边是否有该表：

mysql>use testdb;
mysql>show tables;
mysql>desc user;
1
2
3
回显如下信息，代表从库数据已经同步成功：

（3）测试新增数据：
主库执行如下语句：

mysql>insert into testdb.user(id,name,address) values (1,'张三','陕西西安');
1
从库查询：

mysql>select * from testdb.user;
1
回显如下信息表示新增成功：


其他操作就不再演示了，进行到这一步，说明主从同步已经配置完成了。
三、使用mysqlproxy配置读写分离
1.安装mysqlproxy
上传mysqlproxy软件包到主节点192.168.72.129的/install目录下，执行以下命令解压，并移动到/usr/local/mysql-proxy目录下

mkdir /usr/local/mysql-proxy
tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
cd mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit
mv ./* /usr/local/mysql-proxy/
1
2
3
4
2.配置mysqlproxy

cd /usr/local/mysql-proxy/
mkdir lua                                                        #创建脚本存放目录
mkdir logs                                                       #创建日志目录
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/                #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua                    #复制管理脚本
vim /etc/mysql-proxy.cnf                                        #创建配置文件
1
2
3
4
5
6
配置文件mysql-proxy,cnf内容如下：

[mysql-proxy]
#运行mysql-proxy用户
user=root
#主从mysql共有的用户
admin-username=proxyuser
#用户的密码
admin-password=123456
#mysql-proxy运行ip和端口，不加端口，默认4040
proxy-address=192.168.72.129:3306
#指定后端从slave读取数据,不加端口，默认3306
proxy-read-only-backend-addresses=192.168.72.130:3308
#指定后端主master写入数据,不加端口，默认3306
proxy-backend-addresses=192.168.72.129:3308
#指定读写分离配置文件位置
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
#指定管理脚本
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
#日志位置
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
#定义log日志级别，由高到低分别有(error|warning|info|message|debug)
log-level=info
#以守护进程方式运行
daemon=true
#mysql-proxy崩溃时，尝试重启
keepalive=true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
保存退出后，修改mysql-proxy.cnf文件权限：chmod 660 /etc/mysql-proxy.cnf
3.修改读写分离配置文件：

vim /usr/local/mysql-proxy/lua/rw-splitting.lua
1
修改脚本中如下位置配置：

其中，min_idle_connections表示最小连接数，max_idle_connections表示最大连接为1个后开启读写分离
4.启动mysql-proxy:

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
1
查看配置的3306端口是否监听：

netstat -nltp|grep 3306
1

说明mysql-proxy启动成功。
5.创建用户，供mysql-proxy使用：
192.168.72.129节点上登录主库：mysql -uroot -p123456

mysql>grant all on *.* to 'proxyuser'@'192.168.72.129' identified by '123456';
mysql>flush privileges;
mysql>exit;
1
2
3
grant all on *.* to 'proxyuser'@'192.168.72.129' identified by '123456';命令中：
proxyuser:mysql-proxy连接主从库的用户；
192.168.72.129:mysql-proxy所安装的服务器的ip信息
123456:proxyuser的密码。

6.使用读写分离用户登录mysql-proxy：

mysql -uproxyuser -p123456 -h192.168.72.129 -P3306
1

以后就可以使用proxyuser用户以123456密码登录，连接192.168.72.129服务器的3306端口，先访问mysql-proxy，然后请求经过转发，读写请求发给192.168.72.129节点，读请求发给192.168.72.130节点了，从而缓解数据库压力。

7.配置防火墙，开放3306端口

vim /etc/sysconfig/iptables
1
在里边加入加入如下访问规则：

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
1
如下图：

保存后重启防火墙service iptables restart查看端口配置是否生效：iptables -L -n

这时候，就可以从外部通过mysql-proxy来访问数据库了：

本文转载至 https://blog.csdn.net/m0_37674755/article/details/86186019