06【MySQL命令、日志、MySQL集群】

第1章 MySQL 命令

1.1 MySQL帮助文档

我们在操作MySQL时，经常会忘记一些语法，比如show、alter、create等命令，或者想查询一些函数的使用、说明文档等。MySQL提供了一套非常完善的帮助文档工具，方便我们快捷的查询。

在翻阅非常多的官方语法手册时，经常会出现大括号、中括号、小括号里面包含参数，下列是具体说明：

{PROCEDURE | FUNCTION}：大括号代表必填，一般用于多个参数的选择

-- 示例语法:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

-- 删除存储过程
DROP PROCEDURE test_insert;

-- 删除存储函数
DROP FUNCTION test_insert;

[NO_WRITE_TO_BINLOG | LOCAL]：中括号代表是可选内容，可有可无

-- 示例语法:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

-- 优化表
OPTIMIZE TABLE userinfo;

-- 优化表不写入binlog日志
OPTIMIZE NO_WRITE_TO_BINLOG TABLE userinfo;

(key_part,...)：小括号代表必填选项

-- 示例语法:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]
    
-- 创建唯一索引
create unique index idx_name on userinfo(username);

1.1.1 查看根目录

如果不知道帮助文档能够提供些什么, 可以用? contents命令来显示所有可供查询的分类

mysql> ? contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql>

1.1.2 查询一级分类

我们可以在跟目录里面选择某个组进行详细查询

1）查询数据类型

mysql> ? Data Types;
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

mysql>

接着还可以查询组里面的某个具体的类型的详细介绍：

mysql> ? INT
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://dev.mysql.com/doc/refman/5.7/en/numeric-type-syntax.html


mysql>

2）查询表维护命令

mysql> ? table maintenance;
You asked for help about help category: "Table Maintenance"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ANALYZE TABLE
   CHECK TABLE
   CHECKSUM TABLE
   OPTIMIZE TABLE
   REPAIR TABLE

mysql>

还可以查询更详细的用法：

mysql> ? REPAIR TABLE;
Name: 'REPAIR TABLE'
Description:
Syntax:
REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE repairs a possibly corrupted table, for certain storage
engines only.

This statement requires SELECT and INSERT privileges for the table
....

使用表修复命令：

repair table userinfo;

1.1.3 查询任意命令语法

不仅可以根据分类查询，对于MySQL任何的命令查询都可以查询（?）

例如我们查看drop命令的详细用法：

mysql> ? drop;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   ALTER TABLESPACE
   DEALLOCATE PREPARE
   DROP DATABASE
   DROP EVENT
   DROP FUNCTION
   DROP FUNCTION UDF
   DROP INDEX
   DROP PREPARE
   DROP PROCEDURE
   DROP SCHEMA
   DROP SERVER
   DROP TABLE
   DROP TABLESPACE
   DROP TRIGGER
   DROP USER
   DROP VIEW

mysql>

查看删除存储过程：

mysql> ? drop procedure;
Name: 'DROP PROCEDURE'
Description:
Syntax:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

This statement is used to drop a stored procedure or function. That is,
the specified routine is removed from the server. You must have the
ALTER ROUTINE privilege for the routine. (If the
automatic_sp_privileges system variable is enabled, that privilege and
EXECUTE are granted automatically to the routine creator when the
routine is created and dropped from the creator when the routine is
dropped. See
https://dev.mysql.com/doc/refman/5.7/en/stored-routines-privileges.html
.)

The IF EXISTS clause is a MySQL extension. It prevents an error from
occurring if the procedure or function does not exist. A warning is
produced that can be viewed with SHOW WARNINGS.

URL: https://dev.mysql.com/doc/refman/5.7/en/drop-procedure.html

mysql>

删除存储过程：

drop procedure if exists test_insert;

1.1.4 命令练习

比如我们选择几个常用而且容易忘记命令查询：

alter：

mysql> ? alter
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER DATABASE
   ALTER EVENT
   ALTER FUNCTION
   ALTER INSTANCE
   ALTER LOGFILE GROUP
   ALTER PROCEDURE
   ALTER SCHEMA
   ALTER SERVER
   ALTER TABLE
   ALTER TABLESPACE
   ALTER USER
   ALTER VIEW
   GRANT
   SPATIAL INDEXES

mysql>

查询更详细的说明：

alter table class add PRIMARY KEY(id)

alter table class DROP PRIMARY KEY

mysql> ? alter table;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD CHECK (expr)
  | ALGORITHM [=] {DEFAULT | INPLACE | COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  .......

跟着文档做案例：

-- 给表添加一个新的列
alter table userinfo add column new_colnm varchar(20);

-- 给表移除一列
alter table userinfo drop column new_colnm;

-- 删除主键
alter table userinfo drop primary key;

-- 添加主键
alter table userinfo add constraint primary key(`id`);

show：

mysql> ? show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
......

跟着文档做几个小案例：

-- 查看建表语句
show create table userinfo;

-- 查看指定表有多少索引
show index from userinfo;

总之，MySQL提供的帮助文档，哪里不会"问"哪里（?）。

1.2 MySQL 常用命令

1.2.1 mysqldump

mysqldump命令主要用于备份 MySQL 数据库中的数据，可以将数据库导出为 SQL 脚本文件。

语法：

mysqldump [OPTIONS] database [tables]

参数：

options：
- -h：mysql服务器的IP（默认为127.0.0.1）
- -P：mysql服务器的端口（默认为3306）
- -u：mysql用户名
- -p：mysql密码
- -n（--no-create-db）：不包含创建数据库语句（包含建表语句和数据）
- -t（--no-create-info）：不包含创建表语句（只要插入语句）
- -d（--no-data）：不包含数据
- -B（--database）：导出数据库（包含建库语句也包含数据）
- -A（--all-databases）：导出所有数据库（会加上建库语句）
- -R, --routines：导出数据库中的存储过程和存储函数。
- -E, --events：导出数据库中的事件。
- --triggers：导出数据库中的触发器。

Tips：默认情况下将导出建表语句和表中的数据（不含建库语句）。

1）导出表：

先查看当前数据库有哪些表：

mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| class            |
| student          |
| userinfo         |
+------------------+
3 rows in set (0.00 sec)

导出表：

mysqldump -hip地址 -P端口 -u用户名 -p密码 数据库 表1 表2... > 文件路径

mysqldump -h127.0.0.1 -P3306 -uroot -padmin test01 student > /root/test.sql

如果没有写需要导出的表则默认导出库中所有的表：

mysqldump -uroot -padmin test01 > /root/test.sql

2）导出库：

mysqldump -u用户名 -p密码 --databases 库1 库2... > 文件路径

mysqldump -uroot -padmin --databases test01 > test.sql
mysqldump -uroot -padmin -B test01 > test.sql

Tips：导出库和导出表的区别在于导出库的sql语句里面加了create database db_name语句。

导出当前数据库服务器的所有数据库：

mysqldump -u用户名 -p密码 --all-databases > 文件路径

mysqldump -uroot -padmin --all-databases > /root/test.sql
mysqldump -uroot -padmin -A > /root/test.sql

3）指定条件导出：

只导出某张表的数据（不包含建表语句）：

mysqldump -uroot -padmin -t test01 userinfo > /root/test.sql

只导出指定数据库的所有表数据（不包建库、建表语句）：

mysqldump -uroot -padmin -t -n test01 > /root/test.sql

导出某张表结构（不包含表数据）：

mysqldump -uroot -padmin -d test01 userinfo > /root/test.sql

导出某个数据库的建库、建表语句（不包含表数据）：

mysqldump -uroot -padmin -d -B test01> /root/test.sql

导出当前数据库服务器的所有的建库、建表语句（不包含表数据）：

mysqldump -uroot -padmin -d -A> /root/test.sql

4）规则数据导出：

-T：设置导出的文件路径（secure_file_priv参数所设定的路径）。
--fields-terminated-by：数据列与列之间的分隔符（默认为一个制表位\t）。
--fields-enclosed-by：数据列左右的字符。

通过-T参数导出的数据会生成两份文件，一份是.sql，里面包含了表结构文件，另一份是.txt文件，里面是通过参数指定后生成的规则数据文件。通过secure_file_priv维护着导出的数据文件所在目录。

mysql> select @@secure_file_priv;
+-----------------------+
| @@secure_file_priv    |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.01 sec)

mysql>

Tips：通过上面的参数我们知道，-T参数导出的数据必须在/var/lib/mysql-files/目录。

示例：

mysqldump -uroot -padmin -T /var/lib/mysql-files/ --fields-terminated-by=',' --fields-enclosed-by='+' test userinfo

导出数据格式如下：

+1+,+c3e0e008-999e-11ea-943a-000c29a7676a+,+32+,+83188418748+,+0+,+c3e0e525-999e-11ea-943a-000c29a7676a+,+2020-05-19 15:02:55+,+2020-05-19 15:02:55+,+c3e0e56c-999e-11ea-943a-000c29a7676a+,+69+

示例：

mysqldump -uroot -padmin -T /var/lib/mysql-files/ --fields-terminated-by=',' test userinfo

导出数据格式如下：

1,c3e0e008-999e-11ea-943a-000c29a7676a,32,83188418748,0,c3e0e525-999e-11ea-943a-000c29a7676a,2020-05-19 15:02:55,2020-05-19 15:02:55,c3e0e56c-999e-11ea-943a-000c29a7676a,69

导出数据的格式是怎么样的，那么导入的数据格式应该和导出的数据格式一样：

load data local infile '/var/lib/mysql-files/userinfo.txt' into table userinfo fields terminated by ',' lines terminated by '\n';

1.2.2 数据恢复命令

1）mysql

准备数据：

use test;

insert into userinfo values(1,'aca850a8-98d7-11ea-90c1-000c29a7676a',69,'68226931307',0,'aca850c7-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca850cc-98d7-11ea-90c1-000c29a7676a',32);
insert into userinfo values(2,'aca8532e-98d7-11ea-90c1-000c29a7676a',85,'43250332533',1,'aca8535b-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca85362-98d7-11ea-90c1-000c29a7676a',15);
insert into userinfo values(3,'aca8547c-98d7-11ea-90c1-000c29a7676a',33,'8483425256',1,'aca85494-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca8549a-98d7-11ea-90c1-000c29a7676a',24);
insert into userinfo values(4,'aca85588-98d7-11ea-90c1-000c29a7676a',74,'6538729841',0,'aca8559e-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca855a3-98d7-11ea-90c1-000c29a7676a',92);
insert into userinfo values(5,'aca85990-98d7-11ea-90c1-000c29a7676a',87,'55627691983',0,'aca859bb-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca859c5-98d7-11ea-90c1-000c29a7676a',51);
insert into userinfo values(6,'aca85ae8-98d7-11ea-90c1-000c29a7676a',62,'50662035449',1,'aca85b02-98d7-11ea-90c1-000c29a7676a','2020-05-18 15:17:47.0','2020-05-18 15:17:47.0','aca85b07-98d7-11ea-90c1-000c29a7676a',64);

执行数据导入命令（实质上就是把里面的sql执行一遍）：

mysql -uroot -padmin < import.sql

2）source

source是属于mysql的命令，需要登录进mysql执行（实质也是把里面的sql执行一遍）。

mysql> source /root/import.sql;

这两种类型的导入只适用于临时的数据导入（数据量不会太大），如果数据量大，导入速度会非常慢

3）mysqlimport

mysqlimport命令是用于导入sql语句的一个命令。与前面两个数据导入不同，mysqlimport适用于导入规则数据。

注意的是，mysqlimport导入数据的所在目录也被secure_file_priv变量维护着，因此需要导入的数据必须在/var/lib/mysql-files/目录下。

准备100W数据使用mysqldump导出：

begin;
call test_insert(1000000);
commit;

使用mysqldump导出数据（耗时5秒钟，速度非常快）：

mysqldump -uroot -padmin -T /var/lib/mysql-files/ --fields-terminated-by=',' --fields-enclosed-by='+' test userinfo

将表清空：

truncate userinfo;

使用mysqlimport导入数据（耗时8秒钟，速度非常快）：

mysqlimport -uroot -padmin --fields-terminated-by=',' --fields-enclosed-by='+' test /var/lib/mysql-files/userinfo.txt

Tips：mysqlimport 命令的导入数据必须和 mysqldump 导入出来的数据格式一致（按什么规则导出，就按照什么规则导入）。

1.2.4 mysqlpump

mysqlpump 是 MySQL 提供的一个增强型的数据库备份工具，从 MySQL 5.7.12 版本开始引入。它旨在提供比传统的 mysqldump 更强大的功能和更好的性能，当需要对非常大的数据库进行备份时，mysqlpump 的并行处理能力可以大大减少备份时间。另外如果只需要备份特定的数据库、表或排除某些对象，mysqlpump 提供了丰富的选项来实现这一点。

mysqlpump的主要功能如下：

并行处理：mysqlpump 支持并行处理多个表或数据库，可以显著提高备份速度。
过滤选项：提供了更灵活的数据过滤选项，允许用户选择性地备份数据。
优化导出：在数据插入完成后创建二级索引，并且在dump文件最后完成唯一性检查和外键检查。

参数：

mysqlpump语法：

Usage: mysqlpump [OPTIONS] [--all-databases]
OR     mysqlpump [OPTIONS] --databases DB1 [DB2 DB3...]
OR     mysqlpump [OPTIONS] database [tables]

options：
- -u, --user=name: 指定连接到 MySQL 服务器的用户名。
- -p, --password[=name]: 指定用户的密码。如果直接跟在 -p 后面，则为明文密码；如果不指定密码，程序会提示输入。
- -h, --host=name: 指定主机名或 IP 地址，默认是 localhost。
- -P, --port=num: 指定端口号，默认是 3306。
- --include-databases=db_list: 只包含列出的数据库。
- --exclude-databases=db_list: 排除列出的数据库。
- --include-tables=tbl_list: 只包含列出的表。
- --exclude-tables=tbl_list: 排除列出的表。
- --max-rate=num: 设置每秒的最大写入字节数，限制输出速率。
- --default-parallelism=num: 设置默认的并行线程数。
- --events: 导出事件调度器状态（默认开启，使用--skip-events禁用）。
- --triggers: 包含触发器定义（默认开启，使用--skip-triggers禁用）。
- --routines: 包含存储过程和函数定义（默认开启，使用--skip-routines禁用）。
- --compress-output: 对输出文件启用压缩。

【使用示例】

# 导出所有数据库
mysqlpump -u用户名 -p密码 --all-databases > 文件路径

# 导出指定的数据库
mysqlpump -u用户名 -p密码 --databases 数据库1 数据库2 > 文件路径

# 导出指定数据库的表
mysqlpump -u用户名 -p密码 数据库 表1 表2 > 文件路径

使用mysqldump和mysqlpump分别导出，观察效果：

mysqldump -uroot -padmin test userinfo> /root/mysqldump_test.sql

mysqlpump -uroot -padmin test userinfo > /root/mysqlpump_test.sql

1.2.5 mysqlslap

mysqlslap是MySQL从5.1.4版开始的一个MySQL官方提供的压力测试工具。通过模拟多个并发客户端访问MySQL来执行压力测试，同时详细的提供了“高负荷攻击MySQL”的数据性能报告。并且能很好的对比多个存储引擎在相同环境下的并发压力性能差别。

语法：

mysqlslap [options]

参数说明：

options
- -u：你MySQL的用户名
- -p：你MySQL的密码
- --concurrency：模拟的并发数
- --create-schema：测试的数据库
- --query：执行的SQL语句脚本

MySQL默认的最大连接数为151，我们可以查看max_connections参数：

select @@max_connections;

我们可以编辑MySQL的配置文件（/etc/my.ini），将MySQL最大的连接数调整为2000

max_connections=2000

Tips：MySQL最大可调整的连接数为4190（根据MySQL参数调优、操作系统内核调优以及硬件版本等因素决定）

我们把表中的数据调整到10000条记录，测试1000并发情况下，观察MySQL并发情况：

根据id查询：

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test --query="select * from userinfo where id=1";

根据普通列查询（返回少量数据）：

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test --query="select * from userinfo where age=1";

根据普通列查询（返回大量数据）：

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test --query="select * from userinfo where gender='1'";

我们在表中给age列创建索引，然后发起1000请求进行测试：

create index idx_age on userinfo(age);

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test01 --query="select * from userinfo where age=1";

我们把当前的MySQL能够处理的最大连接数改为了2000，但这并不意味着MySQL实际能够处理2000的并发量，而是要根据当前测试的SQL语句复杂程度、是否使用索引、缓存以及当前安装MySQL机器的硬件等一系列指标并通过一系列测试来得到一个稳定的值。

有的时候可能SQL语句较为简单，数据量也较小，并发量能够突破2000。但也有可能数据大的情况下，也没有命中索引等一系列因素也会导致并发量不足2000。

MySQL内部维护了一个Max_used_connections状态值，它表示自服务器启动以来同时打开的最大连接数。这个值可以帮助我们了解数据库服务器在运行过程中曾经达到的最高并发连接数。

show global status like 'Max_used_connections';

每次启动MySQL服务器时，默认的Max_used_connections大小为1，当需要MySQL创建线程时（并发量高的情况下），MySQL内部会创建好线程，并存放在线程池中（并不会销毁），Max_used_connections记录的是MySQL服务器曾经创建的最大线程数。

通过观察Max_used_connections的值我们可以了解到MySQL实际运行的情况，如下：

性能监控：Max_used_connections 可以帮助你监控 MySQL 服务器的负载情况。如果发现这个值经常接近或等于 max_connections（最大允许连接数），这可能意味着你的服务器正在接近其连接处理能力的上限。
容量规划：通过分析 Max_used_connections 的历史数据，可以更好地规划未来的资源需求。例如，如果这个值持续增长，你可能需要增加 max_connections 或者考虑扩展硬件资源。
故障排除：当遇到性能问题时，查看 Max_used_connections 可以帮助你判断是否是由于连接数过多导致的问题。

Tips：Max_used_connections 是一个状态变量，它记录的是自 MySQL 服务器启动以来同时打开的最大连接数。这个值是单调递增的，也就是说，一旦达到某个最大值，即使之后并发量下降，这个值也不会减少。因此，Max_used_connections 的值不会随着当前并发量的减少而下降。每次服务器重启时，将会被重置为 0，并重新开始记录。

我们再次执行刚刚2000并发的测试，观察不同的SQL语句，MySQL使用了多少线程来处理2000并发：

重启MySQL服务

select * from userinfo where id=1：997连接

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test --query="select * from userinfo where id=1";

重启MySQL服务

select * from userinfo where age=1：1001连接

mysqlslap -uroot -padmin --concurrency=1000 --create-schema=test --query="select * from userinfo where age=1";

重启MySQL服务

select * from userinfo where gender='1'：1001连接

重启MySQL服务

select * from userinfo where age=1（删除索引后）：1002连接

1.2.6 mysqlshow

mysqlshow命令用于显示mysql数据库的一些信息，如当前有几个数据库、指定的数据库有什么表等信息。

查看有多少数据库：

[root@localhost ~]# mysqlshow -uroot -padmin;
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
[root@localhost ~]#

查看当前数据库服务器各数据库的行数：

[root@localhost ~]# mysqlshow -uroot -padmin --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| information_schema |     61 |        15181 |
| mysql              |     31 |         3067 |
| performance_schema |     87 |        43550 |
| sys                |    101 |         5380 |
| test               |      8 |        10002 |
+--------------------+--------+--------------+
5 rows in set.
[root@localhost ~]#

查看指定数据库有哪些表，并且列出每张表有多少行：

[root@localhost ~]# mysqlshow -uroot -padmin test --count;
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: test
+-----------------+----------+------------+
|     Tables      | Columns  | Total Rows |
+-----------------+----------+------------+
| class           |        2 |          3 |
| student         |        4 |          6 |
| t_emp           |        2 |          6 |
| t_order_detail  |        4 |          2 |
| t_orders        |        3 |          2 |
| user            |        3 |          2 |
| user_01         |        2 |          2 |
| user_02         |        2 |          3 |
| user_03         |        2 |          3 |
| user_all        |        2 |          8 |
| user_innodb     |        2 |          4 |
| user_memory     |        2 |          0 |
| user_myisam     |        2 |      50000 |
| userinfo        |       10 |         10 |
| userinfo_myisam |       10 |      10000 |
+-----------------+----------+------------+
15 rows in set.

1.2.7 mysql

MySQL 的客户端程序，用来连接数据库服务器并执行 SQL 命令或脚本文件。用户可以通过这个工具与数据库交互，执行查询、更新数据等操作。

查看mysql命令详细用法：

mysql -?
或者
mysql --help

语法：

mysql [OPTIONS] [database]

options：mysql命令的携带参数，常用参数如下：
- -h（--host=name）：mysql服务器的IP
- -P（--port）：mysql服务器的端口
- -u（--user=name）：mysql用户名
- -p（--password=name）：mysql密码
- -D（--database）：指定连接的数据库

完整语法：

简写：
mysql -h127.0.0.1 -P 3306 -uroot -padmin -D test

全写：
mysql --host=127.0.0.1 --port 3306 --user=root --password=admin --database=test

1.2.8 mysqladmin

mysqladmin命令用于临时执行某些操作，比如创建一个数据库，删除一个数据库，查看数据库状态等。

查看帮助文档：

mysqladmin -?

语法：

mysqladmin [OPTIONS] command command....

示例：

创建、删除数据库

[root@localhost ~]# mysqladmin -uroot -padmin create test01;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqladmin -uroot -padmin drop test01;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'test01' database [y/N] y
Database "test01" dropped
[root@localhost ~]#

查看数据库版本：

[root@localhost ~]# mysqladmin -uroot -padmin version;
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin  Ver 8.42 Distrib 5.7.30, for Linux on x86_64
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.30
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
Uptime:			5 hours 59 min 31 sec

Threads: 2  Questions: 734  Slow queries: 0  Opens: 260  Flush tables: 1  Open tables: 234  Queries per second avg: 0.034
[root@localhost ~]#

第2章 MySQL日志

日志是为我们记录软件工作状态的最佳工具，方便我们实时监控软件运行的状态，在出现错误时，也可以通过日志快速定位到错误的根源，在软件开发中，也可以根据日志快速定位到错误的地方，及时修改。MySQL也提供了一套完整的日志管理系统。在MySQL中，提供有4中不同的日志，分别为：错误日志、二进制日志、查询日志、慢查询日志等。

2.1 错误日志

错误日志记录着MySQL服务器的工作状态。包括启动、停止、运行过程中遇到的错误信息。MySQL默认开启错误日志，我们可以通过命令查询错误日志的存放位置：

mysql> select @@log_error;
+---------------------+
| @@log_error         |
+---------------------+
| /var/log/mysqld.log |
+---------------------+
1 row in set (0.00 sec)

mysql>

查看错误日志：

cat /var/log/mysqld.log

实时查看错误日志：

tail -f /var/log/mysqld.log

2.2 二进制日志

MySQL的二进制日志（Binary Log，简称binlog）主要用于记录所有对数据库执行的更改操作（如INSERT、UPDATE、DELETE等），但不包括SELECT和SHOW这类查询操作，二进制日志记录了对数据库的所有变更操作，常用于做数据恢复、主从复制等。

二进制日志的主要用途：

数据恢复：通过重放二进制日志中的事件，可以将数据库恢复到某个时间点的状态。
主从复制：在主从复制架构中，主库会将写入操作记录到二进制日志，然后从库读取这些日志并应用相同的操作来保持与主库的数据同步。
审计：管理员可以通过分析二进制日志来审查对数据库所做的变更。

MySQL的二进制文件分为三种格式，分别为statement、row、mixed，不同的格式会按照不同的方式来记录SQL日志。

新建测试表：

drop table if exists user;

create table user(
    id int primary key auto_increment,
    name varchar(300),
    age int
);

insert into user values(1,'小灰',18);

2.2.1 二进制文件格式

1）SBR

全称statement based replication：简称SBR，MySQL5.6的默认版本；该格式的二进制文件记录的是对结果集造成影响的SQL语句。注意，SBR格式仅仅记录的是这些对结果集造成影响的SQL语句。

优点：不需要记录每一行的变化，减少了binlog的日志量，节约IO，提高日志记录性能。
缺点：对于一些不确定的数据（如uuid()、now()）SBR格式将会导致主从复制中数据不一致的问题。

2）RBR

全称row based replicaton：简称RBR，MySQL5.7的默认格式；该格式的二进制日志文件记录的是数据的前后对比，记录每一行数据变更前和变更后的数据状态（真实的值），但在修改大数据量的情况下会让row格式的日志暴涨。

优点：可以清晰的记录每一行数据变更的细节
缺点：某个条件修改的数据如果非常多，那么row格式会将每条记录的修改前后的数据都记录到日志。

3）MBR

全称mixed based replication：简称MBR，MBR格式综合了SBR与RBR的优点。在MBR中，大多数情况下采用SBR来记录二进制日志，对于SBR无法记录的SQL语句（例如不确定的数据uuid(),now()等），将采用RBR来记录二进制日志。从 MySQL 5.7.7 开始，默认的二进制日志格式是 MBR。

2.2.2 开启二进制日志

1）测试SBR格式

MySQL默认是没有开启二进制日志的，需要我们手动开启。

修改配置文件（/etc/my.conf）：

# 服务id号
server_id=6666

# 开启binlog日志(设置binlog日志的路径和名称)
log_bin=Test-binlog

#配置二进制日志的格式
binlog_format=statement

# 二进制日志的过期时间
expire_logs_days=7

# 二进制日志文件的大小
max_binlog_size=1G

# 二进制日志缓冲区大小
binlog_cache_size=64K

# 二进制日志写入磁盘的频率
sync_binlog=1

server_id：服务id号，MySQL自己会默认分配一个，如果是MySQL5.7必须设置。
log_bin：开启binlog日志并设置binlog日志的位置和名称，Linux默认为/var/lib/mysql路径。
binlog_format：二进制文件的格式。
expire_logs_days：设置二进制日志文件的自动删除天数，默认为0，表示不自动删除。
max_binlog_size：设置单个二进制日志文件的最大大小，当达到这个大小时，MySQL 会创建一个新的二进制日志文件。默认为1GB
sync_binlog：控制二进制日志写入磁盘的频率。
- 0：不强制同步，由操作系统决定何时将数据写入磁盘。
- N > 1：每 N 个事务提交时，同步一次二进制日志到磁盘。
- 1：默认值，每次事务提交时都同步，提供最高的数据安全性。
binlog-ignore-db：指定不需要记录二进制日志的数据库，默认情况下所有的数据库的更改都会记录二进制日志。
binlog-do-db：指定需要记录二进制日志的数据库。
binlog_cache_size：一个事务中可能包含多个操作，默认情况下MySQL会将这些操作先写入binlog缓冲区而并不是立即写入磁盘，对于提高事务处理性能和减少磁盘 I/O 操作非常重要。binlog_cache_size参数用于设置缓冲区的大小，默认为32KB。

重启MySQL服务器：

systemctl restart mysqld

进入/var/lib/mysql目录，查看文件，发现多了两个文件：

binlog.000001：具体的binlog日志文件，每当MySQL服务器重启过后都会创建一个新的日志文件，名字为binlog.000002、binlog.000003....

binlog.index：记录当前有几个binlog文件。

[root@192 mysql]# cat Test-binlog.index
./Test-binlog.000001

或者执行：

mysql> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| Test-binlog.000001 |       154 |
+--------------------+-----------+
1 row in set (0.00 sec)

执行查看当前的bin-log日志的格式：

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

执行SQL语句：

select * from user;
update user set name='小灰帅锅' where id=1;

查询二进制日志：

mysqlbinlog Test-binlog.000001

查询二进制日志用的是mysqlbinlog命令。

再次执行一部分SQL语句，观察日志：

mysql> select * from user;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 小灰帅锅     |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

mysql> insert into user values(2,"小蓝",25);
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where id=2;
Query OK, 1 row affected (0.00 sec)

再次查看二进制文件：

mysqlbinlog Test-binlog.000001 |grep -v "#" |grep -v "/"

Tips：使用grep过滤掉"#"、"/"开头的行

发现只有update、delete、insert等语句，并没有select语句。

接着我们插入一条含有不确定数据的记录：

insert into user values(3,uuid(),20);

再次查看二进制文件：

mysqlbinlog Test-binlog.000001 |grep -v "#" |grep -v "/"

发现SBR格式日志中记录了原生的SQL语句，但如果其他数据库如果想要恢复这条数据，并不能通过执行这条SQL来恢复数据，这也是SBR格式的局限性。

2）测试RBR格式

修改mysql配置文件：

# 服务id号
server_id=6666

# 开启binlog日志
log_bin=Test-binlog

#配置二进制日志的格式
binlog_format=row

重启MySQL服务器：

[root@192 mysql]# systemctl restart mysqld
[root@192 mysql]# cat Test-binlog.index 
./Test-binlog.000001
./Test-binlog.000002
[root@192 mysql]#

发现多了一个日志文件，原因是因为mysql服务器每次重启都会生成一个新的日志文件，此后mysql客户端产生的所有日志都会记录到这个最新的日志文件中。

执行SQL语句：

mysql> select * from user;
+----+--------------------------------------+------+
| id | name                                 | age  |
+----+--------------------------------------+------+
|  1 | 小灰帅锅                             |   18 |
|  3 | 491d550a-7642-11ef-aa39-0050562ad697 |   20 |
+----+--------------------------------------+------+
2 rows in set (0.01 sec)

mysql> update user set name='小蓝帅锅' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看日志（查看row格式日志需要加上-vv参数）：

mysqlbinlog -vv Test-binlog.000002

再次执行一部分SQL语句，观察日志：

mysql> insert into user values(4,'小明',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 小灰帅锅     |   18 |
|  3 | 小蓝帅锅     |   20 |
|  4 | 小明         |   30 |
+----+--------------+------+
3 rows in set (0.00 sec)

mysql> update user set name="小明帅锅" where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from user where id=4;
Query OK, 1 row affected (0.00 sec)

查看日志：（注意：加上-vv 参数）

mysqlbinlog -vv Test-binlog.000002

...
BEGIN
### INSERT INTO `test`.`user`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小明' /* VARSTRING(900) meta=900 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
# at 719
COMMIT/*!*/;
BEGIN
'/*!*/;
### UPDATE `test`.`user`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小明' /* VARSTRING(900) meta=900 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小明帅锅' /* VARSTRING(900) meta=900 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
# at 1014
COMMIT/*!*/;
BEGIN
'/*!*/;
### DELETE FROM `test`.`user`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小明帅锅' /* VARSTRING(900) meta=900 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
# at 1291
#240919 12:57:30 server id 6666  end_log_pos 1322 CRC32 0xb3e67ebc      Xid = 14
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
...

对于一个SQL语句修改了多条记录的，row格式会把所有有变更的行记录全都记录下来

mysql> select * from user;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 小灰帅锅     |   18 |
|  3 | 小蓝帅锅     |   20 |
+----+--------------+------+
2 rows in set (0.00 sec)

mysql> update user set name="大家都是帅锅";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

查询日志：

在修改大数据量的情况下，应避免row格式。

3）测试MBR格式

修改mysql配置文件：

# 服务id号
server_id=6666

# 开启binlog日志
log_bin=Test-binlog

#配置二进制日志的格式
binlog_format=mixed

重启MySQL服务器：

[root@192 mysql]# systemctl restart mysqld
[root@192 mysql]# cat Test-binlog.index 
./Test-binlog.000001
./Test-binlog.000002
./Test-binlog.000003

对于一般的SQL语句，将采用SBR格式，即直接记录SQL语句：

mysql> select * from user;
+----+--------------------+------+
| id | name               | age  |
+----+--------------------+------+
|  1 | 大家都是帅锅       |   18 |
|  3 | 大家都是帅锅       |   20 |
+----+--------------------+------+
2 rows in set (0.00 sec)

mysql> update user set name="小灰" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set name="小蓝" where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into user values(4,"小绿",25);
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where id=4;
Query OK, 1 row affected (0.00 sec)

查看二进制日志（注意是查看Test-binlog.000003文件）：

mysqlbinlog Test-binlog.000003 |grep -v "#" |grep -v "/"

可以看到对于一般的操作，还是采用SBR格式。

但是如果SQL中出现了一些不确定的数据时，此时将会采用RBR格式来记录：

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小灰   |   18 |
|  3 | 小蓝   |   20 |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> insert into user values(4,uuid(),25);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+--------------------------------------+------+
| id | name                                 | age  |
+----+--------------------------------------+------+
|  1 | 小灰                                 |   18 |
|  3 | 小蓝                                 |   20 |
|  4 | 2589336b-7645-11ef-b13b-0050562ad697 |   25 |
+----+--------------------------------------+------+
3 rows in set (0.00 sec)

2.3 查询日志

MySQL 的查询日志（也称为常规查询日志或 General Query Log）记录了 MySQL 服务器接收到的所有 SQL 语句，包括客户端连接和断开连接的信息。查询日志对于调试、性能分析和安全审计非常有用。

默认情况下，查询日志默认也是关闭的，要我们手动开启。

在配置文件中添加如下配置：

[mysqld]
# 是否开启查询日志，0（默认值）不开启，1开启
general_log=1

# 查询日志的名称
general_log_file=Test-Query.log

重启MySQL服务器：

systemctl restart mysqld

执行一段SQL，观察查询日志的信息：

mysql> select * from user;
+----+--------------------------------------+------+
| id | name                                 | age  |
+----+--------------------------------------+------+
|  1 | 小灰                                 |   18 |
|  3 | 小蓝                                 |   20 |
|  4 | 2589336b-7645-11ef-b13b-0050562ad697 |   25 |
+----+--------------------------------------+------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+--------------------------------------+------+
| id | name                                 | age  |
+----+--------------------------------------+------+
|  1 | 小灰                                 |   18 |
|  3 | 小蓝                                 |   20 |
|  4 | 2589336b-7645-11ef-b13b-0050562ad697 |   25 |
+----+--------------------------------------+------+
3 rows in set (0.00 sec)

mysql> select * from user where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小灰   |   18 |
+----+--------+------+
1 row in set (0.00 sec)

mysql> update user set name = '小绿' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into user values(5,'小明',30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小灰   |   18 |
|  3 | 小蓝   |   20 |
|  4 | 小绿   |   25 |
|  5 | 小明   |   30 |
+----+--------+------+
4 rows in set (0.00 sec)

mysql> delete from user where id=5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小灰   |   18 |
|  3 | 小蓝   |   20 |
|  4 | 小绿   |   25 |
+----+--------+------+
3 rows in set (0.00 sec)

查看查询日志：

[root@192 mysql]# cat /var/lib/mysql/Test-Query.log 
/usr/sbin/mysqld, Version: 5.7.32-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2024-09-19T05:13:43.085677Z	    2 Connect	root@localhost on test using Socket
2024-09-19T05:13:43.086837Z	    2 Query	show databases
2024-09-19T05:13:43.087634Z	    2 Query	show tables
2024-09-19T05:13:43.087754Z	    2 Field List	user 
2024-09-19T05:13:43.091887Z	    2 Query	select * from user
2024-09-19T05:13:43.675995Z	    2 Query	select * from user
2024-09-19T05:13:57.598791Z	    2 Query	select * from user where id=1
2024-09-19T05:14:16.217344Z	    2 Query	update user set name = '小绿' where id=4
2024-09-19T05:14:30.868304Z	    2 Query	insert into user values(5,'小明',30)
2024-09-19T05:14:38.822788Z	    2 Query	select * from user
2024-09-19T05:14:45.252862Z	    2 Query	delete from user where id=5
2024-09-19T05:14:46.219012Z	    2 Query	select * from user
[root@192 mysql]#

2.4 慢查询日志

MySQL内部维护着一个变量long_query_time（默认10秒），如果一段SQL语句的执行时间超过了该变量，那么这条查询语句就被定义为慢查询语句，会被记录到慢查询日志当中。

mysql> select @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

mysql>

慢查询日志默认也是关闭的，需要我们手动开启

在MySQL配置文件中添加如下配置：

[mysqld]
# 是否开启慢查询日志,1,开启， 0,关闭(默认值)
slow_query_log=1 

# 指定慢查询日志文件的名称
slow_query_log_file=slow_query.log

# 设置一段查询语句超过多长时间才会被定义为慢查询(默认10s) 
long_query_time=1

重启MySQL服务器：

systemctl restart mysqld

插入150W数据做测试：

call test_insert(1500000);

执行一段SQL：

select * from userinfo;

select * from userinfo where username='1';

select * from userinfo where age=1;

select * from userinfo where id=1;

select * from userinfo;

查看慢日志：

[root@localhost mysql]# cat slow_query.log 
# Time: 2020-05-26T13:24:00.498579Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 2.869346  Lock_time: 0.000142 Rows_sent: 1500000  Rows_examined: 1500000
SET timestamp=1590499440;
select * from userinfo;
# Time: 2020-05-26T13:24:14.044915Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 1.473253  Lock_time: 0.040000 Rows_sent: 0  Rows_examined: 1500000
SET timestamp=1590499454;
select * from userinfo where username='1';
# Time: 2020-05-26T13:24:27.094149Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 1.255106  Lock_time: 0.000127 Rows_sent: 0  Rows_examined: 1500000
SET timestamp=1590499467;
select * from userinfo where age=1;
# Time: 2020-05-26T13:25:02.034569Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 2.262357  Lock_time: 0.000294 Rows_sent: 1500000  Rows_examined: 1500000
SET timestamp=1590499502;
select * from userinfo;
[root@localhost mysql]#

执行SQL语句消耗时间小于1秒的不会被慢查询日志记录。

直接查看的慢查询日志其他信息较多，我们可以借助mysqldumpslow命令来直观的查看慢查询日志：

[root@localhost mysql]# mysqldumpslow slow_query.log 

Reading mysql slow query log from slow_query.log
Count: 2  Time=2.57s (5s)  Lock=0.00s (0s)  Rows=1500000.0 (3000000), root[root]@localhost
  select * from userinfo

Count: 1  Time=1.43s (1s)  Lock=0.04s (0s)  Rows=0.0 (0), root[root]@localhost
  select * from userinfo where username='S'

Count: 1  Time=1.25s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  select * from userinfo where age=N

[root@localhost mysql]#

第3章表拆分

目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有两种：水平拆分和垂直拆分，两者各有利弊，适用于不同的情况。

表的拆分维度一般分为水平拆分与垂直拆分，两种拆分各有利弊

3.1 水平拆分

水平拆分是指数据表行的拆分，将数据量大的表拆分成若干个数据量小的表；

如图所示：

表一旦进行水平拆分之后，原来在一张表的数据变成了多表，那么在查询数据时必定会有所影响，比如一张有300W记录的用户表tb_user，为提高其查询效率我们把其分成3张表tb_user_1，tb_user_2，tb_user_3，在查询的时候，通常情况下，我们使用取模的方式来进行表的拆分；通过用ID取模的方法把数据分散到3张表内Id%3+1 = [1~3]，同样方法，更新、删除也是通过取模的方法来查询。

另外部分业务逻辑也可以通过地区，年份等字段来进行归档拆分；进行拆分后的表，只能满足部分查询的高效查询需求，这时我们就要在产品策划上，从界面上约束用户查询行为。比如我们是按年来进行归档拆分的，这个时候在页面设计上就约束用户必须要先选择年，然后才能进行查询；

3.2 垂直拆分

垂直拆分是指数据表列的拆分，把一张列比较多的表拆成若干列比较少的表。表的记录并不多，但是字段却特别多，表占用空间很大，检索表的时候需要执行大量的IO，降低了性能。这时需要把大的字段拆分到另一个表，拆分后的表与原表是一对一的关系。

如图所示：

垂直拆分原则：

我们知道垂直拆分表是将一张很多字段的表拆分为若干个较少字段的表，我们通常在垂直拆分表时会遵守一定的规则；

1）把不常用的字段单独放在一张表；

2）较大的列单独拆分出来；

3）经常组合查询的列放在一张表中；

4）所有拆分的表都建立外键与主表关联

3.3 表拆分的利弊

表在拆分完毕之后对查询速度是能够有所提升，带随之也会带来一定的问题

水平拆分：拆分后，对表的增、删、改、查等都得需要特定的方法来操作，这无疑是增加了业务上的复杂性

垂直拆分：要查询到一些主表上没有的列，必须使用关联查询来检索数据，提高了复杂性并且降低了一定的性能

第4章 MySQL的集群方案

4.1 读写分离架构

通常来说，我们的数据库相对而言都是"读多写少"，读的请求一般来说比较大，因此我们就提出一个概念，一个数据库作为主(Master)数据库，负责处理数据的写操作；其他的作为数据库从(Slave)，负责数据库的读操作；主从数据库之间做数据复制的操作，我们把这种架构成为读写分离架构；

从该系统架构中，可以看出：

1）数据库从之前的单节点变为多节点提供服务（提高服务器QPS）

2）主节点数据，同步到从节点数据

3）应用程序需要连接到2个数据库节点，并且在程序内部实现判断读写操作

主从之间的同步，是异步完成，也就意味着这是弱一致性，可能会导致，数据写入主库后，应用程序读取从库获取不到数据，或者可能会丢失数据，对于数据安全性要求比较高的应用是不合适的，该问题可以通过PXC集群解决

4.2 MySQL主从复制

4.2.1 主从复制原理

MySQL主从复制的原理：

1）master将数据改变记录到二进制日志(binary log)中

2）slave将master的binary log events拷贝到它的中继日志(relay log)

3）slave读取中继日志中的数据实现数据的恢复

MySQL主从复制的条件：

1、Master与Slave的数据库版本必须一致

2、Master与Slave在开始建立主从复制时的数据必须一致

3、Master必须开启二进制日志，Master与Slave的server_id必须都唯一

4、Master与Slave的server-uuid（在/var/lib/mysql/auto.cnf文件中保存）不能相同

4.2.2 搭建MySQL主从复制架构

修改server-uuid：

MySQL安装的时候就指随机生成了一个server-uuid，用于标识唯一一台主机，不可能重复，由于我们的虚拟机是克隆的，因此所有主机的MySQL的server-uuid都一致，我们可以手动修改server-uuid

手动修改：

server-uuid=a640dc79-dd6f-11ea-b060-000c29affa62			master
server-uuid=a640dc79-dd6f-11ea-b060-000c29affa63			slave01
server-uuid=a640dc79-dd6f-11ea-b060-000c29affa64			slave02

重启三台服务器

Master配置文件：

# 开启二进制日志
log_bin=binlog

# 设置serverid
server_id=1

#是否只读
    #1:代表只读 
    #0:代表读写(0默认值,此配置对于root用户无效)
read-only=0

#设置root的权限 (1 代表只读, 0 代表读写)
super_read_only=0

#忽略的数据, 指不需要同步的数据库
binlog-ignore-db=mysql

#指定同步的数据库(默认情况下,除了binlog-ignore-db忽略的数据都会同步)
#binlog-do-db=test

在Master中创建同步用户：

# 将MySQL的密码校验强度改为低风险
set global validate_password_policy=LOW;

# 修改MySQL的密码长度
set global validate_password_length=5;

# 授权用户slave01使用admin密码登录mysql
grant replication slave on *.* to 'slave01'@'192.168.133.145' identified by 'admin';

# 授权slave02使用admin密码登录mysql
grant replication slave on *.* to 'slave02'@'192.168.133.146' identified by 'admin';

# 刷新配置 
flush privileges;

# 查看master信息
show master status;

Slave01配置文件：

#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作
server_id=2 

read-only=1				# slave只允许读
super_read_only=1		# slave只允许读(针对于root用户)

#以下执行SQL： 
CHANGE MASTER TO 
master_host='192.168.133.138', 
master_user='slave01', 
master_password='admin', 
master_port=3306, 
master_log_file='binlog.000001', 
master_log_pos=4704;

在Slave01客户端执行：

#启动slave同步 
start slave;

#查看同步状态 
show slave status;

Slave02配置文件：

#指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作
server_id=3 

read-only=1				# slave只允许读
super_read_only=1		# slave只允许读(针对于root用户)

#以下执行SQL： 
CHANGE MASTER TO 
master_host='192.168.133.138', 
master_user='slave02', 
master_password='admin', 
master_port=3306, 
master_log_file='binlog.000001', 
master_log_pos=4704;

在Slave02客户端执行：

#启动slave同步 
start slave; 

#查看同步状态 
show slave status;

一般来说Slave_IO_Running和Slave_SQL_Running处于Yes状态，代表主从复制环境已经搭建成功；

在master中创建数据库、表、插入记录，观察slave是否有同步；

结束主从复制命令：

stop slave;

posted @ 2023-12-08 21:39 绿水长流*z 阅读(97) 评论(0) 收藏举报

刷新页面返回顶部

緑水長流*z

06【MySQL命令、日志、MySQL集群】

06【MySQL命令、日志、MySQL集群】

第1章 MySQL 命令

1.1 MySQL帮助文档

1.1.1 查看根目录

1.1.2 查询一级分类

1）查询数据类型

2）查询表维护命令

1.1.3 查询任意命令语法

1.1.4 命令练习

1.2 MySQL 常用命令

1.2.1 mysqldump

1）导出表：

2）导出库：

3）指定条件导出：

4）规则数据导出：

1.2.2 数据恢复命令

1）mysql

2）source

3）mysqlimport

1.2.4 mysqlpump

1.2.5 mysqlslap

1.2.6 mysqlshow

1.2.7 mysql

1.2.8 mysqladmin

第2章 MySQL日志

2.1 错误日志

2.2 二进制日志

2.2.1 二进制文件格式

1）SBR

2）RBR

3）MBR

2.2.2 开启二进制日志

1）测试SBR格式

2）测试RBR格式

3）测试MBR格式

2.3 查询日志

2.4 慢查询日志

第3章 表拆分

3.1 水平拆分

3.2 垂直拆分

3.3 表拆分的利弊

第4章 MySQL的集群方案

4.1 读写分离架构

4.2 MySQL主从复制

4.2.1 主从复制原理

4.2.2 搭建MySQL主从复制架构

公告

第3章表拆分