MySQL主从复制和读写分离

ゝ一世哀愁。 2022-04-12 13:47 379阅读 0赞

一，MySQL单点服务搭建

1，tar包解压，并重命名为mysql

tar -zxvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz -C /usr/develop/
    cd /usr/develop/
    mv mysql-5.7.21-linux-glibc2.12-x86_64 mysql

2，在mysql下创建data文件夹

cd mysql
    mkdir data

3，添加用户组

// 添加群组
    groupadd mysql
    // 添加用户
    useradd -r -g mysql mysql

4，初始化mysql

./bin/mysqld --initialize --user=mysql --basedir=/usr/develop/mysql --datadir=/usr/develop/mysql/data

\* 初始化成功后如下图，会生成标记出的一串初始密码，该密码为第一次连接的登陆密码

![20181203193013763.png][]

5，配置文件/etc/my.cnf，初始化成功后，会在etc目录下生成mysql的主配置文件；

[mysqld]
    # 数据文件路径
    datadir=/usr/develop/mysql/data
    # 主路径
    basedir=/usr/develop/mysql
    # 端口
    port=3306
    # 绑定IP地址
    bind-address=0.0.0.0
    # 编码集
    character-set-server = utf8
    explicit_defaults_for_timestamp = true
    # 用户
    user=mysql

6，启动服务，配置信息完成后可以启动服务，启动成功后如截图，克隆一个窗口后查看运行状态

./bin/mysqld_safe --user=mysql &

![20181203193419169.png][]

![2018120319351532.png][]

7，客户端连接服务器

./bin/mysql -uroot -p

\* 登陆密码为刚才初始化时候生成的密码，复制直接连接mysql服务（ItGG2wgc3o:+）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70][]

\* 修改密码；初始密码登陆后必须修改密码，不能直接进行操作

![20181203193740459.png][]

set password=password("123456");

\* 如果希望通过sqlyong或者Navicat等客户端进行远程连接，则需要进行授权

// root@'%'：表示对使用root账户登陆的所有ip地址授权
    // 123456：表示登陆密码
    grant all privileges on *.* to'root'@'%' identified by '123456';
    // 授权生效
    flush privileges;

![20181203194025693.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 1][]

8，设置service启动

\* 复制启动文件

// 复制mysql.server文件到init.d下, 并改名为mysql
    cp ./support-files/mysql.server /etc/init.d/mysql

\* 修改文件配置信息，填充basedir和datadir

![20181203194511382.png][]

\* 文件添加系统执行权限

chmod +x /etc/init.d/mysql

\* 使用命令查看状态

![20181203194604194.png][]

9，设置开机启动

// 开机启动配置
    chkconfig --add mysql

二，主从复制

1，Master节点配置

\* 接入mysql，创建主从复制用户

create user admin identified by '123456';

\* 新用户授权

GRANT REPLICATION SLAVE ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';

\* 配置my.cnf主配置文件，指定数据库时，确保该数据库已经存在

// 创建数据库
    create database db_store;

server-id=137			// 唯一标识
    log-bin=dbstore_binlog  // master服务器日志
    # binlog-do-db=db_store   // 指定主从复制的数据库

\* 查看日志, 记录File与Postion字段，用于配置slave节点

SHOW MASTER STATUS;

![2018120320075349.png][]

\* 重启mysql服务

service mysql restart;

2，Slave节点配置

\* 配置my.inf配置文件

server-id=129 // 唯一主键
    relay-log=slave_relay_bin
    relay-log-index=slave_relay_bin.index
    replicate_do_db=db_store // 需要同步的数据库, 提前创建

\* 连接mysql客户端，接入master服务器

// master_host : master节点IP地址
    change master to master_host='192.168.91.128', 
    // master节点端口
    master_port=3306,
    // 用户主从复制的用户名
    master_user='admin',
    // 用于主从复制的密码
    master_password='123456',
    // master节点需要记录的file字段
    master_log_file='dbstore_binlog.000002',
    // master节点需要记录的position字段
    master_log_pos=325;

\* 启动slave节点

// 启动slave节点
    start slave;
    // 停止slave节点
    stop slave;

\* 查看节点状态，节点状态如截图所示，说明正常；

show slave status\G;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 2][]

\* 错误分析：Slave\_IO\_Running和Slave\_SQL\_Running两个属性可能为NO

\* Slave\_IO\_Running为NO说明服务存在问题，检查主从复制是否正常搭建

\* Slave\_SQL\_Running为NO说明程序执行异常，下面会有对应的error提示，比如

![20181203213608713.png][]

\* 发生错误后，节点停止运行，可以跳过当前事务维持系统正常

stop slave;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ;
    start slave;

\* 主从复制演示

-- 插入数据

![20181203213837677.png][]

-- 查看数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 3][]

三，读写分离

读写分离基于中间键mysql-proxy，以及一系列依赖包

1，服务器准备，基于主从复制来实现读写分离

\* 主从复制服务器基于上面操作，128机器为master机器， 129和130机器为slave机器

\* MySQL-Proxy调度服务器为130机器

2，MySql-Proxy安装

\* mysql-proxy安装需要基于lua安装，lua安装在redis博客篇有介绍

\* 下载地址：[MySQL :: Download MySQL Proxy (Archived Versions)][MySQL _ Download MySQL Proxy _Archived Versions]

\* tar包解压

[root@slave_2 myapp]# tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/develop/

\* 重命名文件夹

[root@localhost develop]# mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy

\* 创建日志和配置文件夹及文件

[root@localhost mysql-proxy]# mkdir conf
    [root@localhost mysql-proxy]# mkdir log
    [root@localhost mysql-proxy]# cd conf/
    [root@localhost conf]# touch mysql-proxy.conf
    // 日志文件夹为自动生成

\*  配置rw-splitting.lua

// 创建路径
    mkdir scripts

// 复制一份文件到该路径
    cp /usr/develop/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /usr/develop/mysql-proxy/scripts/rw-splitting.lua

-- 修改文件下的连接数为1，方便后续测试

![2018120800130380.png][]

\* 添加配置信息

[mysql-proxy]
    #运行mysql-proxy用户
    user=root
    #主从mysql共有的用户
    admin-username=proxy
    #用户的密码
    admin-password=123456
    #mysql-proxy运行ip和端口，不加端口，默认4040
    proxy-address=192.168.91.130:3406
    #指定后端mysql从slave读取数据
    proxy-read-only-backend-addresses=192.168.91.129
    #指定后端mysql主master写入数据
    proxy-backend-addresses=192.168.91.128
    #指定读写分离配置文件位置
    proxy-lua-script=/usr/develop/mysql-proxy/scripts/rw-splitting.lua
    #指定管理脚本
    admin-lua-script=/usr/develop/mysql-proxy/lib/mysql-proxy/lua/admin.lua
    #日志位置
    log-file=/usr/develop/mysql-proxy/log/mysql-proxy.log
    #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
    log-level=info
    #以守护进程方式运行
    daemon=true
    keepalive=true

\* 启动服务

// 带配置文件启动方式
     ./mysql-proxy --defaults-file=/usr/develop/mysql-proxy/conf/mysql-proxy.conf
    // 配置文件授权
    chmod 660 /usr/develop/mysql-proxy/conf/mysql-proxy.conf
    // 带配置信息启动方式
    ./mysql-proxy -P 192.168.91.130:3406 -r 192.168.91.129:3306 -b 192.168.91.128:3306 -s /usr/develop/mysql-proxy/scripts/rw-splitting.lua &

3，服务连接授权

\* 启动主从服务三台服务器及mysql-proxy

\* 连接主节点（128）服务，执行授权语句，对读写分离用户进行授权，从节点会根据主从复制共享

grant all on *.* to 'proxy'@'%' identified by '123456';

\* 本地客户端连接后，效果如下

![20181208001820766.png][]

4，读写分离演示

\* 在proxy节点即mysql-proxy上创建库表及数据，会同时同步到主从复制三个节点

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 4][]

\* 数据查看，可以看到此时数据库数据一致

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 5][]

\* 要看出读写分离，可以对读库数据进行手动修改，从上面配置可以看到，128为主库，129为读库，往读库插入一条数据查看

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 6][]

\* 重新查看数据，可以看到此时的查询结果集为读库的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 7][]

[20181203193013763.png]: /images/20220412/fb2701cd28114c1e97b53aa5abe74e81.png
[20181203193419169.png]: /images/20220412/3ced18a8dc0c48699bf77805022fe752.png
[2018120319351532.png]: /images/20220412/ef7c672b3f4d444a9fdcba974b94398d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70]: /images/20220412/be82f115a5d4420b9d0bcedb6030b917.png
[20181203193740459.png]: /images/20220412/276fee50506f4ccfbe4d73f24dcb4aba.png
[20181203194025693.png]: /images/20220412/7c9e0e905de44329a9312eb956af3d73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 1]: /images/20220412/5c1b4775d5364202968988e00e18e460.png
[20181203194511382.png]: /images/20220412/fedde008ed424fb39ebfe0ba2a76f270.png
[20181203194604194.png]: /images/20220412/c2c180a23d9a4011938fbbd54f3f9ac5.png
[2018120320075349.png]: /images/20220412/8bbe326bcc7644bf9d874ff9e5e7e374.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 2]: /images/20220412/b1123c9602554e5290311a0addcb95c3.png
[20181203213608713.png]: /images/20220412/ace1fc85c8064f19a674c9dbedc6bd04.png
[20181203213837677.png]: /images/20220412/a129b66a8d514f4d9d6dfcb9a78adc05.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 3]: /images/20220412/6bd47d7dfa9048bca295210191fc4697.png
[MySQL _ Download MySQL Proxy _Archived Versions]: https://downloads.mysql.com/archives/proxy/
[2018120800130380.png]: /images/20220412/37aff5a29a3c43659255eac170dc6799.png
[20181208001820766.png]: /images/20220412/94000d171e54442893badb06bd82a515.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 4]: /images/20220412/b88ea860b3684154aa4fbad836b3f034.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 5]: /images/20220412/2318d1b2ee1a4961801e7d0b3c3a5a8f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 6]: /images/20220412/07ed5f2a4c5e4c32b7af33fe57c29306.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTE5NzYzODg_size_16_color_FFFFFF_t_70 7]: /images/20220412/132016bb1e744c73b22275673b0892c2.png