mysql 集群数据同步

悠悠 2023-07-12 05:13 28阅读 0赞

mysql集群配置在网站负载均衡中是必不可少的；

首先说下我个人准备的负载均衡方式；

1、通过nginx方向代理来将服务器压力分散到各个服务器上；

2、每个服务器中代码逻辑一样；

3、通过使用redis缓存来保存内存中数据，使用redis同步功能来同步不同服务器内存中的数据；

4、在通过mysql的集群配置来实现数据库数据同步；

这里我整理了几种数据同步方式；

一：主从服务器同步；

顾名思义：主服务器负责数据的增删改查，从服务器负责同步数据；

主服务器建立二进制文件；每产生语句变化或磁盘变化写入日至；

从服务器读主服务二进制日至；将读到的日至转成从服务的relaylog，从服务读取relaylog同步主主服务器；

主服务器建立授权复制账号；

从服务器利用账号来监听主服务器；

步骤：

1、首先需要至少两台服务器，我这边118.xxx.xxx.1(主)，118.xxx.xxx.2(从)两台服务器；两台搭建mysql方式不同，一台安装mysql，和mysql-server;一台通过直接安装mariadb方式；没什么影响；

2、主服务器修改/etc/my.cnf;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

#在[mysqld]下添加,建立二进制日至
    #server-id一般用服务器后一位
    server-id=1
    log-binary=mysql-bin
    
    #监听变化方式，statement语句变化，row行变化，mixed智能选择
    binlog-format=mixed

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

关于binlog-format的参数statement/row

当影响一行如更新一行、插入一行、删除一行时使用row比较合理

当更新插入删除多行时使用statement比较合理

mixed是mysql根据条件自动选择使用哪一个参数

3、从服务器修改/etc/my.cnf

#[mysqld]下添加relay-log
    
    #server-id一般用服务器后一位

server-id=2

relay-log=mysql-relay

4、重启两台服务器mysql 服务

//1
    systemctl restart mysql
    
    //2
    systemctl restart mariadb

5、主服务建立授权账号replication client,replication slave账号允许服务二进制日至

grant replication client,replication slave on *.* to 'repl1'@'118.xxx.xxx.2' identified by 'password';

注意这里账号的权限只能填\*.\*；否则会报如下错误

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

6、从服务通过账号链接主服务器

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

#进入mysql,清除从库中的同步复制信息；
    reset slave;
    
    #改变同步主机的信息
    change master to
    master_host='118.xxx.xxx.1',
    master_user='repl1',
    master_password='password',
    master_log_file='mysql-bin.000003',
    master_log_pos=543;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

主服务器二进制日至在/var/lib/mysql中

在主服务器中show master status;查看主服务日至状态；file目前使用的日志文件；position目前使用的日志文件中的位置；开始从这个位置往后同步；

![format_png][]

7、查看从服务同步信息状态，并启动；

show slave status \G

![format_png 1][]

参数详解：

slave\_io\_state:线程正在试图链接到主服务器的状态；

master\_host:主服务器ip地址

master\_user:被用于链接的当前用户

master\_port:当前的连接主服务器接口

Connect\_Retry:--master-connect-retry选项的当前值

Master\_Log\_File:sql线程当前正在读取和执行的中继日志文件名称

Read\_Master\_Log\_Pos:当前中继日至中，sql线程已经读取和执行的位置

Slave\_IO\_Running:io线程是否被启动并成功连接上主服务

//启动同步
    start slave;

7、查看启动后的状态waiting for master to send event 表示正常；

![format_png 2][]

错误：

The server is not configured as slave; fix in config file or with CHANGE MASTER TO

这表示server-id 错误；将server-id 正确写在\[mysqld\]下的位置；

Failed to open the relay log './mariadb-relay-bin.000001' (relay\_log\_pos 4)

没有找到relay-bin.000001日至；因为修改前没有清除原有的配置；

解决方案：reset slave;后在change master to ....;

一：主主服务器同步；

主主服务器mysql同时两台服务器同时监听对方服务器mysql变化；当有一台服务器中数据有变化，另一台实时同步；

主从服务器同时建立二进制文件；每产生语句变化或磁盘变化写入日至；

主从服务器同时读另一台服务二进制日至；将读到的日至转成本身服务的relaylog，然后读区本服务器的relaylog同步数据；

两台服务器建立授权复制账号；

两台服务器利用账号来监听另一台服务器；

1、两台服务器my.cnf中配置

//服务器1
    server-id=163
    log-bin=mysql-bin
    binlog-format=mixed
    relay-log=mysql-relay

//服务器2
    server-id=244
    log-bin=mysql-bin
    binlog-format=mixed
    relay-log=mysql-relay

2、两台服务器都建立授权监听账号

grant replication client,replication slave on *.* to 'repl163'@'118.xxx.xxx.1' identified by 'code@password';

grant replication client,replication slave on *.* to 'repl163'@'118.xxx.xxx.2' identified by 'code@password';

3、两台服务器都监听对方log日至

change master to master_host='118.xxx.xxx.1',master_user='repl163',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=746;

change master to master_host='118.xxx.xxx.2',master_user='repl163',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=746;

4、通过show slave status \\G查看监听状态

错误：Last\_IO\_Error: error connecting to master 'repl163@118.xxx.xxx.1:3306' - retry-time: 60  retries: 2

表示连接出错，检查得知为ip写错了；

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

表示在没有解锁的情况下停止slave进程：需要clear slave;

然后再执行change master to...

5、show slave status \\G没有问题后启动；

start slave

三：mysql-proxy实现负载均衡和读写分离

//install
    yum install mysql-proxy

//101.xxx.xxx.1代理mysql端口4040;没有守护进程
    mysql-proxy -P 101.xxx.xxx.1:4040 --proxy-backend-addresses=101.xxx.xxx.1:3306 --proxy-backend-addresses=101.xxx.xxx.2:3306

//读写分离写法,守护进程启动
    mysql-proxy -b 101.xxx.xxx.1:3306 -r 101.xxx.xxx.1:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --daemon

[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvODQzNzY2LzIwMTcwMi84NDM3NjYtMjAxNzAyMTUxNTM5NTEwOTctNjI3NTQ5ODEwLnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvODQzNzY2LzIwMTcwMi84NDM3NjYtMjAxNzAyMTUxNTUzMTQ5MTAtODM5MDc1OTE1LnBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvODQzNzY2LzIwMTcwMi84NDM3NjYtMjAxNzAyMTUxNTUxMTc3MjItMjA4MTExNTY0Mi5wbmc?x-oss-process=image/format,png