首页 » 服务器工具 » MySQL » 正文

MySQL5.7 主从配置

大型网站为了缓解大量的并发访问,可以在网站实现分布式负载均衡,但这是远远不够的。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,大量的数据库连接操作可能导致数据库的崩溃,如果发生数据丢失事故,后果更是不堪设想。这时候,我们会考虑如何减少数据库的压力,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据缓存技术如:memcached;如果资金丰厚的话,我们也可以架设服务器集群,来分担主数据库的压力。今天我们利用MySQL主从配置,可以实现读写分离,从而减轻数据库的压力。

概述:

搭设一台Master服务器(CentOS7系统,ip:192.168.1.201)
搭设一台Slave服务器(CentOS7系统,ip:192.168.1.202)

原理:

主服务器(Master)负责网站NonQuery操作,从服务器负责Query操作,用户可以根据网站功能模特性块固定访问Slave服务器,或者自己写个池或队列,自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件,实现数据同步。二进制日志由主服务器产生,从服务器响应获取同步数据库。

具体实现:

1、在主从服务器上都装上MySQL数据库,尽量数据库的版本一致。

2、配置Master主服务器:

(1)找到MySQL安装文件夹修改my.cnf文件。我们只要启动二进制日志log-bin就可以了。

在[mysqld]下面修改或增加下面几行,然后重启MySQL数据库。

[mysqld]
log-bin=mysql-bin
server-id=1 //给数据库服务的唯一标识,一般为大家设置服务器Ip的末尾号

(2)在Master MySQL上创建一个用户“repl”,并赋予“REPLICATION SLAVE”权限,即允许其他Slave服务器可以通过远程访问Master,通过该用户读取二进制日志,实现数据同步。

mysql -uroot -p //登录mysql控制台
create user repl; //创建新用户

//repl用户必须具有REPLICATION SLAVE权限,除此之外没有必要添加不必要的权限,密码为mysql。说明一下192.168.0.%,这个配置是指明repl用户所在服务器,这里%是通配符,表示192.168.0.0-192.168.0.255的Server都可以以repl用户登陆主服务器。当然你也可以指定固定Ip。
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'mysql';

(3)查看日志:
show master status;

3、Slave从服务器配置

(1)找到MySQL安装文件夹修改my.cnf文件,并在[mysqld]下面增加下面几行,并重启MySQL服务。

 

[mysqld]
server-id=2

(2)创建同步任务:

CHANGE MASTER TO
MASTER_HOST='192.168.1.201',
MASTER_USER='repl',
MASTER_PASSWORD='Rep@1234',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;

(3)启动Slave

start slave;

(4)查看是否连接成功

show slave status \G

 
(5)查看线程详细状态

select * from performance_schema.replication_applier_status_by_worker\G

 
至此所有配置都完成了,这时候我们可以在MasterMySQL中进行测试了,因为我们监视的是Master MySQL所有操作日志,所以,任何改变主服务器数据库的操作,都会同步到从服务器上。

4、停止移出主从

stop slave;
reset slave all;

5、跳过错误

1.跳过指定数量的事务:
mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #跳过一个事务
mysql>start slave;

2.跳过指定错误类型
修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

5、多线程并行复制

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
#关闭从库的binlog校验,根据个人情况而定
sync_binlog=0  
innodb_flush_log_at_trx_commit=0

6、只同步部分库或表

replicate-do-db    设定需要复制的数据库(多数据库使用逗号,隔开)
replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号,隔开)
replicate-do-table  设定需要复制的表
replicate-ignore-table 设定需要忽略的复制表 
replicate-wild-do-table 同replication-do-table功能一样,但是可以通配符replicate-wild-ignore-table 同replication-ignore-table功能一样,但是可以加通配符

7、删除MySQL的 bin log日志文件

PURGE {MASTER | BINARY} LOGS TO'log_name';
PURGE {MASTER | BINARY} LOGSBEFORE 'date';

 

补充:
1. 如果完整拷贝主库MySQL目录,建议还原performance_schema,sys数据库,注意数据库权限;
2.还原auto.cnf文件,防止UUID冲突;
3.排除replicate-ignore-db=performance_schema,sys,information_schema
4.清除 relay_log 信息:mysql> reset slave;

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据