MySQL常用命令/数据库备份与恢复

文章目录
1.MySQL常用命令
2.MySQL创建用户以及用户授权
3.常用MySQL语句
4.MySQL密码设置与更改
5.MySQL数据备份与恢复
1.MySQL常用命令
显示所有库：

show databases;
1
切换到mysql库：

use mysql;
1
显示mysql库中的表：

show tables;
1
查看tb_user表的字段：

desc tb_user;
1
查看建表语句：

show create table tb_user\G
1
#\G：有序的显示（不加\G输出的内容排版混乱，使用\G后sql语句可以不加分号）

查看当前用户：

select user();
1
查看当前所在数据库：

select database();
1
查看mysql版本：

select version();
1
创建数据库：

create database db_1;
1
创建表：

create table tb_1(idint(4),namechar(40));
1
创建表的同时指定引擎、字符编码：

create table tb_1(idint(4),namechar(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
查看数据库状态：

show status;
1
查看各参数配置：

show variables;
1
查看指定参数：

show variables like ‘slow%’;
1
临时修改参数值：

set global max_connect_errors=200;
1
#mysql重启后失效，需要永久修改编辑/etc/my.cnf配置文件

查看mysql消息队列：

show processlist;
show full processlist;
1
2
#使用full可以将查询结果info字段中正在执行的sql语句显示完整

2.MySQL创建用户以及用户授权
1.创建用户test，密码test123：

grant all on . to ‘test’@‘localhost’ identified by ‘test123’;
1
*.表示：库.表，也就是授权所有库的所有表，只授权db1库的所有表：db1.

localhost表示授权只能从本机通过socket文件登录，常用登录方式：mysql -uroot -p就是通过socket文件mysql.sock登录，命令写全为：

mysql -uroot -pxxx -S/tmp/mysql.sock
1
如果授权从127.0.0.1登录，那么登录时需要加上-h参数指定host：

mysql -uroot -pxxx -h127.0.0.1
1
授权所以地址登录使用百分号%即可：‘test’@’%’

2.创建用户test2，密码test123：

grant SELECT,UPDATE,INSERT on mysql.* to ‘test2’@‘192.168.234.128’ identified by ‘test123’;
1
授权用户test2只能从192.168.234.128登录，并且只有mysql库所有表的查询、更改、新增权限

mysql授权用户的登录IP为整个网段：‘test2’@‘192.168.234.%’

3.查询mysql用户授权内容：

show grants;
1
#默认查询当前用户的授权信息

查询指定用户的授权信息：

MariaDB [(none)]> show grants for test2@‘192.168.234.128’;
±-------------------------------------------------------------------------------------------------------------------+
| Grants for test2@192.168.234.128 |
±-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘test2’@‘192.168.234.128’ IDENTIFIED BY PASSWORD '676243218923905CF94CB52A3C9D3EB30CE8E20D’ |
| GRANT SELECT, INSERT, UPDATE ON mysql. TO ‘test2’@‘192.168.234.128’ |
±-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
1
2
3
4
5
6
7
8
一般用于增加授权IP，当前test2用户授权地址为192.168.234.128，如果需要给test2用户添加一个授权地址192.168.234.130，直接复制表格中的两句内容执行即可：

MariaDB [(none)]> GRANT USAGE ON . TO ‘test2’@‘192.168.234.130’ IDENTIFIED BY PASSWORD ‘*676243218923905CF94CB52A3C9D3EB30CE8E20D’;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE ON mysql.* TO ‘test2’@‘192.168.234.130’;
Query OK, 0 rows affected (0.000 sec)
1
2
3
4
5
注意：mysql执行创建用户或密码变更操作后需要执行：flush privileges 刷新系统权限相关的表才能生效，或者重启mysql服务使其生效

3.常用MySQL语句
查询：select * from table;

统计行数：select(*) from table;

更改数据：update table set tb_xxx=xxx;

删除数据：delete from table where tb_xxx=xxx;

清空表数据：truncate table_name;（保留表结构）

删除表：drop table table_name;

删除库：drop database db_name;

4.MySQL密码设置与更改
设置root用户密码：

[root@linux ~]# mysqladmin -uroot password “123456”
1
更改root用户密码：

[root@linux ~]# mysqladmin -uroot -p’123456’ password ‘654321’
1
登录数据库更改密码：

MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set password=password(‘123456’) where user=‘root’;
1
2
#password()：mysql加密函数，使密码在表中不以明文显示

注意：在当前较高的mysql版本中，root密码已经不存放在user表的password字段，而是存放在authentication_string字段

高版本mysql更改密码：

MariaDB [mysql]> update user set authentication=password(‘123456’) where user=‘root’;
1
在不知道root用户密码的情况下修改密码的方法，在上一篇文章中有介绍：
https://blog.csdn.net/Powerful_Fy/article/details/102689689

5.MySQL数据备份与恢复
备份mysql库：

[root@linux ~]# mysqldump -uroot -p123456 mysql > /tmp/mysql_bak.sql
1
恢复备份的mysql库到mysql2数据库：

[root@linux ~]# mysql -uroot -p123456 mysql2 < /tmp/mysql_bak.sql
1
备份mysql库中的user表：

[root@linux ~]# mysqldump -uroot -p123456 mysql user > /tmp/mysql_tbuser.sql
1
恢复备份的user表到mysql库：

[root@linux ~]# mysql -uroot -p123456 mysql < /tmp/mysql_tbuser.sql
1
备份所有库：

[root@linux ~]# mysqldump -uroot -p123456 -A > /tmp/mysql_all.sql
1
只备份mysql库的表结构：

[root@linux ~]# mysqldump -uroot -p123456 -d mysql > /tmp/mysql_tb.sql
1
只备份mysql库user表的表结构：

[root@linux ~]# mysqldump -uroot -p123456 -d mysql user > /tmp/mysql_user.sql
1
补充：mysqldump方法备份适用于数据量不大的数据库，数据量几百G，甚至多少T的数据量备份起来会非常的慢，可以使用xtrabackup工具，不仅支持全量备份，还支持增量备份

xtrabackup工具使用介绍：https://blog.csdn.net/Powerful_Fy/article/details/102749992