MYSQL教程优化篇:数据库多节点之Mysql主从复制,主主复制

作者 : IT 大叔 本文共20375个字,预计阅读时间需要51分钟 发布时间: 2020-11-10

数据库多节点

多节点数据库的好处:
1.性能更高,通过负载均衡提高服务器的负载
2.安全问题,如果有多节点,一个数据库宕机还有另一个数据库可以访问
或者如果因为宕机导致一个数据库的数据丢失,还可以有其他数据库的备份,这个叫做数据冗余。

主从复制就是数据库多节点的其中一个方法:
它依赖于binlog日志

主从复制的使用场景:
两种:数据库备份 和 读写分离

实现读写分离,从而提高数据库的性能,比如有两台服务器,一台主节点,一台从节点,在主库进行增删改,所以主库要开事务,操作起来比较慢。而从库只进行读取,读是可以不添加事务的,会快很多,我们可以部署多台从库进行读取,只要数据写进了主库,所有的从库都会进行更新。
而且实现了数据冗余,增加了安全性。

主从复制的原理:
假设现在有一台主节点,四台从节点。主库专门负责写,从库只负责读。
主库开事务,从库不开。
当主库插入了一条数据,那么这个插入操作会被记录到主库的日志文件,而从库会读取主库的日志文件里面的操作,并执行一遍,这样从库也会产生这么一条数据。这个日志就是binlog日志

从节点不能太多,否则这些从库都去读取日志文件并同步就会导致同步的延时问题,从节点们间的数据就可能不一致。

如果从节点太多,我们就要在业务上进行改善了。

===================== =================================
binlog日志:

在这里顺便说一下,MySQL有哪些日志:
Error log  错误日志
General query log 普通查询日志
Slow query log  慢日志文件
binary log  二进制日志

binary log 就是个二进制文件,记录着数据库所有DML和DDL操作,他的作用有两个:
1.增量备份
2.主从复制

我们使用mysqldump进行备份的时候,一般是备份整个数据库,但是会产生比较大的数据,如果希望只备份新产生的值,就需要用到二进制日志,读取里面的操作。

mysql默认不开启binlog的。

show variables like "%log_bin%"
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

 

开启binlog

进入my.cnf

vi /etc/my.cnf

在[mysqld]下加入两行(在其他地方加无效):

server-id = 1
log-bin=/var/lib/mysql/mysql-bin  #指定二进制文件的文件名的basename

重启

此时/var/lib/mysql会出现两个文件
mysql-bin.000001    #二进制文件
mysql-bin.index     #二进制索引文件

如何查看二进制日志内容:
mysqlbinlog 二进制文件名路径

或者 先登录客户端,然后

show binglog events in "日志文件名"

 

二进制日志部分内容如下:

# at 59484
#200113  9:14:45 server id 1  end_log_pos 59591         Query   thread_id=461  exec_time=0      error_code=0
use `test`/*!*/;
SET TIMESTAMP=1578878085/*!*/;
create table test1(id int,name varchar(100))
/*!*/;

# at 59591
#200113  9:15:15 server id 1  end_log_pos 59659         Query   thread_id=461  exec_time=0      error_code=0
SET TIMESTAMP=1578878115/*!*/;
BEGIN
/*!*/;

# at 59659
#200113  9:15:15 server id 1  end_log_pos 59756         Query   thread_id=461  exec_time=0      error_code=0
SET TIMESTAMP=1578878115/*!*/;
insert into test1 values (1,"zbp")
/*!*/;

# at 59756
#200113  9:15:15 server id 1  end_log_pos 59783         Xid = 25496
COMMIT/*!*/;

上面从 # at xxx 到 /*!*/;是一个完整的部分,这部分叫做事件,事件包括两部分:
事件头 是该操作的一些信息,如操作在二进制日志的开始位置 "# at 59591",执行时的时间 "SET TIMESTAMP=1578878115",执行所化的时间“exec_time=0” 等

事件体 具体的操作语句 如 “insert into test1 values (1,"zbp")”

所以根据二进制日志,我们可以做一些恢复操作。

show binglog events;  # 只显示mysql-bin.000001的内容,如果想查看其他日志文件内容就要指定日志文件名

*************************** 593. row ***************************
   Log_name: mysql-bin.000001
        Pos: 58929
 Event_type: Query
  Server_id: 1
End_log_pos: 59099
       Info: use `zbpblog`; UPDATE `blogs`  SET `real_click` = `real_click` + 1  WHERE  `create_time` <= 1578876263  AND `id` = '26'
*************************** 594. row ***************************
   Log_name: mysql-bin.000001
        Pos: 59099
 Event_type: Query
  Server_id: 1
End_log_pos: 59171
       Info: COMMIT
*************************** 595. row ***************************
   Log_name: mysql-bin.000001
        Pos: 59171
 Event_type: Query
  Server_id: 1
End_log_pos: 59242
       Info: BEGIN
*************************** 596. row ***************************
   Log_name: mysql-bin.000001
        Pos: 59242
 Event_type: Query
  Server_id: 1
End_log_pos: 59412
       Info: use `zbpblog`; UPDATE `blogs`  SET `real_click` = `real_click` + 1  WHERE  `create_time` <= 1578876861  AND `id` = '32'
*************************** 597. row ***************************
   Log_name: mysql-bin.000001
        Pos: 59412
 Event_type: Query
  Server_id: 1
End_log_pos: 59484
       Info: COMMIT

每次重启服务器,会新创建一个binlog日志(通过flush logs命令),那么这有什么好处呢:
比如昨天有一个binlog文件,今天有一个binlog日志,今天发现昨天有一个误操作,要恢复,就可以针对昨天的日志文件来做恢复。而且这样也不会让一个binlog文件太大。

二进制操作:

flush logs  # 刷新日志文件
show binlog events in "xxx"     #查看日志文件内容
show master status      # 查看当前使用的日志的状态
show master logs        # 查看所有日志文件(相当于查看日志索引文件)
reset master            # 清空所有日志文件(非常危险,不建议)

# 如何使用二进制文件恢复数据(此方法只适用于恢复少量数据)

mysqlbinlog mysql-bin.000001 | mysql -uroot -p

该命令(使用了管道符)会将mysql-bin.000001中所有的MySQL操作都执行一遍;

 

但是我们的误操作为:
delete from user where id = 56;

此时我们要恢复的数据只有一条,不可能为了恢复一条数据而将一整个日志文件都执行一遍。
此时我们就要找出 id为56的这条数据时在啥时候创建的,然后找到相应的binlog日志文件,使用 mysqlbinlog 文件名 去查看创建id为56的数据的起始和结束位置(就是 #at xxx ,结束位置就是下一个事件的起始位置),假如这个位置是 123 166

执行:

mysqlbinlog mysql-bin.000001 --start-position 123 --stop-position 166 | mysql -uroot -p

即可;

还可以使用 --start-datetime=xxx 和 --stop-datetime=xxx 根据时间戳范围去恢复,当然还是要查看二进制文件,看这个事件是什么时间创建的数据。

所以二进制日志文件恢复数据是有局限的:
如果删除的是很久远的数据,要找到这个数据创建或者修改操作实在哪个二进制日志的哪个位置,很麻烦。
如果删除的数据不只是一条,还要找多条数据的创建是在哪几个日志的哪些位置。

所以该方法适用于刚刚发生的或几天内发生的,而且是少量数据的误操作的恢复。
如果是大量数据的误删除,我们只能通过平时备份来恢复。所以平时多做备份才是王道。

PS:二进制日志只记录增删改,不会记录查询语句

关于二进制日志的三种模式(格式):

mysql复制主要有三种方式:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。

对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED。

① STATEMENT模式(SBR)

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)

② ROW模式(RBR)

不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。

③ MIXED模式(MBR)

以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。

SBR 的优点:

历史悠久,技术成熟
binlog文件较小
binlog中包含了所有数据库更改信息,可以据此来审核数据库的安全等情况
binlog可以用于实时的还原,而不仅仅用于复制
主从版本可以不一样,从服务器版本可以比主服务器版本高

SBR 的缺点:

不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UU