MySQL-运维篇 初识

1 日志

1.1 错误日志

1. 介绍

• 概念：记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库发生故障的时候，建议优先查看次日志。

2. 语法

show variables like '%log_error%'
关联文件是/var/log/mysqld.log    
1
2

3. 案例

• 查看错误日志对应的文件位置

mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

• 修改mysql服务的id配置文件

-- 修改配置文件
[root@hadoop ~]# vim /var/lib/mysql/auto.cnf
server-uuid=175f2c08-651f-11ec-967b-000c29569efa

-- 重启mysqld服务，然后报错
[root@hadoop ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
1
2
3
4
5
6
7

• 查看错误日志，里面有详情

[root@hadoop ~]# tail -f /var/log/mysqld.log
2022-08-29T19:16:57.818881Z 0 [ERROR] [MY-010073] [Server] The server_uuid stored in auto.cnf file is not a valid UUID.
2022-08-29T19:16:57.818932Z 0 [ERROR] [MY-010076] [Server] Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID.
2022-08-29T19:16:57.818959Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-29T19:16:57.819421Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.26)  MySQL Community Server - GPL.
1
2
3
4
5

1.2 二进制日志

1. 介绍

• 概念：二进制binlog记录了所有DDL和DML语言，但不包括select、show语句

• 作用：

  • 灾难时的数据恢复
  • MySQL的主从复制

2. 语法

show variables like '%log_bin%';
1

mysqlbinlog [ 参数选项 ] logfilename
参数选项：
-d 指定数据库名称，只列出指定的数据库相关操作。
-o 忽略掉日志中的前n行命令。
-v 将行事件(数据变更)重构为SQL语句
-vv 将行事件(数据变更)重构为SQL语句，并输出注释信息
1
2
3
4
5
6

3. 案例

• 日志文件位置

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |	默认开启状态
| log_bin_basename                | /var/lib/mysql/binlog       |	文件放置地
| log_bin_index                   | /var/lib/mysql/binlog.index |	索引
1
2
3
4
5
6
7

-- 看下文件
[root@hadoop ~]# cd /var/lib/mysql
[root@hadoop mysql]# ll
total 322960
-rw-r----- 1 mysql mysql       56 Aug 30 03:20 auto.cnf
-rw-r----- 1 mysql mysql      179 Aug 15 04:43 binlog.000001
-rw-r----- 1 mysql mysql      179 Aug 15 05:55 binlog.000002
-rw-r----- 1 mysql mysql      179 Aug 15 06:01 binlog.000003
-rw-r----- 1 mysql mysql      208 Aug 30 03:20 binlog.index

-- 看下索引文件
[root@hadoop mysql]# cat binlog.index 
./binlog.000001
./binlog.000002
./binlog.000003
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 日志格式为ROW下：改变行数据并查看二进制文件

mysql> update course set name = 'PHP' where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
3

[root@hadoop mysql]# mysqlbinlog -v binlog.000013 			-v 将行事件(数据变更)重构为SQL语句
### UPDATE `itcast`.`course`
### WHERE
###   @1=2					-- 更新前
###   @2='SpringBoot'
### SET						-- 更新后
###   @1=2	
###   @2='PHP'
# at 444
1
2
3
4
5
6
7
8
9

• 日志格式为statement下：改变行数据并查看二进制文件

-- 去mysql的配置文件修改日志格式

[root@hadoop mysql]# vim /etc/my.cnf
#将日志格式换成statment
binlog_format=STATEMENT


-- 修改行数据
mysql> update course set name = 'SpringBoot' where id =2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0


-- 查看二进制文件
[root@hadoop mysql]# mysqlbinlog binlog.000014			不用-v，因为本身就记录了sql语句
update course set name = 'SpringBoot' where id =2		select不记录，就增删改记录，DDL和DML会
/*!*/;
# at 469
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4. 日志格式

• statement：基于SQL语句的日志记录，记录的是SQL语句，对数据的进行修改都记录在日志文件中
• row：基于行的日志，记录的是每一行数据变更(默认),变更前后都会记录
• mixed：混合了statement和row格式，采用采用statement，某些特殊情况自动转换为row进行记录
• 语法

show variables like '%binlog_format%';
1

• 案例

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

5. 日志删除

• 原因：比较繁忙的业务系统，每天生成binlog数据巨大，如果长时间不清除，将会占大量磁盘空间
• 语法
  • reset master :删除全部binlog日志，删除后，binlog还是会从binlog.000001重新开始
  • purge master logs to ‘binlog.编号’：删除指定编号之前的所有日志
  • purge master logs before ‘yyyy-mm-dd hh24:mi:ss’：删除日志为‘yyyy-mm-dd hh24:mi:ss’之前产生的日志
  • 也可以在以mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除

mysql> show variables like '%binlog_expire_logs_seconds%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
+----------------------------+---------+
1
2
3
4
5
6

• 案例

mysql> purge master logs to 'binlog.000002';
Query OK, 0 rows affected (0.01 sec)
1
2

[root@hadoop mysql]# ll
total 322948
-rw-r----- 1 mysql mysql       56 Aug 30 03:20 auto.cnf
-rw-r----- 1 mysql mysql      179 Aug 15 05:55 binlog.000002
-rw-r----- 1 mysql mysql      179 Aug 15 06:01 binlog.000003
1
2
3
4
5

1.3 查询日志

1. 介绍

• 概念：包含了客户端所有(增删改查)的操作语句，而二进制日志不包含查询数据的SQL语句。默认关闭

2. 语法

show variables like '%general%';
1

3. 案例

• 查看是否开启

mysql> show variables like '%general%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | OFF                       |
| general_log_file | /var/lib/mysql/hadoop.log |
+------------------+---------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

• 更改查询日志的默认关闭，打开查询日志并修改查询日志名称

[root@hadoop mysql]# vim /etc/my.cnf
#开启查询日志
general_log=1
#修改查询日志文件名称
general_log_file=/var/lib/mysql/mysql_query.log
[root@hadoop mysql]# systemctl restart mysqld
1
2
3
4
5
6

• 看下有没有mysql_query.log文件

[root@hadoop mysql]# cd /var/lib/mysql
[root@hadoop mysql]# ll
-rw-r----- 1 mysql mysql      179 Aug 30 04:49 mysql_query.log		还真有
1
2
3

• 查看mysql_query.log的内容

[root@hadoop mysql]# tail -f mysql_query.log
/usr/sbin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2022-08-29T20:54:39.492052Z	    8 Connect	root@localhost on  using Socket
2022-08-29T20:54:39.492461Z	    8 Init DB	itcast
2022-08-29T20:54:39.493048Z	    8 Query	show databases
2022-08-29T20:54:39.495374Z	    8 Query	show tables
2022-08-29T20:54:44.542363Z	    8 Query	show tables
2022-08-29T20:54:57.807081Z	    8 Query	select * from course
2022-08-29T20:55:49.831565Z	    8 Query	update course set name = 'redis' where id = 2
1
2
3
4
5
6
7
8
9
10
11

1.3 慢查询日志

不重复记了，回头去看进阶 2 索引中的慢查询日志的相关操作

但仍需补充

1. 默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。

   需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

   可以使用 log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes

#记录执行较慢的管理语句
log_slow_admin_statements =1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
1
2
3
4

2 主从复制

2.1 概述

1. 含义：

• 主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库中，然后在从库上对这些日志重新执行(重做)，使得从库和主库的数据保持同步

• MySQL支持一台主库同时向多台从库进行复制，从库也可以变成其他服务器的主库，实现链状复制。主库叫Matser，从库叫Slave。

2. 作用

• 主库出现问题，可以快速切换到从库提供服务
• 实现读写分离，降低主库的访问压力，读在从库，写在主库
• 可以在从库中执行备份，以免备份的时候影响主库服务

2.2 原理

1. master主库的事务提交增删改会写到日志文件binlog中
2. 从库slave有两个线程，一个叫做IOthread，会发出请求连接master数据库，去读master中的binlog，然后写入到自身的中继日志relay log中
3. slave有另外一个线程，叫做SQLthread，主要负责读取刚刚IOthread将master的binlog转换成的relay log的数据，将这份日志中的数据变化，在反应到自身的数据库的变化
   

2.3 搭建

1. 配置基础：两台服务器，并且开放指定的端口3306，或者直接关闭服务器防火墙，并且两台服务器都安装好了MySQL

2. 主库配置

• 修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=1
#是否只读,1 代表只读, 0 代表读写
read-only=0
#忽略的数据, 指不需要同步的数据库       不设置表示所有的数据库都要进行同步
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01
1
2
3
4
5
6
7
8

[root@hadoop ~]# vim /etc/my.cnf
#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=1
#是否只读,1 代表只读, 0 代表读写
read-only=0
1
2
3
4
5

再往后真心肝不动了。。。。。。。。。。。。。。

后面的全部在改配置文件，对于动手能力不行的我直接劝退了，可以肝，但暂时没必要，暂时再鸽一下，希望有朝一日可以补全