数据库多节点

多节点数据库的好处：
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()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
INSERT ... SELECT 会产生比 RBR 更多的行级锁
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响
存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数，这个可以说是坏事也可能是好事
确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源

RBR 的优点：

任何情况都可以被复制，这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：
* INSERT ... SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执行 INSERT，UPDATE，DELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能

RBR 的缺点：

binlog 大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
UDF 产生的大 BLOB 值会导致复制变慢
无法从 binlog 中看到都复制了写什么语句
当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生

另外，针对系统库 mysql 里面的表发生变化时的处理规则如下：
如果是采用 INSERT，UPDATE，DELETE 直接操作表的情况，则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 SBR 模式记录
注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。

所以如果要进行主从复制的话,最好是设定binlog的格式为mixed,可以防止因为binlog格式和mysql版本不一致导致的同步sql失败
只需在配置文件中添加:

binlog_format=mixed     # 默认是statement格式

即可；
 

============================================

MySQL主从复制：

假如有1个主节点 1个从节 点（一个从节点和多个从节点的操作相同）

步骤如下：
1.配置主节点：
创建用户赋予权限
开启binlog日志

2.配置从节点
配置同步主节点日志
指定主节点的ip，端口，用户
启动从节点

 

配置主节点的步骤 

 

创建用户： 

首先我们要创建用户，指定来访者的IP，指定该用户的权限

现在我们指定用户名为repl，密码 repl，来访ip为 192.168.153.%,即这个网段的IP都能访问

现在我们给该用户赋予权限
grant replication slave on *.* to "repl"@"192.168.153.%" identified by "repl";

意思是对repl这个用户赋予主从复制的权限，范围是所有库和表

 

开启binlog日志

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

配置从节点的步骤  
配置同步日志： 

server-id=10
relay-log=/var/lib/mysql/relay-bin  #同步日志文件的路径
relay-log-index=/var/lib/mysql/relay-bin.index  #同步日志文件索引的路径

注意，同步日志和从服务器自己的二进制日志是两回事，同步日志只负责记录主节点的日志内容（主节点的操作），不会记录从节点本身的操作

指定主节点的ip，端口，用户：

change master to master_host="主节点主机ip",master_port=3306,master_user="用户",master_password="用户密码",master_log_file="mysql-bin.000001",master_log_pos=0;

# 其中 master_log_file="mysql-bin.000001",master_log_pos=0 这两项不是随便填的，而是要在主节点执行show master status 来查看主节点当前使用的二进制文件名和最后的位置pos

启动从节点:
start slave 

查看从节点状态：
show slave status

如果没有配置成功，看里面的Last_IO_Error这个字段，他会告诉你错误原因

此时你很可能发现主节点连不上，这是因为防火墙以及数据库中配置文件限定了bind-address=127.0.0.1 ，也就是主节点只允许本机连接。

如果是防火墙的问题，关闭防火墙，然后再从服务器中stop slave 再start slave
如果是只允许本机连接，则在bind-address中添加一条从服务器的IP
bind-address=127.0.0.1 从服务器IP

设定bind-address=0.0.0.0 表示允许任何ip连接主服务器，但是这个行为很危险，如果你的数据库密码设置的简单的话很可能被入侵数据库。（修改配置文件后记得重启服务）

 

接下来使用PHP实现主从节点读写分离：

思路很简单:
1.定义好负责写的主节点的ip和众多从节点的ip
2.根据sql语句判断是select还是增删改操作
3.如果是增删改则连接主节点执行操作,否则连接从节点进行操作
4.连接从节点的时候是遵循随机分配,使用mt_rand()函数即可

如果是使用TP5的话更简单,只需要进行配置即可。

==============================================

主主复制： 
有两个主节点A B，都有binlog日志，我们可以对A写和读，也可以对B写和读，不像主从复制，主节点负责写，众多从节点负责读。

对节点A写，节点B可以同步节点A的数据，反之亦然

也就是说A是主节点也是从节点，B也是主节点也是从节点；

所以，我们只需要将上面的主从复制的步骤进行两遍即可：    

假设现在已经配置成功主主复制了，会出现这么一个问题：

假设A节点往一个空的表 t1 插入5条数据（t1表只有一个主键字段id）:

insert into t1 (id) value (null);   # 执行5次

那么B节点也会自动插入5条数据，假设id为1，2，3，4，5；

现在我往B节点插入一条数据
insert into t1 (id) value (null);
此时就会报错说主键重复。

我们查看一下两个主节点的建表语句中的自增id
show create table t1

发现A节点的auto_increment=6 
    B节点的auto_increment=1
    
也就是说B节点会复制A节点的数据却无法复制A节点的自增值。如果是在mysql 5.7，那么不存在这个问题，主节点之间会在同步数据的时候也同步自增。

如果版本低于5.7 那么可以通过以下方法解决：
设定 A节点设置自增的步长为2，自增id从1开始

在配置文件中：
auto_increment_increment=2   #步长
auto_increment_offset=1      #从1开始

# 重启服务

设定 B节点设置自增的步长为2，自增id从2开始
在配置文件中：
auto_increment_increment=2   #步长
auto_increment_offset=2      #从2开始

# 重启服务

当然，一开始数据要为空，从空表开始。
这么一来，如果从A的t1表连续插入2条数据，他的id是1，3
此时B的t1也是1，3

再在B插入2条数据，此时查询表得到1，2，3，4

如果有3个主服务器进行主主复制的话，那么步长要设置为3，而offset分别为1，2，3

如果3台节点都是mysql 5.7 版本的，那么就不用这么设置。如果有一台是低于5.7版本的，就要这么设置。

当然,如果是主从复制是不会出现这个问题的,因为从节点只负责读,不负责写入。

PS：由于二进制日志不记录查询语句，所以对表A查询不会导致在表B又查询一次，所以主主复制也是可以进行负载均衡的。

 

下一节将具体操作主从复制和主主复制。