当前位置:首页 > 技术分享 > 正文内容

mysql数据基础常用命令汇总

有云转晴3年前 (2022-05-06)技术分享275890

从零开始搭建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

  1. rpm -qa | grep -i mysql 查看已安装MySQL

  2. yum -y remove 删除上述提示的所有MySQL

  3. find / -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;



mysql数据基础常用命令汇总  您阅读本篇文章共花了: 

分享到:

    扫描二维码推送至手机访问。

    版权声明:本文由有云转晴发布,如需转载请注明出处。

    本文链接:https://www.yyzq.cf/?id=173

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法和观点。