mysql备份与恢复

一、 冷备：只需要备份数据库的frm文件、共享表空间文件、独立表空间文件(*.ibd)、重做日志文件。

            优点：备份简单、恢复速度快

            缺点：文件比逻辑备份文件大、备份不总是易于跨平台

二、 逻辑备份

        1. mysqldump

            （1）备份指定数据库

//因为在本地安装了两个版本mysql，所以在备份时显示指定所要备份备份的数据库实例以及相应的数据库
//格式1
C:\Users\v074>mysqldump -h196.6.8.193 -uroot -P3306 -p --databases db2 > E:\MySQL\backup\master_db2.sql
Enter password: *****

//格式2
C:\Users\v074>mysqldump -uroot -P3306 -p --databases db1 > E:\MySQL\backup\master_db1.sql
Enter password: *****

C:\Users\v074>
//如果主机仅有一个MySQL实例则：mysqldump --databases db1 > E:\MySQL\backup\master_db1.sql

            （2）备份所有数据库

C:\Users\v074>mysqldump -uroot -P3306 -p --all-databases > E:\MySQL\backup\master_all.sql
Enter password: *****

C:\Users\v074>

            （3）对db2这个架构进行备份

C:\Users\v074>mysqldump -uroot -P3306 -p --single-transaction db2 > E:\MySQL\backup\master_db2_frame.sql
Enter password: *****

C:\Users\v074>

        2. select ... into outfile：逻辑备份方法，导出一个表的数据

mysql> select * into outfile 'E:\\MySQL\\backup\\db2_user.txt' from user;
Query OK, 1 row affected (0.00 sec)

mysql> select * into outfile 'E:\\MySQL\\backup\\db2_user.sql' from user;
Query OK, 1 row affected (0.00 sec)

mysql>

        3. 逻辑备份恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| user               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database db2;   //第一步：删除数据库db2
Query OK, 0 rows affected (0.05 sec)

mysql> create database db2; //第二步：为了导入db2结构和数据新建数据库db2
Query OK, 1 row affected (0.05 sec)

mysql>

//第三步：导入db2结构
C:\Users\v074>mysqldump -uroot -P3306 -p --databases db2 < E:\MySQL\backup\master_db4_frame.sql
Enter password: *****
-- MySQL dump 10.13  Distrib 8.0.12, for Win64 (x86_64)
--
-- Host: localhost    Database: db2
-- ------------------------------------------------------
-- Server version       8.0.12

/*!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 */;
 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 */;

--
-- Current Database: `db2`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db2` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db2`;
/*!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 2019-07-10 14:16:57

C:\Users\v074>

//将数据导入db2
mysql> source E:\MySQL\backup\master_db3.sql;
ERROR:
Unknown command '\M'.
ERROR:
Unknown command '\b'.
ERROR:
Unknown command '\m'.
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

//...假装省略号...//

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_db2   |
+-----------------+
| customers       |
| t_linear_hash   |
//...假装省略号...//
| t_list_null     |
| t_range         |
| t_range_null    |
| t_sub           |
| t_sub2          |
+-----------------+
18 rows in set (0.00 sec)

mysql>

        4. load data infile：将通过mysqldump --tab或select into outfile导出的表数据导入到数据库中的表

mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | mike  |
|    2 | tom   |
|    3 | jack  |
|    4 | marry |
+------+-------+
4 rows in set (0.00 sec)

mysql> truncate user;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> load data infile 'E:\\MySQL\\backup\\db2_user.txt'  into table user;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|    1 | mike  |
|    2 | tom   |
|    3 | jack  |
|    4 | marry |
+------+-------+
4 rows in set (0.00 sec)

mysql>

        5. mysqlimport：

三、二进制日志备份与恢复

四、热备

        1. ibbackup

        2. xtrabackup

        3. xtrabackup增量备份

五、复制

        主从数据不同步处理过程

            1. 查看主库状态

mysql> show master status\G
*************************** 1. row ***************************
             File: master-bin.000003
         Position: 92269
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> 

            2.在从库使用主库日志重新连接

mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

mysql> CHANGE MASTER TO MASTER_HOST='196.6.8.193',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='my
sql',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=92269;
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event  //io状态正常
                  Master_Host: 196.6.8.193
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 92269
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes  //io线程处于运行状态
            Slave_SQL_Running: Yes  //sql线程处于运行状态
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 92269
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 74dab5d7-9960-11e9-ab76-fefcfe15f5af
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
1 row in set (0.00 sec)

mysql>