Mysql主从复制原理
整体上来说,复制有3个步骤:
(1)master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
(2)slave将master的binary log events拷贝到它的中继日志(relay log);
(3)slave重做中继日志中的事件,将改变反映到它自己的数据库。
Mysql传统复制与GTID复制
MySQL传统复制是基于MySQL二进制文件(mysql-bin.000001),加上对应日志文件中每个事件的偏移量位置点(postion)。 GTID事物是全局唯一性的,且一个事务对应一个GTID 一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致。 GTID用来代替classic的复制方法,不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。
GTID比传统复制的优势与限制
优势 1、更简单的实现failover,不用以前那样在需要找log_file和log_Pos。 2、更简单的搭建主从复制。 3、复制集群有一个统一的方式识别复制位置,给集群管理带来了便利。 4、正常情况下,GTID是连续没有空洞的,因此主从库出现数据冲突时,可以用添加空事物的方式进行跳过。 GTID的限制 1、在一个事务里面混合使用引擎如Innodb(支持事务)、MyISAM(不支持事务), 造成多个GTIDs和同一个事务相关联出错 2、CREATE TABLE…..SELECT 不能使用,该语句产生的两个event在某一情况 会使用同一个GTID(同一个GTID在slave只能被使用一次) 3、CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 不能在事务内使用 (启用了--enforce-gtid-consistency参数)。
Mysql二进制日志格式
Mysql binlog日志有三种格式,分别为ROW、Statement以及MiXED。 Row Level Binary Log会记录成【每一行数据被修改的形式】,然后在Slave端再对相同的数据进行修改。 如果修改了表的结构,那么binlog日志记录的是重新创建表,在插入字段、update等操作语句,而不是的alter的动作。 优点:在Row Level模式下,Binnary Log可以不记录执行的Query语句的上下文相关信息,只要记录哪一行修改了,修改成什么样子。Row Level会详细的记录下每一行数据的修改细节,而且不会出现某个特定情况下的存储过程,或Function,以及Trigger的调用和触发无法被正确复制问题。 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。
Statment Level Statment Level【每一条会修改的SQL语句】都会记录到Master的Binnary中。Slave端在复制的时候,SQL线程会解析成和原来Master端执行过相同的SQL语句,并再次执行。 优点:首先,解决了Row Level下的缺点,不须要记录每一行的数据变化,减少了Binnary Log日志量,节约了IO成本,提高了性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。) 缺点:由于它是记录的执行语句,为了让这些语句在Slave端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息,即上下文信息,以保证所有语句在Slave端被执行的时候能够得到和在Master端执行时相同的结果。另外,由于MySQL发展比较快,很多新功能不断加入,使得MySQL复制遇到了不小的挑战,复制时设计的内容越复杂,越容易出bug。在Statement Level下,目前已发现不少的情况下会造成MySQL的复制问题。主要是在修改数据使用了某些特定的函数货功能后,出现,比如:Sleep()函数在有些版本中就不能正确的复制,在存储过程中使用了last_insert_id()函数,可能会使Slave和Master的到不一致的ID,等等。 使用以下函数的语句也无法被复制: * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (除非启动时启用了 --sysdate-is-now 选项) 同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁
Mixed Level 在Mixed模式下, 是以上两种level的混合使用。 一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。除了MySQL认为通过Statement方式可能造成复制过程中Master和Slave之间产生不一致数据。(如特殊Procedure和Funtion的使用,UUID()函数的使用等特殊情况)时,它会选择ROW的模式来记录变更之外,都会使用Statement方式。
Mysql异步复制
传统mysql主从复制,是在主节点执行和提交事务,然后把他们异步的发送到从节点,行复制的重新执行主节点的SQL语句,这是一个 shared-nothing 的系统,默认情况下所有 server 成员都有一个完整的数据副本
Mysql半同步复制
半同步复制,它在协议中添加了一个同步步骤。 这意味着主节点在提交时需要等待从节点确认它已经接收到事务。只有这样,主节点才能继续提交操作。
数据库主从架构
工厂主数据库复制→工厂从数据库: ①异步GTID复制+ROW ②半同步GTID复制+statement 工厂从数据库复制→总部从数据库 ①异步GTID复制+ROW ②异步GTID复制+statement ③异步传统复制+statement
数据库主从同步异常监控
① nagios,zabbix等IT运维监听软件的监听。
② 通过java定时器按固定频率监听主从数据库的线程状态,当出现异常情况,自动触发邮件通知数据库管理员进行及时处理。 binlog_row_image=minimal
主数据的配置文件
server-id=1
log-bin=mes-bin-1
binlog-do-db=jfinal_demo
binlog-ignore-db=mysql
binlog_cache_size=10M
binlog_format=mixed
max_binlog_size=100M
expire_logs_days=7
sync_binlog=1
gtid_mode=on
enforce-gtid-consistency=true
从数据的配置文件
server-id=2
log-bin=mes-bin-1000
binlog-do-db=jfinal_demo222
binlog-ignore-db=mysql
binlog_cache_size=10M
max_binlog_size=100M
expire_logs_days=7
log-slave-updates=ON
master-info-repository=TABLE
relay-log-info-repository=TABLE
max_relay_log_size=100M
relay_log=mes-relay-2222
relay_log_recovery=ON
skip-slave-start=1
gtid-mode=ON
enforce-gtid-consistency=true
binlog-format=mixed
sync-binlog=1
replicate-rewrite-db="jfinal_demo->jfinal_demo222"
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
总部从数据的配置文件
server-id=4999
sync_binlog=1
gtid_mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay_log_recovery=ON
max_relay_log_size=100M
relay_log=mes-relay-40023
skip-slave-start=1
修改存储引擎
show table status from db_pldb where Engine != 'InnoDB'
修改数据库名
show table status from db_test
rename table olddatabase.table to newdatabase.table
innodb-read-only=1
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;
跳过事务
stop slave for channel 'db_test_backup'
Set @@SESSION.GTID_NEXT='de86174c-99c7-11e7-ac62-801844e7a18d:63785069'
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
start slave for channel 'db_test_backup';
change master to master_host='127.0.0.1',master_port=3306,
master_user='repl',
master_password='test',
master_log_file='test-bin-23.000091',
master_log_pos=194;