佛山优化企业网站排名平台,十大互联网培训机构,网络广告推广计划方案,学管理培训班去哪里学MySQL数据库入门到精通——运维篇#xff08;1#xff09; 1. 日志1.1 错误日志1.2 二进制日志1.3 查询日志1.4 慢查询日志 2. 主从复制2.1 主从复制的概述2.2 主从复制的原理2.3 主从复制的搭建2.3.1 服务器准备2.3.2 主库配置2.3.3 从库配置2.3.4 测试 1. 日志
在任何一种… MySQL数据库入门到精通——运维篇1 1. 日志1.1 错误日志1.2 二进制日志1.3 查询日志1.4 慢查询日志 2. 主从复制2.1 主从复制的概述2.2 主从复制的原理2.3 主从复制的搭建2.3.1 服务器准备2.3.2 主库配置2.3.3 从库配置2.3.4 测试 1. 日志
在任何一种数据库中都会有各种各样的日志这些日志记录了数据库运行的各个方面。可以帮助数据库管理员追踪数据库曾经发生的一些事情。
对于MySQL数据库提供了四种不同的日志帮助我们追踪。
错误日志二进制日志查询日志慢查询日志
1.1 错误日志
错误日志是MySQL中最重要的日志之一它记录了当mysqld(MySQL服务)启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。 当数据库出现任何故障导致无法正常使用时建议首先查看此日志。 该日志是默认开启的默认存放目录/var/logl默认的日志文件名为psmysqld.log。查看日志位置
show variables like %log_error%
C:\Users\HPmysql -uroot -p
Enter password: **************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.33 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql show variables like %log_error%;
--------------------------------------------------------------------
| Variable_name | Value |
--------------------------------------------------------------------
| binlog_error_action | ABORT_SERVER |
| log_error | .\DESKTOP-JL38L7P.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
--------------------------------------------------------------------
5 rows in set, 1 warning (0.08 sec)打开第二个控制台 输入tail -f /var/log/mysqld.log
这样就可以在第二个控制台中实时查看错误日志。 1.2 二进制日志
二进制日志(BINLOG) 记录了所有的DDL (数据定义语言)语句和DML (数据操纵语言)语句但不包括数据查询SELECT、 SHOW 语句。
作用
①.灾难时的数据恢复; ②. MySQL的主从复制。
在MySQL8版本中默认二进制日志是开启着的涉及到的参数如下: show variables like %log_bin%
mysql show variables like %log_bin%;
-------------------------------------------------------------------------------------------------------
| Variable_name | Value |
-------------------------------------------------------------------------------------------------------
| log_bin | ON |
| log_bin_basename | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-JL38L7P-bin |
| log_bin_index | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-JL38L7P-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
-------------------------------------------------------------------------------------------------------
6 rows in set, 1 warning (0.00 sec)涉及两类文件索引文件、日志文件
日志格式
MySQL服务器中提供了多种格式来记录二进制日志具体格式及特点如下
日志格式含义STATEMENT基于SQL语句的日志记录记录的是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, 1 warning (0.00 sec)我们现在想要查看日志但是二进制对于我们来讲是无意义的。
所以学一个日志查看 由于日志是以二进制方式存储的不能直接读取,需要通过二进制日志查询工具mysqlbinlog来查看具体语法:
mysqlbinlog [ 参数选项] logfilename
参数选项:-d 指定数据库名称只列出指定的数据库相关操作。-o 忽略掉日志中的前n行命令。-v 将行事件(数据变更)重构为SQL语句-vv 将行事件(数据变更)重构为SQL语句并输出注释信息mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mybatis |
| mysql |
| performance_schema |
| sakila |
| sys |
| test |
| tlias |
| world |
--------------------
9 rows in set (0.03 sec)mysql use test
Database changed
mysql show tables;
----------------
| Tables_in_test |
----------------
| account |
| course |
| dept |
| emp |
| emp2 |
| stu |
| student |
| student_course |
| tb_user |
| tb_user_edu |
| user |
----------------
11 rows in set (0.01 sec)mysql select * from tb_user_edu;
----------------------------------------------------------------------------
| id | degree | major | primaryschool | middleschool | university | userid |
----------------------------------------------------------------------------
| 1 | 本科 | 舞蹈 | 静安区第一小学 | 静安区第一中学 | 北京舞蹈学院 | 1 |
| 2 | 硕士 | 表演 | 朝阳区第一小学 | 朝阳区第一中学 | 北京电影学院 | 2 |
| 3 | 本科 | 英语 | 杭州市第一小学 | 杭州市第一中学 | 杭州师范大学 | 3 |
| 4 | 本科 | 应用数学 | 阳泉第一小学 | 阳泉区第一中学 | 清华大学 | 4 |
----------------------------------------------------------------------------
4 rows in set (0.02 sec)mysql update tb_user_edu set university 北京大学;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql日志删除
对于比较繁忙的业务系统每天生成的binlog数据巨大如果长时间不清除将会占用大量磁盘空间。可以通过以下几种方式清理日志;
指令含义reset master删除全部binlog日志删除之后日志编号将从binlog.000001重新开始purge master logs to binlog. ****删除*****编号之前的所有日志purge master logs before ‘yyy-mm-dd hh24:mi:ss’删除日志为yyy-mm-dd hh24:mi:ss之前产生的所有日志
也可以在mysql的配置文件中配置二进制日志的过期时间设置了之后,二进制日志过期会自动删除。
show variables like %binlog_expire%; 1.3 查询日志
查询日志中记录了客户端的所有操作语句而二进制日志不包含查询数据的SQL语句。默认情况下查询日志是未开启的。如果需要开启查询日志可以设置以下配置︰
show variables like %general%;
mysql show variables like %general%;
---------------------------------------
| Variable_name | Value |
---------------------------------------
| general_log | OFF |
| general_log_file | DESKTOP-JL38L7P.log |
---------------------------------------
2 rows in set, 1 warning (0.00 sec)1.4 慢查询日志
慢查询日志记录了所有执行时间超过参数long_ query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志默认未开启。 long_query_time 默认为10秒最小为0精度可以到微秒。 #慢查询日志
slow_ query_ log1
#执行时间参数
long_query_time2默认情况下不会记录管理语句也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes, 如下所述。
#记录执行较慢的管理语句
log_slow_admin_statements 1
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes 1总结
错误日志 主要记录的是MYSQL服务启动及运行过程中的发生的一些错误、异常。那么通过错误日志我们可以排查数据库运行在哪里报错了从而定位错误解决问题。二进制日志 主要记录的是DDL与DML语句通过二进制日志我们可以完成数据的恢复那么对于Mysql的主从复制也是基于二进制日志的其中数据格式有三种实际就两种默认ROW。查询日志 二进制日志中没有记录对应的查询语句查询日志记录的所有的语句。这份日志会很大所以默认是关闭的。慢查询日志 定位执行效率比较低的语句。
2. 主从复制
2.1 主从复制的概述
主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中然后在从库上对这些日志重新执行(也叫重做) 从而使得从库和主库的数据保持同步。 MySQL支持一台主库同时向多台从库进行复制从库同时也可以作为其他从服务器的主库 实现链状复制。
MySQL复制的有点主要包含以下三个方面
主库出现问题可以快速切换到从库提供服务实现读写分离降低主库的访问压力如果增删改对主库 查询对从库可以在从库中执行备份以避免备份期间影响主库服务。 2.2 主从复制的原理
原理无非就是 主库如何同步到从库中 主从复制基于二进制文件
从上图来看复制分成三步
Master主库在事务提交时会把数据变更记录在二进制日志文件Binlog中。从库读取主库的二进制日志文件Binlog写入到从库的中继日志Relay Log。slave重做中继日志中的事件将改变反映它自己的数据。 2.3 主从复制的搭建
主从复制的搭建步骤
准备主从复制服务器环境完成主库配置完成从库配置
2.3.1 服务器准备 准备好两台服务器之后在上述的两台服务器中分别安装好MySQL并完成基础的初始化准备工作。
2.3.2 主库配置
修改配置文件/etc/my.cnf
#mysql服务ID保证整个集群环境中唯一 取值范围: 1 - 2^{32}-1默认为1
server-id1
#是否只读,1代表只读, 0代表读写
read-only0
#忽略的数据指不需要同步的数据库
#binlog-ignore-dbmysql
#指定同步的数据库
#binlog-do-dbdb01重启MySQL服务器 systemctl restart mysqld 登录mysql,创建远程连接的账号,并授予主从复制权限
#创建itcast用户并设置密码该用户可在任意主机连接该MySQL服务
CREATE USER itcast% IDENTIFIED WITH mysqL native_password BY Root123456 ;
#为itcast%用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO itcast%;2.3.3 从库配置
修改配置文件/etc/my.cnf
#mysql服务ID保证整个集群环境中唯一 取值范围: 1 - 2^{32}-1和主库不一样即可
server-id2
#是否只读,1代表只读, 0代表读写
read-only1重启MySQL服务器 systemctl restart mysqld 登录Mysql设置主库配置
参数名含义8.0.23之前SOURCE_HOST主库IP地址MASTER_HOSTSOURCE_USER连接主库的用户名MASTER_USERSOURCE_PASSWORD连接主库的密码MASTER_PASSWORDSOURCE_LOG FILEbinlog日志文件名MASTER LOG_FILESOURCE_LOG POSbinlog日志文件位置MASTER_LOG_POS
开启同步操作
start replica ;
#8.0.22之后
start slave ;
#8.0.22之前2.3.4 测试
在主库上创建数据库、表并插入数据
create database db01;use db01;create table tb_user(id int(11) not null,name varchar(50) not null,sex varchar(1),primary key (id)
)engineinnodb default charsetutf8;insert into tb_user(id,name,sex) values(1,Tom,1);
insert into tb_user(id,name,sex) values(2,Trigger,0);
insert into tb_user(id,name,sex) values(3,Dawn,1);insert into tb_user(id,name,sex) values(4,Jack Ma,1);
insert into tb_user(id,name,sex) values(5,Coco,0);
insert into tb_user(id,name,sex) values(6,Jerry,1);在从库中查询数据验证主从是否同步
