Mysql 架构及优化之-主从复制同步部署

JellyBool 发布于2019-07-31 16:12 / 1115人阅读

摘要：概念主从复制台以上服务器做负载均衡主服务器负责增删改从服务器负责查询同步原理开启日志主服务器所有的增删改操作会记录到日志然后主服务器把日志发送给从服务器从服务器重放日志确保数据同步开启日志配置文件并重启开启之后对应的文件已经出现

概念

主从复制:2台以上mysql服务器, 做负载均衡, 主服务器负责增删改 , 从服务器负责查询

同步原理：mysql开启bin-log日志,主服务器所有的增删改操作会记录到bin-log日志
然后主服务器把bin-log日志发送给从服务器 , 从服务器重放bin-log日志确保数据同步

开启bin-log日志

配置 my.cnf 文件并重启 mysql

[root@localhost etc]# vim /etc/my.cnf

[root@localhost etc]# service  mysql restart

开启之后 mysql-bin对应的文件已经出现

[root@localhost var]# cd /usr/local/mysql/var && ll

通过 show master status 命令查看最新一个binlog日志及开始行数

mysql> show master status;

查看binlog日志内容可见最新一行日志在位置107

$ /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001

测试删除数据可见 binlog文件新增日志内容

bin-log日志相关命令

flush logs

新建一个binlog日志增删改日志在新文件中插入新的日志end-log-positon 是
107行，107行记录了mysql内部日志

reset master

清空所有bin-log日志只保留 mysql-bin.000001 文件

mysqlbinlog

查看bin-log日志

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001

show binlog events 查看binlog记录事件

mysqlbinlog mysql -uroot -psmudge smudge_database

#重放单个日志
/usr/local/mysql/bin/mysqlbinlog  
/usr/local/mysql/var/mysql-bin.000002 | mysql -uroot -psmudge smudge

# 重放指定位置 指定日期的日志断
/usr/local/mysql/bin/mysqlbinlog

--start-position=106 --stop-position=134

--start-date="2016-05-13 17:30:05" --stop-date="2016-05-13 17:41:28" 

/usr/local/mysql/var/mysql-bin.000002 | mysql -uroot -psmudge smudge

重放bin-log日志恢复数据其实就是再把日志中的sql语句执行一边而已
(注意：select 语句和delete语句不可以放在一起重放因为你最后还是得不到数据)
恢复原理就是：执行之前的insert语句或者之前的update语句
如果你的单纯的delete物理删除别想恢复了因为再次执行的还是delete语句

create 创建用户 + grant用户授权

主服务器查看用户密码

mysql> select host,user,password from mysql.user;

添加主服务器用户密码

CREATE USER "kang"@"192.168.206.132" IDENTIFIED BY "smudge";

创建用户kang 可以在ip为192.168.206.132主机上访问数据库

给用户kang授权所有的库的权限
GRANT ALL ON *.* TO "kang"@"192.168.206.132";

从服务器ip : 192.168.206.132
从服务器登录主服务器

[root@localhost ~]#mysql -h192.168.206.128 -ukang -psmudge

可见服务器中的mysql服务可以当作客户端可以连接任何一台mysql服务器进行操作

主服务器配置

主服务器ip ： 192.168.206.128

配置主服务器my.cnf 文件

vim /etc/my.cnf

配置之后刷新binlog文件

flush logs with read lock 确保获得一致性快照

等待主从binlog日志同步完毕达到数据一致

或者使用mysqldump备份sql 文件

将主服务器一致都是sql文件备份传递到从服务器

mysqldump -uroot -psmudge smudge -l -F > "/home/smudge.sql"

-l 是指锁表防止新数据插入

-F 是刷新生成一个新的binlog日志

(如果你数据库中有merge表容易会提示Unable to open underlying table which is differently defined or ofnon-MyISAM type ordoesn"t exist when using LOCK TABLES )

使用scp隧道传输命令传递文件

scp /home/smudge.sql 192.168.206.132:/home

从服务器配置

恢复一部分主服务器备份的数据

新建smudge库

mysql导入sql文件
[root@localhost ~]# mysql -uroot -psmudge smudge < /home/smudge.sql

配置从服务器my.cnf文件

vim /etc/my.cnf

其中用户名和密码就是上述我们在主服务器添加的信息

(如果你的mysql版本5.1(mysql>status查看)之前的，配置这4项，启动之后就不必使用change master 命令进行主动同步)

保存并重启mysql

查看主服务器master binlog 文件

启动slave进程,开启主从同步

因为我的mysql版本是5.7的, 所以我使用change master 命令
change master to
master_host="192.168.206.128",master_user="kang",master_password="smudge",
(master_log_file="mysql-bin.000005",master_log_pos=759); (初始化配置可选)
start slave 启动同步服务

show slave status 查看从服务器状态

表明同步功能已经开启

从服务器常用命令

start slave 启动复制线程
stop slave 停止复制线程
show master logs 查看主数据库日志
change master to master_host ,master_user 动态切换主数据库
show processlist 查看运行进程 (主动服务器都适用)

常见错误排错

show slave status 检查主动状态

数值为NO

数值为NULL

表明同步出现了故障可能是slave服务器执行了写操作或者从服务器重启有事务回滚操作

解决

从服务器: stop slave 关闭复制线程

主服务器：show master status 查看最新二进制文件和位置偏移量

从服务器执行：change master to master_host ...
master_log_file="mysql-bin.000005",master_log_pos=759 命令

idc机房托管云服务器 mysql主从复制原理 mysql 主从复制延迟 mysql 主从复制的种类 mysql主从同步

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/46706.html

SegmentFault 技术周刊 Vol.42 - MySQL：从删库到跑路

摘要：肖鹏微博数据库那些事儿肖鹏，微博研发中心技术经理，主要负责微博数据库相关的业务保障性能优化架构设计，以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造，包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说，没有误删过库的人生是...

王陆宽 2019-08-06 14:09 评论0 收藏0
SegmentFault 技术周刊 Vol.42 - MySQL：从删库到跑路

摘要：肖鹏微博数据库那些事儿肖鹏，微博研发中心技术经理，主要负责微博数据库相关的业务保障性能优化架构设计，以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造，包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说，没有误删过库的人生是...

YJNldm 2019-07-31 17:44 评论0 收藏0
SegmentFault 技术周刊 Vol.42 - MySQL：从删库到跑路

摘要：肖鹏微博数据库那些事儿肖鹏，微博研发中心技术经理，主要负责微博数据库相关的业务保障性能优化架构设计，以及周边的自动化系统建设。经历了微博数据库各个阶段的架构改造，包括服务保障及体系建设微博多机房部署微博平台化改造等项目。 showImg(https://segmentfault.com/img/bV24Gs?w=900&h=385); 对于手握数据库的开发人员来说，没有误删过库的人生是...

aboutU 2019-06-25 18:34 评论0 收藏0
Mysql主从同步原理

摘要：主从同步原理在服务器上执行命令开启主从复制开关，开始进行主从复制。架构的优化当用户完全掌握了如何基于搭建主从集群后，可以自己尝试，专门创建一个节点，作为数据同步节点使用。其他节点从数据同步节点来同步数据。 Mysql主从同步原理 showImg(/img/bVx9pb?w=506&h=339); 1）在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。...

macg0406 2019-08-27 14:26 评论0 收藏0
MySQL数据库运维之主从复制搭建

摘要：上篇文章详细介绍了数据库的单机多实例搭建，本篇文章将在上篇文章的基础上介绍主从复制过程，其中常见的复制架构有异步复制，半同步复制及同步复制。上篇文章详细介绍了MySQL数据库的单机多实例搭建，本篇文章将在上篇文章的基础上介绍MySQL主从复制过程，其中常见的复制架构有：异步复制，半同步复制及同步复制。常用的复制架构有异步复制及半同步复制！一、常见的复制架构 1、主主复制（1）结构图...

ad6623 2019-07-31 18:08 评论0 收藏0