1.MySQL常用命令实践小结

1.1MySQL启动命令

补充说明：

Centos7.2Yum安装MySQL
~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm  #下载MySQL源安装包
~]# yum localinstall mysql57-community-release-el7-8.noarch.rpm               #安装MySQL源
~]# yum repolist enabled | grep "mysql.*-community.*"                         #检查是否安装成功
~]# yum install mysql-community-server                                        #安装MySQL
~]# systemctl start mysqld
~]# systemctl status mysqld.service

1.MySQL单实例启动命令

~]# systemctl start mysqld           ##yum安装，如果编译安装使用MySQL的命令路径
[root@node01 ~]# lsof -i:3306
COMMAND      PID      USER     FD    TYPE   DEVICE   SIZE/OFF NODE NAME
mysqld       2304     mysql    22u   IPv6   22489    0t0      TCP  *:mysql (LISTEN)
MySQL安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录MySQL进行修改：
[root@node01 ~]# grep 'temporary password' /var/log/mysqld.log
2018-06-14T14:01:32.403670Z 1 [Note] A temporary password is generated for root@localhost: 7Qe398gp-?L*
[root@node01 ~]# mysql -uroot -p7Qe398gp-?L*
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.22

Copyright (c) 2000, 2018, 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>set password for 'root'@'localhost'=password('@sangfor123');     
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决办法
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@validate_password_length; 参数默认为8，它有最小值的限制，最小值为
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                        8   |
+----------------------------+
1 row in set (0.00 sec)
这些参数，默认值均为1，所以validate_password_length最小值为4
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@validate_password_length;
+----------------------------+
| @@validate_password_length |
+----------------------------+
|                        4   |
+----------------------------+
1 row in set (0.00 sec)

mysql> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

当然，前提是validate_password插件必须已经安装，MySQL5.7是默认安装的。
那么如何验证validate_password插件是否安装呢？可通过查看以下参数，如果没有安装，则输出将为空。
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             |  4    |
| validate_password_mixed_case_count   |  1    |
| validate_password_number_count       |  1    |
| validate_password_policy             |  LOW  |
| validate_password_special_char_count |  1    |
+--------------------------------------+-------+
7 rows in set (0.02 sec)

退出重新登录
[root@node01 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

2.多实例启动与关闭

启动：
${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf &>/dev/null
关闭：
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock_path}/mysql.sock shutdown

3.MySQL启动基本原理

先调用mysql_safe，再调用mysqld主程序启动mysql

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args &>/dev/null
mysqld_safe --user=mysql & 这样也能启动MySQL
说明：
1.当我们root密码忘记的时候可以用这条命令带上参数启动
2.当我们自己写脚本的时候可能也会用到这中方法
3.它与/etc/init.d/mysqld的启动实质是一样的

1.2登录MySQL数据库

1.单实例MySQL登录

musql
mysql -uroot
mysql -uroot -p
##强制Linux不记录历史命令
HISTCONTROL=ignorespace   ##登录MySQL的时候开头输入一个空格 则不记录密码

2.多实例MySQL登录

mysql -uroot -p${mysql_pwd} -S ${mysql_sock_file} 
远程登录MySQL多实例无需指定sock路径：
mysql -uroot -p${mysql_pwd} -h ${server_IP} -P${port}

更改MySQL数据库登录提示符方法如下：

mysql> prompt \u@mysql01 \r:\m:\s->
PROMPT set to '\u@mysql01 \r:\m:\s->'
root@mysql01 02:20:07->
配置文件修改
[mysql]
prompt=\\u@mysql01 \\r:\\m:\\s->

MySQL帮助

help grant #不等
删除root用户进行创建，必须加上grant option才等价与root用户

1.3设置及修改MySQL root用户密码

MySQL数据库用户安全策略

删除无用的myslq库用户账号，包括root超级用户

delete from mysql.user;
增加system提升超级用户等价root用户
grant all privileges on *.* to system@'localhost' identufied by '123456' with grant option;
带密码的文件或脚本权限最好给700，用户和组最好是root或mysql
delete from mysql.user where user="root" and host="John";
drop无法删除时使用delete删

设置密码及更改密码

mysqladmin -u root password '123456'   #多实例  mysqladmin -u root -S ${sock_path} password '123456'
更改密码：
mysqladmin -u root -p${pwd} password '@sangfor123'
登录数据库修改密码：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)

mysql> update mysql.user set authentication_string=password('12345678') where user='root' and host='localhost';  #5.7版本命令改了5.6也能用
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1

1.4找回丢失的MySQL root密码

1.单实例找回

停止MySQL
~]# systemctl stop mysqld  #Centos7.2系统
~]# mysqld_safe –user=mysql –skip-grant-tables –skip-networking &
mysql> update mysql.user set authentication_string=password('12345678') where user='root' and host='localhost';

2.多实例找回

~]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
~]# mysql -uroot -p -S /data/3306/mysql.sock #登录时空密码
mysql> update mysql.user set password=password('12345678') where user='root' and host='localhost';

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quilt ##退出

1.5SQL结构化查询语言

1.SQL语言存取数据库使用的语言

结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统

2.SQL分类

DDL（Data Definition Language）数据定义语言（CREATE , ALTER , DROP），管理基础数据，例如：库，表  ##熟练

DCL（Data Control Language）数据控制语言（GRANT , REVOKE , COMMIT , ROLLBACK），用户授权，权限回收，数据提交回滚  ##熟练

DML（Data Manipulation Language）数据操作语言（SELECT , INSERT , DELETE , UPDATE），针对数据库里的表，记录

1.6MySQL数据库常见管理应用

命令小结：

mysql> show databases;                     ##查询数据库
mysql> create database john;               ##创建数据库
mysql> show create database john;          ##查看建库结构语句 
mysql> create database john_gbk default character set gbk collate gbk_chinese_ci; ##创建字符集gbk数据库
Query OK, 1 row affected (0.00 sec)
mysql> show character set;                 ##查看字符集
mysql> show databases like "my%";          ##模糊查询相当grep过滤
mysql> drop database john_gbk;             ##删除数据库
mysql> system pwd                          ##不跳出mysql命令窗口执行shell命令行命令
/root
mysql> select database();                  ##查看当前所在数据库位置
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use john;                          ##进入使用数据库
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| john       |
+------------+
1 row in set (0.00 sec)

mysql> select user();                     ##查看当前用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

表的操作语句：
mysql> select user,host from mysql.user;  ##查看现在用户是什么，在user，host字段去mysql的user表查
两种授权的方法：
第一种先创建后授权：
mysql> create user test@'localhost' identified by '123456';
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for test@'localhost'\G
*************************** 1. row ***************************
Grants for test@localhost: GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.07 sec)

mysql> grant all on john.* to test@'localhost'; ##授予所有权限john库的所有表给test用户
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

第二种授权方法：
mysql> grant all on db1.* to 'jeffrey'@'localhost' identified by 'mypass';
Query OK, 0 rows affected (0.00 sec)
命令说明：
grant 授权命令
all 对应的权限
on dbname.* 目标：库和表
to username@‘localhost’ 用户名和客户端主机
identified by ‘mypass’ 用户密码
说明：
如果是web连接数据库的用户，不用授予all，而是select，insert，update，delete的权限

回收权限：
mysql> REVOKE INSERT ON john.* FROM 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test@'localhost'\G
*************************** 1. row ***************************
Grants for test@localhost: GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE
4568DDA7DC67ED2CA2AD9'
*************************** 2. row ***************************
Grants for test@localhost: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORAR
Y TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `john`.*
TO 'test'@'localhost'
2 rows in set (0.00 sec)

可设置的权限：
mysql> select * from mysql.user\G

*************************** 8. row ***************************
 Host: localhost
 User: jeffrey
 Password: *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
 Select_priv: N
 Insert_priv: N
 Update_priv: N
 Delete_priv: N
 Create_priv: N
 Drop_priv: N
 Reload_priv: N
 Shutdown_priv: N
 Process_priv: N
 File_priv: N
 Grant_priv: N
 References_priv: N
 Index_priv: N
 Alter_priv: N
 Show_db_priv: N
 Super_priv: N
 Create_tmp_table_priv: N
 Lock_tables_priv: N
 Execute_priv: N
 Repl_slave_priv: N
 Repl_client_priv: N
 Create_view_priv: N
 Show_view_priv: N
 Create_routine_priv: N
 Alter_routine_priv: N
 Create_user_priv: N
 Event_priv: N
 Trigger_priv: N
Create_tablespace_priv: N
 ssl_type: 
 ssl_cipher: 
 x509_issuer: 
 x509_subject: 
 max_questions: 0
 max_updates: 0
 max_connections: 0
 max_user_connections: 0
 plugin: 
 authentication_string: NULL
8 rows in set (0.00 sec)

Get busy living or get busy dying. 努力活出精彩的人生，否则便如行尸走肉

 

 

AD：《熊孩子博客》熊孩子QQ:443060965