mysql数据基础常用命令汇总
从零开始搭建mysql服务
一台vps虚拟主机：
系统：centos7
配置：1核1g内存
云服务商：谷歌云
1.清除冲突软件mariadb(如果安装了的话)
rpm -q mariadb-server mariadb systemctl stop mariadb rpm -e --nodeps mariadb mariadb-server rm -rf /etc/my.cnf rm -rf /var/lib/mysql/*
卸载原有MySQL
rpm -qa | grep -i mysql查看已安装MySQLyum -y remove删除上述提示的所有MySQLfind / -name mysql查找MySQL的所有目录
2.下载mysql的rpm包mysql-5.7.17.tar
yum -y install wget && wget https://alist.yyzq.cf/d/%20%E6%9C%AC%E5%9C%B0%E7%BD%91%E7%9B%98/linux/mysql-5.7.17.tar
3.安装软件mysql 社区开源版软件
tar -xf mysql-5.7.17.tar yum -y install mysql-community-*.rpm
4.启动服务并设置开机运行 查看进程和端口
systemctl start mysqld systemctl enable mysqld netstat -utnlp | grep 3306 # 等效与 ss -utnlp | grep 3306 ps -C mysqld #等效于 ps aux | grep mysqld
5.查看连接MySQL服务初始密码
grep password /var/log/mysqld.log | tail -1 2022-05-06T08:12:39.685990Z 1 [Note] A temporary password is generated for root@localhost: p.7jr.uy.aiZ
6.使用初始密码连接服务
mysql -hlocalhost -uroot -p'p.7jr.uy.aiZ'
7.修改登录密码（服务强制修改且修改的密码要符合服务要求的复杂度）
mysql> alter user root@"localhost" identified by "123qqq...A";
8.断开连接
mysql> exit;
9.使用修改的密码登录并查看数据
mysql -hlocalhost -uroot -p123qqq...A
10.查看已有的库 默认的4个库 不允许删除 库存放的是不同类型的数据
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
11.mysql常用查看数据
查看数据库版本。
select version();
+-----------+
| version() |
+-----------+
| 5.7.17 |
+-----------+
1 row in set (0.00 sec)
查看某项配置，如连接数设置。
show variables like '%max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
查看当前连接信息。
show full processlist;
+----+------+-----------+------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+-----------------------+
| 5 | root | localhost | NULL | Query | 0 | starting | show full processlist |
+----+------+-----------+------+---------+------+----------+-----------------------+
1 row in set (0.00 sec)
查看当前登录用户。
select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
查看数据库列表。
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
切换到数据库
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
查看所有库表，需先切换库。
show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
查看某个表结构。
desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.02 sec)
查看数据库用户列表。
select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user order by query;
+--------------------------------+
| query |
+--------------------------------+
| User: 'mysql.sys'@'localhost'; |
| User: 'root'@'localhost'; |
+--------------------------------+
2 rows in set (0.01 sec)修改数据库服务器的密码策略（设置密码的复杂度）
说明：要求数据管理员root 能连接数据库服务
1 查看默认使用的密码策略和密码长度
2 命令行修改密码策略和密码长度
3 修改密码验证修改的密码策略和密码长度
4 永久修改密码策略和密码长度
Bash
[root@host50 ~]# mysql -hlocalhost -uroot -p123qqq...A
SQL
mysql> show variables like "%password%"; #查看与密码相关的配置项 mysql> set global validate_password_policy=0; #修改密码等级为0 mysql> set global validate_password_length=6; #修改最小密码长度 mysql> alter user root@"localhost" identified by "tarena"; #根据新密码策略修改密码 mysql> exit;
Bash
[root@host50 ~]# mysql -hlocalhost -uroot -ptarena #使用修改后的密码登陆
SQL
mysql> exit;
Bash
[root@host50 ~]# vim /etc/my.cnf #（永久配置）把修改 添加到配置文件里 数据库服务重启了 依然有效 [mysqld] validate_password_policy=0 validate_password_length=6 :wq [root@host50 ~]# systemctl restart mysqld #重启服务 [root@host50 ~]# mysql -hlocalhost -uroot -ptarena #登陆后
SQL
mysql> show variables like "%password%"; #查看密码策略
增加新用户
grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"
例如：增加一个用户yyzq密码为Www.yyzq.cf，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* to yyzq@localhost Identified by "Www.yyzq.cf";
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
update user set Host='%' where User='root'; FLUSH PRIVILEGES;
