mysql主从同步
实现数据自动同步的服务结构
主服务器master:接受客户端访问连接
从服务器slave:自动同步主服务器数据
主从同步原理
master启用binlog日志
slave_io:复制master主机binlog日志文件里的sql命令到本机的relay-log中继日记
slave_sql:执行本机relay-log文件里的sql语句,实现master数据一致。
基本应用:
一主一从 单点故障
一主多从 主挂,多台从不同步
主->从->从 链式复制
互为主从 主<->主
一主一从: 两台数据库一样才能配
主服务器启用binlog日志,授权用户,查看binlog日志信息
vim /etc/my.cnf 找到[mysql]在下面插入
log_bin=master //启用binlog日志在/var/lib/mysql下
server_id=51 //启用服务器id主机位
:wq
systemctl restart mysqld
主数据库:
grant replication slave on *.* to repluser@"%" identified by "密码“;
show master status; 查看启用日志信息
从服务器配置:
指定server_id(编辑my.cnf文件)
vim /etc/my.cnf 在[mysql]下插入
server_id=主机位
:wq
systemctl restart mysqld
指定主服务器信息
show slave status; 查看是不是从服务器
change master to master_host="192.168.4.51",
master_user="repluser",master_password="123",
master_log_file="master51.000001"(主服务器show master status),
master_log_pos=441(主服务器show master status);
show slave status \G;
start slave; 开启slave_io_running,slave_sql_running
存放在数据库目录下:
删除文件,重启数据库服务,可把主机恢复为独立数据服务器
在从服务器上/var/lib/mysql/文件名
master.info 主库信息
relay-log-info 中继日志信息
主机名-relay-bin-xxxxxxx 中继日志var/lib/mysql/
主机名-relay-bin.index 索引文件
配置mysql一主多从:
在已有主从中加从:主安装innobackupex拷贝数据库给新从服务器
主服务器:innobackupex --user root --password 123 --slave-info /alldb --no-timestampnong
scp -r /alldb 从服务器ip:/opt/
从服务器:
systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log /opt/alldb
innobackupex --copy-back /opt/alldb
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
cat /opt/alldb/xtrabackup_binlog_info 看日志文件和便宜量给配置从服务器指定
配置从服务器
vim /etc/my.cnf 找到[mysql]在下面插入
server_id=52 //启用服务器id主机位
:wq
systemctl restart mysqld
change master to master_host="192.168.4.51",
master_user="repluser",master_password="123",
master_log_file="master51.000001",
master_log_pos=441;
start slave;
show master status;
53主机服务器:启动binlog日志,用户授权,查看日志信息
vim /etc/my.cnf 在[mysql]下插入
log-bin=日志名
server_id=id号
:wq
systemctl restart mysqld
show user,host from mysql.user;
grant replication slave on *.* to 用户名@c”从库ip地址“ identified by ”密码“;
systemctl restart mysqld
show grants for repluser@"%";
配置 即是从服务器又是主服务器
5.4主机从服务主服务器 修改配置文件 用户授权 指定主库信息 启动slave进程
vim /etc/mysql.cnf 在[mysql] 下插入
server_id=id号
log_bin=日志名
log_slave_updates //允许级联复制
:wq
systemctl restart mysqld
grant replication slave on *.* to 用户名@”%“ identified by "密码”; //用户授权
change master to master_host="192.168.4..53",master_user="repluser",master_password="密码“,master_log_file="master53.00001"(查看show master status),master_log_pos=154;
start slave;
show slave status \G; 查看是否是从服务器
做主服务器配置:
grant replication slave on *.* jim@"%" identified by "密码”;
show master status; 查看日志文件
5.5从服务器:
修改vim /etc/my.cnf 在[mysql]插入
server_id=数字
:wq
change master to master_host=54ip ,master_user="jim",
master_password="123",master_log_file="master54.00001",
master_log_pos=334;
start slave;
show slave status;
在主库授权访问数据的连接用户
客户端连接主库,执行与权限匹配的sql操作
授权用户连接第1台从库,可以看到主库的数据
授权用户连接第2台从库,可看到主库的数据f
grant all on “库” to 用户@“客户端” identified by “密码”;
mysql -u主服务ip地址 -p 密码 //客户端连接主服务器
介绍:
异步复制:主服务器执行完一次事务后,立即将结果返回给客户端,不关心从服务器是否已经同步数据。客户端体验好 默认异步复制
半同步复制:主服务器在执行依次事务后,等待至少一台从服务器同步数据完成,才将结果返回给客户端。客户端体验下降
临时命令复制主服务器:install plugin rpl_semi_sync_master SONAME "semisync master.so";
永久设置:vim /etc/my.cnf ,在[mysqld]下方添加
plugin-load="rpl_semi_sync_master=semisync_master.so"
临时命令从服务器:install plugin rpl_semi_sync_slave SONAME “semisync_slave.so"
永久设置:vim /etc/my.cnf ,在[mysqld]下方添加
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1
即是主又是从服务器两个都配;
永久:vim /etc/my.cnf 在[mysql]下
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
systemctl restart mysqld
show variables like "%semi%";
查看模块是否加载:
select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
set global rpl_semi_sync_master_enabled=1; //启用master模块
set global rpl_semi_sync_slave_enabled=1; //启用slave模块
show variables like "rpl_semi_sync_%_enabled"; //查看
把客户端查询数据的请求和存储数据请求分给不同数据服务器
添加一个mysql代理,接收客户端访问,为客户提供访问数据接口
由mysql代理面向客户端提供服务
收到sql写请求时,交给master服务器处理
收到sql读请求,交给slave服务器处理 mycat mysql-proxy maxscale
mysql中间键
一主一从读写分离:
56主机主服务器:
58主机从服务器
57主机读写分离:
MaxScale 代理软件
由mysql的兄弟公司maradb开发
下载地址https://downloads.mariadb.com/files/MaxScale
安装软件:
rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
ls /etc/maxscale.cnf
max
ls /var/log/maxscale/
vim /etc/maxscale.cnf 在[maxscale]
threads=auto //定义线程个数
[server1] //定义数据库服务器
type=server
address=192.168.4.56
port=3306
protocol=mysqlbackend
[server2]
type=server
address=192.168.4.58
port=3306
protocol=mysqlbackend
[MySQL Monitor] //定义监视的数据库服务器
servers=server1,server2
user=maxscalemon //监视用户
password=123 //
#[Read-Only Service] //注释只读
#type=service
#route=server1
#server=server1
#user=myuser
#passwd=mypwd
#router_options=slave
[Read-Write Service] //定义读写分离服务
servers=server1,server2
user=maxscalerouter //监视路由用户
password=123
[MaxAdmin Listener] //定义管理服务端口
port=4016
:wq
配置数据库服务器
创建授权用户
再主服务器添加,从服务器会自动同步授权用户
//创建监控用户
grant replication slave,replication client on *.* to maxscalemon@"%" identified by "密码”;
//创建路由用户
grant select on mysql.* to maxscalerouter@"%" identified by “密码”;//创建路由用户
启动服务
maxscale -f /etc/maxscale.cnf //启动服务
netstat -utnlp | grep maxscale //查看端口
kill -9 pid //停止服务
netstat -utnlp | grep 4406
netstat -utntp | grep 4016
netstat -untlp | grep maxscale
ps -C maxscale 进程pid
ls /var/log/maxscale/ 日志信息
57主机:maxadmin -uadmin -pmariadb -P4016 //管理
list servers
访问代理服务:
主服务器授权连接用户
grant all on testdb.* to plj@"%“ identified by "123";
客户端连接代理查看代理服务
mysql -h192.168.4.57 -uplj -p123 -P4006
insert into
select 查询数据
验证读写分离:
在从服务器上添加新纪录
客户端连接代理服务器
查看记录
写入记录
将存放在一台数据库服务器中的数据,按照特定方式进行拆分,
分散存放到多台数据库服务器中,以达到分散单台服务器负载的效果
水分分割:
横向切分:
纵向切分:将单个数据库的多个表,按业务类型,分撒存储到不同的数据库
MyCAT 是基于java的分布式数据库系统中间件,为高并发环境分布式存储提供解决方案
适合数据大量写入的存储需求
支持mysql oracle sqlserver mongodb等
提供数据读写分离服务
提供数据分片服务
基于阿里巴巴cobar进行研发的开源软件
mycat 提供十种分片规则
枚举法 sharding-by-intfile
固定分片 rule1
范围约定auto-sharding-long
求模法 mod-long
日期列分区法 sharding-by-date
通配取模 sharding-by-pattern
ascll 码求模通配 sharding-by-prefixpattern
编程指定 sharding-by-substring
字符串拆分hash解析 sharding-by-stringhash
一致性hash sharding-by-murmur
工作过程
当mycat 收到一个sql命令时
解析sql命令涉及到的表
然后看对表的配置,如果有分片规则,则获取sql命令分片字段的值
并匹配分片函数,获取分片列表
然后将sql命令发往对应的数据库服务器去执行
最后收集和处理所有分片结果数据,并返回到客户端
规划角色与ip
主机名 角色 数据库 ip地址
client 客户端 无 192.168.4.50/24
mycat 分片 无 192.168.4.56/24
mysql53 数据库服务器 db1 192.168.4.53/24
mysql54 数据库服务器 db2 192.168.4.54/24
mysql55 数据库服务器 db3 192.168.4.55/24
mycat主机服务配置:
安装jdk
系统自带的即可
yum -y install java-1.8.0-openjdk.x86_64
下载源码包
http://www.mycat.org.cn
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
mv mycat /usr/local/
ls /usr/local/mycat/
bin //mycat命令 catlet //扩展功能 conf //配置文件 lib //mycat使用的jar包
logs //mycat启动日志和运行日志
wrapper.log //mycat 服务启动日志
mycat.log //记录sql脚本执行后的报错内容
vim /usr/local/mycat/conf/server.xml 定义客户端的用户和密码
<user name="root">
<property name="password">1234</property>
<property name="schemas">TESTDB(显示在客户端的虚拟库)</property>
</user>
<user name="root">
<property name="password">1234</property>
<property name="schemas">TESTDB(显示在客户端的虚拟库)</property>
<property name="readOnly">true</property> //只读权限
</user>
:wq
schema.xml
<schema></schema>定义分片信息
<table></table> 定义表
name //逻辑库名或逻辑表名
dataNode 指定数据库服务器库名信息
datahost 配置数据库ip地址
rule 指定使用的分片规则
type=global 数据部分片存储
sed -i "56,77d" schema.xml 删掉读写分离部分
server.xml :定义客户端连接用户密码逻辑库名访问权限
schema.xml:T<table>定义逻辑表名,服务器台数,分片规则
vim /usr/local/mycat/conf/schema.xml
<schema name="TESTDB" ..>
<table/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1">
<dataHost name="localhost1">
<writeHost host="host1" url="数据库ip" user="数据库授权用户" password="23>
配置数据库服务器
根据分片配置做相应的设置
创建存储数据库db1 db2 db3
create database db1; //53主机
create database db2; //54主机
create database db3 ; //55主机
添加授权用户
grant all on *.* to admin@"%" identified by "密码”; 所有主机
56主机启用mycat
/usr/local/mycat/bin/mycat start|stop
netstat -untlp | grep 8066
分片字段值,必须在规则文件定义的值里选择
vim /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile"> 在schema.xml下rule
<rule>
<columns>sharding-id</columns> 分片字段i是id改名
<algorithm>hash-int</algorithm> 算法
....
<function name="hash-int">
<property name=mapfile>partition-hash-int.txt</property> 需要vim 文件更改
:wq
vim ./partition-hash-int.txt
10000=0
10010=1
10020=2
:wq
/usr/local/mycat/bin/mycat stop
/usr/local/mycat/bin/mycat start
查看使用枚举规则表 schema.xml table.rule=""
分片规则对应的算法及分片字段名 cat rule.xml
查看分片规则对应的配置文件 shardind_id
parttition-hash-int.txt
建表
字段id
必要字段sharding_id
插入记录
根据字段值与设定的数字求模结果存储数据
schema.xml rule.xml
建表
create table hostname (ID int primary key auto_increment,
num int , title char(15) ,comment varchar(150));
求模法ID必须赋值可以NULL占位
insert into hotnews(ID,num,title,comment) values (NULL(自增),22,"kk","ll");
修改server.xml 文件
<property name="schemas">TESTDB,BBSDB</property>
修改schema.xml 在<mycat:schema...> 下 添加几行
<schema name="BBSDB"...> 可参考下面
<table name="user" dataNode"db1,db2,db3",rule="mod-long“></table>
</schema>
:wq
重启