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