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