MySQL之数据库维护_mysql数据库维护

1 数据库维护

数据库维护是运维工程师或者DBA主要工作，包括性能监控、性能分析、性能调优、数据库备份和恢复等。

1.1 数据库文件

MySQL的每个数据库都对应存放在一个与数据库同名的文件夹中，MySQL数据库文件包括MySQL所建数据库文件和MySQL所用存储引擎创建的数据库文件。

1.1.1 MySQL创建并管理的数据库文件

.frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。
除了必有的.frm文件，根据MySQL所使用的存储引擎的不同（MySQL常用的两个存储引擎是MyISAM和InnoDB），存储引擎会创建各自不同的数据库文件

MyISAM数据库表文件：

• .MYD文件：即MY Data，表数据文件
• .MYI文件：即MY Index，索引文件
• .log文件：日志文件

InnoDB采用表空间（tablespace）来管理数据，存储表数据和索引，InnoDB数据库文件（即InnoDB文件集，ib-file set）：

• ibdata1、ibdata2等：系统表空间文件，存储InnoDB系统信息和用户数据库表数据和索引，所有表共用
• .ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引
• 日志文件： ib_logfile1、ib_logfile2

1.1.2 MySQL数据库存放位置

MySQL如果使用MyISAM存储引擎，数据库文件类型就包括.frm、.MYD、.MYI，默认存放位置是C:\Documentsand Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data

MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1、.ibd，.frm和.ibd文件默认存放位置是MySQL安装目录下的data文件夹

1.2 性能状态关键指标QPS和TPS

QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数
TPS，Transactions Per Second：每秒处理事务数
通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：

• Uptime：服务器已经运行的实际，单位秒
• Questions：已经发送给数据库查询数
• Com_select：查询次数，实际操作数据库的
• Com_insert：插入次数
• Com_delete：删除次数
• Com_update：更新次数
• Com_commit：事务次数
• Com_rollback：回滚次数

那么，计算方法来了，基于Questions计算出QPS：

mysql> show global status like 'Questions';
mysql> show global status like 'Uptime';
QPS = Questions / Uptime
1
2
3

基于Com_commit和Com_rollback计算出TPS：

mysql> show global status like 'Com_commit';
mysql> show global status like 'Com_rollback';
mysql> show global status like 'Uptime';
TPS = (Com_commit + Com_rollback) / Uptime
1
2
3
4

另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

mysql> show global status where Variable_name in('com_select','com_insert','com_delete','com_update');
1

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS

TPS计算方法：

mysql> show global status where Variable_name in('com_insert','com_delete','com_update');
1

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。
经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。

1.3 开启慢查询日志

MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

mysql> set global slow-query-log=on  #开启慢查询功能
mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #指定慢查询日志文件位置
mysql> set global log_queries_not_using_indexes=on;   #记录没有使用索引的查询
mysql> set global long_query_time=1;   #只记录处理时间1s以上的慢查询

1
2
3
4
5

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。

mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    
#查看最慢的前三个查询
1
2

也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。

分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log
分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql
pt-query-digest –type=binlog mysql-bin.000001.sql
分析普通日志：pt-query-digest –type=genlog localhost.log
1
2
3
4

1.4 数据库备份

备份数据库是最基本的工作，也是最重要的，否则后果很严重，但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。

1.4.1 myqldump示例

使用myqldump时，注意myqldump不是sql语句，mysqldump是mysql用于转存储数据库的实用程序

不能在MySQL可视化工具或者DOS里的mysql下直接执行。

1. 导出整个数据库
   导出文件默认是存在mysql\bin目录下

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql
1
2

2. 导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql
1
2

3. 导出一个数据库结构

mysqldump -u user_name -p -d -add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table
1
2

4. 带语言参数导出

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-optdatabase_name > outfile_name.sql
1

1.5 数据库修复

有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck

1.5.1 myisamchk修复

myisamchk： 只能修复myisam表，需要停止数据库
常用参数：

• -f –force 强制修复，覆盖老的临时文件，一般不使用
• -r –recover 恢复模式
• -q –quik 快速恢复
• -a –analyze 分析表
• -o –safe-recover 老的恢复模式，如果-r无法修复，可以使用此参数试试
• -F –fast 只检查没有正常关闭的表

快速修复weibo数据库:

cd /var/lib/mysql/weibo
myisamchk -r -q *.MYI
1
2

1.5.2 mysqlcheck修复

mysqlcheck： myisam和innodb表都可以用，不需要停止数据库，如修复单个表，可在数据库后面添加表名，以空格分割
常用参数：

• -a –all-databases 检查所有的库
• -r –repair 修复表
• -c –check 检查表，默认选项
• -a –analyze 分析表
• -o –optimize 优化表
• -q –quik 最快检查或修复表
• -F –fast 只检查没有正常关闭的表

快速修复weibo数据库:

mysqlcheck -r -q -uroot -p123 weibo
1

1.5.3 .frm文件修复

在MYSQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的.frm文件，.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，.frm文件跟数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为数据表名.frm，如user.frm。 .frm文件可以用来在数据库崩溃时恢复表结构。

1.5.3.1 InnoDB表结构的恢复

假定：MYSQL数据库已经崩溃，目前只有对应表的.frm文件，大家都知道，.frm文件无法通过文本编辑器查看，因为如果不恢复，基本上来说对我们没什么用。这里我们为了测试，假定该文件为test_innodb.frm

该表创建脚本如下(建表后就把该表所在数据库服务器给强行关闭，模拟崩溃场景)

mysql> create table test_innodb
    -> (A int(11) default NULL,
    -> B varchar(30) default NULL,
    -> C date default NULL) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
1
2
3
4
5

恢复方法介绍(过程)：

1. 在新的正常工作的MYSQL环境下建立一个数据库，比如aa
2. 在aa数据库下建立同名的数据表test_innodb，表结构随意，这里只有一个id字段，操作过程片段如下：

mysql> create table test_innodb (id bigint not null)engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+--------------+
| Tables_in_aa |
+--------------+
| test_innodb |
+--------------+
2 rows in set (0.00 sec)

mysql> desc test_innodb;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3. 把系统崩溃后留下的test_innodb.frm文件拷贝到此处正常数据库的数据目录aa下，覆盖掉下边同名的.frm文件
4. 重新启动MYSQL服务。
5. 测试下是否恢复成功，进入aa数据库，用desc命令测试下：

mysql> desc test_innodb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| A     | int(11)     | YES  |     | NULL    |       |
| B     | varchar(30) | YES  |     | NULL    |       |
| C     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9

OK，发现表结构已经恢复过来了。

1.5.3.2 MyISAM表结构的恢复

MyISAM类型的表恢复相对比较简单。
同样先假定需要恢复的表的FRM文件为test_myisam.frm,表结构为

mysql> create table test_myisam
    -> (A int(11) default NULL,
    -> B varchar(30) default NULL,
    -> C date default NULL) engine=myisam;
Query OK, 0 rows affected (0.05 sec)
1
2
3
4
5

恢复过程如下：

1. 直接将test_myisam.frm拷贝到正常数据库对应的数据目录下。这时测试

mysql> show tables;
+--------------+
| Tables_in_aa |
+--------------+
| test_innodb |
| test_myisam |
+--------------+
3 rows in set (0.00 sec)

mysql> desc test_myisam;
ERROR 1017 (HY000): Can't find file: 'test_myisam' (errno: 2)
1
2
3
4
5
6
7
8
9
10
11

发现只能通过show tables命令看见表名，但是表结构还是没有恢复，desc命令报错。

2. 在与test_myisam.frm同一目录建立以下2个文件，文件内容可以为空：test_myisam.MYD 表数据文件和test_myisam.MYI索引文件
3. 在MYSQL命令行使用MYSQL本身的数据表恢复命令repair命令恢复表，如下：

mysql> repair table test_myisam USE_FRM;
+-----------------+--------+----------+----------+
| Table           | Op     | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| aa.test_myisam | repair | status   | OK       |
+-----------------+--------+----------+----------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

根据结果可以知道,恢复命令执行成功，下边用desc命令测试下：

mysql> desc test_myisam;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| A     | int(11)     | YES  |     | NULL    |       |
| B     | varchar(30) | YES  |     | NULL    |       |
| C     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9

果然恢复成功了。
也可以用show create table命令测试下：

mysql> show create table test_myisam;
+--------------+-----------------------------------------------------------------
----------------------------------------------------------------------+
| Table        | Create Table
                                                                      |
+--------------+-----------------------------------------------------------------
----------------------------------------------------------------------+
| test_myisam | Create TABLE `test_myisam` (
  `A` int(11) DEFAULT NULL,
  `B` varchar(30) DEFAULT NULL,
  `C` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
1
2
3
4
5
6
7
8
9
10
11
12

在恢复MyISAM表结构时，提到MYD文件和MYI文件，这两个文件都专属于MyISAM存储引擎的，前者用来保存MyISAM表的数据，后者用来存放MyISAM表的索引信息。

1.6 MySQL恢复误删表而丢失的数据

相信后端研发的同学在开发过程经常会遇到产品临时修改线上数据的需求，如果手法很稳那么很庆幸可以很快完成任务，很不幸某一天突然手一抖把表里的数据修改错误或者误删了，这个时候你会发现各种问题反馈接踵而来。

1.6.1 查看是否开启binlog

保证 mysql 已经开启 binlog，查看命令：

查看binklog是否开启
show variables like '%log_bin%';

查看binlog存放日志文件目录
show variables like '%datadir%';
1
2
3
4
5

值为OFF，需开启，值为ON，表示已开启
如果没有开启 binlog ，也没有预先生成回滚SQL，那可能真的无法快速回滚了。对存放重要业务数据的 MySQL，强烈建议开启 binlog

1.6.2 查找日志文件

1.6.2.1 路径下查找

进入binlog文件目录，找出日志文件

1.6.2.2 命令下查找

查看下当前正在写入的 binlog 文件：

show master status;
1

也可以查看下当前有多少个 binlog 日志文件了：

show binary logs;
1

1.6.3 通过mysqlbinlog查看DML记录

1.6.3.1 在mysql服务器内

切换到mysqlbinlog目录，当线上数据出现错误的时候首先可以询问具体操作人记录时间点，这个时候可以借助mysql自带的binlog解析工具mysqlbinlog，具体位置在mysql 安装目录 **/mysql/bin/下

通过 mysqlbinlog 工具命令查看数据库增删改查记录（必须切换到 mysqlbinlog 目录才有效）

例子1：查询2022-11-12 09:00:00到2022-11-13 20:00:00 数据库为 test 的操作日志，输入如下命令将数据写入到一个备用的txt文件中

 mysqlbinlog --no-defaults --database=test --start-datetime="2022-11-12 09:00:00" --stop-datetime="2022-11-13 20:00:00" /data/mysql/mysql-bin.000015    > template_coupon_tb_product_category.txt
1

例子2：查询2022-11-12 09:00:00到2022-11-13 20:00:00 数据库为 test 的操作日志，并输出到屏幕上

mysqlbinlog --no-defaults --database=test --start-datetime="2022-11-12 09:00:00" --stop-datetime="2022-11-13 20:00:00" /data/mysql/mysql-bin.000015   |more
1

例子3：查询2022-11-12 09:00:00到2022-11-13 20:00:00 数据库为 test 的操作日志，并且过滤出 只包括 template_coupon_tb_product_category 表数据的操作记录 ，输入如下命令将数据写入到一个备用的txt文件中

mysqlbinlog --no-defaults --database=test--start-datetime="2022-11-12 09:00:00" --stop-datetime="2022-11-13 20:00:00" /data/mysql/mysql-bin.000015   | grep template_coupon_tb_product_category   > template_coupon_tb_product_category.txt
1

1.6.3.2 用命令查看

在 SQL 控制台查看下 binlog 信息：

show binlog events in 'binlog.000002';
1

这样还不能看出刚才修改前的内容，下面就要使用 mysqlbinlog 工具进行分析了，首先进入到 binlog 日志的位置，我们根据时间点过滤下：

mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2022-03-13 19:00:00" --stop-datetime="2022-03-13 19:10:00" ./binlog.000002 
1

1.6.4 mysqlbinlog 命令

mysqlbinlog 命令的语法格式：

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

---

参数选项解释：

• start-position=875：起始pos点
• stop-position=954：结束pos点
• start-datetime="2022-9-25 22:01:08"：起始时间点
• stop-datetime="2022-9-25 22:09:46"：结束时间点
• database=test：指定只恢复test数据库(一台主机上往往有多个数据库，只限本地log日志)
• no-defaults：表示不使用配置文件中（ my.cnf 里配的 [client] ）的参数，可以避免有些 mysqlbinlog 没有的参数导致的失败
• base64-output=decode-rows：解码方式，不加的话看到的是 base64 之后的
• v 显示 sql 语句，也可以 vv 显示 sql 语句和类型
• r：可以将结果输出到文件中
• -u 或--user=name：连接到远程主机的用户名
• -p 或--password[=name]：连接到远程主机的密码
• -h 或--host=name：从远程主机上获取binlog日志
• --read-from-remote-server：从某个MySQL服务器上读取 binlog 日志