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

数据库多节点

多节点数据库的好处：
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