MySQL用户管理、常用SQL语句及数据库备份与恢复

MySQL用户管理、常用SQL语句及数据库备份与恢复

evobot 1,015 2018-06-20

本文主要介绍MySQL的用户管理、常用的SQL语句,以及如何使用mysqldump进行数据库的备份和恢复作业,另外介绍了innobackupex工具的使用,这个工具能够进行MySQL数据库的全量与增量备份,并且备份速度更快。


MySQL用户管理

创建用户及授权

针对所有权限

  • 为了保证安全性,我们不能将MySQL的root用户密码给每个开发人员,所以需要单独创建用户并加以授权,让指定的用户只能操作指定的数据库来保证安全;

  • 创建用户使用下面的命令:

    grant all on *.* to 'username'@'127.0.0.1' identified by 'password';
    
    • 这里grant表示授权,all是指所有的操作,如删除、创建、修改等;
    • *.*表示所有的库和表,第一个*指库名,后面一个*表示表名,*.*则表示所有库的所有表;
    • ‘user1'@'127.0.0.1'是指创建user1用户,并且user1只能通过127.0.0.1这个来源ip登录MySQL,也可以写为%表示所有的ip;
    • identified by则是指定user1的密码。
  • 由于在创建用户时指定了来源ip,所以登录时必须使用-h选项指定host,因为默认登录使用的是MySQL的socket进行连接:

    [root@evobot ~]# mysql -uuser1 -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
    
    [root@evobot ~]# mysql -uuser1 -p -h127.0.0.1
    Enter password: 
    Welcome to the MySQL monitor. 
    ...
    
  • 如果想要使用户登录MySQL使用socket连接,那么在指定host时,写为localhost即可,如下:

    grant all on *.* to 'username'@'localhost' identified by 'password';
    

    示例:

    [root@evobot ~]# mysql -uuser5 -p123456
    Warning: Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    mysql> 
    

针对指定权限、数据库

  • 针对指定的数据库授权指定的权限,使用下面的SQL语句:

    grant SELECT,UPDATE,INSERT on db_name.* to 'username'@'localhost' identified by 'password';
    

    示例:

    mysql> grant SELECT,UPDATE,INSERT on test.* to 'user6'@'localhost' identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
    
    [root@evobot ~]# mysql -uuser6 -p
    Enter password: 
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    +--------------------+
    2 rows in set (0.00 sec)
    
    mysql> drop database test;
    ERROR 1044 (42000): Access denied for user 'user6'@'localhost' to database 'test'
    
    • 限定用户只能操作test数据库,所以用户登录后只能看到test数据库,并且由于用户没有drop权限,所以操作会被拒绝。

查看权限

  • 对于已经登录的当前用户,使用show grant;可以查看当前用户的权限:

    mysql> select user();
    +-----------------+
    | user()          |
    +-----------------+
    | user6@localhost |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> show grants;
    +---------------------------------------------------------------------------+
    | Grants for user6@localhost                                                |
    +---------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'user6'@'localhost' IDENTIFIED BY PASSWORD <secret> |
    | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'user6'@'localhost'           |
    +---------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
  • 对于具有操作名为mysql数据库的用户,查看其他用户的权限,使用的sql语句如下:

    show grants for user3@'%';
    

    示例:

    mysql> select user();
    +-----------------+
    | user()          |
    +-----------------+
    | user5@localhost |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for user3@'%';
    +---------------------------------------------------------------------------------------------------------------+
    | Grants for user3@%                                                                                            |
    +---------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'user3'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +---------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    
  • 上面两种show grants命令所列出的权限信息,可以方便我们对用户的权限进行更改,如在不知道用户密码的情况下,需要更改用户的源IP,那么在root用户下可以直接复制列出的语句,把其中的IP更改执行即可:

    mysql> show grants for user5@'localhost';
    +-----------------------------------------------------------------------------------------------------------------------+
    | Grants for user5@localhost                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'user5'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +-----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>  GRANT ALL PRIVILEGES ON *.* TO 'user5'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show grants for user5@'127.0.0.1';
    +-----------------------------------------------------------------------------------------------------------------------+
    | Grants for user5@127.0.0.1                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'user5'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
    +-----------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

常用SQL语句

  • 操作MySQL的SQL主要就是增删改查,包括selectinsertupdatedrop等;

查询语句

  • select count(*) from mysql.user;,其中count(*)是统计行数,即统计mysql库中user表一共有多少条数据:

    mysql> select count(*) from mysql.user;
    +----------+
    | count(*) |
    +----------+
    |       15 |
    +----------+
    1 row in set (0.00 sec)
    
    
  • select * from mysql.db;,查看mysql库的db表中所有的数据,一般不建议使用,查询所有数据非常耗费资源;

  • select db from mysql.db;,查看mysql.db表中的db字段,字段名可以有多个:

    mysql> select db,user from mysql.db;
    +---------+-------+
    | db      | user  |
    +---------+-------+
    | test    |       |
    | test\_% |       |
    | test    | user6 |
    +---------+-------+
    3 rows in set (0.00 sec)
    
    
  • select * from mysql.db where host like 'local%'\G;:

    mysql> select * from mysql.db where host like 'local%';
    +-----------+------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | Host      | Db   | User  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
    +-----------+------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    | localhost | test | user6 | Y           | Y           | Y           | N           | N           | N         | N          | N               | N          | N          | N                     | N                | N                | N              | N                   | N                  | N            | N          | N            |
    +-----------+------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
    1 row in set (0.00 sec)
    
    

增加语句

  • insert into db_name.tb_name values (value1,value2);,向指定的数据表内插入一条数据:

    mysql> insert into evobot.test1 values (1,'lilei');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from test1;
    +------+-------+
    | id   | name  |
    +------+-------+
    |    1 | lilei |
    +------+-------+
    1 row in set (0.00 sec)
    
    

    插入数据的时候,数据的格式要符合表的字段定义,如字符,就需要使用单引号。

修改语句

  • update db_name.tb_name set field='new_value' where filed=1;,表示修改指定表中指定字段的值:

    mysql> update evobot.test1 set name='hanmeimei' where id=1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from test1;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | hanmeimei |
    +------+-----------+
    1 row in set (0.00 sec)
    
    

    如果where筛选出的有多条数据,那么筛选出来的数据都会被修改。

删除语句

  • delete from db_name.tb_name where filed=value;,删除指定表中的指定的数据:

    mysql> delete from evobot.test1 where id=1;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test1;
    Empty set (0.00 sec)
    
    
  • truncate db_name.tb_name;,清空指定的表的所有内容:

    mysql> select * from test1;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | lilei     |
    |    2 | hanmeimei |
    +------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> truncate evobot.test1;
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> select * from test1;
    Empty set (0.00 sec)
    
    mysql> desc test1;
    +-------+----------+------+-----+---------+-------+
    | Field | Type     | Null | Key | Default | Extra |
    +-------+----------+------+-----+---------+-------+
    | id    | int(4)   | YES  |     | NULL    |       |
    | name  | char(40) | YES  |     | NULL    |       |
    +-------+----------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    
    
  • drop table db_name.tb_name;,表示删除指定的数据表,drop database db_name;表示删除数据库:

    mysql> drop table test1;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> drop database evobot;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
    
  • 删除、清空数据库或表的操作一定要谨慎,一旦删错而又没有备份,会导致严重后果!

数据库备份恢复

  • 备份数据库,是非常重要的操作,能够保证我们的数据库在出现问题时,尽快恢复到之前的状态和数据;

备份操作

  • mysqldump -uroot -p db_name > /path/to/db_backup.sql,表示备份指定的数据库:

    [root@evobot ~]# mysqldump -uroot -p123456 mysql > /tmp/mysql_bak.sql
    Warning: Using a password on the command line interface can be insecure.
    
  • 备份表的操作,相比备份数据库,只需要在库名后加上表名即可,命令为mysqldump -uroot -p db_name tb_name > /path/to/backup.sql

    mysqldump -uroot -p evobot test1 > /tmp/tb_test1.sql
    
  • 备份有的数据库,使用命令mysqldump -uroot -p -A /path/to/bak.sql

    mysqldump -uroot -p -A > /tmp/mysql_A.sql
    
  • 只备份表的结构,是指不备份数据库表的数据内容,只备份表的结构,即备份文件中没有insert语句,命令为mysqldump -uroot -p -d db_name > /path/to/bak.sql

    [root@evobot ~]# mysqldump -uroot -p -d evobot > /tmp/evobot.sql
    Enter password: 
    [root@evobot ~]# cat /tmp/evobot.sql 
    -- MySQL dump 10.13  Distrib 5.6.36, for linux-glibc2.5 (x86_64)
    --
    -- Host: localhost    Database: evobot
    -- ------------------------------------------------------
    -- Server version	5.6.36
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    --
    -- Table structure for table `test1`
    --
    
    DROP TABLE IF EXISTS `test1`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `test1` (
      `id` int(4) DEFAULT NULL,
      `name` char(40) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    /*!40101 SET character_set_client = @saved_cs_client */;
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2018-06-20 23:41:10
    

恢复操作

  • mysqldump -uroot -p db_name < /path/to/backup.sql,表示将备份sql文件恢复到指定库中:

    [root@evobot ~]# mysql -uroot -p123456 -e "create database mysql2"
    
    [root@evobot ~]# mysql -uroot -p mysql2 < /tmp/mysql_bak.sql 
    
    # 登录并直接进入mysql2数据库
    [root@evobot ~]# mysql -uroot -p mysql2
    
    mysql> select database();
    +------------+
    | database() |
    +------------+
    | mysql2     |
    +------------+
    1 row in set (0.00 sec)
    
    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          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | 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                      |
    +---------------------------+
    28 rows in set (0.00 sec)
    
    
  • 恢复备份的数据表,与恢复数据库相同,指定数据库名即可;

innobackex备份工具

安装

  • mysqldump只适合备份数据量小的数据库和表,一旦数据量大,则不再使用mysqldump,而是使用xtrbackupinnobackupex这两个工具;

  • xtrbackup只能用于备份innodb引擎的数据库,而innobackupex 是在xtrbackup基础上增加的工具,既可以备份innodb引擎的数据库,也可以备份myisam引擎的数据库。备份时也可分为全量备份和增量备份。

  • 使用innobackupex,需要先安装percona-release扩展源:

    rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
    
  • 然后安装percona-xtrbackup软件包,mysql5.7版本需要使用percona-xtrabackup2.4以上版本:

    yum install percona-xtrabackup
    

全量备份

  1. 首先创建并授权备份用户,为了保证安全,备份用户不授予all权限,而是只给予RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS四个权限:

    GRANT RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost' identified by '123456';
    
    FLUSH PRIVILEGES;
    
  2. 接着创建保存备份的目录:

    $ mkdir /data/backup
    
  3. 完成后就可以使用innobackup命令进行备份,备份为全量备份,命令如下:

    $ innobackupex --defaults-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock /data/backup/
    
    • 其中--default-file是指定mysql的配置文件my.cnf所在位置,这个选项必须紧跟在命令后面作为第一个选项;
    • --user--password则是上一步在mysql中创建的备份用户的用户名和密码;
    • -S则是指定mysql的socket所在路径;
    • 最后再指定备份保存的路径即可;其余选项如指定端口等,可以查看innobackupex的帮助文档 。
  4. 备份完成后,会在指定的备份目录中生成以备份时间命名的目录:

    $ ls
    2018-06-25_23-00-40
    $ ls 2018-06-25_23-00-40/
    backup-my.cnf   ibdata1  performance_schema  xtrabackup_checkpoints  xtrabackup_logfile
    ib_buffer_pool  mysql    sys                 xtrabackup_info
    

全量备份恢复

  1. 恢复已经备份的数据库,需要先停止MySQL服务,然后将原有的MySQL数据目录重命名保存,并重新创建数据目录同时更改属主和属组为mysql:

    $ mv /data/mysql/ /data/mysql.bak
    $ mkdir /data/mysql
    $ chown -R mysql.mysql /data/mysql
    $ ls -ld /data/mysql
    drwxr-xr-x. 2 mysql mysql 6 6月  25 23:11 /data/mysql
    
    
  2. 仍然使用innobackupex进行恢复操作,命令如下:

    $ innobackupex --use-memory=512M --apply-log /data/backup/2018-06-25_23-00-40/
    
    
    • --use-memory选项是指定回复时使用的内存大小,一般指定的内存越大,恢复速度越快;
    • --apply-log则是指定备份的所在路径,即前面备份时以时间命令的目录路径。
  3. 完成恢复到MySQL操作:

    $ innobackupex --default-file=/etc/my.cnf --copy-back /data/backup/2018-06-25_23-00-40/ 
    
    
    • 同样需要指定MySQL配置文件所在路径,并且使用--copy-back选项指定备份所在路径,同时,这里省略了--use-memory选项,需要的时候可以加上这个选项。
  4. 最后重新更改数据库目录权限即可启动MySQL服务:

    $ chown -R mysql.mysql /data/mysql
    

增量备份

  1. 增量备份需要在全量备份的基础上进行,我们首先安装全量备份的操作,进行一次全量备份,然后再对数据库进行一些操作,如新增数据库表,可以MySQL官方仓库下载测试数据库然后导入到MySQL中:
$ innobackupex --default-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock /data/backup/

$ mysql -uroot -p < employees.sql
  1. 首先进行第一次增量备份操作:
$ innobackupex --default-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock --incremental /data/backup/ --incremental-basedir=/data/backup/2018-06-25_23-00-40/
  • --incremental选项指定备份存放路径,--incremental-basedir则是指定首次全量备份存放的以时间命名的目录,这是因为我们的增量是在指定的全量备份基础上进行的备份;
  • 第一次增量备份的文件夹为2018-06-25_23-41-08
  1. 再对数据库进行一些操作,以便进行第二次增量备份:
$ mysql -uroot -p -e "create database testdb"

$ mysql -uroot -p testdb < /root/123.sql 
  1. 进行第二次增量备份:
$ innobackupex --default-file=/etc/my.cnf --user=bakuser --password='123456' -S /tmp/mysql.sock --incremental /data/backup/ --incremental-basedir=/data/backup/2018-06-25_23-41-08/

  • 需要注意,第二次增量备份指定的incremental-basedir不再是全量备份目录,而是第一次增量备份的目录;
  1. 我们查看三次备份目录中的xtrabackup_checkpoints文件:

    $ cat 2018-06-25_23-00-40/xtrabackup_checkpoints 
    backup_type = full-prepared
    from_lsn = 0
    to_lsn = 2559852
    last_lsn = 2559861
    compact = 0
    recover_binlog_info = 0
    
    $ cat 2018-06-25_23-41-08/xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 2559852
    to_lsn = 293401900
    last_lsn = 293401909
    compact = 0
    recover_binlog_info = 0
    
    $ cat 2018-06-25_23-54-25/xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 293401900
    to_lsn = 321030299
    last_lsn = 321030308
    compact = 0
    recover_binlog_info = 0
    
    
    • 这个文件中,全量备份的from_lsn为0,to_lsn则是第一次增量备份的from_lsn,这样三次备份的检查点就是连续的;
    • 如果在第二次增量时,incremental-basedir指定的依然是全量备份,那么第一次增量备份就被跳过,在恢复时,就不再需要恢复第一次增量备份,可以根据需要选择增量的incremental-basedir。

增量备份恢复

  1. 首先依然是停止MySQL服务,并且将原数据存储目录重命名保存,然后重新创建数据存储目录,并更改属主和属组为mysql;

  2. 然后与全量备份恢复不同的是,增量恢复需要将增量备份合并到全量备份中去:

    $ innobackupex --apply-log --redo-only /data/backup/2018-06-25_23-00-40/
    
    • 这里需要使用--redo-only参数,跟随的参数值是全量备份的路径,用来初始化全量备份,将增量备份合并逐步合并到全量备份中;
  3. 合并第一次增量备份:

    $ innobackupex --apply-log --redo-only /data/backup/2018-06-25_23-00-40/ --incremental-dir=/data/backup/2018-06-25_23-41-08/
    
    • --redo-only仍然指向全量备份,而incremental-basedir则指向第一次增量备份。
  4. 合并第二次增量备份

    $ innobackupex --apply-log /data/backup/2018-06-25_23-00-40/ --incremental-dir=/data/backup/2018-06-25_23-54-25/
    
    
    • 合并最后一次的增量备份则不需要使用--redo-only选项。
  5. 再次初始化全量备份,同样不再使用--redo-only选项:

    $ innobackupex --apply-log /data/backup/2018-06-25_23-00-40/
    
  6. 最后与全量备份恢复一样,执行恢复数据库操作:

    $ innobackupex --default-file=/etc/my.cnf --copy-back /data/backup/2018-06-25_23-00-40/
    
    • 这里的copy-back指定的是全量备份,因为增量备份都已经合并到全量中去了。
  7. 更改权限然后启动MySQL服务:

    $ chown -R mysql.mysql /data/mysql
    

恢复操作中,一旦出错,会导致全量备份数据污染,所以在执行恢复操作前,要将备份文件再拷贝一份另存,防止出现数据损毁。



# Linux # Centos # MySQL