数据库技术：数据库学习之十二--mysql主从复制

作者:cuiyongchao007更新于： 2020-05-14 11:37:09

　　数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，它产生于距今六十多年前，随着信息技术和市场的发展，特别是二十世纪九十年代以后，数据管理不再仅仅是存储和管理数据，而转变成用户所需要的各种数据管理的方式。数据库有很多种类型，从最简单的存储有各种数据的表格到能够进行海量数据存储的大型数据库系统都在各个方面得到了广泛的应用。

　　十二、MySQL主从复制

　　1、主从复制原理

　　1.主从复制的前提：

　　1.1两台mysql实例（多台物理机，或者多实例）

　　1.2主库要开启二进制日志

　　1.3主库要提供复制相关用户，replicationslave,一个比较特殊的权限。

　　grantreplicationslaveon*.*torepl@'10.0.0.%'identifiedby'123';

　　1.4从库需要将和主库相差的数据，进行追加

　　一般情况下可以人为备份主库数据，恢复到从库上。

　　1.5应该从恢复之后的时间点，开始自动从主库获取二进制日志开始应用

　　需要人为告诉从库，从哪里开始自动开始复制二进制日志(file+position)，另外还需要告诉从库user,password,ip,port

　　2.复制中的线程及文件

　　2.1主库

　　dump（IO）thread:在复制过程中，主库发送二进制日志的线程。

　　2.2从库

　　IOthread:向主库请求二进制日志，并且接受二进制日志的线程。

　　SQLthread:执行请求过来的二进制的线程

　　2.3在主库的文件

　　binlog文件，主库的二进制日志

　　2.4从库文件

　　relaylog：中继日志，存储请求过来的二进制日志。

　　master.info:

　　​1.从库连接主库的重要参数（user，password，ip，port）

　　​2.上次获取过的主库二进制日志的位置

　　relay-log.info

　　​存储从库SQL线程已经执行过的relaylog日志位置。

　　3.主从复制的工作原理

　　3.1从库，IO线程，读取master.info中的信息，获取到连接参数（user，password，ip，port），和上次用过的主库的binlog的位置（mysqlbin-0000,position）。

　　3.2IO线程使用连接到主库，拿着上次从主库获取到的binlog的位置，问主库有没有比这个更新的二进制日志。

　　3.3主库查询二进制日志，并对比从库发送过来的位置信息，如果有新的二进制日子，就通过dumpthread发送给我从库。

　　3.4从库通过IO线程，接受主库发来的二进制日志，存储到TCP/IP缓存中，并且返回ACK确认给主库，这时主库认为复制完成了，可以继续其他工作了。

　　3.5从库更新master.info，二进制日志的为新的位置信息。

　　3.6从库IO线程会将TCP/IP缓存中的日志，存储到relay-log中继日志文件中。

　　3.7从库SQL线程，读取relay-log.info，获取到上次执行到的relay-log日志位置，以这个位置为起点，往后继续执行中继日志。

　　3.8SQL线程执行完成所有relay之后，会更新relay-log.info信息为新位置信息。

　　到此位置，一次完成的复制过程完成。

　　4、搭建主从复制

　　1、准备环境

　　思路：

　　1、两个以上节点（多实例）

　　3307：master

　　3308：slave1

　　3309：slave2

　　2、主库binlog开启，从库开启relay-log（默认在数据目录下生成）

　　vim/data/3307/my.cnf

　　log-bin=/data/3307/mysql-bin

　　binlog_format=row

　　3、server-id不同

　　[root@db02data]#cat/data/3307/my.cnf|grepserver-id

　　server-id=3307

　　[root@db02data]#cat/data/3308/my.cnf|grepserver-id

　　server-id=3308

　　[root@db02data]#cat/data/3309/my.cnf|grepserver-id

　　server-id=3309

　　4、关闭数据库的自动域名解析

　　每个节点都加入以下配置：

　　skip-name-resolve

　　5、启动多实例

　　mysqld_safe--defaults-file=/data/3307/my.cnf&

　　mysqld_safe--defaults-file=/data/3308/my.cnf&

　　mysqld_safe--defaults-file=/data/3309/my.cnf&

　　6、主库创建复制账户

　　连接到主库：

　　mysql-S/data/3307/mysql.sock

　　grantreplicationslaveon*.*torepl@'10.0.0.%'identifiedby'123';

　　7、从库数据的追加

　　 （1）不需要追加的情况

　　 主和从同时搭建的新环境，就不需要备份主库数据，恢复到从库了，直接从第一个binlog（mysql-bin.000001）的开头位置（120）。

　　 （2）如果主库已经工作了很长时间了，我们一般需要备份主库数据，恢复到从库，然后从库从备份的时间点起自动进行复制。

　　重点针对第二种情况进行演示：

　　备份主库：

　　mysqldump-S/data/3307/mysql.sock-A-R--triggers--master-data=2--single-transaction>/tmp/full.sql

　　sed-n'22p'/tmp/full.sql

　　--CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=325;

　　恢复到从库：

　　mysql-S/data/3308/mysql.sock

　　mysql>setsql_log_bin=0;

　　mysql>source/tmp/full.sql

　　8、从库开启主库：

　　mysql-S/data/3308/mysql.sock

　　helpchangemasterto

　　CHANGEMASTERTO

　　MASTER_HOST='10.0.0.203',

　　MASTER_USER='repl',

　　MASTER_PASSWORD='123',

　　MASTER_PORT=3307,

　　MASTER_LOG_FILE='mysql-bin.000003',

　　MASTER_LOG_POS=325;

　　开启主从（开启IO和SQL线程）：

　　startslave;

　　9、查看主从状态：

　　showslavestatus\\G

　　Slave_IO_Running:Yes

　　Slave_SQL_Running:Yes

　　10、主从重要状态信息介绍

　　showslavestatus\\G

　　Slave_IO_Running:Yes

　　Slave_SQL_Running:Yes

　　Last_IO_Errno:0

　　Last_IO_Error:

　　Last_SQL_Errno:0

　　Last_SQL_Error:

　　IO线程故障：

　　1、主库连接不上

　　user、password、port、ip错误

　　 解决方案：

　　 stopslave;

　　 resetslaveall;

　　 changemasterto

　　 startslave;

　　防火墙

　　网络不通

　　skip-name-resolve

　　 stopslave;

　　 startslave;

　　2、主库二进制日志丢失或损坏

　　 解决方案：

　　 stopslave;

　　 resetslaveall;

　　 重新备份恢复

　　 changemasterto

　　 startslave;

　　5、SQL线程故障

　　SQL线程故障：

　　执行relaylog日志新事件

　　1、删除、修改对象的操作时，没有这个对象

　　2、创建对象时，对象已存在

　　3、主键冲突

　　从库做写入操作，会导致以上问题出现

　　处理方法：

　　stopslave;

　　setglobalsql_slave_skip_counter=1;

　　startslave;

　　/etc/my.cnf

　　slave-skip-errors=1032,1062,1007

　　但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。

　　怎么预防以上问题？

　　从库加入配置文件

　　setglobalread_only=1;

　　vim/etc/my.cnf

　　read_only=1---->只能控制普通用户

　　6、主从异常--主从延时过长

　　showslavestatus\\G

　　Seconds_Behind_Master:0

　　默认的主从复制机制是异步的一个过程。

　　主库原因:

　　1、主库做修改操作之后，才会记录二进制日志。

　　sync_binlog=0/1

　　Ifthevalueofthisvariableisgreaterthan0,

　　theMySQLserversynchronizesitsbinarylogtodisk(usingfdatasync())

　　aftersync_binlogcommitgroupsarewrittentothebinarylog.

　　Thedefaultvalueofsync_binlogis0,whichdoesnosynchronizingtodisk—inthiscase,

　　theserverreliesontheoperatingsystemtoflushthebinarylog'scontentsfromtimetotimeasforanyotherfile.

　　Avalueof1isthesafestchoicebecauseintheeventofacrashyouloseatmostonecommitgroupfromthebinarylog.

　　However,itisalsotheslowestchoice(unlessthediskhasabattery-backedcache,whichmakessynchronizationveryfast)

　　---------------------

　　1：表示：每次事务commit，刷新binlog到磁盘

　　0：系统决定binlog什时候刷新到磁盘

　　2、主库的压力特别大（大事务、多事务）

　　3、从库数量多，导致dump线程繁忙

　　-------------------

　　从库原因：

　　1、relay-log写入慢

　　2、SQL线程慢（主从硬件差异比较大）

　　-----------------------------

　　尽可能的避免主从延时

　　1、sync_binlog=1

　　2、大事务拆成小事务，多事务进行分离

　　3、使用多级主从，分库分表架构

　　4、将binlog放到ssd或者flash上，高性能存储

　　5、将relay放到ssd或者flash上

　　6、尽量选择和主库一致硬件和配置

　　7、主从复制高级功能--半同步复制

　　出发点：保证主从数据一致性的问题，安全的考虑

　　5.5出现的概念，但是不建议使用，性能太差

　　5.6以后出现groupcommit组提交功能，来提升开启版同步复制的性能

　　5.7增强半同步复制的新特性：aftersync;

　　------

　　加载插件

　　主:

　　INSTALLPLUGINrpl_semi_sync_masterSONAME'semisync_master.so';

　　从:

　　INSTALLPLUGINrpl_semi_sync_slaveSONAME'semisync_slave.so';

　　查看是否加载成功:

　　showplugins;

　　启动:

　　主:

　　SETGLOBALrpl_semi_sync_master_enabled=1;

　　从:

　　SETGLOBALrpl_semi_sync_slave_enabled=1;

　　重启从库上的IO线程

　　STOPSLAVEIO_THREAD;

　　STARTSLAVEIO_THREAD;

　　查看是否在运行

　　主:

　　showstatuslike'Rpl_semi_sync_master_status';

　　从:

　　showstatuslike'Rpl_semi_sync_slave_status';

　　-----

　　补充：

　　rpl_semi_sync_master_timeout|10000

　　默认情况先,到达10秒钟还没有ack，主从关系自动切换为普通复制

　　如果是1主多从的半同步复制，只要有一台落地relaylog，返回ack，这次半同步就完成了。

　　8、主从复制高级特性--延时从库

　　会专门找一个节点，配置成延时节点，尽可能防止逻辑损坏，一般情况下这个节点会被用备份

　　我们配置的是SQL_thread的延时

　　mysql>stopslave;

　　mysql>CHANGEMASTERTOMASTER_DELAY=60;

　　mysql>startslave;

　　mysql>showslavestatus\\G

　　SQL_Delay:300

　　取消延时：

　　mysql>stopslave;

　　mysql>CHANGEMASTERTOMASTER_DELAY=0;

　　mysql>startslave;

　　9、主从复制高级功能--复制过滤

　　主库方面控制（不建议使用）：

　　白名单:只记录白名单中列出的库的二进制日志

　　binlog-do-db

　　黑名单：不记录黑名单列出的库的二进制日志

　　binlog-ignore-db

　　从库方面控制：

　　showslavestatus\\G;查看相关参数。

　　白名单：只执行白名单中列出的库或者表的中继日志

　　--replicate-do-db=test

　　--replicate-do-table=test.t1

　　--replicate-wild-do-table=test.x*

　　黑名单：不执行黑名单中列出的库或者表的中继日志

　　--replicate-ignore-db

　　--replicate-ignore-table

　　--replicate-wild-ignore-table

　　只复制world数据库的数据

　　10、主从复制新特性--GTID复制

　　GTID

　　5.6新特性

　　GTID(GlobalTransactionID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。

　　它的官方定义如下：

　　GTID=source_id：transaction_id

　　7E11FA47-31CA-19E1-9E56-C43AA21293967:29

　　每一台mysql实例中，都会有一个唯一的uuid，标识实例的唯一性

　　auto.cnf，存放在数据目录下

　　重要参数：

　　gtid-mode=on

　　enforce-gtid-consistency=true

　　log-slave-updates=1

　　gtid-mode=on --启用gtid类型，否则就是普通的复制架构

　　enforce-gtid-consistency=true --强制GTID的一致性

　　log-slave-updates=1 --slave更新是否记入日志

　　-----------------

　　构建1主2从的GTID复制环境：

　　3台虚拟机，

　　db02克隆两台虚拟机环境，分别命名为db01、db03，在生产中准备3台真实的物理机，不用多实例

　　要求：

　　1、IP地址、主机名

　　 db01:10.0.0.51/24

　　 db03:10.0.0.53/24

　　2、清理所有之前3306的相关数据，只留软件

　　db01:

　　cd/application/mysql/data/

　　\\rm-rf*

　　cd/data/binlog/

　　\\rm-rf*

　　db02:

　　cd/application/mysql/data/

　　\\rm-rf*

　　cd/data/binlog/

　　\\rm-rf*

　　db03:

　　cd/application/mysql/data/

　　\\rm-rf*

　　cd/data/binlog/

　　\\rm-rf*

　　3、准备配置文件

　　规划：

　　 主库：10.0.0.51/24

　　 从库1:10.0.0.52/24

　　 从库2：10.0.0.53/24

　　主库：

　　加入以下配置信息

　　db01:10.0.0.51/24

　　vim/etc/my.cnf

　　[mysqld]

　　basedir=/application/mysql

　　datadir=/application/mysql/data

　　socket=/tmp/mysql.sock

　　log-error=/var/log/mysql.log

　　log_bin=/data/binlog/mysql-bin

　　binlog_format=row

　　skip-name-resolve

　　server-id=51

　　gtid-mode=on

　　enforce-gtid-consistency=true

　　log-slave-updates=1

　　[client]

　　socket=/tmp/mysql.sock

　　slave1：

　　db02:10.0.0.52/24

　　vim/etc/my.cnf

　　[mysqld]

　　basedir=/application/mysql

　　datadir=/application/mysql/data

　　socket=/tmp/mysql.sock

　　log-error=/var/log/mysql.log

　　log_bin=/data/binlog/mysql-bin

　　binlog_format=row

　　skip-name-resolve

　　server-id=52

　　gtid-mode=on

　　enforce-gtid-consistency=true

　　log-slave-updates=1

　　[client]

　　socket=/tmp/mysql.sock

　　slave2：

　　db02:10.0.0.53/24

　　vim/etc/my.cnf

　　[mysqld]

　　basedir=/application/mysql

　　datadir=/application/mysql/data

　　socket=/tmp/mysql.sock

　　log-error=/var/log/mysql.log

　　log_bin=/data/binlog/mysql-bin

　　binlog_format=row

　　skip-name-resolve

　　server-id=53

　　gtid-mode=on

　　enforce-gtid-consistency=true

　　log-slave-updates=1

　　[client]

　　socket=/tmp/mysql.sock

　　-----------------

　　三台节点分别初始化数据：

　　/application/mysql/scripts/mysql_install_db--user=mysql--basedir=/application/mysql--datadir=/application/mysql/data/

　　分别启动三个节点mysql：

　　/etc/init.d/mysqldstart

　　测试启动情况：

　　mysql-e"showvariableslike'server_id'"

　　master:51

　　slave:52,53

　　51:

　　grantreplicationslaveon*.*torepl@'10.0.0.%'identifiedby'123';

　　52\\53:

　　changemastertomaster_host='10.0.0.51',master_user='repl',master_password='123',MASTER_AUTO_POSITION=1;

　　startslave;

　　在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。