IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
  • 登录 注册

    MySQL:备份与恢复(mysqldump)

    Adamhuan发表于 2016-08-22 16:00:40
    love 0

    如题所示,本文会记录一些关于通过【mysqldump】备份与恢复MySQL数据库的样例。

    首先查看当前我的环境中的MySQL的现状:

    [root@mysql-1 mysql]# rpm -qa | grep MySQL --color
    MySQL-shared-compat-5.5.37-1.rhel5.x86_64
    MySQL-client-5.5.37-1.rhel5.x86_64
    MySQL-test-5.5.37-1.rhel5.x86_64
    MySQL-server-5.5.37-1.rhel5.x86_64
    MySQL-embedded-5.5.37-1.rhel5.x86_64
    MySQL-devel-5.5.37-1.rhel5.x86_64
    MySQL-shared-5.5.37-1.rhel5.x86_64
    [root@mysql-1 mysql]# 
    [root@mysql-1 mysql]# rpm -ql MySQL-server | grep --color ".cnf"
    /etc/my.cnf
    /usr/share/doc/MySQL-server-5.5.37/my-huge.cnf
    /usr/share/doc/MySQL-server-5.5.37/my-innodb-heavy-4G.cnf
    /usr/share/doc/MySQL-server-5.5.37/my-large.cnf
    /usr/share/doc/MySQL-server-5.5.37/my-medium.cnf
    /usr/share/doc/MySQL-server-5.5.37/my-small.cnf
    /usr/share/mysql/my-huge.cnf
    /usr/share/mysql/my-innodb-heavy-4G.cnf
    /usr/share/mysql/my-large.cnf
    /usr/share/mysql/my-medium.cnf
    /usr/share/mysql/my-small.cnf
    [root@mysql-1 mysql]#

    查看当前MySQL状态:

    [root@mysql-1 software]# service mysql status
    MySQL running (33961)[  OK  ]
    [root@mysql-1 software]# 
    [root@mysql-1 software]# ps faux | grep --color mysql
    root      34009  0.0  0.0 103316   828 pts/3    S+   04:15   0:00  |       \_ grep --color mysql
    root      33870  0.0  0.0  11344  1388 pts/3    S    04:14   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql-1.pid
    mysql     33961  0.1  2.1 422688 40168 pts/3    Sl   04:14   0:00  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-1.err --pid-file=/var/lib/mysql/mysql-1.pid
    [root@mysql-1 software]#

    可以看到:
    这个环境中的MySQL的数据目录位于:/var/lib/mysql
    日志文件:/var/lib/mysql/mysql-1.err(在MySQL中,错误日志一般是:【hostname】.error)

    在我的环境中,我的备份策略设定是这样的:

    [root@mysql-1 software]# env | grep --color LANG
    LANG=en_US.UTF-8
    [root@mysql-1 software]# export LANG=en_US
    [root@mysql-1 software]# 
    [root@mysql-1 software]# tree /backup/
    /backup/
    `-- db
        `-- mysql
            |-- dumpfile
            |   |-- MySQL_db_mysql_after_adamhuan.backup
            |   `-- MySQL_db_mysql.backup
            `-- tarfile
    
    4 directories, 2 files
    [root@mysql-1 software]#

    其中:
    dumpfile,存放通过mysqldump导出的备份文件。
    tarfile,存放通过tar压缩的MySQL数据目录。(该文件可用的前提是,MySQL停机状态下的冷备份。)

    一、冷备:数据目录
    先停库,做一个冷备:

    [root@mysql-1 software]# service mysql status
    MySQL running (33961)[  OK  ]
    [root@mysql-1 software]# 
    [root@mysql-1 software]# service mysql stop
    Shutting down MySQL.[  OK  ]
    [root@mysql-1 software]# 
    [root@mysql-1 software]# ps -ef | grep mysql
    root      34083   9675  0 04:22 pts/3    00:00:00 grep mysql
    [root@mysql-1 software]# 
    [root@mysql-1 software]# netstat -tupln | grep mysql
    [root@mysql-1 software]# 
    
    [root@mysql-1 tarfile]# pwd     
    /backup/db/mysql/tarfile
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# ls -ltr
    total 0
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# ls -ltr /var/lib/mysql/
    total 233524
    drwxr-xr-x. 2 mysql mysql      4096 Aug 23 01:03 test
    drwx------. 2 mysql mysql      4096 Aug 23 01:03 performance_schema
    drwx------. 2 mysql mysql      4096 Aug 23 03:10 kohler_run_minisite_bj
    drwx------. 2 mysql mysql      4096 Aug 23 03:10 web_kohler_wx
    drwx------. 2 mysql mysql      4096 Aug 23 03:10 web_kohler_wx_admin
    drwx------. 2 mysql mysql     12288 Aug 23 03:10 chinawebsite
    drwx--x--x. 2 mysql mysql      4096 Aug 23 03:19 mysql
    -rw-r--r--. 1 mysql mysql       500 Aug 23 04:13 RPM_UPGRADE_MARKER-LAST
    -rw-r--r--. 1 mysql mysql       613 Aug 23 04:13 RPM_UPGRADE_HISTORY
    -rw-rw----. 1 mysql mysql   5242880 Aug 23 04:14 ib_logfile1
    -rw-rw----. 1 mysql mysql 228589568 Aug 23 04:22 ibdata1
    -rw-rw----. 1 mysql mysql   5242880 Aug 23 04:22 ib_logfile0
    -rw-r-----. 1 mysql mysql      6816 Aug 23 04:22 mysql-1.err
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# du -sh /var/lib/mysql/
    232M    /var/lib/mysql/
    [root@mysql-1 tarfile]#

    不同的打包(压缩)策略的差异:
    tar -cf

    [root@mysql-1 tarfile]# tar -cf MySQL_coldbackup_datadir_20160822_2254.tar /var/lib/mysql
    tar: Removing leading `/' from member names
    [root@mysql-1 tarfile]# ls
    MySQL_coldbackup_datadir_20160822_2254.tar
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# du -sh MySQL_coldbackup_datadir_20160822_2254.tar 
    232M    MySQL_coldbackup_datadir_20160822_2254.tar
    [root@mysql-1 tarfile]#

    tar -cjf

    [root@mysql-1 tarfile]# tar -cjf MySQL_coldbackup_datadir_20160822_2254.tar.bz2 /var/lib/mysql
    tar: Removing leading `/' from member names
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# du -sh *
    232M    MySQL_coldbackup_datadir_20160822_2254.tar
    52M     MySQL_coldbackup_datadir_20160822_2254.tar.bz2
    [root@mysql-1 tarfile]#

    tar -czf

    [root@mysql-1 tarfile]# tar -czf MySQL_coldbackup_datadir_20160822_2254.tar.gz /var/lib/mysql
    tar: Removing leading `/' from member names
    [root@mysql-1 tarfile]# 
    [root@mysql-1 tarfile]# du -sh *
    232M    MySQL_coldbackup_datadir_20160822_2254.tar
    52M     MySQL_coldbackup_datadir_20160822_2254.tar.bz2
    64M     MySQL_coldbackup_datadir_20160822_2254.tar.gz
    [root@mysql-1 tarfile]#

    可以看到,通过bzip2压缩的压缩率是最高的。

    二、热备:mysqldump导出
    查看当前的数据库状态:

    [root@mysql-1 software]# mysql -uroot -p'abcd1234' 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.5.37 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> 
    mysql> select user,host,password from mysql.user;
    +------+-----------+-------------------------------------------+
    | user | host      | password                                  |
    +------+-----------+-------------------------------------------+
    | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1   |                                           |
    | root | 127.0.0.1 |                                           |
    | root | ::1       |                                           |
    |      | localhost |                                           |
    |      | mysql-1   |                                           |
    +------+-----------+-------------------------------------------+
    6 rows in set (0.00 sec)
    
    mysql> 
    mysql> exit
    Bye
    [root@mysql-1 software]#

    备份当前的数据库状态:

    [root@mysql-1 ~]# cd /backup/db/mysql/dumpfile/
    [root@mysql-1 dumpfile]# ls -ltr
    total 0
    [root@mysql-1 dumpfile]#

    备份全库:

    [root@mysql-1 ~]# cd /backup/db/mysql/dumpfile/
    [root@mysql-1 dumpfile]# ls -ltr
    total 0
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_all_db.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# ls -ltr
    total 528
    -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    [root@mysql-1 dumpfile]#

    备份某个特定的库:

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events mysql > MySQL_db_mysql.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# ls -ltr
    total 1056
    -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp
    -rw-r--r-- 1 root root 538540 Aug 23 01:16 MySQL_db_mysql.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_mysql.dmp
    [root@mysql-1 dumpfile]#

    注意,上面备份中都添加了“–events”目的是为了过滤mysql.events的数据,否则,导出的过程中,会出现这样的警告(warning):

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --all-databases > MySQL_all_db.dmp
    -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
    [root@mysql-1 dumpfile]#

    对于普通的库来说,mysqldump导出的时候,是不需要添加“–events”选项的。

    向数据库中添加一些新的数据库,并建表,写入一些数据:

    [root@mysql-1 software]# mysql -uroot -p'abcd1234'
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.5.37 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> 
    mysql> create database world;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | world              |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use world;
    Database changed
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> 
    mysql> create table people( uid int, uname varchar(100), uage int, uloc varchar(100) );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> 
    mysql> show tables;
    +-----------------+
    | Tables_in_world |
    +-----------------+
    | people          |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> desc people;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | uid   | int(11)      | YES  |     | NULL    |       |
    | uname | varchar(100) | YES  |     | NULL    |       |
    | uage  | int(11)      | YES  |     | NULL    |       |
    | uloc  | varchar(100) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    
    mysql> 
    mysql> select count(*) from people;
    +----------+
    | count(*) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> insert into people values (0,'adamhuan',28,'china');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into people values (1,'allah',28,'china');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into people values (2,'allah',28,'china');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into people values (3,'jean',28,'usa');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> 
    mysql> select * from people;
    +------+----------+------+-------+
    | uid  | uname    | uage | uloc  |
    +------+----------+------+-------+
    |    0 | adamhuan |   28 | china |
    |    1 | allah    |   28 | china |
    |    2 | allah    |   28 | china |
    |    3 | jean     |   28 | usa   |
    +------+----------+------+-------+
    4 rows in set (0.00 sec)
    
    mysql>

    备份:
    全库:

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_db_all_after_create_insert.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_all_after_create_insert.dmp
    528K    MySQL_db_mysql.dmp
    [root@mysql-1 dumpfile]#

    单独备份库:world。

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --databases world > MySQL_db_world_after_create_insert.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_all_after_create_insert.dmp
    528K    MySQL_db_mysql.dmp
    4.0K    MySQL_db_world_after_create_insert.dmp
    [root@mysql-1 dumpfile]#

    创建用户,并赋予对数据库world的权限。

    mysql> select user,host,password from mysql.user;
    +------+-----------+-------------------------------------------+
    | user | host      | password                                  |
    +------+-----------+-------------------------------------------+
    | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1   |                                           |
    | root | 127.0.0.1 |                                           |
    | root | ::1       |                                           |
    |      | localhost |                                           |
    |      | mysql-1   |                                           |
    +------+-----------+-------------------------------------------+
    6 rows in set (0.00 sec)
    
    mysql> 
    mysql> grant all privileges on world.* to god@'192.168.111.%' identified by 'abcd1234';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user,host,password from mysql.user;
    +------+---------------+-------------------------------------------+
    | user | host          | password                                  |
    +------+---------------+-------------------------------------------+
    | root | localhost     | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1       |                                           |
    | root | 127.0.0.1     |                                           |
    | root | ::1           |                                           |
    |      | localhost     |                                           |
    |      | mysql-1       |                                           |
    | god  | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    +------+---------------+-------------------------------------------+
    7 rows in set (0.00 sec)
    
    mysql> show grants for god@'192.168.111.%';
    +----------------------------------------------------------------------------------------------------------------+
    | Grants for god@192.168.111.%                                                                                   |
    +----------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'god'@'192.168.111.%' IDENTIFIED BY PASSWORD '*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' |
    | GRANT ALL PRIVILEGES ON `world`.* TO 'god'@'192.168.111.%'                                                     |
    +----------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql>

    备份这时候的数据库:
    全库:

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --all-databases > MySQL_db_all_after_user.dmp
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_all_after_create_insert.dmp
    528K    MySQL_db_all_after_user.dmp
    528K    MySQL_db_mysql.dmp
    4.0K    MySQL_db_world_after_create_insert.dmp
    [root@mysql-1 dumpfile]#

    单库:world

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --databases world > MySQL_db_world_after_user.dmp
    [root@mysql-1 dumpfile]# 
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_all_after_create_insert.dmp
    528K    MySQL_db_all_after_user.dmp
    528K    MySQL_db_mysql.dmp
    4.0K    MySQL_db_world_after_create_insert.dmp
    4.0K    MySQL_db_world_after_user.dmp
    [root@mysql-1 dumpfile]#

    单库:mysql

    [root@mysql-1 dumpfile]# mysqldump -uroot -p'abcd1234' --events --databases mysql > MySQL_db_mysql_after_user.dmp
    [root@mysql-1 dumpfile]# du -sh *
    528K    MySQL_all_db.dmp
    528K    MySQL_db_all_after_create_insert.dmp
    528K    MySQL_db_all_after_user.dmp
    528K    MySQL_db_mysql_after_user.dmp
    528K    MySQL_db_mysql.dmp
    4.0K    MySQL_db_world_after_create_insert.dmp
    4.0K    MySQL_db_world_after_user.dmp
    [root@mysql-1 dumpfile]#

    三、MySQL恢复

    现有的备份文件阶段:

    [root@mysql-1 dumpfile]# ls -ltr *
    -rw-r--r-- 1 root root 538861 Aug 23 01:15 MySQL_all_db.dmp
    -rw-r--r-- 1 root root 538540 Aug 23 01:16 MySQL_db_mysql.dmp
    -rw-r--r-- 1 root root 539788 Aug 23 01:27 MySQL_db_all_after_create_insert.dmp
    -rw-r--r-- 1 root root   2127 Aug 23 01:28 MySQL_db_world_after_create_insert.dmp
    -rw-r--r-- 1 root root 540108 Aug 23 01:31 MySQL_db_all_after_user.dmp
    -rw-r--r-- 1 root root   2127 Aug 23 01:32 MySQL_db_world_after_user.dmp
    -rw-r--r-- 1 root root 539002 Aug 23 01:33 MySQL_db_mysql_after_user.dmp
    [root@mysql-1 dumpfile]#

    最初,MySQL初始状态:
    全:MySQL_all_db.dmp
    单:MySQL_db_mysql.dmp

    然后,创建world,并建表、插入数据的状态:
    全:MySQL_db_all_after_create_insert.dmp
    单:MySQL_db_world_after_create_insert.dmp

    最后,创建用户,并分配权限后的状态:
    全:MySQL_db_all_after_user.dmp
    单-mysql:MySQL_db_mysql_after_user.dmp
    单-world:MySQL_db_world_after_user.dmp

    从上边的变化阶段你可以知道:
    1. 三个阶段的全库导出文件肯定不同
    2. 三个阶段的单库文件有可能不同:
    相同的部分:
    — 第一阶段到第二阶段,只是创建了库,所以mysql的单库状态没有发生变化
    — 第二阶段到第三阶段,只是新建了用户,所以world的单库状态没有发生变化
    不同的部分:
    — 第二阶段到第三阶段,单库mysql的状态肯定发生了变化

    恢复:
    将全库恢复到第二阶段:MySQL_db_all_after_create_insert.dmp
    这个过程中,变化的应该是mysql.user表。

    [root@mysql-1 software]# mysql -uroot -p'abcd1234'
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 5.5.37 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | world              |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> select user,host,password from mysql.user;
    +------+---------------+-------------------------------------------+
    | user | host          | password                                  |
    +------+---------------+-------------------------------------------+
    | root | localhost     | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1       |                                           |
    | root | 127.0.0.1     |                                           |
    | root | ::1           |                                           |
    |      | localhost     |                                           |
    |      | mysql-1       |                                           |
    | god  | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    +------+---------------+-------------------------------------------+
    7 rows in set (0.00 sec)
    
    mysql> 
    mysql> exit
    Bye
    [root@mysql-1 software]# 
    [root@mysql-1 software]# mysql -uroot -p'abcd1234' < /backup/db/mysql/dumpfile/MySQL_db_all_after_create_insert.dmp 
    [root@mysql-1 software]# 
    [root@mysql-1 software]# mysql -uroot -p'abcd1234'
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 14
    Server version: 5.5.37 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | world              |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> 
    mysql> select user,host,password from mysql.user;
    +------+-----------+-------------------------------------------+
    | user | host      | password                                  |
    +------+-----------+-------------------------------------------+
    | root | localhost | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1   |                                           |
    | root | 127.0.0.1 |                                           |
    | root | ::1       |                                           |
    |      | localhost |                                           |
    |      | mysql-1   |                                           |
    +------+-----------+-------------------------------------------+
    6 rows in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@mysql-1 software]#

    可以看到,全库备份也备份了mysql,所以mysql.user的登陆信息的表也被更改了。

    单库恢复第三阶段mysql:MySQL_db_mysql_after_user.dmp。
    这一步操作,会看到mysql.user发生变化,第三阶段中新增的用户god,会恢复到mysql.user中。
    由于是单库恢复,所以,不会影响其他的数据库状态。

    [root@mysql-1 software]# mysql -uroot -p'abcd1234' < /backup/db/mysql/dumpfile/MySQL_db_mysql_after_user.dmp 
    [root@mysql-1 software]# 
    [root@mysql-1 software]# mysql -uroot -p'abcd1234'
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 16
    Server version: 5.5.37 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2014, 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> select user,host,password from mysql.user;
    +------+---------------+-------------------------------------------+
    | user | host          | password                                  |
    +------+---------------+-------------------------------------------+
    | root | localhost     | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    | root | mysql-1       |                                           |
    | root | 127.0.0.1     |                                           |
    | root | ::1           |                                           |
    |      | localhost     |                                           |
    |      | mysql-1       |                                           |
    | god  | 192.168.111.% | *4AD47E08DAE2BD4F0977EED5D23DC901359DF617 |
    +------+---------------+-------------------------------------------+
    7 rows in set (0.00 sec)
    
    mysql> show grants for god@'192.168.111.%';
    +----------------------------------------------------------------------------------------------------------------+
    | Grants for god@192.168.111.%                                                                                   |
    +----------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'god'@'192.168.111.%' IDENTIFIED BY PASSWORD '*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' |
    | GRANT ALL PRIVILEGES ON `world`.* TO 'god'@'192.168.111.%'                                                     |
    +----------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> 
    mysql> exit
    Bye
    [root@mysql-1 software]#

    四、终了
    MySQL的导出文件,并不像Oracle的导出文件那样,可以导出多个表空间,然后恢复的时候可以指定具体恢复哪一个表空间以及恢复到哪个Schema下。
    MySQL的导出文件,如果包含了实例下的多个数据库,则恢复的时候会同时恢复。这对于一些需要只恢复导出文件中的某一部分库的场景是不太适用的。
    此外,如果MySQL的导出文件包含了mysql库,恢复的时候只想恢复数据而不希望影响MySQL的登陆权限,则也是比较麻烦的,很容易就覆盖了之前的账户信息,造成丢失。

    所以,MySQL的备份,如果采用mysqldump,最好分开备份mysql和非mysql单库。

    ——————————
    Done。



沪ICP备19023445号-2号
友情链接