mysql主从同步数据分离分库分表

2023-02-18 13:12 作者:bili_39183997178 0人读过 | 我要投稿

1.1 主从同步介绍

存储数据的服务结构，分为2种角色：

主服务器(master)：接受客户端访问连接

从服务器(slave)：自动同步主服务器数据

1.2主从同步工作原理

主服务器：启用binlog日志

从服务器：

IO线程：复制master主机 binlog日志文件里的SQL命令保存到中继日志文件里。

SQL线程：执行中继日志文件里的SQL语句，实现与Master数据一致。

1.3常用的主从同步结构

一主一从结构

一主多从结构

主从从结构

主主结构

(一主一从同步结构)

第一步 配置master服务器

具体步骤如下：

1) 启用binlog日志文件

vim /etc/my.cnf

[mysqld]

server_id=51

log_bin=master51 #日志默认存储在数据库目录下

]# systemctl restart mysqld

2）用户授权

]# mysql -uroot -p密码

replication slave 让用户有复制命令权限

mysql> grant replication slave on *.* to repluser@"%"

identified by "123qqq...A";

3）查看日志信息

#查看到的日志名和偏移量是给从服务器使用的

mysql> show master status;

第二步配置slave服务器

1 指定server_id 并重启mysqld服务

vim /etc/my.cnf

[mysqld]

server_id=52 #自己添加的

systemctl restart mysqld

2 确保数据一致（如果一致的此步骤可以省略）

3 指定主服务器信息

]#mysql -uroot -p密码

mysql> show slave status \G #不是从数据库服务器

Empty set (0.00 sec)

mysql> change master to master_host="192.168.4.51",

master_user="repluser" , master_password="123qqq...A",

master_log_file="master51.000001" , master_log_pos=441 ;

4）启动slave进程

mysql> start slave;

5）查看状态（IO线程和 SQL线程必须同时是YES 状态）

mysql> show slave status \G

根据 IO线程和 SQL线程的报错信息排错

mysql> show slave status \G

Last_IO_Error: IO线程的报错信息

Last_SQL_Error: SQL线程的报错信息

在从服务器执行如下操作：

mysql> stop slave;

在主服务器查看日志名和偏移量

host51 show master status;

所有错误的统一解决办法：

把slave角色的服务器恢复为独立的数据库服务器，重新配置为从服务器。

在从服务器执行如下操作：

systemctl stop mysqld

cd /var/lib/mysql/

rm -rf master.info

rm -rf *-relay-bin.*

rm -rf relay-log.info

systemctl start mysqld

一主多从同步结构

和上面的配置一样的只是id不同相同

[root@host53 ~]# vim /etc/my.cnf

[mysqld]

server_id=53 #添加

:wq

[root@host53 ~]# systemctl restart mysqld

主从从同步结构

第1步：配置主数据库服务器 192.168.4.53

1）启用binlog日志

vim /etc/my.cnf

[mysqld]

server_id=53

log_bin=master53

:wq

systemctl restart mysqld

2）用户授权

host53~]# mysql -uroot -p123qqq...A

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

3）查看日志信息

mysql> show master status;

配置从服务器

vim /etc/my.cnf

[mysqld]

server_id=54

log_bin=master54

log_slave_updates #允许级联复制，host54主机把自己主服务器的数据

systemctl restart mysqld

[root@host54 ~]# mysql -uroot -p123qqq...A

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

mysql> show master status;

change master to master_host="192.168.4.53",master_user="repluser",master_password="123qqq...A",

-> master_log_file="master53.000001",master_log_pos=441;

mysql> start slave;

配置从服务器

]# vim /etc/my.cnf

[mysqld]

server_id=55

:wq

systemctl restart mysqld

]# mysql -uroot -p123qqq...A

mysql> change master to master_host="192.168.4.54",master_user="repluser",master_password="123qqq...A",

-> master_log_file="master54.000001",master_log_pos=441;

mysql> start slave;

mysql> show slave status \G

同步结构 主主结构

vim /etc/my.cnf

[mysqld]

server_id=68

log_bin=master68

:wq

systemctl restart mysqld

]# mysql -uroot -p123qqq...A

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show master status;

第二台

[root@host69 ~]# vim /etc/my.cnf

[mysqld]

server_id=69

log_bin=master69

:wq

systemctl restart mysqld

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show master status;

#把自己配置为host68的slave服务器，日志名和偏移量要在host68主机查看后填写

mysql> change master to master_host="192.168.4.68",master_user="repluser",master_password="123qqq...A",

-> master_log_file="master68.000001",master_log_pos=441;

mysql> start slave;

mysql> show slave status \G

Master_Host: 192.168.4.68

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

第3步：在host68 主机数据库管理员登录服务后

#把自己指定为host69 主机的slave 服务器

[root@host68 ~]# mysql -uroot -p123qqq...A

mysql> change master to master_host="192.168.4.69" , master_user="repluser" , master_password="123qqq...A",

-> master_log_file="master69.000001",master_log_pos=441;

mysql> start slave;

mysql> show slave status \G

Master_Host: 192.168.4.69

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql 主从同步复制数据工作模式

1）主从同步复制模式可以在任意数据库服务器启用半同步服务模式

支持的工作方式：

第1种异步复制模式（默认）

主服务器执行完一次事务后，立即将结果返给客户端，

不关心从服务器是否已经同步数据。

第2种半同步复制模式

主服务器在执行完一次事务后，等待至少一台从服务器同步数据完成，

才将结果返回给客户端。

安装模块

#安装master模块

MySQL> INSTALL PLUGIN rpl_semi_sync_master SONAME "semisync_master.so";

#安装slave模块

MySQL> INSTALL PLUGIN rpl_semi_sync_slave SONAME "semisync_slave.so";

mysql> SELECT plugin_name, plugin_status FROM information_schema.plugins

WHERE plugin_name LIKE "%semi%"; 查看模块是否安装成功

启用模块

#启用master模块

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1

#启用slave模块

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

查看模块是否启用

MySQL> SHOW VARIABLES LIKE "rpl_semi_sync_%_enabled";

方法2 永久配置编辑主配置文件

vim /etc/my.cnf

[mysqld]

#安装模块

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#启用模块

rpl_semi_sync_slave_enabled=1

rpl_semi_sync_master_enabled=1

:wq

数据读写分离介绍

把查询请求select 命令给slave服务器处理

把存储请求insert 命令给master服务器处理

提供数据读写分离功能的中间件软件有： mysql-proxy maxscale mycat

使用maxscale 提供数据读写服务

第1 步：把host51 配置为master数据库服务器

第2 步：把host52 配置为slave数据库服务器

步骤参考 RDBMS2_day01 的一主一从配置例子

安装一台maxscale主机

修改配置文件

cp /etc/maxscale.cnf /root/ 备份主配置文件

[root@host57 ~]# vim /etc/maxscale.cnf

#服务启动后线程的数量

[maxscale]

threads=auto

[server1] 指定第1台数据库服务器的ip地址

type=server

address=192.168.4.51

port=3306

protocol=MySQLBackend

[server2] 指定第2台数据库服务器的ip地址

type=server

address=192.168.4.52

port=3306

protocol=MySQLBackend

[MySQL Monitor] 定义监视的数据库服务器

type=monitor

module=mysqlmon

servers=server1,server2 监视server1和server2

user=mysqla 监控用户

passwd=123qqq...A 连接密码

monitor_interval=10000

#禁止只读服务

#[Read-Only Service]

#type=service

#router=readconnroute

#servers=server1

#user=myuser

#passwd=mypwd

#router_options=slave

[Read-Write Service] 启用读写分离服务

type=service

router=readwritesplit

servers=server1,server2 读写分离服务在server1和server2服务器之间进行

user=mysqlb 路由用户

passwd=123qqq...A 连接密码

max_slave_connections=100%

[MaxAdmin Service] 管理服务（通过访问管理服务可以查看监控信息）

type=service

router=cli

因为只读服务没有启用所有也不需要定义服务使用的端口号

#[Read-Only Listener]

#type=listener

#service=Read-Only Service

#protocol=MySQLClient

#port=4008

[Read-Write Listener] 定义读写分离服务使用端口号

type=listener

service=Read-Write Service

protocol=MySQLClient

port=4006 端口号

[MaxAdmin Listener] 定义管理服务使用端口号

type=listener

service=MaxAdmin Service

protocol=maxscaled

socket=default

port=4016 端口号

配置数据库服务器(在数据库服务器上添加监控用户和路由用户)

注意：因为是主从结构，所以只需要在主服务器添加，从服务器会自动同步

添加监控用户 mysqla 用户

mysql> grant replication slave , replication client on *.* to

mysqla@"%" identified by "123qqq...A";

权限说明：

replication client 监视数据库服务的运行状态

replication slave 数据库服务器的主从角色

添加路由用户 mysqlb 用户

mysql> grant select on mysql.* to mysqlb@"%" identified by "123qqq...A"; #对授权库下的表有查询权限

[root@host57 ~]# maxscale -f /etc/maxscale.cnf 启动服务

[root@host57 ~]# ls /var/log/maxscale/ 查看日志文件

maxscale.log

[root@host57 ~]# netstat -utnlp | grep 4006 查看读写分离服务端口号

tcp6 0 0 :::4006 :::* LISTEN 1580/maxscale

[root@host57 ~]# netstat -utnlp | grep 4016 查看读写分离服务端口号

多实例：

在一台服务器上允许多个数据库服务

为什么要使用多实例？节约运维成本提高硬件利用率

]# rpm -q libaio || yum -y install libaio

]# tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql

]# PATH=/usr/local/mysql/bin:$PATH

]# vim /etc/bashrc

export PATH=/usr/local/mysql/bin:$PATH 添加在文件的末尾

:wq

[root@host58 ~]# id mysql || useradd mysql

id: mysql: no such user

[root@host58 ~]# grep mysql /etc/passwd

mysql:x:1000:1000::/home/mysql:/bin/bash

ls /etc/my.cnf 如果文件有的话要删除 rm -rf /etc/my.cnf

]# vim /etc/my.cnf 创建文件并编辑

#管理多实例服务的运行配置

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe 服务启动的时候执行的是那个命令

mysqladmin = /usr/local/mysql/bin/mysqladmin 修改数据库管理员密码使用的命令

user = root 管理服务的启动者

#定义实例1

[mysqld1]

datadir = /dir1 数据库目录

port = 3307 服务的端口号

log-error = /dir1/mysqld1.err 错误日志文件

pid-file = /dir1/mysqld1.pid pid号文件

socket = /dir1/mysqld1.sock socket文件 (在数据库服务器本机访问多实例时

通过socket区分连接的实例服务)

#定义实例2

[mysqld2]

datadir = /dir2 数据库目录

port = 3308 服务的端口号

log-error = /dir2/mysqld2.err 错误日志文件

pid-file = /dir2/mysqld2.pid pid号文件

socket = /dir2/mysqld2.sock socket文件 (在数据库服务器本机访问多实例时

通过socket区分连接的实例服务)

mysqld_multi start 1 启动实例1

查看目录下的文件列表

[root@host58 ~]# ls /dir1/

auto.cnf ib_logfile0 mysql mysqld1.sock sys

ib_buffer_pool ib_logfile1 mysqld1.err mysqld1.sock.lock

ibdata1 ibtmp1 mysqld1.pid performance_schema

可以查看到端口

[root@host58 ~]# netstat -utnlp | grep 3307

使用初始密码连接服务

[root@host58 ~]# mysql -uroot -p'avH,8dVtZnaq' -S /dir1/mysqld1.sock

[root@host58 ~]# mysqld_multi --user=root --password=123456 stop 1 停止实例需要对应的密码及用户

分库分表

1）什么是分库分表

将存放在一台数据库服务器中的数据，按照特定方式进行拆分，

分散存放到多台数据库服务器中，以达到分散单台服务器负载的效果

2）分库分表分割方式

垂直分割(纵向切分)

按业务类型分类，将存储在一个库里的表分别存储到不同的数据库器里

水平分割(横向切分)

将表记录按行切分，分散存储到多个数据库服务器表中。

3) MyCAT软件介绍（提供数据分库分表存储的服务软件（中间件））

mycat是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案

适合数据大量写入的存储需求

支持MySQL、Oracle、Sqlserver、Mongodb等

提供数据读写分离服务

提供数据分片服务

基于阿里巴巴Cobar进行研发的开源软件

4）提供的10种分片规则

1 枚举法 sharding-by-intfile

2 固定分片 rule1

3 范围约定 auto-sharding-long

4 求模法 mod-long

5 日期列分区法 sharding-by-date

6 通配取模 sharding-by-pattern

7 ASCII码求模通配 sharding-by-prefixpattern

8 编程指定 sharding-by-substring

9 字符串拆分hash解析 sharding-by-stringhash

10 一致性hash sharding-by-murmur

5）分片服务器的工作过程；当mycat收到一个SQL命令时

第一步：解析SQL命令涉及到的表

第二步：根据表使用分片规则的计算结果（看对表的配置，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表）

第三步：将SQL命令发往对应的数据库服务器去执行

第四步：最后收集和处理所有分片结果数据，并返回到客户端

部署mycat服务

第一步：安装软件

[root@maxscale56 ~]# yum -y install java-1.8.0-openjdk.x86_64

[root@maxscale56 ~]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@maxscale56 ~]# mv mycat/ /usr/local/

[root@maxscale56 ~]# ls /usr/local/mycat/

bin catlet conf lib logs version.txt

第二步：了解安装目录文件列表

bin //mycat命令

catlet //扩展功能

conf //配置文件

.txt 和 ..properties 结尾的是分片规则配置文件

.xml 结尾的是 mycat服务配置文件

lib //mycat使用的jar包

logs //mycat启动日志和运行日志

version.txt //mycat软件说明文件

第三步：修改配置文件

# vim /usr/local/mycat/conf/server.xml (使用默认配置即可)

<user name="root"> 用户名

<property name="password">123456</property> 密码

<property name="schemas">TESTDB</property> 虚拟库名

</user>

<user name="user"> 用户名

<property name="password">user</property> 密码

<property name="schemas">TESTDB</property> 虚拟库名

<property name="readOnly">true</property> 只读访问权限

</user>

:wq

#设置分片存储数据的表 schema.xml 文件格式说明

vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

#定义分片存储数据的表

....

</table>

</schema>

#定义数据库服务器主机名

#定义数据库服务器ip地址

......

</dataHost>

</mycat:schema>

vim /usr/local/mycat/conf/schema.xml

#定义3台数据库服务器的主机名

<dataNode name="dn1" dataHost="mysql53" database="db1" /> 第一台

<dataNode name="dn2" dataHost="mysql54" database="db2" /> 第二台

<dataNode name="dn3" dataHost="mysql55" database="db3" /> 第三台

#指定名称叫mysql53主机的ip地址

......

</writeHost>

</dataHost>

#指定名称叫mysql54主机的ip地址

......

</writeHost>

</dataHost>

#指定名称叫mysql55主机的ip地址

......

</writeHost>

</dataHost>

:wq

修改后的内容

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"

rule="mod-long" />

<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"

rule="sharding-by-intfile" />

<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"

rule="sharding-by-intfile">

<childTable name="orders" primaryKey="ID" joinKey="customer_id"

parentKey="id">

<childTable name="order_items" joinKey="order_id"

parentKey="id" />

</childTable>

<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"

parentKey="id" />

</table>

</schema>

<dataHost name="mysql53" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

<dataHost name="mysql54" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

<dataHost name="mysql55" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

</mycat:schema>

配置数据库服务器

# 添加用户pljadmin

mysql -uroot -p123qqq...A -e 'grant all on *.* to pljadmin@"%" identified by "123qqq...A" #几台都要添加

启动mycat服务

[root@host56 mycat]# /usr/local/mycat/bin/mycat start

查看日志 文件

[root@host56 mycat]# ls /usr/local/mycat/logs/

查看端口号

[root@host56 mycat]# netstat -utnlp | grep 8066

停止服务的命令

[root@host56 mycat]# /usr/local/mycat/bin/mycat stop

服务没有启动的排错方法

查看日志文件获取报错信息

vim /usr/local/mycat/logs/wrapper.log

建表存储数据（验证分片规则）

配置思路：

1 确定使用分片规则的表叫什么名字名

（查看schema.xml 文件里 <table> ）

2 确定分片字段的表头名

（查看rule.xml文件里的 <tableRule> ）

3 确定分片规则使用的配置文件，定义分片字段的值

（查看rule.xml文件里的 <function> ）

4 修改分片规则的配置文件，定义分片字段的值

5 重启mycat服务

6 根据分片规则建表存储数据

5 在数据库服务器本机查看存储的数据

#找使用sharding-by-intfile分片规则的表名

vim /usr/local/mycat/conf/schem.xml

#创建employee分片字段名 sharding_id

vim /usr/local/mycat/conf/rule.xml

<tableRule name="sharding-by-intfile"> 分片规则名

<rule>

<columns>sharding_id</columns> 分片字段名

<algorithm>hash-int</algorithm> 算法名 (存储数据的计算规则)

</rule>

</tableRule>

#找到sharding-by-intfile分片规则配置文件partition-hash-int.txt

<function name="hash-int" 算法名

class="io.mycat.route.function.PartitionByFileMap"> 算法调用的执行程序

<property name="mapFile">partition-hash-int.txt</property> 配置文件名

</function>

#编辑 sharding-by-intfile分片规则配置文件partition-hash-int.txt 设置分片字段的值

host56]# vim /usr/local/mycat/conf/partition-hash-int.txt

分片字段值=第几台数据库服务器

10000=0 # 0对应dn1 mysql53 192.168.4.53 db1

10010=1 # 1对应dn2 mysql54 192.168.4.54 db2

10020=2 # 2对应dn3 mysql54 192.168.4.54 db3

:wq

[root@host56 conf]# /usr/local/mycat/bin/mycat restart

mod-long分片规则工作过程：根据分片字段值与设定的数字求模结果存储数据

当余数是 0 数据存储到 dn1

当余数是 1 数据存储到 dn2

当余数是 2 数据存储到 dn3

创建表：必须根据表使用的分片规则建表,具体操作如下：

1 确定使用分片规则的表叫什么名字名（查看schema.xml 文件里 <table> ）

[root@maxscale56 ~]# vim /usr/local/mycat/conf/schema.xml

<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"

rule="mod-long" />

2 确定分片字段的表头名（查看rule.xml文件里的 <tableRule> ）

[root@maxscale56 ~]# vim /usr/local/mycat/conf/rule.xml

<tableRule name="mod-long"> 分片规则名

<rule>

<columns>id</columns> 分片字段名

<algorithm>mod-long</algorithm> 算法

</rule>

</tableRule>

3 确定分片规则使用的配置文件，定义分片字段的值（查看rule.xml文件里的 <function> ）

[root@maxscale56 ~]# vim /usr/local/mycat/conf/rule.xml

<property name="count">3</property> 定义分片字段做求模计算的数字（是数据库服务器的台数）

</function>

4 重启mycat服务（配置文件没有做过修改，服务不需要重启）

5 创建表并存储数据

标签：

mysql主从同步数据分离分库分表

第一步：安装软件

第二步：了解安装目录文件列表

#设置分片存储数据的表 schema.xml 文件格式说明

mod-long分片规则工作过程：根据分片字段值与设定的数字求模结果存储数据