首页 > 代码库 > mysql备份与恢复

mysql备份与恢复

  1. mysqldump进行mysqll数据库的备份与恢复

命令说明: Schema和数据存储一起、巨大的SQL语句、单个巨大的备份文件

    mysqldump: 客户端,通过mysql协议连接至mysqld;

        mysqldump [options] [db_name [tbl_name ...]]

        shell> mysqldump [options] db_name [tbl_name ...]
        shell> mysqldump [options] --databases db_name ...
        shell> mysqldump [options] --all-databases

        -A, --all-databases

        MyISAM, InnoDB: 温备
             -x, --lock-all-tables:锁定所有库的所有表;
             -l, --lock-tables:对每个单独的数据库,在启动备份之前锁定其所有表;

        InnoDB:
            --single-transaction:启动一个大的单一事务实现备份

        -B, --databases db_name1 db_name2 ...:备份指定的数据库

        -C, --compress:压缩传输;

    命令的语法格式:
        mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表
        mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库
        mysqldump [OPTIONS] --all-databases [OPTIONS]:备份所有库


    其它选项:

        -E, --events:备份指定库的事件调度器event scheuler;
        -R, --routines:备份存储过程和存储函数;
        --triggers:备份触发器


        --master-data[=#]:
            1:记录CHANGE MASTER TO语句;此语句未被注释;
            2:记录为注释语句;

        --flush-logs, -F:锁定表之后执行flush logs命令;

实验及目的:

故障数据库:192.168.150.138
恢复至数据库:192.168.150.137
目的:故障数据库将备份恢复还原至新建数据库,并保证数据一致性

故障服务器进行的备份操作

1、为保证数据的一致性,故障服务器需先开启二进制日志功能

MariaDB [(none)]> SHOW GLOBAL VARIABLES like ‘%log% 查看二进制日志功能是否开启
| log_bin                                   | OFF  

修改配置文件进行全局修改
可以修改的地方:
/etc/my.conf
/etc/my.cnf.d/
此次修改为/etc/my.cnf.d/server.cnf
[server]
log_bin=mysql-bin   #mysql-bin即为二进制日志的名称

修改配置文件需对数据库进行重启操作
 ~]# systemctl restart mariadb.service

再次确认二进制日志功能是否已经开启:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%log%‘;
| log_bin                                   | ON

2、进行mysqldump操作
~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=http://www.mamicode.com/2 > /root/all.sql>

新数据库进行还原操作:

1、将备份的.sql文件拷贝至新数据库
2、注意点:在数据库进行还原操作是,为了减少IO负担,可以将二进制日志文件在session层面进行暂时的关闭

SET sql_log_bin=OFF;

3、首先我查看新数据,并无任何hellodb的数据
root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.54-MariaDB Source distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myda               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

4、进行还原操作:需要注意mysql用户对all.sql有读取权限
source /tmp/all.sql:

5、数据确认:此时的数据仅仅为我备份点时的数据,顾还需要进行数据的一个前滚操作
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| myda               |
| mydb               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)

数据的一个前滚的操作:

为了保证数据的一致性,通过mysqldump进行备份时,一定要定期对二进制日志进行备份
故障数据库的操作:
1、~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001
可以查看到在备份点之后我做过的所有sql操作

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170119 15:39:40 server id 1  end_log_pos 245   Start: binlog v 4, server v 5.5.52-MariaDB created 170119
 15:39:40 at startup# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
PG2AWA8BAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA8bYBYEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAW+9+9w==
‘/*!*/;
# at 245
#170119 15:44:08 server id 1  end_log_pos 316   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811848/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.au
tocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/
;SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 316
#170119 15:44:08 server id 1  end_log_pos 344   Intvar
SET INSERT_ID=28/*!*/;
# at 344
#170119 15:44:08 server id 1  end_log_pos 492   Query   thread_id=4 exec_time=0 error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1484811848/*!*/;
INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES(‘void‘,11,‘M‘,3,6)
/*!*/;
# at 492
#170119 15:44:08 server id 1  end_log_pos 519   Xid = 431
COMMIT/*!*/;
# at 519
#170119 15:45:05 server id 1  end_log_pos 590   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811905/*!*/;
BEGIN
/*!*/;
# at 590
#170119 15:45:05 server id 1  end_log_pos 690   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1484811905/*!*/;
DELETE FROM students WHERE StuID=3
/*!*/;
# at 690
#170119 15:45:05 server id 1  end_log_pos 717   Xid = 434
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2、将这些sql操作导出至增量文件,并拷贝至新数据库
 ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 >incre.sql

3、新数据库进行前滚操作:
MariaDB [hellodb]> source /tmp/incre.sql

4、数据确认:此时我备份点之后的数据修改也全部还原
    MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | JinJiao King  | 100 | M      |    NULL |         1 |
|    27 | YinJiao King  |  98 | M      |    NULL |         2 |
|    28 | void          |  11 | M      |       3 |         6 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)


本文出自 “Open World” 博客,请务必保留此出处http://voidyao000.blog.51cto.com/12458042/1906271

mysql备份与恢复