任务1 更改root密码

1.更改环境变量

修改/etc/profile文件，文件尾添加mysql的绝对路径，修改环境变量如图所示：

  1. [root@lnmp ~]# vim /etc/profile

image.png

2.创建MySQL密码

使用命令mysqladmin -uroot password ‘123456’为root用户创建初始密码，如图所示：
image.png
可以忽略warning内容，指的是明码输入屏幕不安全。
使用命令mysql -uroot -p123456，完成初始密码登录，如图所示：
image.png

  1. [root@lnmp ~]# mysqladmin -uroot password '123456'
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@lnmp ~]# mysql -uroot -p123456
  4. Warning: Using a password on the command line interface can be insecure.
  5. Welcome to the MySQL monitor.  Commands end with ; or \g.
  6. Your MySQL connection id is 2
  7. Server version: 5.6.47 MySQL Community Server (GPL)
  9. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  17. mysql> exit
  18. Bye
  19. [root@lnmp ~]#

3.密码重置

修改配置文件/etc/my.cnf，在mysqld配置段，增加字段skip-grant，如图所示：
image.png
修改完成后，重启MySQL服务：/etc/init.d/mysqld restart

  1. [root@lnmp ~]# /etc/init.d/mysqld restart
  2. Shutting down MySQL.. SUCCESS! 
  3. Starting MySQL. SUCCESS! 
  4. [root@lnmp ~]#

使用命令登入MySQL（修改的配置段，是完成忽略授权的操作，可以直接登入，无需输入用户名密码），切换到MySQL库，对user表进行更新操作，如图所示：
image.png

  1. [root@lnmp ~]# mysql -uroot -p123456     
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1
  5. Server version: 5.6.47 MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database           |
  18. +--------------------+
  19. | information_schema |
  20. | mysql              |
  21. | performance_schema |
  22. | test               |
  23. +--------------------+
  24. 4 rows in set (0.00 sec)
  26. mysql> use mysql;
  27. Reading table information for completion of table and column names
  28. You can turn off this feature to get a quicker startup with -A
  30. Database changed
  31. mysql> update user set password=password('123456') where user='root';
  32. Query OK, 3 rows affected (0.00 sec)
  33. Rows matched: 4  Changed: 3  Warnings: 0
  35. mysql>

修改完成后，确认新密码登录有效。把/etc/my.cnf改回原有状态，并重启MySQL服务。

  1. [root@lnmp ~]# mysql -uroot -p123456
  2. [root@lnmp ~]# vim /etc/my.cnf
  3. [root@lnmp ~]# /etc/init.d/mysqld restart
  4. Shutting down MySQL.. SUCCESS! 
  5. Starting MySQL. SUCCESS! 
  6. [root@lnmp ~]#

任务2 连接MySQL

1. mysql -uroot -p123456

  1. [root@lnmp ~]# mysql -uroot -p123456     
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 1
  5. Server version: 5.6.47 MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql>

2. mysql -uroot -p123456 -h127.0.0.1 -P3306.

  1. [root@lnmp ~]# mysql -uroot -p123456 -h127.0.0.1 -P3306
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 2
  5. Server version: 5.6.47 MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql>

3. mysql -uroot -p123456 -S/tmp/mysql.sock

  1. [root@lnmp ~]# mysql -uroot -p123456 -S/tmp/mysql.sock   
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 3
  5. Server version: 5.6.47 MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql>

4. mysql -uroot -p123456 -e “show databases”

  1. [root@lnmp ~]# mysql -uroot -p123456 -e "show databases" 
  2. Warning: Using a password on the command line interface can be insecure.
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. | mysql              |
  8. | performance_schema |
  9. | test               |
  10. +--------------------+
  11. [root@lnmp ~]#

任务3 MySQL常用命令

1. 查询库 show databases;

如下图所示：
image.png

2. 切换库 use mysql;

如下图所示：
image.png

3. 查看库里的表 show tables;

如下图所示：

image.png

4. 查看表里的字段 desc tb_name;

如下图所示：
image.png

5. 查看建表语句 show create table tb_name\G;

如下图所示：
image.png

6. 查看当前用户 select user();

如下图所示：
image.png

7. 查看当前使用的数据库 select database();

如下图所示：
image.png

8. 创建库 create database db1;

如下图所示：
image.png

9. 创建表 use db1; create table t1(id int(4), name char(40));

如下图所示：
image.png

10. 查看当前数据库版本 select version();

如下图所示：
image.png

11. 查看数据库状态 show status;

如下图所示：
image.png

12. 查看各参数 show variables; show variables like ‘max_connect%’;

如下图所示：
image.png

12. 查看各参数 show variables; show variables like ‘max_connect%’;

如下图所示：
image.png

14. 查看队列 show processlist; show full processlist;

如下图所示：
image.png

任务4 MySQL创建用户以及授权

1. 进行授权

  1. mysql> grant all on *.* to 'user1' identified by 'passwd';
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
  8. Query OK, 0 rows affected (0.00 sec)
  10. mysql>

image.png

2. 查看授权表

  1. mysql> show grants;
  2. +----------------------------------------------------------------------------------------------------------------------------------------+
  3. | Grants for root@localhost                                                                                                              |
  4. +----------------------------------------------------------------------------------------------------------------------------------------+
  5. | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
  6. | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
  7. +----------------------------------------------------------------------------------------------------------------------------------------+
  8. 2 rows in set (0.00 sec)
  10. mysql> 
  11. mysql> show grants for user2@192.168.133.1;
  12. +------------------------------------------------------------------------------------------------------------------+
  13. | Grants for user2@192.168.133.1                                                                                   |
  14. +------------------------------------------------------------------------------------------------------------------+
  15. | GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
  16. | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1'                                               |
  17. +------------------------------------------------------------------------------------------------------------------+
  18. 2 rows in set (0.00 sec)
  20. mysql>

image.png

任务5 MySQL常用SQL语句

1. 查看表内行数select count(*) from mysql.user;

如下图所示：
image.png

2.查看db表内的内容 select * from mysql.db;

如下图所示：
image.png

3.查看db表内含有db字段的内容 select db from mysql.db;

如下图所示：
image.png

4. 搜索查看多个字段 select db,user from mysql.db;

如下图所示：
image.png

搜索多个字段时，字段中间要用“，”隔开

5. 查询host为127.0的内容 select * from mysql.db where host like ‘192.168.%’;

如下图所示;
image.png

6. 向db1.t1中插入内容 insert into db1.t1 values (1, ‘abc’);

如下图所示：
image.png

7. 把id=1的字段内容更新成aaa update db1.t1 set name=’aaa’ where id=1;

如下图所示：
image.png

8. 清空db1.t1表内的内容 truncate table db1.t1;

如下图所示：
image.png

清空后表的结构依然存在

9. 删除db1.t1表内的内容 drop table db1.t1;

如下图所示：
image.png

清空后连同表的结构一同删除

10. 清空db1.t1数据库 drop database db1;

如下图所示：
image.png

任务6 MySQL数据库的备份与恢复

1. 备份库

  1. [root@lnmp ~]# mysqldump -uroot -p123456 mysql > /tmp/mysql.sql 
  2. Warning: Using a password on the command line interface can be insecure.

2. 恢复库

  1. [root@lnmp ~]# mysql -uroot -p123456 mysql < /tmp/mysql.sql          
  2. Warning: Using a password on the command line interface can be insecure.

3. 备份表

  1. [root@lnmp ~]# mysqldump -uroot -p123456 mysql user > /tmp/user.sql     
  2. Warning: Using a password on the command line interface can be insecure.

4. 恢复表

  1. [root@lnmp ~]# mysql -uroot -p123456 mysql < /tmp/user.sql     
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@lnmp ~]#

备份库、表与恢复库、表如图所示：
image.png

5. 备份所有库

  1. [root@lnmp ~]# mysqldump -uroot -p -A > /tmp/123.sql              
  2. Enter password: 
  3. [root@lnmp ~]#

6. 只备份表结构

  1. [root@lnmp ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql
  2. Warning: Using a password on the command line interface can be insecure.
  3. [root@lnmp ~]#

image.png