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

淡淡的烟草味﹌ 2022-03-28 07:40 324阅读 0赞

**一、配置前准备**  
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][https_blog.csdn.net_m0_37674755_article_details_85869424]  
3.下载mysqlproxy软件，下载链接：[https://downloads.mysql.com/archives/proxy/，选择合适的版本后下载：][https_downloads.mysql.com_archives_proxy]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70]  
下载后的文件名为 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;

其中：  
testuser：创建的用户名  
192.168.72.130：从库ip  
123456：testuser的密码

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

（3）修改my.cnf配置文件

vim /etc/my.cnf

在\[myqld\]下面加上如下配置：

#数据库的唯一标示
        server_id = 1
        #二进制日志的记录文件
        log-bin = log-bin
        #需要进行同步的数据库
        binlog-do-db=testdb
        #需要忽略的数据库
        binlog-ignore-db=mysql

如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 1]  
配置完之后重启主节点mysql服务：`service mysqld restart`  
![在这里插入图片描述][20190109224007847.png]  
（4）登录mysql，查看主库状态：  
首先给mysql加个锁，防止写入数据

mysql>flush tables with read lock;
    mysql>show master status;

查看主库状态，记录需要同步的日志名和偏移量  
![在这里插入图片描述][20190109225913807.png]

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

mysqldump -uroot -p123456 testdb>./testdb.sql

使用scp命令将testdb.sql上传到从库/tmp/目录下：

scp testdb.sql root@192.168.72.130:/tmp/

![在这里插入图片描述][20190109234545759.png]  
也可以使用ftp或者其他工具上传。上传后登录从库,创建testdb数据库：

mysql>create database testdb;

使用如下导入命令将testdb.sql中的数据导入到从库中：

mysql -uroot -p123456 testdb</tmp/testdb.sql

（2）修改my.cnf文件

vim /etc/my.cnf

在\[mysqld\]中加入配置：

server_id = 2

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 2]

重启从库后登录从库:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 3]

停止从库同步状态：

mysql> stop slave;

设置主库信息：

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;

其中：  
192.168.72.129：主库所在节点ip；  
3308：master节点数据库的端口号；  
testuser：步骤二中在主库创建的用户；  
123456：步骤二中在主库创建的用户的密码；  
master\_log\_file：步骤二中使用`show master status`命令查询到的二进制日志文件的名称；  
120：步骤二中使用`show master status`命令查询到的日志记录的偏移量。

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

mysql>start slave;

查看从库同步进程的状态：

mysql>show slave status\G

如果回显信息中Slave\_IO\_Running和Slave\_SQL\_Running都是Yes，则说明主从同步状态正常。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 4]  
***3.主库解锁，测试同步***  
（1）登录主库，给主库解锁：

mysql>unlock tables;

（2）测试建表  
在主库执行如下建表语句：

mysql>use testdb
    mysql>create table user( id bigint(20), name varchar(26), address varchar(50));

在从库查询testdb里边是否有该表：

mysql>use testdb;
    mysql>show tables;
    mysql>desc user;

回显如下信息，代表从库数据已经同步成功：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 5]  
（3）测试新增数据：  
主库执行如下语句：

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

从库查询：

mysql>select * from testdb.user;

回显如下信息表示新增成功：  
![在这里插入图片描述][20190109233713943.png]

其他操作就不再演示了，进行到这一步，说明主从同步已经配置完成了。  
**三、使用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/

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                                        #创建配置文件

配置文件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

保存退出后，修改mysql-proxy.cnf文件权限：`chmod 660 /etc/mysql-proxy.cnf`  
3.修改读写分离配置文件：

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

修改脚本中如下位置配置：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 6]  
其中，min\_idle\_connections表示最小连接数，max\_idle\_connections表示最大连接为1个后开启读写分离  
4.启动mysql-proxy:

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

查看配置的3306端口是否监听：

netstat -nltp|grep 3306

![在这里插入图片描述][20190114224653559.png]  
说明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;

`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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 7]  
以后就可以使用proxyuser用户以123456密码登录，连接192.168.72.129服务器的3306端口，先访问mysql-proxy，然后请求经过转发，读写请求发给192.168.72.129节点，读请求发给192.168.72.130节点了，从而缓解数据库压力。

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

vim /etc/sysconfig/iptables

在里边加入加入如下访问规则：

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 8]  
保存后重启防火墙`service iptables restart`查看端口配置是否生效：iptables -L -n  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 9]  
这时候，就可以从外部通过mysql-proxy来访问数据库了：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 10]

[https_blog.csdn.net_m0_37674755_article_details_85869424]: https://blog.csdn.net/m0_37674755/article/details/85869424
[https_downloads.mysql.com_archives_proxy]: https://downloads.mysql.com/archives/proxy/%EF%BC%8C%E9%80%89%E6%8B%A9%E5%90%88%E9%80%82%E7%9A%84%E7%89%88%E6%9C%AC%E5%90%8E%E4%B8%8B%E8%BD%BD%EF%BC%9A
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70]: /images/20220328/3cedad7544834cf68a30a9314c001862.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 1]: /images/20220328/ebd0fbe9b75e46388c73a0c2339119aa.png
[20190109224007847.png]: /images/20220328/0e064b9a5f28439f96b01c5263be41f9.png
[20190109225913807.png]: /images/20220328/bbe5eae618cd4903b6c6bca604917666.png
[20190109234545759.png]: /images/20220328/2235df454fb845cbbbfac2047dd52992.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 2]: /images/20220328/32266d173a8d45d099745d888c83ee38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 3]: /images/20220328/67916a12b0944811a03074c0072e4ba7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 4]: /images/20220328/7b5e233120514ba395f383ff02f5dae5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 5]: /images/20220328/b1a7f436ba5343ff9efc52bc9b987d56.png
[20190109233713943.png]: /images/20220328/245b8daa440d45feb4a6f72692df7ecf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 6]: /images/20220328/e30f849727774d23aadf9311468900d9.png
[20190114224653559.png]: /images/20220328/2bacce0f01ba483f8b9b5b89facff101.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 7]: /images/20220328/57ea37432a084ac08f55270c9cd61e84.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 8]: /images/20220328/6e88332817574d23924b4f38a86f7d99.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 9]: /images/20220328/cc57329b63824ccbbd012a4f20396171.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3Njc0NzU1_size_16_color_FFFFFF_t_70 10]: /images/20220328/986cbac40d834fe29c511b1c0f309723.png