day 60 MySQL主从配置

17.1 MySQL主从介绍

1、MySQL主从原理

• MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的
• MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
• 主从过程大致有3个步骤

1）主将更改操作记录到binlog里

2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

3）从根据relaylog里面的sql语句按顺序执行

• 主上有一个log dump线程，用来和从的I/O线程传递binlog
• 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

2、主从应用场景

• 数据备份

在主上进行读写操作，在从上仅仅进行数据备份，若主机器宕机或损坏，则可以启用从机器，给客户端提供服务 

•  备份

不仅仅在从机器上备份，客户端也要在从机器上读数据，若主机器压力比较大，则在从机器上读取数据，但不能在从机器上写，因为主从机器有方向性，只能往主机器上写

17.2 配置主

• 修改my.cnf，增加server-id=130和log_bin=aminglinux1
• 修改完配置文件后，启动或者重启mysqld服务：/etc/init.d/mysqld restart
• 把mysql库备份并恢复成test-db库，作为测试数据

1). mysqldump -uroot mysql > /tmp/mysql.sql #备份mysql库

2). mysql -uroot -e “create database test-db” #创建新库test-db

3). mysql -uroot test-db < /tmp/mysql.sql #将已经被分的mysql库恢复到test-db库

• 创建用作同步数据的用户

从机器要从从机器上同步日志，需要一个特定用户进行认证，该用户具有哪些权限、用户密码等均需要创建

1). grant replication slave on *.* to 'repl'@slave_ip identified by 'password'; #创建用户repl，授予权限“replication slave”，

2). flush tables with read lock; #锁上表，保持目前状态暂停写入数据

3). show master status; #记住file和Position，在配置从时会用到

17.3 配置从

1、配置从机器

• 从机器上安装mysql
• 编辑my.cnf，添加server-id=132，要求和主不一样，从机器上log_bin不用配置，只有主才需要二进制文件
• 修改完配置文件后，启动或者重启mysqld服务
• 把主机器上的test-db库同步到从机器上. 把主机器上的所有库同步到从机器上: scp 主机器IP:/tmp/*.sql /tmp/
• 可以先在从机器上创建test-db库：create database test-db，然后把主机器上已经备份好的/tmp/test-db.sql拷贝到从机器上，然后导入test-db库: mysql -uroot test-db < /tmp/test-db.sql
• mysql -uroot #alias 'mysql=/usr/local/mysql/bin/mysql'
• stop slave；
• change master to master_host='主机器的IP', master_user='repl', master_password='xxx', master_log_file='xxx', master_log_pos=xxx, #其中 “master_log_file”和“master_log_pos”要填入上一节中命令”show master status;”显示的“file“和”Position“项的结果
• start slave;
• show slave status\G #执行命令查看主从是否配置成功若“Slave_IO_Running”和“Slave_SQL_Running”均为“Yes”，则表示配置成功，其中有一个是“No”，则表示主从已经断开
• 还要到主上执行命令： unlock tables，恢复写操作

2、查看主从同步是否正常

1).在从上执行mysql -uroot

2). show slave stauts\G #查看从的状态，主要关注如下两个参数

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

3). 还需关注如下几个参数

• Seconds_Behind_Master: 0      #为主从延迟的时间
• Last_IO_Errno: 0
• Last_IO_Error:
• Last_SQL_Errno: 0
• Last_SQL_Error:

17.4 测试主从同步

1、配置文件my.cnf里定义的几个参数

（1）主服务器上

• binlog-do-db=                 #仅同步指定的库，有多个库则用逗号分隔
• binlog-ignore-db=           #忽略指定库

（2）从服务器上

• replicate_do_db=                #从机器上同步指定的库
• replicate_ignore_db=         #从机器上忽略指定的库
• replicate_do_table=           #从机器上同步指定的表
• replicate_ignore_table=     #从机器上忽略指定的表，尽量避免使用
• replicate_wild_do_table=   #如aming.%, 支持通配符%，推荐使用
• replicate_wild_ignore_table=

2、测试主从

(1).主上执行如下操作：

• mysql -uroot test-db 
• select count(*) from db;
• truncate table db;                  #清空表的内容

(2).从上执行如下操作：

• mysql -uroot test-db
• select count(*) from db;

(3).主上删掉一个表wp_users：drop table wp_users; 之后用命令查看：select * from wp_users；

(4).从上查看db表：select * from wp_users；

(5).主上也可删掉一个库：drop database db；从上查看(show slave status;)主从就断了，需要重做主从，在从上重新进行change master的配置(Position已变)