MySQL基础知识

1、MySQL帮助命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
mysql> help 

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file

For server side help, type 'help contents'

2、查看数据库

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

3、查看当前数据库

1
2
3
4
5
6
7
mysql> select  database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

4、查看当前用户

1
2
3
4
5
6
7
mysql> select  user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

5、修改提示符

临时修改 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@Rocky8-11 ~]# export MYSQL_PS1="\\r:\\m:\\s(\\u@\\h) [\\d]\\_"

[root@Rocky8-11 ~]# mysql -uroot -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.30 Source distribution

Copyright (c) 2000, 2022, 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.

03:49:30(root@localhost) [(none)]

永久修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@Rocky8-11 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]\\_"
 
[root@Rocky8-11 ~]# mysql --print-defaults -v
mysql would have been started with the following arguments:
--prompt=\r:\m:\s(\u@\h) [\d]\_ -v 

[root@Rocky8-11 ~]# mysql -uroot -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.30 Source distribution

Copyright (c) 2000, 2022, 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.

03:56:00(root@localhost) [(none)]

6、MySQL自动登录

1
2
3
4
[root@Rocky8-11 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
[client]
user=root
password=654321

7、MySQL用户账号

MySQL用户账号由两部分组成

‘USERNAME‘@’HOST’

1
2
3
4
root@'localhost'          ## 本地登录账号
xcjyc@'10.0.0.11'         ## 指定IP
xcjyc@'10.0.0.%'          ## 匹配指定网段 
xcjyc@'%'                 ## 匹配所有地址

8、创建用户/授权

1
2
3
4
5
6
7
8
04:39:29(root@localhost) [(none)]  create user test@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

04:39:44(root@localhost) [(none)] grant all on hellodb.* to test@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

早期版本一条命令创建+授权：
grant all on hellodb.* to test@'10.0.0.%' identified by '123456'

9、修改密码

1
2
3
4
5
mysql> set password for root@'localhost'='654321';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER root@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

10、遗忘root密码

1
2
3
4
5
6
[root@Rocky8-11 ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
skip-networking
[root@Rocky8-11 ~]# systemctl restart mysqld
[root@Rocky8-11 ~]# mysql
1
2
3
mysql> update mysql.user set authentication_string='123456' where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
1
2
[root@Rocky8-11 ~]# vim /etc/my.cnf     ## 删除参数
[root@Rocky8-11 ~]# systemctl restart mysqld

11、查看当前默认的存储引擎

1
2
3
4
5
6
7
8
9
10
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

12、修改最大并发连接数

默认为151

1
2
3
4
5
6
7
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

修改该变量为8000

1
2
3
4
5
6
7
8
9
10
mysql> set global max_connections=8000;
Query OK, 0 rows affected (0.00 sec)

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

修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@Rocky8-11 ~]# vim /etc/my.cnf
[mysqld]
max_connections = 8000

[service]
limitnofile = 65535

[root@Rocky8-11 ~]# systemctl restart mysqld
[root@Rocky8-11 ~]# mysql 
mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              8000 |
+-------------------+
1 row in set (0.00 sec)

13、查看数据库表结构

1
2
3
4
5
6
7
8
9
10
11
12
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| StuID     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)      | NO   |     | NULL    |                |
| Age       | tinyint unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')    | NO   |     | NULL    |                |
| ClassID   | tinyint unsigned | YES  |     | NULL    |                |
| TeacherID | int unsigned     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

14、查看索引信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
09:19:06(root@localhost) [hellodb] show index from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)


15、创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
09:23:00(root@localhost) [hellodb] create index idx_name on students(name(10));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

09:24:11(root@localhost) [hellodb] show indexes from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: 10
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

16、Profile工具

打开Profile选项后，会显示语句执行的详细过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
09:26:02(root@localhost) [hellodb] set profiling = on
09:28:20(root@localhost) [hellodb] show profiles;
Empty set, 1 warning (0.00 sec)
09:28:30(root@localhost) [hellodb] show indexes from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: 10
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

09:28:39(root@localhost) [hellodb] show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00853900 | show indexes from students |
+----------+------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

查看详细执行步骤和时长：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
09:31:05(root@localhost) [hellodb] show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000726 |
| Executing hook on transaction  | 0.000018 |
| starting                       | 0.000426 |
| checking permissions           | 0.000023 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.004917 |
| init                           | 0.000036 |
| System lock                    | 0.000023 |
| optimizing                     | 0.000142 |
| statistics                     | 0.000375 |
| preparing                      | 0.000065 |
| Creating tmp table             | 0.000475 |
| executing                      | 0.000385 |
| checking permissions           | 0.000592 |
| end                            | 0.000020 |
| query end                      | 0.000009 |
| waiting for handler commit     | 0.000050 |
| closing tables                 | 0.000099 |
| freeing items                  | 0.000128 |
| cleaning up                    | 0.000021 |
+--------------------------------+----------+
20 rows in set, 1 warning (0.00 sec)

查看CPU消耗

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
09:31:13(root@localhost) [hellodb] show profile cpu for query 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000726 | 0.000713 |   0.000000 |
| Executing hook on transaction  | 0.000018 | 0.000015 |   0.000000 |
| starting                       | 0.000426 | 0.000429 |   0.000000 |
| checking permissions           | 0.000023 | 0.000021 |   0.000000 |
| checking permissions           | 0.000010 | 0.000010 |   0.000000 |
| Opening tables                 | 0.004917 | 0.004928 |   0.000000 |
| init                           | 0.000036 | 0.000032 |   0.000000 |
| System lock                    | 0.000023 | 0.000023 |   0.000000 |
| optimizing                     | 0.000142 | 0.000144 |   0.000000 |
| statistics                     | 0.000375 | 0.000365 |   0.000000 |
| preparing                      | 0.000065 | 0.000064 |   0.000000 |
| Creating tmp table             | 0.000475 | 0.000477 |   0.000000 |
| executing                      | 0.000385 | 0.000373 |   0.000000 |
| checking permissions           | 0.000592 | 0.000591 |   0.000000 |
| end                            | 0.000020 | 0.000018 |   0.000000 |
| query end                      | 0.000009 | 0.000009 |   0.000000 |
| waiting for handler commit     | 0.000050 | 0.000050 |   0.000000 |
| closing tables                 | 0.000099 | 0.000099 |   0.000000 |
| freeing items                  | 0.000128 | 0.000122 |   0.000000 |
| cleaning up                    | 0.000021 | 0.000019 |   0.000000 |
+--------------------------------+----------+----------+------------+
20 rows in set, 1 warning (0.00 sec)

17、事务日志

redo log：记录某数据块被修改后的值，数据更新前先记录redolog，可以用来恢复未写入datafile的已成功事务更新的数据

undo log：保存与执行的操作相反的操作，即记录某数据被修改前的值，可以用来在事务失败时进行rollback(回退)

事务型存储引擎自行管理和使用，建议与数据文件分开存放

事务日志文件根据设置的日志组文件成员个数和日志文件大小，循环覆盖写入日志文件

事务日志相关配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
05:07:25(root@localhost) [(none)] show variables like '%innodb_log%';
+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 16777216 |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |      ## 每个日志文件大小
| innodb_log_files_in_group          | 2        |      ## 日志组文件成员个数
| innodb_log_group_home_dir          | ./       |      ## 事务日志文件路径
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
| innodb_log_writer_threads          | ON       |
+------------------------------------+----------+
11 rows in set (0.00 sec)

事务日志性能优化：

1 默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。

0 提交时没有写磁盘的操作，每秒执行一次将日志缓冲区提交的事务写入刷新到磁盘，可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务

2 每次提交都会写入OS的缓冲区，但每秒会进行一次刷新到磁盘文件，性能比0略差，可能会丢失最后一秒的数据，

建议：OS比mysql进程稳定，高并发业务选择配置2。

1
2
3
4
5
6
7
05:13:38(root@localhost) [(none)] select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

18、错误日志

错误日志的路径

1
2
3
4
5
6
7
05:13:50(root@localhost) [(none)] show GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| log_error     | /var/log/mysql/mysqld.log |
+---------------+---------------------------+
1 row in set (0.00 sec)

19、通用日志

默认没有启用通用日志

1
2
3
4
5
6
7
05:25:31(root@localhost) [(none)] select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

启用通用日志

1
2
3
4
5
6
7
8
9
10
05:25:35(root@localhost) [(none)] set global general_log=1;
Query OK, 0 rows affected (0.01 sec)

05:26:28(root@localhost) [(none)] select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

通用日志存放类型（可以存放在文件或表中）

1
2
3
4
5
6
7
05:26:36(root@localhost) [(none)] show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)

通用日志文件路径

1
2
3
4
5
6
7
05:27:52(root@localhost) [(none)] select @@general_log_file;
+------------------------------+
| @@general_log_file           |
+------------------------------+
| /var/lib/mysql/Rocky8-11.log |
+------------------------------+
1 row in set (0.01 sec)

20、慢查询日志

慢查询日志记录执行查询时长超出指定时长的操作

慢查询分析工具：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@Rocky8-11 ~]# mysqldumpslow --help 
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

示例：

1
[root@Rocky8-11 ~]# mysqldumpslow -s c -t 2 /var/lib/mysql/slow.log

21、MySQL可用选项列表及当前值

1
[root@Rocky8-11 ~]# mysqld --verbose --help

22、MySQL当前服务启动选项

1
2
3
[root@Rocky8-11 ~]# mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--default_authentication_plugin=mysql_native_password --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysql/mysqld.log --pid-file=/run/mysqld/mysqld.pid --max_connections=8000

命令行中设置：

1
2
[root@Rocky8-11 ~]# /usr/bin/mysqld_safe --skip-name-resolve=1
[root@Rocky8-11 ~]# /usr/libexec/myslqd --basedir=/usr

配置文件设置：

1
2
3
4
[root@Rocky8-11 ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

23、查看global系统变量

1
02:16:22(root@localhost) [(none)] show global variables;

查看指定变量

1
2
02:18:36(root@localhost) [(none)] show variables like '变量';
02:20:01(root@localhost) [(none)] select @@变量;

查看选项和部分变量：

1
[root@Rocky8-11 ~]# mysqladmin variables

修改变量：set

1
2
3
4
5
6
7
8
9
10
11
12
help set
SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | {PERSIST | @@PERSIST.} system_var_name
  | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}
1
2
02:24:45(root@localhost) [(none)] set character_set_results='utf8mb4';
Query OK, 0 rows affected (0.01 sec)

character_set_results不是服务器选项，写入配置文件将导致服务无法启动

1
2
3
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character_set_results=utf8mb4
MySQL
#MariaDB #MySQL
MySQL基础知识
https://www.xcjyc.top/2023/05/11/MySQL基础知识/
作者
XCJYC
发布于
2023年5月11日
许可协议