• Mysql备份与还原实例


    一、备份数据库

    ----清空一下日志
    mysql> reset master;
    Query OK, 0 rows affected (0.02 sec)
    ----查看一下echo表的存储引擎
    mysql> show table status like 'echo' G;
    *************************** 1. row ***************************
               Name: echo
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 3
     Avg_row_length: 5461
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 94371840
     Auto_increment: NULL
        Create_time: 2013-09-23 16:45:31
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    ----开始备份
    [root@rhel5 dump]# mysqldump -u root -p --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases jack > /mysql/mysql5.5/dump/echo.sql
    
    Enter password: 
    [root@rhel5 dump]# ll
    总计 4
    -rw-r--r-- 1 root root 1558 09-23 21:47 echo.sql 
    ---查看备份出来的sql
    [root@rhel5 dump]# more echo.sql 
    -- MySQL dump 10.13  Distrib 5.5.22, for Linux (i686)
    --
    -- Host: localhost    Database: jack
    -- ------------------------------------------------------
    -- Server version    5.5.22-log
    /*!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 */;
    
    --
    -- Position to start replication or point-in-time recovery from
    --
    
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
    
    --
    -- Current Database: `jack`
    --
    
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `jack`;
    
    --
    -- Table structure for table `echo`
    --
    
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `echo` (
      `id` int(11) DEFAULT NULL,
      `msg` varchar(100) DEFAULT NULL
    );
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `echo`
    --
    
    INSERT INTO `echo` VALUES (1,'aaa');
    INSERT INTO `echo` VALUES (1,'bbb');
    INSERT INTO `echo` VALUES (1,'');
    /*!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 */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2013-09-23 21:47:55

    二、对表进行修改,然后删除

    ----插入一下内容
    mysql> insert into echo values(4,'ccc');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into echo values(5,'ddd');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from echo;
    +------+------+
    | id   | msg  |
    +------+------+
    |    1 | aaa  |
    |    1 | bbb  |
    |    1 ||
    |    4 | ccc  |
    |    5 | ddd  |
    +------+------+
    5 rows in set (0.00 sec)
    
    mysql> drop table echo;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select * from echo;
    ERROR 1146 (42S02): Table 'jack.echo' doesn't exist

    三、查看表删除的位置

    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       593 |
    +------------------+-----------+
    1 row in set (0.00 sec)
    
    mysql> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show binlog events;
    +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
    | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                    |
    +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
    | mysql-bin.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.22-log, Binlog ver: 4                   |
    | mysql-bin.000001 | 107 | Query       |         1 |         175 | BEGIN                                                   |
    | mysql-bin.000001 | 175 | Query       |         1 |         270 | use `jack`; insert into echo values(4,'ccc')            |
    | mysql-bin.000001 | 270 | Xid         |         1 |         297 | COMMIT /* xid=176 */                                    |
    | mysql-bin.000001 | 297 | Query       |         1 |         365 | BEGIN                                                   |
    | mysql-bin.000001 | 365 | Query       |         1 |         460 | use `jack`; insert into echo values(5,'ddd')            |
    | mysql-bin.000001 | 460 | Xid         |         1 |         487 | COMMIT /* xid=177 */                                    |
    | mysql-bin.000001 | 487 | Query       |         1 |         593 | use `jack`; DROP TABLE `echo` /* generated by server */ |
    | mysql-bin.000001 | 593 | Rotate      |         1 |         636 | mysql-bin.000002;pos=4                                  |
    +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
    9 rows in set (0.00 sec)
    ----从上面可以看出drop table 是在487----
    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       636 |
    | mysql-bin.000002 |       107 |
    +------------------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> show binlog events in 'mysql-bin.000001' G;
    *************************** 1. row ***************************
       Log_name: mysql-bin.000001
            Pos: 4
     Event_type: Format_desc
      Server_id: 1
    End_log_pos: 107
           Info: Server ver: 5.5.22-log, Binlog ver: 4
    *************************** 2. row ***************************
       Log_name: mysql-bin.000001
            Pos: 107
     Event_type: Query
      Server_id: 1
    End_log_pos: 175
           Info: BEGIN
    *************************** 3. row ***************************
       Log_name: mysql-bin.000001
            Pos: 175
     Event_type: Query
      Server_id: 1
    End_log_pos: 270
           Info: use `jack`; insert into echo values(4,'ccc')
    *************************** 4. row ***************************
       Log_name: mysql-bin.000001
            Pos: 270
     Event_type: Xid
      Server_id: 1
    End_log_pos: 297
           Info: COMMIT /* xid=176 */
    *************************** 5. row ***************************
       Log_name: mysql-bin.000001
            Pos: 297
     Event_type: Query
      Server_id: 1
    End_log_pos: 365
           Info: BEGIN
    *************************** 6. row ***************************
       Log_name: mysql-bin.000001
            Pos: 365
     Event_type: Query
      Server_id: 1
    End_log_pos: 460
           Info: use `jack`; insert into echo values(5,'ddd')
    *************************** 7. row ***************************
       Log_name: mysql-bin.000001
            Pos: 460
     Event_type: Xid
      Server_id: 1
    End_log_pos: 487
           Info: COMMIT /* xid=177 */
    *************************** 8. row ***************************
       Log_name: mysql-bin.000001
            Pos: 487
     Event_type: Query
      Server_id: 1
    End_log_pos: 593
           Info: use `jack`; DROP TABLE `echo` /* generated by server */
    *************************** 9. row ***************************
       Log_name: mysql-bin.000001
            Pos: 593
     Event_type: Rotate
      Server_id: 1
    End_log_pos: 636
           Info: mysql-bin.000002;pos=4
    9 rows in set (0.00 sec)
    
    ERROR: 
    No query specified

    四、查看备份的位置

    [root@rhel5 dump]# more echo.sql 
    -- MySQL dump 10.13  Distrib 5.5.22, for Linux (i686)
    --
    -- Host: localhost    Database: jack
    -- ------------------------------------------------------
    -- Server version    5.5.22-log
    /*!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 */;
    
    --
    -- Position to start replication or point-in-time recovery from
    --
    
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
    
    --
    -- Current Database: `jack`
    --
    
    CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */;
    
    USE `jack`;
    
    --
    -- Table structure for table `echo`
    --
    
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `echo` (
      `id` int(11) DEFAULT NULL,
      `msg` varchar(100) DEFAULT NULL
    );
    /*!40101 SET character_set_client = @saved_cs_client */;
    
    --
    -- Dumping data for table `echo`
    --
    
    INSERT INTO `echo` VALUES (1,'aaa');
    INSERT INTO `echo` VALUES (1,'bbb');
    INSERT INTO `echo` VALUES (1,'');
    /*!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 */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    
    -- Dump completed on 2013-09-23 21:47:55
    
    [root@rhel5 dump]# grep "CHANGE MASTER" ./echo.sql 
    -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

    五、还原数据库

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | jack               |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    5 rows in set (0.01 sec)
    ----因为没有测试库,所以只能把jack数据库给删除掉
    mysql> drop database jack;
    Query OK, 0 rows affected (0.02 sec)
    ----还原数据库
    [root@rhel5 dump]# mysql -u root -p < ./echo.sql
    Enter password: 
    
    mysql> use jack
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_jack |
    +----------------+
    | echo           |
    +----------------+
    1 row in set (0.00 sec)
    ---已经还原到表被修改之前
    mysql> select * from echo;
    +------+------+
    | id   | msg  |
    +------+------+
    |    1 | aaa  |
    |    1 | bbb  |
    |    1 ||
    +------+------+
    3 rows in set (0.01 sec)
    
    mysql> show binary logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000001 |       636 |
    | mysql-bin.000002 |      1065 |
    +------------------+-----------+
    2 rows in set (0.00 sec)
    
    mysql> show binlog events in 'mysql-bin.000002';
    +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
    | Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                                               |
    +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
    | mysql-bin.000002 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.22-log, Binlog ver: 4                                                              |
    | mysql-bin.000002 |  107 | Query       |         1 |         188 | drop database jack                                                                                 |
    | mysql-bin.000002 |  188 | Query       |         1 |         337 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `jack` /*!40100 DEFAULT CHARACTER SET utf8 */             |
    | mysql-bin.000002 |  337 | Query       |         1 |         486 | use `jack`; CREATE TABLE `echo` (
    
      `id` int(11) DEFAULT NULL,
      `msg` varchar(100) DEFAULT NULL
    ) |
    | mysql-bin.000002 |  486 | Query       |         1 |         554 | BEGIN                                                                                              |
    | mysql-bin.000002 |  554 | Query       |         1 |         652 | use `jack`; INSERT INTO `echo` VALUES (1,'aaa')                                                    |
    | mysql-bin.000002 |  652 | Xid         |         1 |         679 | COMMIT /* xid=206 */                                                                               |
    | mysql-bin.000002 |  679 | Query       |         1 |         747 | BEGIN                                                                                              |
    | mysql-bin.000002 |  747 | Query       |         1 |         845 | use `jack`; INSERT INTO `echo` VALUES (1,'bbb')                                                    |
    | mysql-bin.000002 |  845 | Xid         |         1 |         872 | COMMIT /* xid=207 */                                                                               |
    | mysql-bin.000002 |  872 | Query       |         1 |         940 | BEGIN                                                                                              |
    | mysql-bin.000002 |  940 | Query       |         1 |        1038 | use `jack`; INSERT INTO `echo` VALUES (1,'')                                                     |
    | mysql-bin.000002 | 1038 | Xid         |         1 |        1065 | COMMIT /* xid=208 */                                                                               |
    +------------------+------+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
    13 rows in set (0.00 sec)

    六、恢复数据库

    [root@rhel5 data]# mysqlbinlog -u root -p --start-position=107 --stop-position=487 -vv ./mysql-bin.000001 | mysql -u root -p
    Enter password: Enter password: 
    123456
    
    mysql> select * from echo;
    +------+------+
    | id   | msg  |
    +------+------+
    |    1 | aaa  |
    |    1 | bbb  |
    |    1 ||
    |    4 | ccc  |
    |    5 | ddd  |
    +------+------+
    5 rows in set (0.00 sec)
  • 相关阅读:
    前端开发常用工具
    Promise和setTimeout执行顺序
    化生汤
    与vue+element相对于的组合
    脾胃笔记
    中医脉象
    javacript 面向对象
    fabric 安装及使用
    jquery.tablesorter.js 学习笔记
    iframe 标签自适应高度和宽度
  • 原文地址:https://www.cnblogs.com/Richardzhu/p/3336222.html
Copyright © 2020-2023  润新知