1. 错误日志

MySQL有4种日志类型，分别是错误日志、二进制日志、查询日志和慢查询日志。

错误日志主要是记录启动、运行或停止mysqld时出现的问题。

错误日志

默认是开启

的。一般情况下，错误日志存储在数据文件夹下，

错误日志以

文本

文件的形式存储，直接使用普通文本工具就可以查看。

SHOW VARIABLES LIKE ‘log_error’;

查看错误日志文件所在的目录及文件名信息。

mysqladmin -uroot -p flush-logs

可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。

执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为

filename.err-old

。

错误日志的相关配置

[mysqld]
# 指定 错误日志 的存放位置，以及 错误日志的 名称
log_error=/var/log/mysql/mysql_error.log

2. binlog 日志

MySQL的二进制日志可以说是MySQL最重要的日志了，它以事件de形式记录了所有的DDL和DML(除了数据查询语句)语句，

记录内容还包含语句所执行的消耗的时间。

binlog是 Server 层的日志

binlog日志主要作用：

恢复数据

主从复制

binlog日志两类文件：

二进制日志索引文件
，文件名后缀为.index，

二进制日志文件
，文件名后缀为.00000*，记录数据库所有的DDL和DML

开启

配置开启

# 在配置文件中
[mysqld]
log_bin=mysql-bin
server-id=721657

# 重启mysql服务

log_bin=mysql-bin

mysql会根据这个配置自动设置log_bin为on状态，自动设置log_bin_index、log_bin_basename文件

server-id=721657

如果是5.7及以上版本加入了很多集群相关的代码，如果没有该配置，重启mysql服务会报错。该配置指定当前mysql的唯一id标识，不能和其他集群中机器重名的字符串。

查看是否开启

show variables like '%log_bin%';  

+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+

binlog的三种模式

Statement （默认模式）

每一条会修改数据的
sql
都会记录在binlog中。

优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点

slave可与master上要保持一致会有很多问题.

Row （重量模式）

不记录sql语句上下文相关信息，仅保存哪条
记录
被修改。

优点

rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。

而且不会出现某些特定情况下的存储过程，或function，

以及trigger的调用和触发无法被正确复制的问题。

缺点

可能会产生大量的日志内容,

比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，

特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Mixed （自动模式，推荐使用）

是以上两种level的混合使用。

一般的语句修改使用statment格式保存binlog，

如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,

MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式。

binlog日志详解

查看当前 binlog 状态信息

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |   410713 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

mysql-bin.000001 是
当前
正在被mysql写入的二进制日志文件名。

410713 这个数值表示的就是binlog的
当前位置
。每次执行dml操作，position都会改变。

查看 binlog 内容

查看某个binlog日志内容，常用有两种方式

方式一： mysqlbinlog

文本的方式(cat、less、more) 查看 mysql-bin.000001的内容是一堆乱码，

mysql给我们提供了一个用于查看binlog日志的工具，叫做mysqlbinlog，

mysqlbinlog mysql-bin.000001

方式二： show binlog events 查看binlog日志

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
               IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
               FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
               LIMIT [offset,] 偏移量(不指定就是0)
               row_count       查询总条数(不指定就是所有行)

# 指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，偏移2行，查询10条
show binlog events in 'mysql-bin.000002' from 534620 limit 2,10


# 实操
show binlog events in 'mysql-bin.000003'\G

*************************** 1. row ***************************
   Log_name: mysql-bin.000003          　　　　　　　　　　     ----> 查询的binlog日志文件名
        Pos: 4                 　　　　　　　　　　　　　　       ----> pos起始点:
 Event_type: Update_rows、Delete_rows、Write_rows    　　　    ----> 事件类型
  Server_id: 1                         　　　　　　　　　　      ----> 标识是由哪台服务器执行的
End_log_pos: 120                　　　　　　　　　　　　　      　----> pos结束点:120(即：下行的pos起始点)
       Info: use `sdf`; INSERT INTO `wtt` VALUES (1,'tom')   ---> 执行的sql语句

*************************** 2. row ***************************

binlog文件操作

查看当前

show master status;

查看全部

show master logs;

刷新当前

flush logs;

该操作会新建一个日志文件，并指定为当前文件。

mysql 重启一次，会自动执行此命令，所以也会自动生成一个新的binlog文件

在mysqldump备份数据时加

-F

选项,在备份完成后也会自动执行此命令。

手动删除

# 删除 指定日期 以前 的 
purge master logs before '2017-03-30 00:20:00'; 

# 删除 指定日志文件 以前 的
mysql> purge master logs to 'mysql-bin.000009';

自动删除

# 自动删除 超过一周 的日志文件
set global expire_logs_days=7;

# 查看 设置的 日志有效期
show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     | # 如果是 0， 代表不要自动删除。 
+------------------+-------+

清空所有

reset master;

在生产环境中，这个操作是非常危险的

常用配置

[mysqld]
#binlog日志格式，可选值有：ROW / STATEMENT / MIXED， 推荐使用 MIXED 模式
binlog_format=MIXED 

# binlog过期清理时间
expire_logs_days=7

# binlog每个日志文件大小
max_binlog_size 100m

# 需要备份的数据库名，如果备份多个数据库，==重复设置== 这个选项即可
binlog_do_db=wtt

# 不需要备份的数据库苦命，如果备份多个数据库，重复设置这个选项即可
binlog_ignore_db=test

数据恢复

本质：让 binlog记录的操作再执行一遍。

################################################################
  mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 [数据库名]
################################################################

mysqlbinlog 常用命令参数：
        --start-position=953                    起始pos点
        --stop-position=1437                    结束pos点
        --start-datetime="2017-6-20 13:18:54"   起始时间点
        --stop-datetime="2017-6-20 13:21:53"    结束时间点
        --database=wtt                          指定只恢复 wtt 数据库


mysqlbinlog 不常用命令参数：   
        -u --user=name              连接到远程主机的用户名
        -p --password[=name]        连接到远程主机的密码
        -h --host=name              从远程主机上获取binlog日志
        --read-from-remote-server   从某个MySQL服务器上读取binlog日志


######### 使用案例
mysqlbinlog --stop-position=759 --database=wtt mysql-bin.000007 | mysql -uroot -p123456 -v wtt

mysqlbinlog --start-position=759 --stop-position=880 --database=wtt mysql-bin.000007 | mysql -uroot -p123456 wtt

mysqlbinlog --start-datetime="2017-6-20 13:18:54" --stop-datetime="2013-16-20 14:21:53" --database=wtt mysql-bin.000007 | mysql -uroot -p123456 -v juzidb

当要某个数据库被删除了，要恢复数据库这个级别时，mysql -uroot -p123456 后面无需带数据库名称，

当要恢复某个数据库的数据表or记录时， mysql -uroot -p123456 后面需带数据库名称，

mysql 的 -v 说明：

-V是查看版本的， -v 主要用在脚本、命令中可以用来简化代码

若要同时显示语句本身：-v

若要增加查询结果行数：-vv

若要增加执行时间：-vvv

上面的-v完全是就可以省略的。

3. 主从复制

简单来说，就是保证主SQL（Master）和从SQL（Slave）的数据是一致性的，

向Master插入数据后，Slave会自动从Master把修改的数据同步过来（避免不了的是会有一定的延迟），

通过这种方式来保证数据的一致性，就是主从复制。

注意：

主从服务器操作系统、版本、位数要保持一致,

Master 和 Slave 数据库的版本要一致,

Master和Slave的 server_id 必须唯一

原理

主要基于MySQL二进制日志,主要包括三个线程（2个I/O线程，1个SQL线程）

MySQL将数据变化记录到二进制日志中；

Slave将MySQL的二进制日志拷贝到Slave的中继日志中；

Slave将中继日志中的事件再做一次，将数据变化，反应到自身（Slave）的数据库

实现原理

：

在主mysql 上设置一个权限账号；

在从mysql 上通过权限账号连接、读取主mysql 的 binlog，进行数据同步。

复制过滤

只复制服务器中的一部分数据，有两种复制过滤：

在Master上过滤二进制日志中的事件

在Slave上过滤中继日志中的事件。

复制类型

基于语句的复制, 对应 binlog的默认模式；

基于行的复制，对应 binlog的重量模式；

默认采用基于语句的复制，对应 binlog的智能模式；

实践

配置 master

step1: 配置文件

[mysqld]
#设置server_id，一般设置为IP,注意要唯一
server_id=100

#复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
# 其原理就是 不让 哪个库 产生 binlog 日志。
binlog-ignore-db=mysql 

#开启二进制日志功能，以备Slave作为其它Slave的Master时使用，名称自定义
log-bin=wtt-bin 

#relay_lo g配置中继日志
relay_log=wtt-bin 

#为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M 

#日志模式
binlog_format=mixed 

#日志有效期
expire_logs_days=7 

#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062 

#log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1

配置完成后重启mysql

step2: 创建数据同步用户

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

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

flush privileges;

配置 slave

step1: 配置文件

[mysqld]
server_id=101 

#防止 普通用户 改变数据，但是 root用户仍然可以改变数据
read_only=1

建立连接

保证带同步的数据库中数据一致。

step1: 查看master的日志状态

# master
show master status\G
# 记录 当前二进制日志文件的 文件名（假设为 aaa.000001） 和 Position列的 值 （假设为：721657）

step2: Slave中设置Master信息

# slave
# 如果用docker搭建， master_host 可以写 docker0 的地址
change master to master_host='192.168.100.70', master_user='slave', \ 
master_password='123456', master_port=3306, \
master_log_file='aaa.000001', master_log_pos=721657, \
master_connect_retry=30;


### 解释
#Master的IP地址
master_host=’192.168.100.70′
 
#用于同步数据的用户（在Master中授权的用户）
master_user=’slave’
 
#同步数据用户的密码
master_password=’123456′
 
#Master数据库服务的端口
master_port=3306
 
#指定Slave从哪个日志文件开始读复制数据（Master上执行命令的结果的File字段）
master_log_file=’edu-mysql-bin.000001′
 
#从哪个POSITION号开始读（Master上执行命令的结果的Position字段）
master_log_pos=120
 
#当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。
master_connect_retry=30

step3: Slave中开启主从同步

# slave
start slave;

step4: Slave中查看主从同步状态

# slave
show slave status;

### 重点字段说明：
1. SlaveIORunning 和 SlaveSQLRunning  这两个都是Yes 才表明已经开始工作了.
2. 报错信息 存储在 字段：Last_IO_Errno 、Last_IO_Error 、Last_SQL_Errno、Last_SQL_Error 中

如果 Slave_IO_Running 出现错误，IO线程一直处于 Connecting 正在连接状态

原因一：

首先在从数据库尝试登录主数据库，查看是否能够成功登录

mysql -h -P -uslave -p123345

原因二：

由于docker启动的 mysql 其使用同一个镜像，这就导致主和从数据库的uuid 重复了，

解决方法：进入主 or 辅的容器， cd 到 /var/lib/mysql下，找到

auto.cnf文件

，

对
server-uuid=64e8e1b7-e13e-11ea-9d5f-000c29a7b93f
进行修改，例如将最后的 f 修改文 1

step5: 测试

在主mysql 中创建一个数据库，从mysql 中也会同步出现。

注意，如果从mysql中没有的 aaa数据库，主mysql 有 aaa数据，那么操作主mysql 中 aaa数据是不会同步到从mysql上的。

常见问题

重置从mysql 的跟随信息

先将 slave的跟随活动停下来：
stop slave;

修改跟随信息：
change master to ******

启动 slave：
start slave;

如果报错：

Slave failed to initialize relay log info structure from the repository

说明从库已经存在之前的relay log，清除掉即可：
reset slave;

从库出现错误，停止主从复制

我们知道从库是不允许手动操作一些数据的，否则会造成手动添加的数据从库从主库中拉取的数据产生冲突。

一旦从库出现错误就会停止主从复制的活动。

解决方法：

将从库中已有的数据库全部删除；

然后重置从mysql 的跟随信息，将一开始走过的路再重新走一遍。

4. show 看见

系统

查看MySQL版本信息

select version();

安装以后可用的存储引擎和默认引擎

show engines;

系统中正在运行的所有进程

show processlist;

当前MySQL本次启动后的运行统计时间(单位：秒)

show status like 'uptime';
select 查询到的uptime值 /3600/24 as day;

查看
当前实时连接数
。

mysql> show status like 'Threads%';  
    +-------------------+-------+  
    | Variable_name     | Value |  
    +-------------------+-------+  
    | Threads_cached    | 58    |  
    | Threads_connected | 57    |   # 这个数值指的是 打开的连接数  
    | Threads_created   | 3676  |   # 表示 创建过的 线程数，
    | Threads_running   | 4     |   #这个数值指的是激活的连接数,代表当前 并发数，这个数值一般远低于Threads_connected 数值  
    +-------------------+-------+  

性能优化点：
    如果发现threads_created值过大的话，表明mysql服务器一直在 创建线程，这也是比较耗资源，
优化方法：
    适当增加配置文件中 thread_cache_size 值。

变量

show variables用于显示mysql服务器变量,通常结合like使用

mysqld服务维护两种变量:

全局变量
影响服务器的全局操作。

修改全局变量： set global binlog_format=mixed; //全局更改，数据库重启后失效

会话变量
影响具体客户端连接相关操作。

修改会话变量：set session binlog_format=mixed; //客户端更改，退出终端后失效.

查看全局字符集

show global variables like 'character%';

查看当前错误日志配置

show global variables like '%log_err%';

查询Mysql最大连接数（也可直接查看配置文件）

show variables like '%max_connections%';

当客户端（Navicat + 后台程序）连接到 mysql服务的信息

show full processlist; 

Id|User|Host            |db   |Command|Time|State   |Info                                                                                 |
--+----+----------------+-----+-------+----+--------+-------------------------------------------------------------------------------------+
 2|root|172.17.0.1:56562|mysql|Sleep  |  34|        |                                                                                     |
 3|root|172.17.0.1:56566|mysql|Sleep  |  34|        |                                                                                     |
 4|root|172.17.0.1:50600|zhd  |Query  |   0|starting|/* ApplicationName=DBeaver 22.1.2 - SQLEditor <Script-5.sql> */ show full processlist|

全局查看wait_timeout值：

show global variables like ‘wait_timeout’;

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+


# 查看的变量 就是配置文件的 某一项，这里进行 命令的临时修改
set global wait_timeout=3600;

数据库

表创建语句的SQL

show create table table_name;

事务锁

查看锁表情况

show status like 'Table%';

查看正在被锁定的的表

show OPEN TABLES where In_use > 0;

查询进程

show processlist # 查询到相对应的进程可以 kill    id

查看未提交事务:

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

5. 备份

备份的目的：

做灾难恢复：对损坏的数据进行恢复和还原

需求改变：因需求改变而需要把数据还原到改变以前

测试：测试新功能是否可用

要考虑的问题：

可以容忍丢失多长时间的数据；

恢复数据要在多长时间内完；

恢复的时候是否需要持续提供服务；

恢复的对象，是整个库，多个表，还是单个库，单个表。

备份的分类

冷备、温备、热备

根据是否需要数据库离线

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；

温备（warm backup）：服务在线，但仅支持读请求，不允许写请求；

热备（hot backup）：备份的同时，业务不受影响。

MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具

完全、增量、差异

根据要备份的数据集合的范围

完全备份：full backup，备份全部字符集。

增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。

差异备份：differential backup 上次完全备份以来改变了的数据。

物理备份、逻辑备份

物理备份：直接备份

数据文件

逻辑备份: 备份表中的

数据和代码

物理备份 + 完全备份

备份

向所有表施加
读锁

mysql> FLUSH TABLES WITH READ LOCK; 

Query OK, 0 rows affected (0.00 sec)

备份数据文件

#保留权限的拷贝源数据文件
cp -a /var/lib/mysql/* /backup/

解除第一步加的读锁

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

恢复

手动制造数据丢失

# 删除数据库的所有文件
rm -rf /var/lib/mysql/*    

# 重启MySQL, 注意： 如果 mysql 是编译安装的， 则由于 核心文件丢失 则无法启动了。
# docker 的 mysql容器 和 rpm 安装的 mysql 是可以 重启的。
service mysqld restart

数据恢复

# 因为 我们是 全备份，所以 将 残留的 文件 都清理干净， 避免 文件复制时 产生冲突
rm -rf /var/lib/mysql/*    

#将备份的数据文件拷贝回去
cp -a /backup/* /var/lib/mysql/    

# 重启MySQL
service mysqld restart

说明：

以 docker 的 mysql容器来说，我们指定宿主机的 data目录为 mysql111容器的数据目录，

指定 data2 为 mysql222 容器的数据目录。

如果把 mysql111 中的内容全复制到 data2 中，那么 mysql222 就和 mysql111 中有一个样的数据了。

即使新启动一个 mysql333 让其数据目录映射到宿主机的 data目录的备份目录，那么 mysql333 一启动，

其中的数据就和 mysql111 是一样的。

为什么建议物理备份的时候使用全量备份？

接着上面的例子进一步说明， mysql111容器创建一个 abc 数据库，那么在 data目录下就会有一个 abc目录，

其中记录着 abc 数据库相关的内容。

如果直接把 data下的 abc目录复制到 data2 下，那么在 mysql222 中会查看到增加了一个 abc数据库，

但是在 mysql222 下却无法查看 abc数据库下所有表中的具体数据。

这是因为在 mysql111容器创建一个 abc 数据库，不仅仅是在 data目录下就会有一个 abc目录，这么简单，

在其他核心文件中对 abc数据库做了行为记录，

仅仅将 abc目录复制到 data2 下， data2 下是没有对 abc数据库行为记录描述的，所以无法打开。

因此建议做物理备份的时候选择全量备份。

逻辑备份 + 增量备份

逻辑备份一般使用 mysqldump命令，

mysqldump

是一个客户端的逻辑备份工具,

可以生成一个重新创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备。

增量备份使用的是 binlog 文件复制。

mysqldump 用法说明

# --databases:
mysqldump [options] db_name ...               # 恢复 需要手动   创建数据库
mysqldump [options] --databases db_name ...   # 恢复 不需要手动 创建数据库

# -E,--events: 备份 事件调度器

# -R, --routines: 备份 存储过程 和 存储函数

# --triggers: 备份表的 触发器;

# --lock-all-tables: 备份时，锁表

备份

查看逻辑备份之前 binlog 情况

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      106 |              |                  |
+------------------+----------+--------------+------------------+

逻辑备份

# 备份abc数据库：（创建数据表语句 + 表中的数据） 
mysqldump -u root -p --databases abc --lock-all-tables  > backup.sql

# 备份abc数据库：（创建数据表语句） 
mysqldump -u root -p -d --databases abc --lock-all-tables  > backup.sql

# 备份abc数据库 下的 aaa表,这里不用 --databases 参数
mysqldump -u root -p --lock-all-tables abc aaa > backup.sql

增量备份

# 一段时间后，将 逻辑备份 之后的 binglog 进行备份
cp /var/lib/mysql/mysql-bin.000003 /my_cp  #备份二进制文件
cp /var/lib/mysql/mysql-bin.000004 /my_cp  
......

恢复

先恢复逻辑备份

mysql> SET sql_log_bin=OFF;   # 暂时先将二进制日志关闭  

mysql> source backup.sql  # 恢复数据

mysql> SET sql_log_bin=ON; # 开启二进制日志

再恢复增量备份

# 从 指定 位置 开始恢复
mysqlbinlog --start-position=106 --database=abc mysql-bin.000003 | mysql -uroot -p123456 -v abc

# 全 恢复
mysqlbinlog --database=abc mysql-bin.000004 | mysql -uroot -p123456 -v abc

逻辑备份的大数据备份工具

mysqldump 和 source 是官方自带的备份、恢复工具。

但是，mysqldump有个缺点是，
单线程执行
备份，速度较慢。在生产环境使用中，做一些小库的备份或者表的备份比较灵活，但是无法应对大数据量的备份。

因此生产环境里用的较多的是

mydumper（备份）/myloader（恢复）

。

特性

针对MySQL和Drizzle的
高性能多线程
备份和恢复工具，开发人员主要来自MySQL,Facebook,SkySQL公司。

mydumper（Facebook开源）最大的特点就是可以多线程执行备份和压缩，速度相对快很多，空间占用也较小（压缩率是10%-15%）。

本质上mydumper备份原理与mysqldump类似，也是把数据转换成SQL语句的形式输出到文件中。

不过文件展示形式有区别：mysqldump整体只有一个文件；　mydumper则是每个表对应
两个文件
（一个是
表结构文件
，也就是create table语句，一个是
表数据文件
，也就是insert 语句）

安装

apt install mydumper

#安装检测
mydumper -v
myloader -v

常用命令参数

mydumper参数：

序号	参数	解释
1	-B, –database	要备份的数据库，不指定则备份所有库
2	-T, –tables-list	需要备份的表，名字用逗号隔开
3	-o, –outputdir	备份文件输出的目录
4	-s, –statement-size	生成的insert语句的字节数，默认1000000
5	-r, –rows	将表按行分块时，指定的块行数，指定这个选项会关闭 –chunk-filesize
6	-F, –chunk-filesize	将表按大小分块时，指定的块大小，单位是 MB
7	-c, –compress	压缩输出文件
8	-e, –build-empty-files	如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
9	-x, –regex	是同正则表达式匹配 ‘db.table’
10	-i, –ignore-engines	忽略的存储引擎，用逗号分割
11	-m, –no-schemas	不备份表结构
12	-k, –no-locks	不使用临时共享只读锁，使用这个选项会造成数据不一致
13	–less-locking	减少对InnoDB表的锁施加时间（减少锁等待的时间）
14	-l, –long-query-guard	设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
15	–kill-long-queries	杀掉长查询 (不退出)
16	-b, –binlogs	导出binlog
17	-D, –daemon	启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
18	-I, –snapshot-interval	dump快照间隔时间，默认60s，需要在daemon模式下
19	-L, –logfile	使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
20	–tz-utc	跨时区是使用的选项，不解释了
21	–skip-tz-utc	同上
22	–use-savepoints	使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
23	–success-on-1146	如果表不存在，则不增加错误计数和警告
24	-h, –host	连接的主机名
25	-u, –user	备份所使用的用户
26	-p, –password	备份用户的密码
27	-P, –port	端口
28	-S, –socket	使用socket通信时的socket文件
29	-t, –threads	开启的备份线程数，默认是4
30	-C, –compress-protocol	压缩与mysql通信的数据
31	-V, –version	显示版本号
32	-v, –verbose	输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

myloader参数：

序号	参数	解释
1	-d, –directory	备份文件的目录
2	-q, –queries-per-transaction	每次事物执行的查询数量，默认是1000
3	-o, –overwrite-tables	如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
4	-B, –database	需要还原的数据库
5	-e, –enable-binlog	启用还原数据的二进制日志
6	-h, –host	连接的主机名
7	-u, –user	备份所使用的用户
8	-p, –password	备份用户的密码
9	-P, –port	端口
10	-S, –socket	使用socket通信时的socket文件
11	-t, –threads	开启的备份线程数，默认是4
12	-C, –compress-protocol	压缩协议
13	-V, –version	显示版本
14	-v, –verbose	输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

使用案例

备份

# 常用备份参数
mydumper -u root -p 123456 -B wtt -t 8 -r 10000 -c --less-locking -v 3 -L ./info.log -o ./


# 备份后得到的目录
.
├── info.log
├── metadata
├── wtt.a-schema.sql.gz
├── wtt.a.sql.gz
├── wtt.b-schema.sql.gz
├── wtt.b.sql.gz
├── wtt.c-schema.sql.gz
├── wtt.c.sql.gz
├── wtt-schema-create.sql.gz
├── wtt.shop-schema.sql.gz
├── wtt.shop.sql.gz
├── wtt.test-schema.sql.gz
└── wtt.test.sql.gz

还原

# 常用恢复参数
myloader -u root -p 123456 -B wtt2 -e -t 8 -d ./ --overwrite-tables -v 3

# 恢复命令的输出
** Message: 14:05:57.169: 8 threads created
** Message: 14:05:57.169: Dropping table or view (if exists) `wtt2`.`shop`
** Message: 14:05:57.169: Creating table `wtt2`.`shop`
** Message: 14:05:57.189: Dropping table or view (if exists) `wtt2`.`c`
** Message: 14:05:57.190: Creating table `wtt2`.`c`
** Message: 14:05:57.211: Dropping table or view (if exists) `wtt2`.`test`
** Message: 14:05:57.212: Creating table `wtt2`.`test`
** Message: 14:05:57.234: Dropping table or view (if exists) `wtt2`.`b`
** Message: 14:05:57.235: Creating table `wtt2`.`b`
** Message: 14:05:57.255: Dropping table or view (if exists) `wtt2`.`a`
** Message: 14:05:57.256: Creating table `wtt2`.`a`
** Message: 14:05:57.281: Thread 4 restoring `wtt`.`c` part 0
** Message: 14:05:57.281: Thread 5 restoring `wtt`.`shop` part 0
** Message: 14:05:57.281: Thread 1 restoring `wtt`.`test` part 0
** Message: 14:05:57.281: Thread 8 restoring `wtt`.`a` part 0
** Message: 14:05:57.281: Thread 3 shutting down
** Message: 14:05:57.281: Thread 7 shutting down
** Message: 14:05:57.281: Thread 2 restoring `wtt`.`b` part 0
** Message: 14:05:57.281: Thread 6 shutting down
** Message: 14:05:57.286: Thread 8 shutting down
** Message: 14:05:57.288: Thread 4 shutting down
** Message: 14:05:57.288: Thread 1 shutting down
** Message: 14:05:57.291: Thread 5 shutting down
** Message: 14:05:57.293: Thread 2 shutting down

6. 优化

查询优化

避免使用 select *

应该只查询需要的数据。多余的占用计算机和io资源。

select * 不会走索引。会出现大量的回表操作，从而导致查询的sql性能很低。

使用 union all 代替 union

(select * form users where id=1)
union
(select * form users where id=2)

使用 union 会获取去重之后的数据，使用 union all 会获取所有的数据，

去重的操作需要遍历、排序、比较数据，这是比较消耗 cpu的。

小表驱动大表

select * from order
where uid in (select id from users where status=1);

假设有 order 和 users 两张表，其中order表中有一万条数据，users表中有一百条数据，

如果想查询所有有效的用户下过的订单列表，可以使用 in 关键字实现上面的查询。

即
先查询
的表一定要是数据
小的表
，
后查询
的表为数据量
大的表
，

这样做的目的是可以减少检索的数据范围。

多用 limit

不论是查询还是更新操作，能用 limit的就尽量用limit，可以减少数据的检索数量。

in 中值太多

如果 in 查询中的值过多，可以将值分开，进行批量查询，然后将查询的结果拼上。

高效的分页

select name from users limit 100000, 20;

mysql 会查询到 100020 条数据，然后丢弃前面的 100000 条数据，

显然这个是非常浪费资源的，可以做一下优化：

-- 假设 上一次查询的 记录的 id为100000
select name from users id > 100000 limit 20;

用连接代替子查询

-- 子查询
select * from order where uid in (select id from users where stats=1);

-- 连接查询
select o.* form order o
join users u
on o.uid=u.id
where u.status=1;

mysql中如果需要从两张或者两张以上的表中查询数据的话，一般有两种实现方法：

子查询

连接查询

推荐使用连接查询，是因为子查询执行过程中会产生

临时表

，查询完毕后需要在删除这些临时表。

这就会产生额外性能的消耗。

连接查询中的值得一提的是一次性关联查询的表不易过多，根据阿里手册，不宜超过3个。

索引优化

配置优化

常见问题

1、mysql 的主键id默认自动升序，导致断层的情况：

数据库回滚操作
：当一个事务执行了一些插入操作，但是由于某些原因事务被回滚，这些插入的数据就不会被真正写入数据库，导致主键id出现断层。
数据库异常关闭：当数据库发生异常关闭时，很可能造成主键id的断层。这是因为，在异常关闭的情况下，未被写入磁盘的数据将会丢失，这些数据对应的主键id也就消失了。
手动插入数据：在插入数据时，如果手动指定了主键id，而且这个id已经被占用了，那么插入操作就会失败，导致主键id出现断层。
手动删除数据：当删除数据时，如果不是根据主键id删除，而是根据其他条件删除，就可能出现主键id的断层。这是因为MySQL在删除数据时不会重置自增长的id，而是保持原有的自增长规则，因此会出现断层。
手动修改主键id：在某些情况下，可能需要修改某条数据的主键id，这种操作也可能导致主键id出现断层。
数据库主副本同步：在进行数据库主副本同步时，如果出现网络波动等异常情况，就可能造成主键id出现断层。
主键id超出范围：主键id是一个整型数据，在MySQL中，主键id的数据类型通常是int或bigint，如果插入的数据量超过这个数据类型的最大值，就会出现主键id断层。

原文链接：https://blog.csdn.net/weixin_45541665/article/details/123662304

1. 错误日志

2. binlog 日志

开启

配置开启

查看是否开启

binlog的三种模式

Statement （默认模式）

Row （重量模式）

Mixed （自动模式， 推荐使用）

binlog日志详解

查看 当前 binlog 状态信息

查看 binlog 内容

binlog文件操作

数据恢复

3. 主从复制

原理

实现原理 ：

实践

配置 master

配置 slave

建立连接

常见问题

4. show 看见

系统

变量

数据库

事务锁

5. 备份

备份的分类

冷备、温备、热备

完全、增量、差异

物理备份、逻辑备份

物理备份 + 完全备份

备份

恢复

说明：

逻辑备份 + 增量备份

mysqldump 用法说明

备份

恢复

逻辑备份 的 大数据 备份工具

特性

安装

常用命令参数

备份

还原

6. 优化

查询优化

索引优化

配置优化

常见问题

1、mysql 的主键id默认自动升序，导致断层的情况：

你可能也喜欢

Mixed （自动模式，推荐使用）

查看当前 binlog 状态信息

实现原理

：

逻辑备份的大数据备份工具