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

---

概念

主从复制: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 命令