# ll -h /data/mysql total 155M -rw-r----- 1 mysql mysql 56 Dec 6 23:11 auto.cnf -rw------- 1 mysql mysql 1.7K Dec 6 23:11 ca-key.pem -rw-r--r-- 1 mysql mysql 1.1K Dec 6 23:11 ca.pem -rw-r--r-- 1 mysql mysql 1.1K Dec 6 23:11 client-cert.pem -rw------- 1 mysql mysql 1.7K Dec 6 23:11 client-key.pem -rw-r----- 1 mysql mysql 5.8K Dec 6 23:11 ib_buffer_pool -rw-r----- 1 mysql mysql 12M Dec 6 23:11 ibdata1 -rw-r----- 1 mysql mysql 48M Dec 6 23:11 ib_logfile0 -rw-r----- 1 mysql mysql 48M Dec 6 23:11 ib_logfile1 drwxr-x--- 2 mysql mysql 6 Dec 6 23:11 #innodb_temp drwxr-x--- 2 mysql mysql 143 Dec 6 23:11 mysql -rw-r----- 1 mysql mysql 26M Dec 6 23:11 mysql.ibd -rw-r----- 1 mysql mysql 168 Dec 6 23:11 mysql-slow.log drwxr-x--- 2 mysql mysql 8.0K Dec 6 23:11 performance_schema -rw------- 1 mysql mysql 1.7K Dec 6 23:11 private_key.pem -rw-r--r-- 1 mysql mysql 452 Dec 6 23:11 public_key.pem -rw-r--r-- 1 mysql mysql 1.1K Dec 6 23:11 server-cert.pem -rw------- 1 mysql mysql 1.7K Dec 6 23:11 server-key.pem drwxr-x--- 2 mysql mysql 28 Dec 6 23:11 sys -rw-r----- 1 mysql mysql 10M Dec 6 23:11 undo_001 -rw-r----- 1 mysql mysql 10M Dec 6 23:11 undo_002
# grep 'password' ~/passwd 2023-12-06T15:11:09.255822Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: >i_Nt=kCf6:6
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
# mysql -uroot -p'>i_Nt=kCf6:6' -A 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.17
Copyright (c) 2000, 2019, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> quit Bye
-u：指定用户。
-p：指定需要输入密码。
-A：取消命令提示，使用tab不会有命令提示。
-h：指定登录到哪台主机，默认使用localhost。
-S：指定使用哪个socket，针对socket放置在不同位置的。
配置socket在非/tmp目录：
1 2 3 4
# cp /etc/my.cnf /etc/my.cnf.bak # vim /etc/my.cnf # 尾行，添加配置 socket=/data/mysql/mysql.sock
1
# systemctl restart mysqld
重新登录失败，需要指定socket：
1 2 3
# mysql -uroot -p'>i_Nt=kCf6:6' -A mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
1 2 3
# mysql -uroot -p'>i_Nt=kCf6:6' -A -S /data/mysql/mysql.sock
mysql> create table user (id bigint); Query OK, 0 rows affected (0.02 sec)
mysql> insert into user (id) values (11111111111111111111); Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from user; +---------------------+ | id | +---------------------+ | 9223372036854775807 | +---------------------+ 1 row in set (0.00 sec)
int不能存储浮点数：
1 2 3 4 5 6 7 8 9 10 11
mysql> insert into user (id) values (5.5); Query OK, 1 row affected (0.00 sec)
mysql> select * from user; +---------------------+ | id | +---------------------+ | 9223372036854775807 | | 6 | +---------------------+ 2 rows in set (0.00 sec)
mysql> select * from user where id=1; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | teacher | red | +----+----------+----------+ 1 row in set (0.01 sec)
mysql> select * from user where id<=13; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | teacher | red | | 2 | student | yellow | | 3 | teacher | red | | 4 | student | yellow | | 6 | teacher | red | | 7 | student | yellow | | 8 | teacher | red | | 9 | student | yellow | | 13 | teacher | red | +----+----------+----------+ 9 rows in set (0.00 sec)
条件之字符串匹配：
=：等于。
!=：不等于。
1
mysql> select * from user where username='student';
多条件查询：
and多条件同时满足。
or只需要满足其中一个条件。
1 2 3 4 5 6 7 8 9 10 11 12
mysql> select * from user where username='student' and id <10; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 2 | student | yellow | | 4 | student | yellow | | 7 | student | yellow | | 9 | student | yellow | +----+----------+----------+ 4 rows in set (0.00 sec)
mysql> select * from user where id<10 or username='teacher';
like模糊查询，匹配：
%：匹配任意多个字符。
_：代表匹配任意单个字符。
1 2 3 4 5
mysql> select * from user where username like 'tea%';
mysql> select * from user where username like '%t%';
mysql> select * from user where username like '%den_';
in查询，指定：
1 2 3 4 5 6 7 8 9 10
mysql> select * from user where id in (1,2); +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | teacher | red | | 2 | student | yellow | +----+----------+----------+ 2 rows in set (0.00 sec)
mysql> select * from user where username in ('student','yellow');
mysql> select * from myuser where username=null; Empty set (0.00 sec)
mysql> select * from myuser where username!=null; Empty set (0.00 sec)
mysql> select * from myuser where username is null; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | NULL | NULL | +----+----------+----------+ 1 row in set (0.00 sec)
mysql> select * from myuser where username is not null; +----+-----------+----------+ | id | username | password | +----+-----------+----------+ | 2 | classmate | black | +----+-----------+----------+ 1 row in set (0.00 sec)
mysql> select * from user where id=1; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | teacher | color | +----+----------+----------+ 1 row in set (0.00 sec)
mysql> update user set password='orange' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user limit 5; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | teacher | orange | | 2 | student | color | | 3 | teacher | color | | 4 | student | color | | 6 | teacher | color | +----+----------+----------+ 5 rows in set (0.00 sec)
删除语法：
delete from 表名 where 条件。
建议先select，后delete。
条件一定要加，不然就全删除了。
删除id大于50的：
1 2 3 4 5 6
mysql> select * from user where id>100;
mysql> delete from user where id>100; Query OK, 194 rows affected (0.00 sec)
mysql> select * from user;
删除username=teacher的：
1 2 3 4
mysql> delete from user where username='teacher'; Query OK, 32 rows affected (0.00 sec)
mysql> select * from user order by id; +----+-----------+-----------+-------+ | id | username | learntype | grade | +----+-----------+-----------+-------+ | 1 | teacher | c | 80 | | 2 | teacher | python | 93 | | 3 | student | c | 76 | | 4 | student | python | 88 | | 5 | classmate | c | 87 | | 6 | classmate | python | 65 | +----+-----------+-----------+-------+ 6 rows in set (0.00 sec)
降序，id：
1 2 3 4 5 6 7 8 9 10 11 12
mysql> select * from user order by id desc; +----+-----------+-----------+-------+ | id | username | learntype | grade | +----+-----------+-----------+-------+ | 6 | classmate | python | 65 | | 5 | classmate | c | 87 | | 4 | student | python | 88 | | 3 | student | c | 76 | | 2 | teacher | python | 93 | | 1 | teacher | c | 80 | +----+-----------+-----------+-------+ 6 rows in set (0.00 sec)
升序，learntype：
1 2 3 4 5 6 7 8 9 10 11 12
mysql> select * from user order by learntype; +----+-----------+-----------+-------+ | id | username | learntype | grade | +----+-----------+-----------+-------+ | 1 | teacher | c | 80 | | 3 | student | c | 76 | | 5 | classmate | c | 87 | | 2 | teacher | python | 93 | | 4 | student | python | 88 | | 6 | classmate | python | 65 | +----+-----------+-----------+-------+ 6 rows in set (0.00 sec)
升序，learntype，grade：
1 2 3 4 5 6 7 8 9 10 11 12
mysql> select * from user order by learntype,grade; +----+-----------+-----------+-------+ | id | username | learntype | grade | +----+-----------+-----------+-------+ | 3 | student | c | 76 | | 1 | teacher | c | 80 | | 5 | classmate | c | 87 | | 6 | classmate | python | 65 | | 4 | student | python | 88 | | 2 | teacher | python | 93 | +----+-----------+-----------+-------+ 6 rows in set (0.00 sec)
learntype，升序，grade，降序：
1 2 3 4 5 6 7 8 9 10 11 12
mysql> select * from user order by learntype asc,grade desc; +----+-----------+-----------+-------+ | id | username | learntype | grade | +----+-----------+-----------+-------+ | 5 | classmate | c | 87 | | 1 | teacher | c | 80 | | 3 | student | c | 76 | | 2 | teacher | python | 93 | | 4 | student | python | 88 | | 6 | classmate | python | 65 | +----+-----------+-----------+-------+ 6 rows in set (0.00 sec)
统计最大id：
1 2 3 4 5 6 7
mysql> select max(id) from user; +---------+ | max(id) | +---------+ | 6 | +---------+ 1 row in set (0.00 sec)
统计最小id：
1 2 3 4 5 6 7
mysql> select min(id) from user; +---------+ | min(id) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
统计平均id：
1 2 3 4 5 6 7
mysql> select avg(id) from user; +---------+ | avg(id) | +---------+ | 3.5000 | +---------+ 1 row in set (0.00 sec)
统计id和：
1 2 3 4 5 6 7
mysql> select sum(id) from user; +---------+ | sum(id) | +---------+ | 21 | +---------+ 1 row in set (0.00 sec)
总分，并排序：
1 2 3 4 5 6 7 8 9
mysql> select username,sum(grade) from user group by username order by sum(grade); +-----------+------------+ | username | sum(grade) | +-----------+------------+ | classmate | 152 | | student | 164 | | teacher | 173 | +-----------+------------+ 3 rows in set (0.00 sec)
平均分：
1 2 3 4 5 6 7 8 9
mysql> select username,avg(grade) from user group by username; +-----------+------------+ | username | avg(grade) | +-----------+------------+ | teacher | 86.5000 | | student | 82.0000 | | classmate | 76.0000 | +-----------+------------+ 3 rows in set (0.00 sec)
多少科：
1 2 3 4 5 6 7 8 9
mysql> select username,count(*) from user group by username; +-----------+----------+ | username | count(*) | +-----------+----------+ | teacher | 2 | | student | 2 | | classmate | 2 | +-----------+----------+ 3 rows in set (0.00 sec)
分组统计，以学科来分组：
1 2 3 4 5 6 7 8
mysql> select learntype,avg(grade) from user group by learntype; +-----------+------------+ | learntype | avg(grade) | +-----------+------------+ | c | 81.0000 | | python | 82.0000 | +-----------+------------+ 2 rows in set (0.00 sec)
mysql多表组合查询
node1
删除之前的表：
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysql> show tables; +---------------------+ | Tables_in_sqlserver | +---------------------+ | myuser | | user | +---------------------+ 2 rows in set (0.01 sec)
mysql> insert into user (username,password) values ('one','red'); Query OK, 1 row affected (0.01 sec)
mysql> insert into user (username,password) values ('two','red'); Query OK, 1 row affected (0.00 sec)
mysql> insert into user (username,password) values ('three','yellow'); Query OK, 1 row affected (0.00 sec)
mysql> insert into user (username,password) values ('four','yellow'); Query OK, 1 row affected (0.00 sec)
mysql> select * from user; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | one | red | | 2 | two | red | | 3 | three | yellow | | 4 | four | yellow | +----+----------+----------+ 4 rows in set (0.00 sec)
mysql> select * from logincount; +----------+-------+ | username | count | +----------+-------+ | one | 10 | | two | 20 | | three | 50 | | nono | 90 | +----------+-------+ 4 rows in set (0.00 sec)
inner join，两张表共有：
1 2 3 4 5 6 7 8 9
mysql> select user.username,user.password,logincount.count from user inner join logincount on user.username=logincount.username; +----------+----------+-------+ | username | password | count | +----------+----------+-------+ | one | red | 10 | | two | red | 20 | | three | yellow | 50 | +----------+----------+-------+ 3 rows in set (0.00 sec)
left join，以左表为主：
1 2 3 4 5 6 7 8 9 10
mysql> select user.username,user.password,logincount.count from user left join logincount on user.username=logincount.username; +----------+----------+-------+ | username | password | count | +----------+----------+-------+ | one | red | 10 | | two | red | 20 | | three | yellow | 50 | | four | yellow | NULL | +----------+----------+-------+ 4 rows in set (0.00 sec)
right join，以右表为主：
1 2 3 4 5 6 7 8 9 10
mysql> select user.username,user.password,logincount.count from user right join logincount on user.username=logincount.username; +----------+----------+-------+ | username | password | count | +----------+----------+-------+ | one | red | 10 | | two | red | 20 | | three | yellow | 50 | | NULL | NULL | 90 | +----------+----------+-------+ 4 rows in set (0.00 sec)
mysql正则查询实战
正则表达式：
约定好的符号去表示某个含义。
例如.表示任意单个字符。
约定好的符号当成普通字符使用。
普通正则表达式：
符号
含义
.
表示任意一个字符
*
表示前面一个字符出现0次或者多次
[abc]
表示中括号内任意一个字符
[^abc]
表示非中括号内的字符
[0-9]
表示数字
[a-z]
表示小写字母
[A-Z]
表示大写字母
^xx
示以xx开头
xx$
表示以xx结尾
[a-zA-Z]
表示所有字母
[a-zA-Z0-9]
表示所有字母+数字
\d
表示数字
\D
表示非数字
\s
表示空白字符
\S
表示非空白字符
扩展正则表达式：
符号
含义
?
表示前面字符出现0或者1次
+
表示前面字符出现1或者多次
{a}
表示前面字符匹配a次
{a,b}
表示前面字符匹配a到b次
{,b}
表示前面字符匹配0次到b次
{a,}
前面字符匹配a或a+次
string1|string2
表示匹配string1或者string2
node1
查询数据：
1 2 3 4 5 6 7 8 9 10
mysql> select * from user; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | one | red | | 2 | two | red | | 3 | three | yellow | | 4 | four | yellow | +----+----------+----------+ 4 rows in set (0.00 sec)
正则匹配，带r：
1 2 3 4 5 6 7 8
mysql> select * from user where username regexp 'r'; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 3 | three | yellow | | 4 | four | yellow | +----+----------+----------+ 2 rows in set (0.01 sec)
正则匹配，t开头：
1 2 3 4 5 6 7 8
mysql> select * from user where username regexp '^t'; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 2 | two | red | | 3 | three | yellow | +----+----------+----------+ 2 rows in set (0.00 sec)
正则匹配，e结尾：
1 2 3 4 5 6 7 8
mysql> select * from user where username regexp 'e$'; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | one | red | | 3 | three | yellow | +----+----------+----------+ 2 rows in set (0.00 sec)
扩展正则：
1 2 3 4 5 6 7 8 9
mysql> select * from user where username regexp 'e$|two'; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | one | red | | 2 | two | red | | 3 | three | yellow | +----+----------+----------+ 3 rows in set (0.00 sec)
mysql> insert into user (username,password) values ('special','mypwd'); Query OK, 1 row affected (0.01 sec)
查询数据，超过0.5sec就很长了：
1 2 3 4 5 6 7 8 9
mysql> select * from user where username='special'; +---------+----------+----------+ | id | username | password | +---------+----------+----------+ | 1834967 | special | mypwd | +---------+----------+----------+ 1 row in set (0.37 sec)
mysql> quit
修改慢日志时间，超过0.2秒记录日志：
1 2 3
# vim /etc/my.cnf # 8行，修改配置 long_query_time=0.1
1
# systemctl restart mysqld
数据库查询：
1 2 3 4 5 6 7 8 9 10 11 12
# mysql -uroot -p123456 -A
mysql> use sqlserver; Database changed
mysql> select * from user where username='special'; +---------+----------+----------+ | id | username | password | +---------+----------+----------+ | 1834967 | special | mypwd | +---------+----------+----------+ 1 row in set (0.35 sec)
新建终端，有慢日志产生：
1 2 3 4 5 6
# tail -5 /data/mysql/mysql-slow.log # User@Host: root[root] @ localhost [] Id: 8 # Query_time: 0.155384 Lock_time: 0.000840 Rows_sent: 1 Rows_examined: 1572865 use sqlserver; SET timestamp=1701879345; select * from user where username='special';
mysql数据添加索引，再次查询，秒出，只扫描一行。添加索引比较慢：
1 2 3 4 5 6 7 8 9 10 11
mysql> alter table user add index i_username (username); Query OK, 0 rows affected (1.69 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from user where username='special'; +---------+----------+----------+ | id | username | password | +---------+----------+----------+ | 1834967 | special | mypwd | +---------+----------+----------+ 1 row in set (0.00 sec)
查看扫描函数：
1 2 3 4 5 6 7
mysql> explain select * from user where username='special'; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | user | NULL | ref | i_username | i_username | 402 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
mysql> select * from user where username regexp 'special'; +---------+----------+----------+ | id | username | password | +---------+----------+----------+ | 1834967 | special | mypwd | +---------+----------+----------+ 1 row in set (0.81 sec)
模糊查询可以查询索引：
1 2 3 4 5 6 7 8 9
mysql> select * from user where username like 'speci%'; +---------+----------+----------+ | id | username | password | +---------+----------+----------+ | 1834967 | special | mypwd | +---------+----------+----------+ 1 row in set (0.00 sec)
mysql> quit
mysql的权限控制实战1
mysql监听说明：
监听本地127.0.0.1，无风险。
监听局域网，风险小，一般是监听在内网。
监听公网，风险大，不设置权限容易被入侵。
node1
修改配置文件，监听公网，监听所有网卡0.0.0.0：
1 2 3
# vim /etc/my.cnf # 2行，修改配置 bind-address=0.0.0.0
1 2
# systemctl restart mysqld # systemctl status mysqld
mysql> show variables like '%authen%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | caching_sha2_password | +-------------------------------+-----------------------+ 1 row in set (0.00 sec)
回收权限，再次登录权限受限：
1 2 3 4 5
mysql> revoke all privileges on *.* from 'testuser'@'127.0.0.1'; Query OK, 0 rows affected (0.01 sec)
mysql> select * from user; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | student | mypwd | | 2 | friend | yourpwd | +----+----------+----------+ 2 rows in set (0.00 sec)
mysql> quit
备份所有数据库，包括内置数据库，用得少：
1 2
# mysqldump -uroot -p123456 --all-databases sqlserver linkserver > /tmp/sql-all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份多个数据库：
1 2
# mysqldump -uroot -p123456 --databases sqlserver linkserver > /tmp/all.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份sqltest数据库，备份单个数据库还原时需要自己建立数据库：
1 2
# mysqldump -uroot -p123456 sqlserver > /tmp/sqlserver.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份sqltest数据库的user表：
1 2
# mysqldump -uroot -p123456 sqlserver user > /tmp/sqlserver_user.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
忽略指定数据库的某个表：
1 2
# mysqldump -uroot -p123456 sqlserver --ignore-table=sqlserver.user > /tmp/ignore.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
备份数据库的表结构，不备份数据：
1 2
# mysqldump -uroot -p123456 sqlserver -d > /tmp/sqlserver_struct.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases; ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> quit
1 2 3 4 5 6 7 8 9 10 11 12
# mysql -utestuser -ppassword -A -h10.80.10.1
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
testuser用户窗口查看：
1 2 3 4 5 6 7 8 9 10 11
mysql> show full processlist; +----+-----------------+------------------+------+---------+------+------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+-----------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 148 | Waiting on empty queue | NULL | | 8 | root | localhost | NULL | Query | 0 | starting | show full processlist | | 10 | testuser | 10.80.10.1:33854 | NULL | Sleep | 4 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+-----------------------+ 3 rows in set (0.00 sec)
mysql> quit
新窗口shell直接查看登录ip：
1 2 3 4 5 6 7 8 9 10 11 12 13
# mysql -uroot -p123456 -A -e "show full processlist" mysql: [Warning] Using a password on the command line interface can be insecure. +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 216 | Waiting on empty queue | NULL | | 14 | root | localhost | NULL | Query | 0 | starting | show full processlist | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ # mysql -uroot -p123456 -A -e "show full processlist" | awk '{print $3}' mysql: [Warning] Using a password on the command line interface can be insecure. Host localhost localhost
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)
mysql> set autocommit=OFF; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec)
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec)
mysql> set global max_connections=1024; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1024 | +-----------------+-------+ 1 row in set (0.00 sec)
mysql> quit Bye
写入配置文件，永久生效：
1 2 3
# vim /etc/my.cnf # 3行，添加配置 max_connections=1024
1 2
# systemctl restart mysqld # systemctl status mysqld
1 2 3 4 5 6 7 8 9
# mysql -uroot -p123456 -A
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1024 | +-----------------+-------+ 1 row in set (0.01 sec)
设置全局，慢日志时间：
1 2 3 4 5 6 7 8 9 10
mysql> show variables like '%query_time%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.100000 | +-----------------+----------+ 1 row in set (0.00 sec)
mysql> set global long_query_time = 0.2; Query OK, 0 rows affected (0.00 sec)
mysql状态：
1
mysql> show global status;
mysql启动时间：
1 2 3 4 5 6 7 8
mysql> show global status like '%uptime%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | Uptime | 19 | | Uptime_since_flush_status | 19 | +---------------------------+-------+ 2 rows in set (0.00 sec)
mysql流量信息：
1 2 3 4 5 6 7 8 9 10 11 12
mysql> show global status like '%bytes%'; +--------------------------------+----------+ | Variable_name | Value | +--------------------------------+----------+ | Bytes_received | 786 | | Bytes_sent | 14969 | | Innodb_buffer_pool_bytes_data | 15335424 | | Innodb_buffer_pool_bytes_dirty | 0 | | Mysqlx_bytes_received | 0 | | Mysqlx_bytes_sent | 0 | +--------------------------------+----------+ 6 rows in set (0.00 sec)
