MySQL高级日志及备份篇

十七、其他数据库日志

1. MySQL支持的日志

a.日志类型

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、错误日志 、通用查询日志 、慢查询日志、中继日志和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便对查询进行优化
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令， 对复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便了解服务器的状态，从而对服务器进行维护
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故 障时数据的无损失恢复
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。 从服务器通过读取中继日志的内容，来同步主服务器上的操作
• 数据定义语句日志：记录数据定义语句执行的元数据操作

除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中

b.日志的弊端

• 日志功能会 降低MySQL数据库的性能 。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志
• 日志会 占用大量的磁盘空间 。对于用户量非常大，操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大

2.慢查询日志(slow query log)

慢查询日志用来记录MySQL中响应时间超过阈值的语句，具体运行时间超过long_query_time值的SQL，则会被记录在慢查询日志中，long_query_time默认值为10(运行10秒以上的语句)

主要作用：帮助发现执行时间特别长的SQL查询，并且有针对性的进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢等情况时，可以通过检查慢查询日志，找到慢查询语句，一般一条sql语句超过5秒即为慢查询。可以通过结合explain语句收集超过5秒的sql进行全面分析

默认情况下MySQL数据库没有开启慢查询日志，当需要调优时再开启即可

a.开启慢查询日志参数

1.开启 slow_query_log

查看慢查询日志是否开启

mysql > show variables like '%slow_query_log';

显示慢查询日志信息

SHOW VARIABLES LIKE `slow_query_log%`;

开启慢查询日志

set global slow_query_log='ON';

2.修改 long_query_time 阈值

查看慢查询的时间阈值

show variables like '%long_query_time%';

临时性设置慢查询的时间阈值

set global long_query_time = 1;

永久设置慢查询的时间阈值

修改配置文件vim /etc/my.cf

[mysqld]
slow_query_log=ON #开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/atguigu-low.log #慢查询日志的目录和文件名信息
long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

注意：修改慢查询是否开启设置后，需要重启mysql服务器systemctl restart mysqld.service

b.查看慢查询数目

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

c.案例

CREATE TABLE `student`(
`id` INT NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT DEFAULT NULL,
`classId` INT DEFAULT NULL,
 PRIMARY KEY (`id`)
)AUTO_INCREMENT=1;
 

1.设置参数 log_bin_trust_function_creators

• 命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1; #不加global只是当前窗口有效。

2.创建函数

随机产生字符串

DELIMITER //
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255)#该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i +1;
 END WHILE;
 RETURN return_str;
END //
DELIMITER ;

#测试
SELECT rand_string(10);

产生随机数值

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(from_num +RAND()*(to_num - from_num+1));
 RETURN i;
END //
DELIMITER ;

#测试：
SELECT rand_num(10,100);

3.创建存储过程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
 SET autocommit = 0; #设置手动提交事务
 REPEAT #循环
 SET i = i +1; #赋值
 INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
 UNTIL i = max_num
 END REPEAT;
 COMMIT; #提交事务
END //
DELIMITER ;

4.调用存储过程

#调用刚刚写好的函数,40000条记录,从100001号开始

CALL insert_stu1(100001,40000);

5.测试及分析

测试

SELECT * FROM student WHERE stuno = 100760;

SELECT * FROM student WHERE NAME = 'WkjrhJ';

分析

show status like 'slow_queries';

e.慢查询日志分析工具：mysqldumpslow

mysqldumpslow --help

mysqldumpslow 命令的具体参数：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序：
  • c: 访问次数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间（默认方式）
  • ac:平均查询次数
• -t: 为返回前面多少条的数据
• -g: 后边搭配一个正则匹配模式，大小写不敏感

eg：按照查询时间排序，查看前五条 SQL 语句

mysqldumpslow -s t -t 5 /var/lib/mysql/ablelynn100-slow.log

f.关闭慢查询日志

临时性关闭

SET GLOBAL slow_query_log=off;

永久性关闭

[mysqld]
slow_query_log=OFF

注意：修改慢查询是否开启设置后，需要重启mysql服务器systemctl restart mysqld.service

g.删除慢查询日志

显示慢查询日志信息

SHOW VARIABLES LIKE 'slow_query_log%'

得到慢查询日志的目录默认为MySQL的数据目录，手动删除慢查询日志文件

使用命令mysqladmin flush-logs来重新生成查询日志文件，执行完毕会在数据目录下重新生成慢查询日志文件

mysqladmin -uroot -p flush-logs slow

提示

慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须事先备份

3.通用查询日志(general query log)

通用查询日志用来 记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止 时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当数据发生异常时，查看通用查询日志， 还原操作时的具体场景，可以帮助准确定位问题

a.查看当前状态

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | OFF                          | #通用查询日志处于关闭状态
| general_log_file | /var/lib/mysql/atguigu01.log | #通用查询日志文件的名称是atguigu01.log
+------------------+------------------------------+
2 rows in set (0.03 sec)

注意：系统变量general_log默认关闭，因为开启后MySQL会记录所有连接起止的相关SQL操作，会消耗系统资源和占用磁盘空间。在需要的时候手动设置开启日志即可

b.启动日志

方式1：永久性方式

修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项

[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件

如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中， hostname表示主机名

方式2：临时性方式

SET GLOBAL general_log=on; # 开启通用查询日志

SET GLOBAL general_log_file=’path/filename’; # 设置日志文件保存位置

对应的，关闭操作SQL命令如下：

SET GLOBAL general_log=off; # 关闭通用查询日志

查看设置后情况：

SHOW VARIABLES LIKE 'general_log%';

注意：在修改通用查询日志是否开启后，需要重启mysql服务器systemctl restart mysqld.service才可以启动

c.查看日志

通用查询日志是以 文本文件 的形式存储在文件系统中的，可以使用文本编辑器 直接打开日志文件。每台MySQL服务器的通用查询日志内容不同

• 在Windows操作系统中，使用文本文件查看器
• 在Linux系统中，可以使用vi工具或者gedit工具查看
• 在Mac OSX系统中，可以使用文本文件查看器或者vi等工具查看

d.停止日志

方式1：永久性方式

修改 my.cnf 或者 my.ini 文件，把[mysqld]组下的 general_log 值设置为 OFF 或者把general_log一项 注释掉

[mysqld]
general_log=OFF

[mysqld]
#general_log=ON

方式2：临时性方式

使用SET语句停止MySQL通用查询日志功能：

SET GLOBAL general_log=off;

查询通用日志功能：

SHOW VARIABLES LIKE 'general_log%';

e.删除\刷新日志

如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间

手动删除文件

SHOW VARIABLES LIKE 'general_log%';

通过查询得到的通用查询日志的目录位置，然后在该目录下手动删除日志即可

使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文件。前提一定要开启通用日志

mysqladmin -uroot -p flush-logs

如果希望备份旧的通用查询日志，就必须先将旧的日志文件复制出来或者改名，然后执行上面的mysqladmin命令。正确流程如下：

cd mysql-data-directory # 输入自己的通用日志文件所在目录
mv mysql.general.log mysql.general.log.old # 指定旧的文件名 以及 新的文件名
mysqladmin -uroot -p flush-logs

4.错误日志(error log)

记录MySQL服务器启动、停止的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告、提示等

通过错误日志可以查看系统的运行状态，便于及时发现故障、修复故障，如果MySQL服务器出现异常，错误日志是发现问题和解决问题的首选

a.启动日志

在MySQL数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止

默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或 hostname.err （mac系统）。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置：

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

b.查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看

SHOW VARIABLES LIKE 'log_err%';

4.3 删除\刷新日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除， 以保证MySQL服务器上的 硬盘空间 。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以 直接删除

• 第一步（方式1）：删除操作

  rm -f /var/lib/mysql/mysqld.log
  

  在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件

• 第一步（方式2）：重命名文件

  mv /var/log/mysqld.log /var/log/mysqld.log.old
  

• 第二步：重建日志

  mysqladmin -uroot -p flush-logs
  

5.二进制日志(bin log)

bin log可以说是MySQL中比较 重要 的日志了，在日常开发及运维过程中，经常会用到

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等），它以事件形式记录并保存在二进制文件中

主要用于数据的恢复、数据的复制等

a.查看默认情况

查看记录二进制日志是否开启：二进制文件是MySQL8中默认开启

show variables like '%log_bin%';

b.日志参数设置

方式1：永久性方式

修改MySQL的 my.cnf 或 my.ini 文件可以设置二进制日志的相关参数：

[mysqld]
#启用二进制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

log-bin=atguigu-bin：打开日志

binlog_expire_logs_seconds=600：控制二进制日志文件的保留时长，单位为秒，默认2592000 30天

max_binlog_size=100M：控制单个二进制文件的大小，最大和默认值为1GB

设置带文件夹的bin-log日志存放目录

如果想改变日志文件的目录和名称，可以对my.cnf或my.ini中的log_bin参数修改如下：

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

注意：新建的文件夹需要使用mysql用户，使用下面的命令即可

chown -R -v mysql:mysql binlog

方式2：临时性方式

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是 在mysql8中只有 会话级别 的设置，没有了global级别的设置

# global 级别
mysql> set global sql_log_bin=0;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL

# session级别
mysql> SET sql_log_bin=0;
Query OK, 0 rows affected (0.01 秒)

c.查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一 个以“filename”为名称、以“.000001”为后缀的文件

MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的 个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件

查看当前的二进制日志文件列表及大小

SHOW BINARY LOGS;

结合使用 mysqlbinlog 工具进行查看

d.使用日志恢复数据

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志中回复数据

mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中

• filename ：是日志文件名

• option ：可选项，比较重要的两对option参数是–start-date、–stop-date 和 --start-position、-- stop-position

  • --start-date 和 --stop-date ：可以指定恢复数据库的起始时间点和结束时间点
  • --start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复

e.删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。 PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件

1. PURGE MASTER LOGS：删除指定日志文件

PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

2. RESET MASTER: 删除所有二进制日志文件

6.中继日志(relay log)

a.介绍

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫 中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主 从服务器的 数据同步

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下

文件名的格式是： 从服务器名 -relay-bin.序号 。中继日志还有一个索引文件：从服务器名 -relaybin.index ，用来定位当前正在使用的中继日志

b.查看中继日志

中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看

十八、主从复制

1.主从复制的概念

在实际工作中，常常将Redis作为缓存与MySQL配合来使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出。如果不存在再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。Redis的缓存架构是高并发架构中非常重要的一环

2.主从复制的作用

主从同步设计提高数据库的吞吐量

**第1个作用：读写分离。**通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力

Master主库，负责写入数据，称为：写库

Slave从库，负责读取数据，称为：读库

当主库进行更新的时候，会自动将数据复制到从库中，在客户端读取数据的时候，从从库进行读取

面对“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。同时，还能对从服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如让主库负责写，当主库出现写锁的时候，不会影响到从库进行SELECT的读取

**第2个作用就是数据备份。**通过主从复制将主库上的数据复制到从库上，相当于一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务

**第3个作用是具有高可用性。**数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行

3.主从复制的原理

Slave 会从 Master 读取 binlog 来进行数据同步

a.原理剖析

1)三个线程

主从同步的原理就是基于binlog进行数据同步。在主从复制过程中，会基于 3 个线程 来操作，一个主库线程，两个从库线程

二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉

从库 I/O 线程 会连接到主库，向主库发送请求更新Binlog。此时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的Binlog更新部分，并且拷贝到本地的中继日志 （Relay log）

从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步

2)复制三步骤

步骤1： Master 将写操作记录到二进制日志（ binlog ）

步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）

步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从 接入点 开始复制

3)复制的基本原则

• 每个 Slave 只有一个 Master
• 每个 Slave 只能有一个唯一的服务器ID
• 每个 Master 可以有多个 Slave

4.主从复制的配置

主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母

a.主机配置文件

具体参数配置如下：

• 必选

#[必须]主服务器唯一ID
server-id=1

#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin
log-bin=ablelynn-bin

• 可选

#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0

#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000

#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M

#[可选]设置不要复制的数据库
binlog-ignore-db=test

#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字

#[可选]设置binlog格式
binlog_format=STATEMENT

重启后台mysql服务，使配置生效

注意：

• 先搭建完主从复制，再创建数据库

• MySQL主从复制起始时，从机不继承主机数据

① binlog格式设置：

格式1： STATEMENT模式 （基于SQL语句的复制(statement-based replication, SBR)）

binlog_format=STATEMENT

每一条会修改数据的sql语句会记录到binlog中。是默认的binlog格式

• SBR 的优点：
  • 历史悠久，技术成熟
  • 不需要记录每一行的变化，减少了binlog日志量，文件较小
  • binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
  • binlog可以用于实时的还原，而不仅仅用于复制
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高
• SBR 的缺点：
  • 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
• 使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
  • INSERT … SELECT 会产生比 RBR 更多的行级锁
  • 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
  • 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
  • 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发 生变化的记录产生影响
  • 执行复杂语句如果出错的话，会消耗更多资源
  • 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

② ROW模式（基于行的复制(row-based replication, RBR)）

binlog_format=ROW

不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了

• RBR 的优点：
  • 任何情况都可以被复制，这对复制来说是最 安全可靠 的。（比如：不会出现某些特定情况下 的存储过程、function、trigger的调用和触发无法被正确复制的问题）
  • 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
  • 复制以下几种语句时的行锁更少：INSERT … SELECT、包含 AUTO_INCREMENT 字段的 INSERT、 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
  • 执行 INSERT，UPDATE，DELETE 语句时锁更少
  • 从服务器上采用 多线程 来执行复制成为可能
• RBR 的缺点：
  • binlog 大了很多
  • 复杂的回滚时 binlog 中会包含大量的数据
  • 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
  • 无法从 binlog 中看到都复制了些什么语句

③ MIXED模式（混合模式复制(mixed-based replication, MBR)）

binlog_format=MIXED

MySQL提供的Mixed格式，实际上就是Statement与Row的结合

在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog

MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种

b.从机配置文件

• 必选

  #[必须]从服务器唯一ID
  server-id=2
  

• 可选

  #[可选]启用中继日志
  relay-log=mysql-relay
  

重启后台mysql服务，使配置生效。

注意：主从机都关闭防火墙
service iptables stop #CentOS 6
systemctl stop firewalld.service #CentOS 7

c.主机：建立账户并授权

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

#此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;

查询Master的状态，并记录下File和Position的值

show master status;

d.从机：配置需要复制的主机

**步骤1：**从机上复制主机的命令

CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;

举例：

CHANGE MASTER TO
MASTER_HOST='192.168.1.150',MASTER_USER='slave1',MASTER_PASSWORD='slave1',MASTER_LOG_F
ILE='ablelynn-bin.000007',MASTER_LOG_POS=154;

步骤2：

#启动slave同步
START SLAVE;

如果报错：

可以执行如下操作，删除之前的relay_log信息。然后重新执行 CHANGE MASTER TO …语句即可。

mysql> reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件

接着，查看同步状态：

SHOW SLAVE STATUS\G;

显式如下的情况，就是不正确的。可能错误的原因有：

1. 网络不通
2. 账户密码错误
3. 防火墙
4. mysql配置文件问题
5. 连接服务器时语法
6. 主服务器mysql权限

5.同步数据一致性问题

主从同步的要求：

• 读库和写库的数据一致(最终一致)
• 写数据必须写到写库
• 读数据必须到读库(不一定)

a.主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行 网络传输 的过程中就一定会 存在主从延迟 （比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的 数据不一致性 问题

b.主从延迟问题原因

在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T2-T1的值是非常小的。即，网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差

**主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。**造成原因：

1. 从库的机器性能比主库要差

2. 从库的压力大

3. 大事务的执行

eg

• **举例1：**一次性用delete语句删除太多数据

  • 结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除

• **举例2：**一次性用insert…select插入太多数据

• **举例3：**大表DDL

  • 比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟

c.减少主从延迟

若想要减少主从延迟的时间，可以采取下面的办法：

1. 降低多线程大事务并发的概率，优化业务逻辑
2. 优化SQL，避免慢SQL， 减少批量操作 ，建议写脚本以update-sleep这样的形式完成
3. 提高从库机器的配置 ，减少主库写binlog和从库读binlog的效率差
4. 尽量采用 短的链路 ，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时
5. 实时性要求的业务读强制走主库，从库只做灾备，备份

d.解决一致性问题

如果操作的数据存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取的时候就不会发生数据不一致的情况。但这时从库的作用就是 备份 ，并没有起到 读写分离 ，分担主库 读压力 的作用

读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式

方法 1：异步复制

异步模式就是客户端提交 COMMIT 之后不需要等从库返回任何结果，而是直接将结果返回给客户端

好处是不会影响主库写的效率

缺点：可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的

方法 2：半同步复制

半同步复制原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了binlog，并且写入中继日志后，再返回给客户端

好处提高了数据的一致性

缺点相比于异步复制，增加了网络连接延迟，降低了主库写的效率

方法 3：组复制(MGR)

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。MGR很好地弥补了这两种复制模式的不足

组复制技术，简称 MGR（MySQL Group Replication）。这种复制技术是基于 Paxos 协议的状态机复制

MGR工作流程

首先将多个节点共同组成一个复制组，在 执行读写（RW）事务 的时候，需要通过一致性协议层 （Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节 点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对 只读（RO）事务 则不需要经过组内同意，直接 COMMIT 即可

在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消 息和全局有序消息，从而保证组内数据的一致性

十九、数据库备份和恢复

1.物理备份和逻辑备份

**物理备份：**备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份

**逻辑备份：**对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现

2. mysqldump实现逻辑备份

mysqldump是MySQL提供的一个非常有用的数据库备份工具

a.备份一个数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据

• 查出需要备份的表的结构，在文本文件中生成一个CREATE语句
• 将表中的所有记录转换为一条INSERT语句

基本语法：

mysqldump –u用户名称 –h主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

说明： 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的

b.备份全部数据库

用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数：

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

c.备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在

mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

d.备份部分表

一般在表变更之前做备份

mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

e.备份单表的部分数据

可以使用 --where 选项，where后面附带需要满足的条件

mysqldump –u user –h host –p 数据库的名称 [表名1 --where="条件" [表名2...]] > 备份文件名称.sql

f.排除某些表的备份

在备份某个数据库时，可以排除一些表，只备份其他的表

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

g.只备份结构或只备份数据

• 只备份结构可以使用 --no-data 简写为 -d 选项
• 只备份数据可以使用 --no-create-info 简写为 -t选项

• 只备份结构

  mysqldump -uroot -p atguigu --no-data > ablelynn_no_data_bak.sql
  #使用grep命令，没有找到insert相关语句，表示没有数据备份。
  [root@node1 ~]# grep "INSERT" ablelynn_no_data_bak.sql
  [root@node1 ~]#
  

• 只备份数据

  mysqldump -uroot -p atguigu --no-create-info > ablelynn_no_create_info_bak.sql
  #使用grep命令，没有找到create相关语句，表示没有数据结构。
  [root@node1 ~]# grep "CREATE" ablelynn_no_create_info_bak.sql
  [root@node1 ~]#
  

h.备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件，可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件

i.mysqldump常用选项

mysqldump其他常用选项如下：

--add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。

--add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。

--add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

--all-database, -A：转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。

--comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。

--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-keys和--skip-add-locking选项。

--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。

--complete_insert, -c：使用包括列名的完整的INSERT语句。

--debug[=debug_options], -#[debug_options]：写调试日志。

--delete，-D：导入文本文件前清空表。

--default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。

--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。

--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。

--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。

--force，-f：在表转储过程中，即使出现SQL错误也继续。

--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。

--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。

--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出--database或--all-database选项，就包含到输出中。

--no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。

--no-data，-d：不写表的任何行信息，只转储表的结构。

--opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。

--password[=password]，-p[password]：当连接服务器时使用的密码。

-port=port_num，-P port_num：用于连接的TCP/IP端口号。

--protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。

--replace，-r –replace和--ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

--silent，-s：沉默模式。只有出现错误时才输出。

--socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。

--user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。

--verbose，-v：冗长模式，打印出程序操作的详细信息。

--xml，-X：产生XML输出。

运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表

3.mysql命令恢复数据

使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql命令来恢复备份的数据

mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据

基本语法：

mysql –u root –p [dbname] < backup.sql

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MySQL服务器中已存在的这些数据库

a.单库备份中恢复单库

使用root用户

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示

mysql -uroot -p < XXX.sql

否则需要指定数据库名称，如下所示

mysql -uroot -p atguigu4< XXX.sql

b.全量备份恢复

1)整个恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：

mysql –u root –p < all.sql

mysql -uroot -pxxxxxx < all.sql

执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库

2)只恢复某一个库

如果有的是整个实例的备份，可以从全量备份中分离出单个库的备份

eg：

sed -n '/^-- Current Database: `XXX`/,/^-- Current Database: `/p' all_database.sql > XXX.sql
#分离完成后我们再导入atguigu.sql即可恢复单个库

c.从单库备份中恢复单表

eg：

cat XXX.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat XXX.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

use XXX;
mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)

4. 物理备份：直接复制整个数据库

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同：

• 在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQL Server 8.0\Data ”或者其他用户自定义目录
• 在Linux平台下，数据库目录位置通常为/var/lib/mysql/
• 在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”

但为了保证备份的一致性。需要保证：

• 方式1：备份前，将服务器停止
• 方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中 的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索 引页写入硬盘

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能不允许停止MySQL服务器或者锁住表，而且这种方法 对InnoDB存储引擎 的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况

注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为

说明： 在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同

5. 物理恢复：直接复制到数据库目录

步骤：

1）演示删除备份的数据库中指定表的数据

2）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器

3）查询相关表的数据是否恢复。需要使用下面的 chown 操作

要求：

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同
  • 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的
• 这种方式对 MyISAM类型的表比较有效 ，对于InnoDB类型的表则不可用
  • 因为InnoDB表的表空间不能直接复制
• 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：

chown -R mysql.mysql /var/lib/mysql/dbname

其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录

提示 Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户 组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将 文件夹的用户组变为mysql，将用户变为mysql

6.数据库迁移

a.概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认为是整个数据迁移过程的一部分

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来

b.迁移方案

• 物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要 求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题

物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种

不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库

• 逻辑迁移

逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具

c.迁移注意点

1. 相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动

方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库

方式2： 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用 MySQL命令导入

2. 不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷， 因此需要把数据库升级到8.0版本

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据

高版本的MySQL数据库通常都会 兼容低版本 ，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库

3. 不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类 型的数据库。这种迁移没有普适的解决方法

迁移之前，需要了解不同数据库的架构， 比较它们之间的差异 。不同数据库中定义相同类型的数据的 关键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET 类型，这些SQL Server数据库不支持

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容

不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但 是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用 MyODBC 实现MySQL和SQL Server之 间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。 MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后， 手动更改 sql文件中的CREATE语句

d.总结

辑迁移中使用最多的就是通过 mysqldump 等备份工具

c.迁移注意点

1. 相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动

方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库

方式2： 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用 MySQL命令导入

2. 不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷， 因此需要把数据库升级到8.0版本

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据

高版本的MySQL数据库通常都会 兼容低版本 ，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库

3. 不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类 型的数据库。这种迁移没有普适的解决方法

迁移之前，需要了解不同数据库的架构， 比较它们之间的差异 。不同数据库中定义相同类型的数据的 关键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET 类型，这些SQL Server数据库不支持

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容

不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但 是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用 MyODBC 实现MySQL和SQL Server之 间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。 MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后， 手动更改 sql文件中的CREATE语句

d.总结