第二阶段-linux必会的20多种服务2-16-mysql主从

1、 一： MYSQL REPLICATION 什么是 MYSQL REPLICATION Replication 可以实现将数据从一台数据库服务器（master）复制到一或多台数据库服务器（slave） 默认情况下属于异步复制，无需维持长连接 通过配置，可以复制所有的库或者几个库，甚至库中的一些表 是 MySQL 内建的，本身自带的简单的说就是master 将数据库的改变写入二进制日志，slave 同步这些二进制日志，并根据这些二进制日志进行数据操作DML:SQL 操作语句，update,ert，deleteRelay log :中继日志1、Fail Over故障切换2、Backup Serve

2、r 备份服务，无法对 SQL 语句执行产生的故障恢复，有限的备份3、High Performance 高性能，可以多台 slave,实现读写分离整体上来说，复制有 3 个步骤：(1)master 将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志，binary logevents）；REPLICATION 如何工作REPLICATION 的作用REPLICATION 的原理(2)slave 将master 的 binary log events 拷贝到它的中继日志(relay log)；(3)slave 重做中继日志中的，修改salve 上的数据。mysql 主从复制中：

3、第一步：master 记录二进制日志。在每个事务更新数据完成之前，master 在二进制日志记录这些改变。MySQL 将事务写入二进制日志，即使事务中的语句都是交叉执行的。在写入二进制日志完成后，master存储引擎提交事务。第二步：slave 将master 的 binary log 拷贝到它自己的中继日志。首先，slave 开始一个工作线程I/O线程。I/O 线程在master 上打开一个普通的连接，然后开始 binlog dump process。Binlog dumpprocess 从master 的二进制日志中读取，如果已经执行完master 产生的所有文件，它会睡眠并等待maste

4、r 产生新的。I/O 线程将这些写入中继日志。第三步：SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取，并重新执行其中的而更新slave 的数据，使其与master 中的数据一致。1 、 ONE MASTER AND MUTI SALVE一主多备一般用来做读写分离的，master 写，其他 slave 读，这种架构最大问题I/O 压力集中在 Master 上2、 M-S-S使用一台slave 作为中继，分担 Master 的压力，slave 中继需要开启 bin-log,并配置log-slave-updatesREPLICATION 常见方案：

5、 Slave 中继可使用Black-hole 存储引擎，不会把数据存储到磁盘，只记录二进制日志3、 M-M双主互备 (互为主从)很多人误以为这样可以做到 MySQL 负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性4、 M-M-M监控三台机器互相做对方的master天生的缺陷：复制延迟，slave 上同步要慢于master,如果大并发的情况那延迟更严重Mysql 在 5.6 已经自身可以实现 fail over 故障切换5 、 ONE SLAVE MUTI MASTER一从对多主好处：节省成本，将多个master 数据自动化整合缺陷：对库和表数据的修改

6、较多 二：部署 MYSQL 主从同步模式：C/S端口：3306 配置主数据库服务器 XUEGOD63创建需要同步的数据库：mysql create database HA;mysql use HA;mysql create table T1(id int,name varchar(20);service mysqld stop配置f:vim /etc/flog-bin=mysql-bin-master #启用二进制日志环境准备：主机名IP系统/MySQL 版本角色xuegod633CentOS7.4/5.7.20Masterxuegod6410.10.1

7、0.64CentOS7.4/5.7.20slaveserver-id=1#本机数据库 ID 标示binlog-do-db=HA #可以被从服务器复制的库, 二进制需要同步的数据库名binlog-ignore-db=mysql #不可以被从服务器复制的库重启mysql ( 如果重启卡死现象，kill 掉再启动)Systemctl restart mysqld授权：mysql grant replication slave on *.* to slave4 identified by 123456;查看状态信息：mysql show master status;+-+-+-+-

8、+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin-master.000001 |259 | HA| mysql|+-+-+-+-+查看二进制日志：ls /usr/local/mysql/data/mysql show binlog eventsG复制前要保证同步的数据库一致mysqldump -uroot -p123456 HA HA.sql#可以导出数据库将导出的数据库传给从服务器方 法 ：scp HA.sql 4:/root两台数据库服务器mysql 版本要一致mysql

9、show variables like %version%;测试连接到主服务器是否成功mysql -uslave -p123456 -h 3配置从数据库服务器 XUEGOD64只有复制的权限, 是看不到其他库的。正常导入数据库，和主数据库服务器保持一致mysql create database HA;mysql -uroot -p123456 HAstop slave;#停止slavemysql change master tomaster_host=3,master_user=slave,master_password=123456;mysql sta

10、rt slave;#启动slavemaster-host=3 #指定主服务器 IP 地址master-user=slave #指定定在主服务器上可以进行同步的用户名master-password=123456 #master-port=3306master-connect-retry=60 #断点重新连接时间mysql show slave statusG 查看状态Slave_IO_Running ：一个负责与主机的 io 通信Slave_SQL_Running：负责自己的 slave mysql 进程两个为 YES 就成功了！再到主服务器上查看状态：mysql show

11、 processlist Gmysqlert into T1 values (1,man);从数据库上查看：插入数据测试同步： 如果遇到主从不同步，看一下主从 bin-log 的位置，然后再同步。在主服务器上看二进制日志列表mysql show binlog events G从服务器执行 MySQL 命令下：mysql stop slave；#先停止slave 服务mysql change master tomaster_log_file=mysql-bin-master.000001,master_log_pos=1164;#根据上面主服务器的show master status 的结果，进

12、行从服务器的二进制数据库记录回归，达到同步的效果mysqlslave start;#启动从服务器同步服务mysql show slave statusG;#用show slave statusG;看一下从服务器的同步情况Slave_IO_Running: YesSlave_SQL_Running: Yes如果都是 yes，那代表已经在同步重启从服务器，再查看状态：停止从服务器slave stop;开启从服务器slave start;排错思路：1、二进制日志没有开启2、IPTABLES 没有放开端口排错： 3、对应的主机 IP 地址写错了SQL 线程出错1、主从服务器数据库结构不统一出错后，数据

13、少，可以手动解决创建插入，再更新slave 状态。注：如果主上误删除了。那么从上也就误删除了。 #因此主上要定期做mysqldump 备份。 三：部署 MYSQL 主主双向主从复制M-M(恢复快照再实验) 通过mysql 主主：进行mysql 双向同步数据库 HA 的配置mysql 主：服务端：XUEGOD63IP：3mysql 主：服务端：XUEGOD64IP：4如果没有恢复快照要先清空一下二进制日志，mysql reset master在上面主从的基础上进行配置它有两种身份：身份 1： XUEGOD64 的主。 身份 2： XUEGOD64 的从。v

14、im /etc/fserver-id = 1log-bin=mysql-bin-masterbinlog-do-db=HAbinlog-ignore-db=mysql #避免同步mysql 用户 相关配置service mysqld restart授权：配置 XUEGOD63mysql grant replication slave on *.* to slave10.10.10.% identified by 123456;mysql set global validate_password_length=1; #定义长度 默认是 8关闭强度审计，在f 添加validat

15、e-password=offgrant replication slave on *.* to slave10.10.10.% identified by 123456;mysql flush privileges;#记得刷新授权表vim /etc/fserver-id = 2log-bin=mysql-blavebinlog-do-db=HAbinlog-ignore-db=mysql先测试一下从帐号登陆是否正常？rootxuegod64 # mysql -uslave -p123456 -h 3然后作主授权给从xuegod63,5.1 的配置master-ho

16、st=4 #指定主服务器 IP 地址master-user=slave64 #指定定在主服务器上可以进行同步的用户名master-password=123456#master-port=3306 可以不写replicate-do-db=HA#要复制的数据库master-connect-retry=60 #断点重新连接时间配置 XUEGOD64如果不符合要求修改如下（5.7 版本）mysql set global validate_password_policy=0; #定义复杂度rootxuegod64 # grant replication slave on *.* t

17、o slave3 identified by123456;指定哪台是xuegod64 的masterrootXUEGOD64 # mysql -uroot -p123456mysql stop slave;mysql change master to master_host=3master_user=slave,master_password=123456;mysql start slave;mysql show slave statusG再回来配置 XUEGOD63先测试一下从帐号登陆是否正常？rootxuegod63# mysql -uslave -

18、p123456 -h 4#可以登陆指定哪台是xuegod63 的masterrootXUEGOD63 # mysql -uroot -p123456mysql change master tomaster_host=4,master_user=slave,master_password=123456;mysql start slave;mysql show slave statusG检查： XUEGOD63 上查看slave 状态XUEGOD64 上查看slave 状态插入数据测试：在 XUEGOD63 上插入数据，XUEGOD64 上查看mysqler

19、t into T1 values(2,天清);在 XUEGOD64 上插入数据，XUEGOD63 上查看mysqlert into T1 values(3,);注意：这种M-M 架构没有什么好处，他往往给我们产生一种实现了负载均衡的错觉 六： 部署 M-S-S 模型环境：XUEGOD68mastermysql5.7.208XUEGOD69slave 中继mysql5. 7.209XUEGOD70slavemysql5. 7.200在主服务上授权用户：mysql grant replication slave on *.* to rep

20、l10.10.10.% identified by 123456;mysql flush privileges;rootxuegod68 # vim /etc/f#修改配置 增加以下选项server-id=1binlog-do-db=HAlog-bin=mysql-bin-masterbinlog-ignore-db=mysqlsync-binlog=1binlog-format=row部署 MASTERXUEGOD68:重启服务rootxuegod68 # systemctl restart mysqld导出主服务器 HA 库完整备份， 拷贝到 中继服务器 和slave 服务器ro

21、otxuegod68 # mysqldump -uroot -p123456 -B HAha.sqlrootxuegod68 # scp ha.sql 9:/rootrootxuegod68 # scp ha.sql 0:/root导入数据库ha.sqlrootxuegod69 # mysql uroot p123456 stop slave;mysql change master tomaster_host=8,master_user=repl,master_password=123456;mysql start slave;查看中

22、继服务的状态mysql show slave status G部署 SLAVE 中继- XUEGOD69再授权一个用户给slave(xuegod70):mysql grant replication slave on *.* to repl0 identified by 123456;mysql flush privileges;导入数据库ha.sqlrootxuegod70 # mysql uroot p123456 stop slave;mysql change master tomaster_host=9,master_user=repl,mast

23、er_password=123456;部署 SLAVEXUEGOD70mysql start slave;查看从服务的状态mysql show slave status Gmysqlert into test values (1,AA);Query OK, 1 row affected (0.09 sec)然后分别在slave 中继，与slave 上查看为什么中继服务器也查到数据呢？ 因为是存储引擎的问题。修改表的引擎：mysql alter table t1 ENGINE=blackhole; (先关闭日志记录再修改set sql_log_bin=off)你再测试！从 MASTER 上插入数

24、据测试： 排错：此方法可能失效，自行验证mysql show slave statusG由结果可以看到：Read_Master_Log_Pos: 288Exec_Master_Log_Pos: 107Last_SQL_Errno: 1146Last_SQL_Error: Error executing row event: Table HA.student doesnt exist因为只对HA 记录了binlog,当在mydb 库操作其它数据库的表，但该表在slave 上又不存在时就出错了。到master 上查看记录mysql show binlog events in mysql-bin-m

25、aster.000002 from 107G错误 1：由上面的结果可知，我们需要跳过两个事务 173，288然后到salve 中继操作：mysql slave stop;mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;跳过一个事务mysql slave start;这个搞定或者：它提供了一个session 粒度的选项，通过关闭这个选项可以不让主库将打开这个选项或关闭连接前的 SQL语句写入 binlog。set sql_log_bin=off;mysqlalter table aaa add column xxx int default 1 after yyy;由于历史遗留问题，MySQL 主从库的表结构不一致，主库的某个表 tableA 比从库表 tableA 少了一个字段当尝试在主库上更改表结构时，这行alter 语句会随着binlog 同步到从库，如果从库执行这行语句时出错，主从同步线程就会自动停