Mysql 2（6月20日）

2018-06-20

13.4 mysql用户管理
13.5 常用sql语句
13.6 mysql数据库备份恢复

13.4 MySQL用户管理

创建用户并授权

用法：grant 权限 on 库.表用户名@来源IP identified by 密码;

1 2	mysql> grant all on . to 'user1'@'127.0.0.1' identified by '38003800'; Query OK, 0 rows affected (0.00 sec)

all表示所有权限包括SELECT UPDATE DROP INSERT *表示所有库或者表
'user1'@'127.0.0.1' 表示新建user1只能运行127.0.0.1登录

1 2	mysql> grant all on test1.* to 'user2'@'%' identified by '38003800'; Query OK, 0 rows affected (0.00 sec)

%表示所有来源IP

1 2	mysql> grant SELECT,INSERT,UPDATE on test1.* to 'user3'@'localhost' identified by '38003800'; Query OK, 0 rows affected (0.00 sec)

'user3'@'localhost' 表示新建user3既可以使用本地sock登录也可以使用127.0.0.1登录
SELECT UPDATE DROP INSERT 表示增删改查权限

查看用户的授权情况

用法：show grant for 用户@来源IP;

mysql> show grants for 'user3'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for user3@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user3'@'localhost' IDENTIFIED BY PASSWORD '*D0A07A890AC619CBC04D5BF7F677AF7D162E90DF' |
| GRANT SELECT, INSERT, UPDATE ON `test1`.* TO 'user3'@'localhost'                                             |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

当遇到需要把user1用户添加多一个来源IP，而且不知道他的密码，可以使用show grant for 来显示授权情况，再添加来源IP即可

步骤

1.查看user1用户授权

mysql> show grants for 'user1'@'127.0.0.1';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for user1@127.0.0.1                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'127.0.0.1' IDENTIFIED BY PASSWORD '*D0A07A890AC619CBC04D5BF7F677AF7D162E90DF' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.把查看到的授权命令改好来源IP后全部执行一次

这里为user1用户添加192.168.80.102来源IP登录

1 2	mysql> GRANT ALL PRIVILEGES ON . TO 'user1'@'192.168.80.102' IDENTIFIED BY PASSWORD '*D0A07A890AC619CBC04D5BF7F6677AF7D162E90DF'; Query OK, 0 rows affected (0.00 sec)

3.查看user1授权情况

mysql> show grants for 'user1'@'192.168.80.102';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@192.168.80.102                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'192.168.80.102' IDENTIFIED BY PASSWORD '*D0A07A890AC619CBC04D5BF7F677AF7D162E90DF' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@localhost ~]# mysql -uuser1 -p38003800 -h192.168.80.102
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 9
Server version: 5.6.36 MySQL Community Server (GPL)

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

说明user1可以上192.168.80.102上登录

查看当前用户的授权情况

用法：show grants;

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D0A07A890AC619CBC04D5BF7F677AF7D162E90DF' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

13.5 常用sql语句

查询某个表中的行数

用法：select count(*) from 库.表;

查询user表中的总行数

mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

查询某个表中的所有内容

用法：select * from 库.表;

查询user表中所有内容

mysql> select * from mysql.user\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *D0A07A890AC619CBC04D5BF7F677AF7D162E90DF
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y

由于数据量大因此少使用select *

查询表中某个字段

用法：select 字段 from 库.表;

查询出db表中的db字段

mysql> select db from mysql.db;
+---------+
| db      |
+---------+
| test    |
| test1   |
| test\_% |
| test1   |
+---------+
4 rows in set (0.00 sec)

查询表中多个字段

用法：select 字段1,字段2 from 库.表;

查询出db表中db和user字段

mysql> select db,user from mysql.db;
+---------+-------+
| db      | user  |
+---------+-------+
| test    |       |
| test1   | user2 |
| test\_% |       |
| test1   | user3 |
+---------+-------+
4 rows in set (0.00 sec)

查询表中某个具体的内容

用法：select * from 库.表 where 关键字 like 内容;

查询出user表中host中带192.168.80.的内容

mysql> select * from mysql.user where host like '192.168.80.%'\G;
*************************** 1. row ***************************
                  Host: 192.168.80.102
                  User: user1
              Password: *D0A07A890AC619CBC04D5BF7F677AF7D162E90DF
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y

在表中增加内容

用法：insert into 库.表 values (数据1,数据2...);

在test1库中的t2表上增加1和aaa的数据

mysql> insert into test1.t2 values (1,'aaa');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1.t2;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

t2表中有两个字段

在表中修改内容

用法：update 库.表 set 字段=修改的内容 where 关键字;

把t2表中的id=1那行中的name改为bbb

mysql> update test1.t2 set name='bbb' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test1.t2;
+------+------+
| id   | name |
+------+------+
|    1 | bbb  |
+------+------+
1 row in set (0.00 sec)

清空表中的内容（保留字段）

用法：truncate 库.表;

清空t2表中的内容

mysql> truncate test1.t2;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test1.t2;
Empty set (0.00 sec)

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | YES  |     | NULL    |       |
| name  | char(40) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

t2表中的字段还存在

删除表

用法：drop table 库.表;

删除t2表

1 2	mysql> drop table test1.t2; Query OK, 0 rows affected (0.01 sec)

删除库

用法：drop database 库;

删除test1库

1 2	mysql> drop database test1; Query OK, 0 rows affected (0.02 sec)

13.6 MySQL数据库备份恢复

备份库

用法：mysqldump -u用户 -p密码库 > 备份文件

1	[root@localhost ~]# mysqldump -uroot -p'38003800' mysql > /tmp/mysqlbak.sql

恢复库

用法：mysql -u用户 -p密码库 < 备份文件

先创建一个新库mysql2

1	[root@localhost ~]# mysql -uroot -p'38003800' -e "create database mysql2"

把mysql的数据恢复到mysql2中

1	[root@localhost ~]# mysql -uroot -p'38003800' mysql2 < /tmp/mysqlbak.sql

进入mysql2库看看是否恢复成功

1	[root@localhost ~]# mysql -uroot -p'38003800' mysql2 ##后面带库名可以直接进入该库

mysql> show tables;
+---------------------------+
| Tables_in_mysql2          |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |

这里的表内容和mysql里面的一致

备份表

用法：mysqldump -u用户 -p密码库表 > 备份文件

1	[root@localhost ~]# mysqldump -uroot -p'38003800' mysql user > /tmp/userbak.sql

恢复表

用法：mysql -u用户 -p密码库 < 备份文件
恢复表和恢复库的命令是一样的只是备份文件的内容不一样

1	[root@localhost ~]# mysql -root -p'38003800' mysql < /tmp/userbak.sql

备份所有库

用法：mysqldump -u用户 -p密码 -A > 备份文件

1	[root@localhost ~]# mysqldump -uroot -p'38003800' -A >/tmp/all.sql

只备份表结构

用法：mysqldump -u用户 -p密码 -d 库 > 备份文件

把mysql库里面的表结构作备份

1	[root@localhost ~]# mysqldump -uroot -p'38003800' -d mysql >/tmp/mysql1.sql

innobackupex进行全局备份

当遇到比较大的数据时候 mysqldump备份就不好使实在太慢了，因此可以使用xtrabackup来备份但他只能备份innodb引擎的数据库，后来他们又出了个innobackupex工具 innobackupex既可以备份innodb引擎的数据库，也可以备份myisam引擎的数据库。备份时也可分为全量备份和增量备份。

步骤

1.安装yum的扩展源

1	[root@localhost ~]# rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

2.使用yum安装xtrabackup工具

1	[root@localhost ~]# yum install -y percona-xtrabackup

xtrabackup工具里面包含有innobackupex工具

3.在MySQL中创建用于备份的用户（授权某些权限）并加载权限

[root@localhost ~]# mysql -uroot -p'38003800'   ##进入MySQL

mysql> grant reload,lock tables,replication client on *.* to 'backup'@'localhost' identified by '38003800';

mysql> flush privileges;

建立backup用户 reload表示重新加载权限 lock tables表示锁表权限 replication client 表示复制从服务器和主服务器的位置的权限
具体权限 https://blog.csdn.net/qq_29101355/article/details/76135971

4.建立备份目录

1	[root@localhost ~]# mkdir /backup/

5.备份MySQL的所有数据到备份目录下

1	[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=backup --password='38003800' -S /tmp/mysql.sock /backup/

--defaults-file指定MySQL的配置文件 /backup/是备份目录

恢复MySQL数据

先模拟数据库损坏

[root@localhost ~]# service mysqld stop   ##停止MySQL服务
[root@localhost ~]# mkdir -p /tmp/bak/mysql/
[root@localhost ~]# mv /data/ /tmp/bak/mysql/data-bak/  ##把datadir移走到其他目录
[root@localhost ~]# mkdir /data/   ##新建个空的datadir
[root@localhost ~]# chown mysql:mysql /data/

1.进入备份目录并初始化

1 2	[root@localhost ~]# cd /backup/ [root@localhost backup]# innobackupex --use-memory=512M --apply-log 2018-06-19_19-50-14/

--use-memory指定使用内存的大小 --apply-log指定备份的原文件

2.恢复数据

1	[root@localhost backup]# innobackupex --defaults-file=/etc/my.cnf --copy-back 2018-06-19_19-50-14/

--copy-back指定备份的原文件

3.更改datadir的权限

1	[root@localhost ~]# chown -R mysql:mysql /data/

4.启动MySQL服务

1	[root@localhost ~]# service mysqld start

innobackupex进行增量备份

进行了全量备份后，由于工作需要，可能经过一段时间后会对MySQL的内容有所更改，这时候再使用全量备份不太合适，此时可以使用增量备份。
这里我进行两次增量备份为例

步骤

1.全量备份

1	[root@localhost ~]# innobackupex --default-file=/etc/my.cnf --user=backup --password='38003800' -S /tmp/mysql.sock /backup/

让MySQL的数据更改

1
2

[root@localhost ~]# mysql -uroot -p38003800 -e "create database zeng1"  ##增加zeng1库
[root@localhost ~]# mysql -uroot -p38003800 zeng1 < /tmp/mysqlbak.sql  ##把mysqlbak.sql的数据恢复到zeng1中

2.第一次增量备份

1	[root@localhost ~]# innobackupex --default-file=/etc/my.cnf --user=backup --password='38003800' -S /tmp/mysql.sock --incremental /backup/ --incremental-basedir=/backup/2018-06-19_21-50-20/

--incremental 表示增量备份 --incremental-basedir指定全量备份文件的路径

再一次更改MySQL的数据

1 2	[root@localhost ~]# mysql -uroot -p38003800 -e "create database zeng2" [root@localhost ~]# mysql -uroot -p38003800 zeng2 < /tmp/mysqlbak.sql

3.第二次增量备份

1	[root@localhost ~]# innobackupex --default-file=/etc/my.cnf --user=backup --password='38003800' -S /tmp/mysql.sock --incremental /backup/ --incremental-basedir=/backup/2018-06-19_22-07-34/

--incremental 表示增量备份 --incremental-basedir 此时指定第一次备份文件的路径

1 2	[root@localhost ~]# ls /backup/ 2018-06-19_21-50-20 2018-06-19_22-07-34 2018-06-19_22-51-04

2018-06-19_21-50-20是全量备份 2018-06-19_22-07-34是第一次增量 2018-06-19_22-51-04是第二次增量

恢复MySQL数据

先模拟数据库损坏

[root@localhost ~]# service mysqld stop   ##停止MySQL服务
[root@localhost ~]# mkdir -p /tmp/bak/mysql/
[root@localhost ~]# mv /data/ /tmp/bak/mysql/data-bak2/  ##把datadir移走到其他目录
[root@localhost ~]# mkdir /data/   ##新建个空的datadir
[root@localhost ~]# chown mysql:mysql /data/

1.进入备份目录并整合全量备份文件

1 2	[root@localhost ~]# cd /backup/ [root@localhost backup]# innobackupex --apply-log --redo-only 2018-06-19_21-50-20

--redo-only是增量备份恢复的参数 2018-06-19_21-50-20指定全量备份文件

2.把第一次增量备份的文件整合到全量备份文件中

1	[root@localhost backup]# innobackupex --apply-log --redo-only 2018-06-19_21-50-20 --incremental-dir=/backup/2018-06-19_22-07-34

--incremental-dir=/backup/2018-06-19_22-07-34指定第一次增量备份文件

3.把第二次增量备份的文件整合到全量备份文件中

1	[root@localhost backup]# innobackupex --apply-log 2018-06-19_21-50-20 --incremental-dir=/backup/2018-06-19_22-51-04

把最后一次增量备份文件整理到全量备份文件时候不需要使用--redo-only参数 --incremental-dir=/backup/2018-06-19_22-51-04指定第二次增量备份文件

4.初始化全量备份文件

1	[root@localhost backup]# innobackupex --apply-log 2018-06-19_21-50-20/

5.恢复数据

1	[root@localhost backup]# innobackupex --copy-back 2018-06-19_21-50-20/

--copy-back指定整合后的全量备份文件

6.查看datadir并更改datadir权限和启动服务

1
2
3

[root@localhost backup]# ls /data/
ibdata1             ib_logfile1         mysql2/             test/               zeng1/
ib_logfile0         mysql/              performance_schema/ xtrabackup_info     zeng2/

datadir目录下有zeng1和zeng2两个数据库

1	[root@localhost backup]# chown -R mysql:mysql /data/

1	[root@localhost backup]# service mysqld start