--
:
--
:
--
mysql命令
最后更新于:
mysql命令
目录
[toc]
容器起docker
案例：docker容器起mysql
1$ docker run --rm -it --platform=linux/amd64 --network mysql_default mysql:5.7 mysql -h proxy -P 1999 -u root --skip-ssl
2# ......
3mysql> CREATE DATABASE test;
4Query OK, 1 row affected (0.00 sec)
5
6mysql> USE test;
7Database changed
8mysql> CREATE TABLE test ( text VARCHAR(255) );
9Query OK, 0 rows affected (0.01 sec)
10
11mysql> SELECT COUNT(*) FROM test;
12+----------+
13| COUNT(*) |
14+----------+
15| 0 |
16+----------+
171 row in set (0.01 sec)
18
19mysql> INSERT INTO test VALUES ('hello, world!');
20Query OK, 1 row affected (0.00 sec)
21
22mysql> SELECT COUNT(*) FROM test;
23+----------+
24| COUNT(*) |
25+----------+
26| 1 |
27+----------+
281 row in set (0.00 sec)
29
30mysql> exit
31Bye
案例：docker-compose起mysql:5.7
docker-compose.yaml
1version: '3'
2
3services:
4 piwigo:
5 image: lscr.io/linuxserver/piwigo:latest
6 container_name: piwigo
7 ports:
8 - "8080:80" # 端口映射，官方默认80端口，前面可自己修改成VPS未被占用端口
9 depends_on:
10 - db
11 environment:
12 - PUID=1000
13 - PGID=1000
14 - TZ=Asia/Shanghai # 时区，中国时区为Asia/Shanghai
15 volumes:
16 - ./config:/config
17 - ./gallery:/gallery
18
19 db:
20 image: mysql:5.7
21 container_name: mysql_piwigo
22 environment:
23 MYSQL_ROOT_PASSWORD: SSbw5pK_bSDvqIXIEeDHPg # root用户密码
24 MYSQL_DATABASE: piwigo # 数据库名
25 MYSQL_USER: piwigo # 用户名
26 MYSQL_PASSWORD: xO4G-U28etWMNWoyx9OtFg # piwigo用户数据库密码
27 volumes:
28 - ./mysql_data:/var/lib/mysql
29
30volumes:
31 mysql_data:
32 config:
33 gallery:
- 运行项目
1cd /root/piwigo
2docker-compose pull
3docker-compose up -d
4
5#查看
6[root@docusaurus-wiki piwigo]#docker-compose ps
7NAME IMAGE COMMAND SERVICE CREATED STATUS PORTS
8mysql_piwigo mysql:5.7 "docker-entrypoint.s…" db 12 minutes ago Up 12 minutes 3306/tcp, 33060/tcp
9piwigo lscr.io/linuxserver/piwigo:latest "/init" piwigo 12 minutes ago Up 12 minutes 443/tcp, 0.0.0.0:8080->80/tcp
10[root@docusaurus-wiki piwigo]#
k8s里部署mysql
1# mysql.yaml
2apiVersion: v1
3kind: Service
4metadata:
5 name: mysql
6 namespace: kube-example
7 labels:
8 app: mysql
9spec:
10 ports:
11 - port: 3306
12 targetPort: dbport
13 selector:
14 app: mysql
15---
16apiVersion: apps/v1
17kind: StatefulSet
18metadata:
19 name: mysql
20 namespace: kube-example
21 labels:
22 app: mysql
23spec:
24 selector:
25 matchLabels:
26 app: mysql
27 template:
28 metadata:
29 labels:
30 app: mysql
31 spec:
32 containers:
33 - name: mysql
34 image: mysql:5.7
35 imagePullPolicy: IfNotPresent
36 args: # 新版本镜像有更新，需要使用下面的认证插件环境变量配置才会生效
37 - --default_authentication_plugin=mysql_native_password
38 - --character-set-server=utf8mb4
39 - --collation-server=utf8mb4_unicode_ci
40 ports:
41 - containerPort: 3306
42 name: dbport
43 volumeMounts:
44 - name: mysql-data
45 mountPath: /var/lib/mysql
46 env:
47 - name: MYSQL_ROOT_PASSWORD
48 value: rootPassW0rd
49 - name: MYSQL_DATABASE
50 value: wordpress
51 - name: MYSQL_USER
52 value: wordpress
53 - name: MYSQL_PASSWORD
54 value: wordpress
55
56 volumeClaimTemplates:
57 - metadata:
58 name: mysql-data
59 spec:
60 accessModes: ["ReadWriteOnce"]
61 storageClassName: nfs-client
62 resources:
63 requests:
64 storage: 1Gi
wordpress.yaml
1# wordpress.yaml
2apiVersion: v1
3kind: Service
4metadata:
5 name: wordpress
6 namespace: kube-example
7 labels:
8 app: wordpress
9spec:
10 selector:
11 app: wordpress
12 type: NodePort
13 ports:
14 - name: web
15 port: 80
16 targetPort: wdport
17---
18apiVersion: apps/v1
19kind: Deployment
20metadata:
21 name: wordpress
22 namespace: kube-example
23 labels:
24 app: wordpress
25spec:
26 replicas: 2
27 selector:
28 matchLabels:
29 app: wordpress
30 strategy:
31 type: RollingUpdate
32 rollingUpdate:
33 maxSurge: 1
34 maxUnavailable: 0
35 template:
36 metadata:
37 labels:
38 app: wordpress
39 spec:
40 affinity:
41 podAntiAffinity:
42 preferredDuringSchedulingIgnoredDuringExecution: # 软策略
43 - weight: 1
44 podAffinityTerm:
45 topologyKey: kubernetes.io/hostname
46 labelSelector:
47 matchExpressions:
48 - key: app
49 operator: In
50 values:
51 - wordpress
52 containers:
53 - name: wordpress
54 image: wordpress:6.4.3-apache
55 ports:
56 - containerPort: 80
57 name: wdport
58 readinessProbe:
59 tcpSocket:
60 port: 80
61 initialDelaySeconds: 5
62 periodSeconds: 5
63 lifecycle:
64 preStop:
65 exec:
66 command: ["/bin/bash", "-c", "sleep 20"]
67 resources:
68 limits:
69 cpu: 150m
70 memory: 150Mi
71 requests:
72 cpu: 150m
73 memory: 150Mi
74 volumeMounts:
75 - name: wordpress-data
76 mountPath: /var/www/html
77 env:
78 - name: WORDPRESS_DB_HOST
79 value: mysql:3306
80 - name: WORDPRESS_DB_USER
81 value: wordpress
82 - name: WORDPRESS_DB_PASSWORD
83 valueFrom:
84 secretKeyRef:
85 name: wordpress-db-pwd
86 key: dbpwd
87 volumes:
88 - name: wordpress-data
89 persistentVolumeClaim:
90 claimName: wordpress-pvc
91---
92# pdb.yaml
93apiVersion: policy/v1
94kind: PodDisruptionBudget
95metadata:
96 name: wordpress-pdb
97 namespace: kube-example
98spec:
99 maxUnavailable: 1
100 selector:
101 matchLabels:
102 app: wordpress
mysql常用命令
命令汇总
1mysql -uroot -ppassword #登录数据库
2
3show databases; #查看数据库
4
5#创建数据库
6CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
7CHARACTER SET 'character set name'
8COLLATE 'collate name';
9或者
10CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
11或者
12CREATE DATABASE|SCHEMA 'DB_NAME';
13
14#查看创建的数据库字符集
15show create database db1;
16#查看当前告警；
17show warnings;
18
19#修改数据库的字符集
20ALTER DATABASE DB_NAME character set utf8;
21
22mysql test #直接指定对应数据库
23use mysql; #进入数据库
24 show tables; #显示当前数据库的表列表：
25
26 #查看表内容
27 select *from t1;
28 等价于
29 select * from t1;
30
31 #查看用户名和密码
32 select Host,User,Password from user; #查看数据库user表
33 ==等价于
34 SELECT User,Host,Password FROM user; ##注意：关键字这里的大小写都是可以的哦；
35 ==等价于
36 select user,host,password from user;
37
38 #select user,host,password,authentication_string from user; #密码可能存放在这2个字段，和数据版本有关；
39
40 desc user; #查看某张表所有列的内容
41
42 #查看当前用户
43 \s
44 或者
45 select User();
46
47 #加载授权表:立即生效的。
48
49 SHOW CHARACTER SET; #查看支持所有的字符集
50 show variables like 'character%'; #查看当前字符集的使用情况
51
52 show COLLATION; #查看支持所有排序规则
53 SHOW VARIABLES LIKE 'collation%'; #查看当前使用的排序规则
54
55 SELECT VERSION(); #查看当前mysql版本
56 或者
57 \s #也行的
58 mysqld --version
59
60 #查看当前连接数据库的端口
61 show variables like 'PORT';
62 或者
63 show variables like 'port';
64
65#删除数据库
66DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
67
68\q #退出数据库 ==exit
69
70#导入sql数据
71mysql < hellodb_innodb.sql
72
73show processlist; #查看mysql线程
登录数据库、查看当前数据库数量
1#进入pod测试mysql应用
2mysql -uroot -ppassword #登录数据库
3Warning: Using a password on the command line interface can be insecure.
4Welcome to the MySQL monitor. Commands end with ; or \g.
5Your MySQL connection id is 1
6Server version: 5.6.51 MySQL Community Server (GPL)
7
8Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
9
10Oracle is a registered trademark of Oracle Corporation and/or its
11affiliates. Other names may be trademarks of their respective
12owners.
13
14Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15
16mysql> show databases;
17+---------------------+
18| Database |
19+---------------------+
20| information_schema |
21| #mysql50#lost+found |
22| mysql |
23| performance_schema |
24+---------------------+
254 rows in set (0.05 sec)
26
27mysql> create database longhorn;
28Query OK, 1 row affected (0.00 sec)
29
30mysql> show databases;
31+---------------------+
32| Database |
33+---------------------+
34| information_schema |
35| longhorn |
36| #mysql50#lost+found |
37| mysql |
38| performance_schema |
39+---------------------+
405 rows in set (0.00 sec)
41
42mysql> exit
43Bye
44[root@master1 ~]#
使用某个数据库
1mysql> show databases;
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| mysql |
7| performance_schema |
8+--------------------+
93 rows in set (0.01 sec)
10
11mysql> use mysql;
12Reading table information for completion of table and column names
13You can turn off this feature to get a quicker startup with -A
14
15Database changed
16mysql>
显示当前数据库的表列表
1MariaDB [mysql]> show tables;
2+---------------------------+
3| Tables_in_mysql |
4+---------------------------+
5| column_stats |
6| columns_priv |
7| db |
8| event |
9| func |
10| general_log |
11| global_priv |
12| gtid_slave_pos |
13| help_category |
14| help_keyword |
15| help_relation |
16| help_topic |
17| index_stats |
18| innodb_index_stats |
19| innodb_table_stats |
20| plugin |
21| proc |
22| procs_priv |
23| proxies_priv |
24| roles_mapping |
25| servers |
26| slow_log |
27| table_stats |
28| tables_priv |
29| time_zone |
30| time_zone_leap_second |
31| time_zone_name |
32| time_zone_transition |
33| time_zone_transition_type |
34| transaction_registry |
35| user |
36+---------------------------+
3731 rows in set (0.001 sec)
38
39MariaDB [mysql]>
查看数据库user表
1mysql> select Host,User,Password from user;
2+-----------+------+-------------------------------------------+
3| Host | User | Password |
4+-----------+------+-------------------------------------------+
5| localhost | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
6| 127.0.0.1 | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
7| ::1 | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
8+-----------+------+-------------------------------------------+
93 rows in set (0.00 sec)
10
11mysql>
12
13mysql> SELECT User,Host,Password FROM user;
14+------+-----------+-------------------------------------------+
15| User | Host | Password |
16+------+-----------+-------------------------------------------+
17| root | localhost | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
18| root | 127.0.0.1 | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
19| root | ::1 | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
20+------+-----------+-------------------------------------------+
213 rows in set (0.00 sec)
22
23mysql>
注意：
看某张表的内容：
1MariaDB [mysql]> select * from user;
为什么会显示这样呢？
因为这个张表的列太多了，换行导致的。
只查看某2列内容：
1MariaDB [mysql]> select user,host from user;
2+-------------+-------------+
3| User | Host |
4+-------------+-------------+
5| | localhost |
6| mariadb.sys | localhost |
7| mysql | localhost |
8| root | localhost |
9| | vm-template |
10+-------------+-------------+
115 rows in set (0.001 sec)
12
13MariaDB [mysql]>
案例：查看用户名、密码
1mysql> select user,host,password from user;
2+-----------+------+-------------------------------------------+
3| Host | User | Password |
4+-----------+------+-------------------------------------------+
5| localhost | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
6| 127.0.0.1 | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
7| ::1 | root | *ABE374A5F247C93961AD4726B39A5A84FA3BC3B1 |
8+-----------+------+-------------------------------------------+
93 rows in set (0.00 sec)
- 注意
查看用户密码：
1MariaDB [mysql]> select user,host,password,authentication_string from user;
2+-------------+-------------+----------+-----------------------+
3| User | Host | Password | authentication_string |
4+-------------+-------------+----------+-----------------------+
5| mariadb.sys | localhost | | |
6| root | localhost | invalid | invalid |
7| mysql | localhost | invalid | invalid |
8| | localhost | | |
9| | vm-template | | |
10+-------------+-------------+----------+-----------------------+
115 rows in set (0.001 sec)
12
13MariaDB [mysql]>
14
15#password,authentication_string 密码可能存放在这2个字段，和数据版本有关；
限定只能在特定的主机上登录数据库：（加一个host）
基于安全因素考虑，设计了这个host的逻辑。
mysql用户，一部分叫用户名，一部分叫主机名。（主机名+用户名 合起来才叫一个完整的用户名）
查看当前用户：
1status
当前密码是空的：
1MariaDB [mysql]> select user,host,password from user;
2+-------------+-------------+----------+
3| User | Host | Password |
4+-------------+-------------+----------+
5| mariadb.sys | localhost | |
6| root | localhost | invalid |
7| mysql | localhost | invalid |
8| | localhost | |
9| | vm-template | |
10+-------------+-------------+----------+
115 rows in set (0.001 sec)
12
13MariaDB [mysql]>
默认，使用mysql是直接能登陆数据库的：（密码是空的）
1mysql
2==
3mysql -uroot
4
5
6[root@linux-test ~]#mysql ##默认省略了-uroot
7Welcome to the MariaDB monitor. Commands end with ; or \g.
8Your MariaDB connection id is 11
9Server version: 10.4.33-MariaDB MariaDB Server
10
11Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15MariaDB [(none)]>
这里还存在空用户名：(表示匿名用户)
1MariaDB [mysql]> select user,host,password from user;
2+-------------+-------------+----------+
3| User | Host | Password |
4+-------------+-------------+----------+
5| mariadb.sys | localhost | |
6| root | localhost | invalid |
7| mysql | localhost | invalid |
8| | localhost | |
9| | vm-template | |
10+-------------+-------------+----------+
115 rows in set (0.001 sec)
12
13MariaDB [mysql]>
表示随便一个用户都可以登录：
1[root@linux-test ~]#mysql -uxyy
2Welcome to the MariaDB monitor. Commands end with ; or \g.
3Your MariaDB connection id is 12
4Server version: 10.4.33-MariaDB MariaDB Server
5
6Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
7
8Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
9
10MariaDB [(none)]>
当前，这个匿名用户是一个普通用户，其权限很小的
1#只能看到2个数据库:
2MariaDB [(none)]> show databases;
3+--------------------+
4| Database |
5+--------------------+
6| information_schema |
7| test |
8+--------------------+
92 rows in set (0.001 sec)
10
11MariaDB [(none)]>
12
13#当然也无法进入到mysql数据库：
14MariaDB [(none)]> use mysql
15ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
16MariaDB [(none)]>
从很多方便来看，默认数据库安装好后，有很多不安全的地方：
注意：
这里有个情况，我切换到xyy用户，然后直接登录mysql，此时mysql里的用户会使哪个呢？
1[root@linux-test ~]#su - xyy
2[xyy@linux-test ~]$mysql
3Welcome to the MariaDB monitor. Commands end with ; or \g.
4Your MariaDB connection id is 13
5Server version: 10.4.33-MariaDB MariaDB Server
6
7Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
8
9Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10
11MariaDB [(none)]> status
12--------------
13mysql Ver 15.1 Distrib 10.4.33-MariaDB, for Linux (x86_64) using readline 5.1
14
15Connection id: 13
16Current database:
17Current user: xyy@localhost
18SSL: Not in use
19Current pager: stdout
20Using outfile: ''
21Using delimiter: ;
22Server: MariaDB
23Server version: 10.4.33-MariaDB MariaDB Server
24Protocol version: 10
25Connection: Localhost via UNIX socket
26Server characterset: latin1
27Db characterset: latin1
28Client characterset: utf8
29Conn. characterset: utf8
30UNIX socket: /var/lib/mysql/mysql.sock
31Uptime: 1 hour 40 min 47 sec
32
33Threads: 7 Questions: 150 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 30 Queries per second avg: 0.024
34--------------
35
36MariaDB [(none)]> use mysql;
37ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
38MariaDB [(none)]>
可以看到是匿名用户登录。
如果我指定以root身份登录呢？
1[xyy@linux-test ~]$mysql -uroot
2ERROR 1698 (28000): Access denied for user 'root'@'localhost'
3[xyy@linux-test ~]$
会报错，无权限。
那么，这个是什么原因呢，是和版本有关。（我现在用的是10.4）
在10.3版本里：
可以看到，切换到普通用户登录mysql时，默认是以root身份登录上去的。
因此，在早期mysql版本里，基本没安全可言：
任何账户都可以以root账户登录数据库，以任何匿名账户都可以登录数据库。
因此：这里要做安全加固的。
范例：%代表从任何机器都可以访问mysql
1select user,host from user;命令查看mysql用户名里时，host字段一个%代表什么意思？
在 MySQL 中，user 表位于 mysql 数据库中，用于存储数据库用户的信息。通过运行 SELECT user, host FROM mysql.user; 命令，你可以查看现有的数据库用户及其关联的主机信息。
在这个表中，host 字段表示用户可以从哪些主机连接到 MySQL 服务器。字段值中的 % 符号是一个通配符，表示任何主机。具体来说：
- 当
host字段的值为%时，表示该用户可以从任意主机连接到 MySQL 服务器。
例如，如果你有以下记录：
1user | host
2---------|-----
3alice | %
4bob | localhost
alice用户可以从任何主机连接到 MySQL 服务器。bob用户只能从localhost（本地主机）连接到 MySQL 服务器。
使用 % 作为通配符在某些情况下很有用，但需要注意的是，这样做可能会带来安全风险，因为它允许从任意主机进行连接。如果不需要这种全局访问权限，最好将 host 字段限制为特定的主机或IP地址以增强安全性。
查看当前用户
root：mysql里的超级用户。
我们现在登录的就是这个root账号：
查看当前登录用户：
方法1：
1mysql> select User();
2+----------------+
3| User() |
4+----------------+
5| root@localhost |
6+----------------+
71 row in set (0.00 sec)
8
9mysql>
方法2：status
1mysql> \s
2--------------
3mysql Ver 14.14 Distrib 5.6.47, for linux-glibc2.12 (x86_64) using EditLine wrapper
4
5Connection id: 8
6Current database: mysql
7Current user: root@localhost
8SSL: Not in use
9Current pager: stdout
10Using outfile: ''
11Using delimiter: ;
12Server version: 5.6.47 MySQL Community Server (GPL)
13Protocol version: 10
14Connection: Localhost via UNIX socket
15Server characterset: latin1
16Db characterset: latin1
17Client characterset: utf8
18Conn. characterset: utf8
19UNIX socket: /tmp/mysql.sock
20Uptime: 16 days 22 hours 45 min 27 sec
21
22Threads: 1 Questions: 46 Slow queries: 0 Opens: 87 Flush tables: 1 Open tables: 80 Queries per second avg: 0.000
23--------------
24
25mysql>
查看某张表所有列的内容
1MariaDB [mysql]> desc user;
2+------------------------+---------------------+------+-----+----------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+------------------------+---------------------+------+-----+----------+-------+
5| Host | char(60) | NO | | | |
6| User | char(80) | NO | | | |
7| Password | longtext | YES | | NULL | |
8| Select_priv | varchar(1) | YES | | NULL | |
9| Insert_priv | varchar(1) | YES | | NULL | |
10| Update_priv | varchar(1) | YES | | NULL | |
11| Delete_priv | varchar(1) | YES | | NULL | |
12| Create_priv | varchar(1) | YES | | NULL | |
13| Drop_priv | varchar(1) | YES | | NULL | |
14| Reload_priv | varchar(1) | YES | | NULL | |
15| Shutdown_priv | varchar(1) | YES | | NULL | |
16| Process_priv | varchar(1) | YES | | NULL | |
17| File_priv | varchar(1) | YES | | NULL | |
查看支持所有的字符集
1mysql> SHOW CHARACTER SET;
2+----------+-----------------------------+---------------------+--------+
3| Charset | Description | Default collation | Maxlen |
4+----------+-----------------------------+---------------------+--------+
5| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
6| dec8 | DEC West European | dec8_swedish_ci | 1 |
7| cp850 | DOS West European | cp850_general_ci | 1 |
8| hp8 | HP West European | hp8_english_ci | 1 |
9| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
10| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
11| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
12| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
13| ascii | US ASCII | ascii_general_ci | 1 |
14| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
15| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
16| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
17| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
18| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
19| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
20| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
21| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
22| cp1250 | Windows Central European | cp1250_general_ci | 1 |
23| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
24| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
25| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
26| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
27| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
28| cp866 | DOS Russian | cp866_general_ci | 1 |
29| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
30| macce | Mac Central European | macce_general_ci | 1 |
31| macroman | Mac West European | macroman_general_ci | 1 |
32| cp852 | DOS Central European | cp852_general_ci | 1 |
33| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
34| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
35| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
36| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
37| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
38| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
39| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
40| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
41| binary | Binary pseudo charset | binary | 1 |
42| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
43| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
44| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
45+----------+-----------------------------+---------------------+--------+
4640 rows in set (0.01 sec)
47
48mysql>
范例：字符集相关文件
1[root@linux-test ~]#ll /usr/share/mysql/charsets/
2total 232
3-rw-r--r--. 1 root root 5526 Apr 20 2018 armscii8.xml
4-rw-r--r--. 1 root root 5512 Apr 20 2018 ascii.xml
5-rw-r--r--. 1 root root 8241 Apr 20 2018 cp1250.xml
6-rw-r--r--. 1 root root 8365 Apr 20 2018 cp1251.xml
7-rw-r--r--. 1 root root 5569 Apr 20 2018 cp1256.xml
8-rw-r--r--. 1 root root 8902 Apr 20 2018 cp1257.xml
9-rw-r--r--. 1 root root 5506 Apr 20 2018 cp850.xml
10-rw-r--r--. 1 root root 5528 Apr 20 2018 cp852.xml
11-rw-r--r--. 1 root root 5613 Apr 20 2018 cp866.xml
12-rw-r--r--. 1 root root 6529 Apr 20 2018 dec8.xml
13-rw-r--r--. 1 root root 5516 Apr 20 2018 geostd8.xml
14-rw-r--r--. 1 root root 5728 Apr 20 2018 greek.xml
15-rw-r--r--. 1 root root 5517 Apr 20 2018 hebrew.xml
16-rw-r--r--. 1 root root 5502 Apr 20 2018 hp8.xml
17-rw-r--r--. 1 root root 18307 Apr 20 2018 Index.xml
18-rw-r--r--. 1 root root 5529 Apr 20 2018 keybcs2.xml
19-rw-r--r--. 1 root root 5510 Apr 20 2018 koi8r.xml
20-rw-r--r--. 1 root root 6532 Apr 20 2018 koi8u.xml
21-rw-r--r--. 1 root root 9816 Apr 20 2018 latin1.xml
22-rw-r--r--. 1 root root 7238 Apr 20 2018 latin2.xml
23-rw-r--r--. 1 root root 5515 Apr 20 2018 latin5.xml
24-rw-r--r--. 1 root root 7438 Apr 20 2018 latin7.xml
25-rw-r--r--. 1 root root 8047 Apr 20 2018 macce.xml
26-rw-r--r--. 1 root root 8058 Apr 20 2018 macroman.xml
27-rw-r--r--. 1 root root 1749 Apr 20 2018 README
28-rw-r--r--. 1 root root 6530 Apr 20 2018 swe7.xml
29[root@linux-test ~]#
查看当前字符集的使用情况
1mysql> show variables like 'character%';
2+--------------------------+----------------------------------------------------------------+
3| Variable_name | Value |
4+--------------------------+----------------------------------------------------------------+
5| character_set_client | utf8 |
6| character_set_connection | utf8 |
7| character_set_database | latin1 |
8| character_set_filesystem | binary |
9| character_set_results | utf8 |
10| character_set_server | latin1 |
11| character_set_system | utf8 |
12| character_sets_dir | /usr/local/mysql-5.6.47-linux-glibc2.12-x86_64/share/charsets/ |
13+--------------------------+----------------------------------------------------------------+
148 rows in set (0.00 sec)
15
16mysql>
查看支持所有排序规则
1mysql> show COLLATION;
查看当前使用的排序规则
1mysql> SHOW VARIABLES LIKE 'collation%';
2+----------------------+-------------------+
3| Variable_name | Value |
4+----------------------+-------------------+
5| collation_connection | utf8_general_ci |
6| collation_database | latin1_swedish_ci |
7| collation_server | latin1_swedish_ci |
8+----------------------+-------------------+
93 rows in set (0.00 sec)
10
11mysql>
查看当前连接数据库的端口
1mysql> show variables like 'port';
2+---------------+-------+
3| Variable_name | Value |
4+---------------+-------+
5| port | 3306 |
6+---------------+-------+
71 row in set (0.00 sec)
8
9
10mysql> show variables like 'PORT';
11+---------------+-------+
12| Variable_name | Value |
13+---------------+-------+
14| port | 3306 |
15+---------------+-------+
161 row in set (0.01 sec)
17
18mysql>
创建数据库
1CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
2CHARACTER SET 'character set name'
3COLLATE 'collate name';
4
5
6CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
7
8
9CREATE DATABASE|SCHEMA 'DB_NAME';
🍊 范例：创建数据库
1mysql> create database db1;
2Query OK, 1 row affected (0.00 sec)
3
4mysql> show create database db1;
5+----------+-----------------------------------------------------------------+
6| Database | Create Database |
7+----------+-----------------------------------------------------------------+
8| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
9+----------+-----------------------------------------------------------------+
101 row in set (0.00 sec)
11
12mysql>
13[root@linux-test ~]#cat /data/mysql/db1/db.opt
14default-character-set=utf8mb4
15default-collation=utf8mb4_general_ci
16[root@linux-test ~]#
17
18
19
20mysql> create database db1;
21ERROR 1007 (HY000): Can't create database 'db1'; database exists
22mysql> create database IF NOT EXISTS db1;
23Query OK, 1 row affected, 1 warning (0.00 sec)
24
25mysql> show warnings;
26+-------+------+----------------------------------------------+
27| Level | Code | Message |
28+-------+------+----------------------------------------------+
29| Note | 1007 | Can't create database 'db1'; database exists |
30+-------+------+----------------------------------------------+
311 row in set (0.00 sec)
32
33mysql>
🍊 范例：指定字符集创建新数据库
1mysql> create database IF NOT EXISTS db2 CHARACTER SET 'utf8';
2Query OK, 1 row affected (0.01 sec)
3
4mysql> show create database db2;
5+----------+--------------------------------------------------------------+
6| Database | Create Database |
7+----------+--------------------------------------------------------------+
8| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
9+----------+--------------------------------------------------------------+
101 row in set (0.00 sec)
11
12mysql>
13
14
15default-character-set=utf8
16default-collation=utf8_general_ci
17[root@linux-test ~]#
修改数据库的字符集
1mysql> show create database db1;
2+----------+-----------------------------------------------------------------+
3| Database | Create Database |
4+----------+-----------------------------------------------------------------+
5| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
6+----------+-----------------------------------------------------------------+
71 row in set (0.00 sec)
8
9mysql> alter database db1 character set utf8;
10Query OK, 1 row affected (0.00 sec)
11
12mysql> show create database db1;
13+----------+--------------------------------------------------------------+
14| Database | Create Database |
15+----------+--------------------------------------------------------------+
16| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
17+----------+--------------------------------------------------------------+
181 row in set (0.00 sec)
19
20mysql>
21
22[root@linux-test ~]#cat /data/mysql/db1/db.opt
23default-character-set=utf8
24default-collation=utf8_general_ci
25[root@linux-test ~]#
删除数据库
1DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
🍊 范例：删除数据库
1mysql> show databases;
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| db1 |
7| db2 |
8| mysql |
9| performance_schema |
10+--------------------+
115 rows in set (0.00 sec)
12
13mysql> drop database db1;
14Query OK, 0 rows affected (0.02 sec)
15
16mysql> show databases;
17+--------------------+
18| Database |
19+--------------------+
20| information_schema |
21| db2 |
22| mysql |
23| performance_schema |
24+--------------------+
254 rows in set (0.00 sec)
26
27mysql>
28
29[root@linux-test ~]#ls /data/mysql/db1
30ls: cannot access /data/mysql/db1: No such file or directory
31[root@linux-test ~]#
🍊 范例：如果在数据库目录里创建一个文件，删除数据库后，还能删除这个数据库目录吗
1[root@linux-test ~]#ll /data/mysql/db2
2total 4
3-rw-rw---- 1 mysql mysql 61 Apr 23 07:04 db.opt
4[root@linux-test ~]#touch /data/mysql/db2/xxx.txt
5[root@linux-test ~]#ll /data/mysql/db2
6total 4
7-rw-rw---- 1 mysql mysql 61 Apr 23 07:04 db.opt
8-rw-r--r-- 1 root root 0 Apr 23 07:25 xxx.txt
9[root@linux-test ~]#
10
11
12mysql> drop database db2;
13ERROR 1010 (HY000): Error dropping database (can't rmdir './db2/', errno: 17)
14mysql>
15
16#修改权限
17[root@linux-test ~]#ll /data/mysql/db2
18total 0
19-rw-r--r-- 1 root root 0 Apr 23 07:25 xxx.txt
20[root@linux-test ~]#chown -R mysql.mysql /data/mysql/
21[root@linux-test ~]#ll /data/mysql/db2
22total 0
23-rw-r--r-- 1 mysql mysql 0 Apr 23 07:25 xxx.txt
24[root@linux-test ~]#
25
26
27#再次删除还是报错……
28mysql> drop database db2;
29ERROR 1010 (HY000): Error dropping database (can't rmdir './db2/', errno: 17)
30mysql> show databases;
31+--------------------+
32| Database |
33+--------------------+
34| information_schema |
35| db2 |
36| mysql |
37| performance_schema |
38+--------------------+
394 rows in set (0.00 sec)
40
41mysql>
🍊 范例：删除mysql数据库（自杀）
1mysql> show databases;
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| db2 |
7| mysql |
8| performance_schema |
9+--------------------+
104 rows in set (0.00 sec)
11
12mysql> drop database mysql;
13Query OK, 28 rows affected, 2 warnings (0.03 sec)
14
15mysql> show databases;
16+--------------------+
17| Database |
18+--------------------+
19| information_schema |
20| db2 |
21| performance_schema |
22+--------------------+
233 rows in set (0.00 sec)
24
25mysql>
26
27
28##删除mysql数据库后，停止mysql服务再次启动后，会报错的，完了，mysql废了哈哈😂
29[root@linux-test ~]#systemctl status mysql
30● mysqld.service - LSB: start and stop MySQL
31 Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
32 Active: active (running) since Tue 2024-04-23 06:40:40 CST; 47min ago
33 Docs: man:systemd-sysv-generator(8)
34 Process: 6518 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
35 CGroup: /system.slice/mysqld.service
36 ├─6536 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/linux...
37 └─6788 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/...
38
39Apr 23 06:40:40 linux-test systemd[1]: Starting LSB: start and stop MySQL...
40Apr 23 06:40:40 linux-test mysqld[6518]: Starting MySQL SUCCESS!
41Apr 23 06:40:40 linux-test systemd[1]: Started LSB: start and stop MySQL.
42[root@linux-test ~]#systemctl stop mysql
43[root@linux-test ~]#systemctl start mysql
44Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
45[root@linux-test ~]#
46[root@linux-test ~]#netstat -ntlp
47Active Internet connections (only servers)
48Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
49tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 6514/sshd
50tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6877/master
51tcp6 0 0 :::22 :::* LISTEN 6514/sshd
52tcp6 0 0 ::1:25 :::* LISTEN 6877/master
53[root@linux-test ~]#
直接指定对应数据库
就不需要再次使用use来特地登录数据库了：
1[root@linux-test ~]#mysql -uroot -pxyy520 test
2Warning: Using a password on the command line interface can be insecure.
3Reading table information for completion of table and column names
4You can turn off this feature to get a quicker startup with -A
5
6Welcome to the MySQL monitor. Commands end with ; or \g.
7Your MySQL connection id is 11
8Server version: 5.6.47 MySQL Community Server (GPL)
9
10Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
11
12Oracle is a registered trademark of Oracle Corporation and/or its
13affiliates. Other names may be trademarks of their respective
14owners.
15
16Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17
18mysql> show tables;
19+----------------+
20| Tables_in_test |
21+----------------+
22| autiinc1 |
23| student |
24| student2 |
25| t1 |
26| t2 |
27| testdate |
28| testdate2 |
29| testdate3 |
30+----------------+
318 rows in set (0.00 sec)
32
33mysql>
2种写法都可以的
1mysql> select * from t1;
2+----+
3| id |
4+----+
5| 1 |
6| 2 |
7+----+
82 rows in set (0.00 sec)
9
10mysql> select *from t1;
11+----+
12| id |
13+----+
14| 1 |
15| 2 |
16+----+
172 rows in set (0.00 sec)
创建复合主键的方式
1CREATE TABLE student_info (
2 stu_id INT(11) NOT NULL AUTO_INCREMENT ,
3 stu_name VARCHAR(255) DEFAULT NULL,
4 PRIMARY KEY (stu_id,stu_name)
5);
查看mysql线程
106:37:52(root@localhost) [hellodb]> show processlist;
2+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
3| Id | User | Host | db | Command | Time | State | Info | Progress |
4+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
5| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
6| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
7| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
8| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
9| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
10| 13 | admin | 172.29.9.31:38934 | NULL | Sleep | 3199 | | NULL | 0.000 |
11| 29 | root | localhost | hellodb | Query | 0 | Init | show processlist | 0.000 |
12+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
137 rows in set (0.000 sec)
mysql客户端命令
- 客户端命令：本地执行，每个命令都完整形式和简写格式
1mysql> \h, help
2mysql> \u，use
3mysql> \s，status
4mysql> \!，system #（和awk里的system()功能类似） Execute a system shell command.
1MariaDB [mysql]> help
2
3General information about MariaDB can be found at
4http://mariadb.org
5
6List of all client commands:
7Note that all text commands must be first on line and end with ';'
8? (\?) Synonym for `help'.
9clear (\c) Clear the current input statement.
10connect (\r) Reconnect to the server. Optional arguments are db and host.
11delimiter (\d) Set statement delimiter.
12edit (\e) Edit command with $EDITOR.
13ego (\G) Send command to MariaDB server, display result vertically.
14exit (\q) Exit mysql. Same as quit. ##常用
15go (\g) Send command to MariaDB server.
16help (\h) Display this help. ##常用
17nopager (\n) Disable pager, print to stdout.
18notee (\t) Don't write into outfile.
19pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
20print (\p) Print current command.
21prompt (\R) Change your mysql prompt.
22quit (\q) Quit mysql.
23rehash (\#) Rebuild completion hash.
24source (\.) Execute an SQL script file. Takes a file name as an argument.
25status (\s) Get status information from the server. ##常用
26system (\!) Execute a system shell command. ##常用
27tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
28use (\u) Use another database. Takes database name as argument.
29charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
30warnings (\W) Show warnings after every statement.
31nowarning (\w) Don't show warnings after every statement.
32
33For server side help, type 'help contents'
34
35MariaDB [mysql]>
举例:（和awk里的system()功能类似） \!
1system (\!) Execute a system shell command.
2
3MariaDB [(none)]> system ls
4MariaDB [(none)]> system date
5Sat Mar 30 12:17:32 CST 2024
6MariaDB [(none)]> system pwd
7/root
8MariaDB [(none)]>
举例：souce 用sql语言写的sql脚本
1cat > test.sql <<EOF
2use mysql
3select user,host from user;
4EOF
5
6[root@linux-test ~]#cat test.sql
7use mysql
8select user,host from user;
9[root@linux-test ~]#
10
11
12[root@linux-test ~]#mysql
13Welcome to the MariaDB monitor. Commands end with ; or \g.
14Your MariaDB connection id is 12
15Server version: 10.4.33-MariaDB MariaDB Server
16
17Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
18
19Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
20
21MariaDB [(none)]> source test.sql
22Reading table information for completion of table and column names
23You can turn off this feature to get a quicker startup with -A
24
25Database changed
26+-------------+-----------+
27| User | Host |
28+-------------+-----------+
29| mariadb.sys | localhost |
30| mysql | localhost |
31| root | localhost |
32+-------------+-----------+
333 rows in set (0.001 sec)
34
35MariaDB [mysql]>
36
37
38MariaDB [mysql]> \. test.sql
39Database changed
40+-------------+-----------+
41| User | Host |
42+-------------+-----------+
43| mariadb.sys | localhost |
44| mysql | localhost |
45| root | localhost |
46+-------------+-----------+
473 rows in set (0.000 sec)
48
49MariaDB [mysql]>
50
51
52#指定路径
53MariaDB [mysql]> system ls -l /tmp/test.sql
54-rw-r--r-- 1 root root 38 Mar 30 12:27 /tmp/test.sql
55MariaDB [mysql]> \. /tmp/test.sql
56Database changed
57+-------------+-----------+
58| User | Host |
59+-------------+-----------+
60| mariadb.sys | localhost |
61| mysql | localhost |
62| root | localhost |
63+-------------+-----------+
643 rows in set (0.001 sec)
65
66MariaDB [mysql]>
举例：mysql-e选项 非交互式方式
1[root@linux-test ~]#mysql --help
2……
3 -e, --execute=name Execute command and quit. (Disables --force and history
4 file.)
5
6[root@linux-test ~]#mysql -uroot -pxyy520 -e '\s'
7--------------
8mysql Ver 15.1 Distrib 10.4.33-MariaDB, for Linux (x86_64) using readline 5.1
9
10Connection id: 14
11Current database:
12Current user: root@localhost
13SSL: Not in use
14Current pager: stdout
15Using outfile: ''
16Using delimiter: ;
17Server: MariaDB
18Server version: 10.4.33-MariaDB MariaDB Server
19Protocol version: 10
20Connection: Localhost via UNIX socket
21Server characterset: latin1
22Db characterset: latin1
23Client characterset: utf8
24Conn. characterset: utf8
25UNIX socket: /var/lib/mysql/mysql.sock
26Uptime: 2 hours 15 min 18 sec
27
28Threads: 6 Questions: 98 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 30 Queries per second avg: 0.012
29--------------
30
31[root@linux-test ~]#
mysqladmin命令
mysqladmin 命令格式
1mysqladmin [OPTIONS] command command....
范例：
1#查看mysql服务是否正常，如果正常提示mysqld is alive
2[root@linux-test ~]#mysqladmin -uroot -pxyy520 ping
3mysqld is alive
4
5
6#关闭mysql服务，但mysqladmin命令无法开启
7mysqladmin –uroot –pcentos shutdown ##这个方式是比较安全的，也是比较推荐的，它可以正常关闭数据库。 systemctl stop mariadb
8
9
10
11#创建数据库testdb
12mysqladmin -uroot –pcentos create testdb
13#删除数据库testdb
14mysqladmin -uroot -pcentos drop testdb
15
16
17#修改root密码
18mysqladmin –uroot –pcentos password 'magedu'
19
20#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
21mysqladmin -uroot -pcentos flush-logs
22
23#查看变量
24mysqladmin variables |grep port
SQL语言帮助文档
获取SQL 命令使用帮助：
1mysql> HELP KEYWORD
官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
范例：删除mysql空用户
工作里遇到的。
1mysql -uroot -p123456
2
3use mysql;
4select user,host from user;
5
6delete from mysql.user where Host="localhost" and User="";
范例：修改mysql用户密码
关于我
我的博客主旨：
- 排版美观，语言精炼；
- 文档即手册，步骤明细，拒绝埋坑，提供源码；
- 本人实战文档都是亲测成功的，各位小伙伴在实际操作过程中如有什么疑问，可随时联系本人帮您解决问题，让我们一起进步！
🍀 微信二维码
x2675263825 （舍得）， qq：2675263825。
🍀 微信公众号
《云原生架构师实战》
🍀 个人博客站点
🍀 语雀
https://www.yuque.com/xyy-onlyone
🍀 csdn
https://blog.csdn.net/weixin_39246554?spm=1010.2135.3001.5421
🍀 知乎
https://www.zhihu.com/people/foryouone
最后
好了，关于本次就到这里了，感谢大家阅读，最后祝大家生活快乐，每天都过的有意义哦，我们下期见！
📡
推荐使用微信支付
推荐使用支付宝
