MySQL-运维篇 初识

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

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

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

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)

修改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.

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

[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 二进制日志 介绍

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

作用： 灾难时的数据恢复 MySQL的主从复制 语法

show variables like '%log_bin%';

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

案例 日志文件位置

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 |   索引

-- 看下文件
[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

日志格式为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

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

日志格式为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

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

show variables like '%binlog_format%';

案例

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

日志删除 原因：比较繁忙的业务系统，每天生成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 |
+----------------------------+---------+

案例

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

[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.3 查询日志 介绍 概念：包含了客户端所有(增删改查)的操作语句，而二进制日志不包含查询数据的SQL语句。默认关闭 语法

show variables like '%general%';

案例 查看是否开启

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)

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

[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

看下有没有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      还真有

查看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.3 慢查询日志

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

但仍需补充

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

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

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

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

2 主从复制 2.1 概述 含义：

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

MySQL支持一台主库同时向多台从库进行复制，从库也可以变成其他服务器的主库，实现链状复制。主库叫Matser，从库叫Slave。 作用 主库出现问题，可以快速切换到从库提供服务 实现读写分离，降低主库的访问压力，读在从库，写在主库 可以在从库中执行备份，以免备份的时候影响主库服务 2.2 原理 master主库的事务提交增删改会写到日志文件binlog中 从库slave有两个线程，一个叫做IOthread，会发出请求连接master数据库，去读master中的binlog，然后写入到自身的中继日志relay log中 slave有另外一个线程，叫做SQLthread，主要负责读取刚刚IOthread将master的binlog转换成的relay log的数据，将这份日志中的数据变化，在反应到自身的数据库的变化

2.3 搭建

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

主库配置 修改配置文件 /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

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