cbear小站
large picture of the cover

4-1-备份与恢复

1. 备份分类

按照是否能够继续提供服务 , 将数据库备份类型划分为:

  • 热备份: 在线备份 , 能读能写

  • 温备份: 能读不能写

  • 冷备份: 离线备份

按照备份数据库对象分类:

  • 物理备份: 直接复制数据文件

  • 逻辑备份: 将数据导出至文件中 , 必要时将其还原(也包括备份成sql语句的方式)

按照是否备份整个数据集分为:

  • 完全备份: 备份从开始到某段时间的全部数据

  • 差异备份: 备份自完全备份以来变化的数据

    mariadb-backup-1

    假设我们在第一天也进行一次完全备份。第二天差异备份时 , 会备份第二天和第一天之间的差异数据 , 而第二天的备份数据是完全备份加第一次差异备份的数据。第三天进行差异备份时 , 仍和第一天的原始数据进行对比 , 把第二天和第三天所有的数据都备份在第二次差异备份中 , 第三天的备份数据是完全备份加第二次差异备份的数据。第四天进行差异备份时 , 仍和第一天的原始数据进行对比 , 把第二天、第三天和第四天所有的不同数据都备份到第三次差异备份中 , 第四天的备份数据是完全备份加第三次差异备份的数据。

  • 增量备份: 备份自上次增量备份以来变化的数据。

    累计增量备份是指先进行一次完全备份 , 服务器运行一段时间之后 , 比较当前系统和完全备份的备份数据之间的差异 , 只备份有差异的数据。服务器继续运行 , 再经过一段时间 , 进行第二次增量备份。在进行第二次增量备份时 , 当前系统和第一次增量备份的数据进行比较 , 也是只备份有差异的数据。第三次增量备份是和第二次增量备份的数据进行比较 , 以此类推。

    mariadb-backup-2

    假设我们在第一天进行一次完全备份。第二天增量备份时 , 只会备份第二天和第一天之间的差异数据 , 但是第二天的总备份数据是完全备份加第一次增量备份的数据。第三天增量备份时 , 只会备份第三天和第二天之间的差异数据 , 但是第三天的总备份数据是完全备份加第一次增量备份的数据 , 再加第二次增量备份的数据。当然 , 第四天增量备份时 , 只会备份第四天和第三天的差异数据 , 但是第四天的总备份数据是完全备份加第一次增量备份的数据 , 加第二次增量备份的数据 , 再加第三次增量备份的数据。

分类方式不同 , 不同分类的备份没有冲突的关系 , 它们可以任意组合。

2. 备份内容与备份工具

需要备份的内容 : 文件、二进制日志、事务日志、配置文件、操作系统上和MySQL相关的配置(如sudo , 定时任务)。

物理备份和逻辑备份的优缺点:

  • 物理备份: 直接复制数据文件 , 速度较快。

  • 逻辑备份: 将数据导出到文本文件中或其他格式的文件中。有MySQL服务进程参与 , 相比物理备份而言速度较慢；可能丢失浮点数精度；但可以使用文本工具二次处理；可以跨版本和跨数据库系统进行移植。

备份策略: 要考虑安全 , 也要考虑还原时长

  • 完全备份+增量

  • 完全备份+差异

备份工具:

  • mysqldump: 逻辑备份工具。要求mysql服务在线。MyISAM(温备) , InnoDB ( 热备 )

  • mysqlhotcopy: 物理备份工具 , 温备份 , 实际上是冷备。加锁、flush table并进行cp或scp。即将废弃的工具

  • cp :冷备

  • lvm快照: 几乎热备。注意点是: 先flush table、lock table、创建快照、释放锁、复制数据。因为要先flush table和lock table , 这对于MyISAM来说很简单很容易实现。但对于InnoDB来说 , 因为事务的原因 , 锁表后可能还有缓存中的数据在写入文件中 , 所以应该监控缓存中的数据是真的已经完全写入数据文件中 , 之后才能进行复制数据。

  • xtrabackup:开源。MyISAM ( 温备 ) , InnoDB ( 热备 ) , 速度较快。

3. 基础备份

3.1 数据库冷备份

这里需要单独准备一台服务器

  1. 需要将两个主机A和B的mysql服务都停止掉：A备份到B主机上

    [root@mariadb ~]# systemctl stop mariadb

  2. 将主机A的/etc/my.cnf.d/server.cnf配置文件复制到B主机：

    [root@mariadb ~]# scp /etc/my.cnf.d/server.cnf root@192.168.71.11:/etc/my.cnf.d/
root@192.168.71.11's password: 
server.cnf                                                                                                  100% 1405     1.5MB/s   00:00

  3. 将A主机的数据库文件复制到B主机上

    [root@mariadb ~]# rsync -av /var/lib/mysql/* 192.168.71.11:/var/lib/mysql/
root@192.168.71.11's password: 
sending incremental file list
ON.000001
ON.000001.idx
ON.index
.....

  4. 修改B主机的数据库所有者和所属组权限

    [root@backup ~]# chown -R mysql.mysql /var/lib/mysql

  5. 之前已经算备份完成了 , 后续可以开启服务测试数据库信息

    [root@backup ~]# systemctl start mariadb
[root@backup ~]# mysql
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 11.4.3-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, 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           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.002 sec)

MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.001 sec)

3.2 基于LVM备份操作

(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
 (2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
 mysql> SHOW MASTER STATUS;
 mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
 (3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
 (4) 释放锁
mysql> UNLOCK TABLES;
 (5) 挂载快照卷 , 执行数据备份
(6) 备份完成后 , 删除快照卷
(7) 制定好策略 , 通过原卷备份二进制日志

  1. 将/dev/sda磁盘进行分区。

    [root@centos7-1lib]#fdisk /dev/sda      #将/dev/sda进行磁盘分区
Welcome to fdisk (util-linux 2.23.2).
 
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
 
Command (m for help): n   对磁盘进行分区
All primary partitions are in use
Adding logical partition 6
First sector (322967552-419430399, default 322967552):
Using default value 322967552
Last sector, +sectors or +size{K,M,G} (322967552-419430399, default 419430399): +5G  选择5的磁盘
Partition 6 of type Linux and of size 5 GiB is set
Command (m for help): t  查看磁盘类型
Partition number (1-6, default 6): 6
Hex code (type L to list all codes): L
 
 0  Empty           24  NEC DOS         81  Minix / old Lin bf  Solaris       
 1  FAT12           27  Hidden NTFS Win 82  Linux swap / So c1  DRDOS/sec (FAT-
 2  XENIX root      39  Plan 9          83  Linux           c4  DRDOS/sec (FAT-
 3  XENIX usr       3c  PartitionMagic  84  OS/2 hidden C:  c6  DRDOS/sec (FAT-
 4  FAT16 <32M      40  Venix 80286     85  Linux extended  c7  Syrinx        
 5  Extended        41  PPC PReP Boot   86  NTFS volume set da  Non-FS data   
 6  FAT16           42  SFS             87  NTFS volume set db  CP/M / CTOS / .
 7  HPFS/NTFS/exFAT 4d  QNX4.x          88  Linux plaintext de  Dell Utility  
 8  AIX             4e  QNX4.x 2nd part 8e  Linux LVM       df  BootIt        
 9  AIX bootable    4f  QNX4.x 3rd part 93  Amoeba          e1  DOS access    
 a  OS/2 Boot Manag 50  OnTrack DM      94  Amoeba BBT      e3  DOS R/O       
 b  W95 FAT32       51  OnTrack DM6 Aux 9f  BSD/OS          e4  SpeedStor     
 c  W95 FAT32 (LBA) 52  CP/M            a0  IBM Thinkpad hi eb  BeOS fs       
 e  W95 FAT16 (LBA) 53  OnTrack DM6 Aux a5  FreeBSD         ee  GPT           
 f  W95 Ext'd (LBA) 54  OnTrackDM6      a6  OpenBSD         ef  EFI (FAT-12/16/
10  OPUS            55  EZ-Drive        a7  NeXTSTEP        f0  Linux/PA-RISC b
11  Hidden FAT12    56  Golden Bow      a8  Darwin UFS      f1  SpeedStor     
12  Compaq diagnost 5c  Priam Edisk     a9  NetBSD          f4  SpeedStor     
14  Hidden FAT16 <3 61  SpeedStor       ab  Darwin boot     f2  DOS secondary 
16  Hidden FAT16    63  GNU HURD or Sys af  HFS / HFS+      fb  VMware VMFS   
17  Hidden HPFS/NTF 64  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE
18  AST SmartSleep  65  Novell Netware  b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 70  DiskSecure Mult bb  Boot Wizard hid fe  LANstep       
1c  Hidden W95 FAT3 75  PC/IX           be  Solaris boot    ff  BBT           
1e  Hidden W95 FAT1 80  Old Minix     
Hex code (type L to list all codes): 8e  选择8e , 就是逻辑卷LVM格式的磁盘
  
WARNING: If you have created or modified any DOS 6.xpartitions, please see the fdisk manual page for additionalinformation.
 
Changed type of partition 'FAT12' to 'Linux LVM'
 
Command (m for help): w  进行保存磁盘
The partition table has been altered!
 
Calling ioctl() to re-read partition table.
 
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.

  2. 对磁盘进行同步 , 此时的同步方法是centos7的同步方式：

    [root@centos7-1lib]#partprobe
Warning: Unable to open /dev/sr0 read-write (Read-only file system).  /dev/sr0 has been opened read-only.
[root@centos7-1lib]#lsblk
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda      8:0    0  200G  0 disk
├─sda1   8:1    0    1G  0 part /boot
├─sda2   8:2    0  100G  0 part /
├─sda3   8:3    0   50G  0 part /data
├─sda4   8:4    0    1K  0 part
├─sda5   8:5    0    3G  0 part [SWAP]
└─sda6   8:6    0    5G  0 part
sr0     11:0    1   10G  0 rom

  3. 创建物理卷、创建卷组、创建逻辑卷 , 并将卷组加入到逻辑卷中。

    [root@centos7-1lib]#pvcreate /dev/sda6   创建物理卷
  Physical volume "/dev/sda6" successfully created.
[root@centos7-1lib]#vgcreate vg0 /dev/sda6  创建卷组
  Volume group "vg0" successfully created
[root@centos7-1lib]#lvcreate -n mysql -L 1G vg0   创建逻辑卷 , 并将卷组加入到逻辑卷mysql中
  Logical volume "mysql" created.
[root@centos7-1lib]#lvcreate -n binlog -L 1G vg0   创建逻辑卷 , 并将卷组加入到逻辑卷binlog中
Logical volume "binlog" created.

  4. 格式化两个逻辑卷：

    [root@centos7-1lib]#mkfs.xfs /dev/vg0/mysql   格式化mysql逻辑卷
meta-data=/dev/vg0/mysql         isize=512    agcount=4, agsize=65536 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=262144, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
[root@centos7-1lib]#mkfs.xfs /dev/vg0/binlog  格式化binlog逻辑卷
meta-data=/dev/vg0/binlog        isize=512    agcount=4, agsize=65536 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=262144, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

  5. 将逻辑卷挂载到新建的/data/mysql和/data/binlog目录下：

    [root@centos7-1lib]#systemctl stop mariadb  停止mariadb服务
[root@centos7-1lib]#mkdir /data/{mysql,binlog}  新建mysql 和binlog目录
[root@centos7-1lib]#mount /dev/vg0/mysql /data/mysql  将mysql逻辑卷挂载到mysql目录上
[root@centos7-1lib]#mount /dev/vg0/binlog  /data/binlog   将binlog逻辑卷挂载到binlog目录上

  6. 修改mysql配置文件内容

    vim /etc/my.cnf
[mysqld]
log_bin=/data/binlog/mysql-bin  修改mysql的log后缀名称
socket=/data/mysql/mysql.sock   修改sock（套接字）数据路径
datadir=/data/mysql  修改mysql数据库路径

  7. 将mysql数据全部复制过去

    [root@centos7-1lib]#cp -av /var/lib/mysql/* /data/mysql  复制mysql的数据库 , 并将保留所有属性
[root@centos7-1lib]#ll /data/mysql -l   查看此时数据库的所有属性
total 28704
-rw-rw---- 1 mysql mysql    16384 Nov 22 10:44 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 22 10:44 aria_log_control
drwx------ 2 mysql mysql     4096 Nov 20 15:52 hellodb
-rw-rw---- 1 mysql mysql 18874368 Nov 22 10:44 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov 22 10:44 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov 12 14:47 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 12 14:47 mysql
drwx------ 2 mysql mysql     4096 Nov 12 14:47 performance_schema
drwx------ 2 mysql mysql      128 Nov 14 21:20 studentdb
drwx------ 2 mysql mysql       20 Nov 12 15:17 testdb
[root@centos7-1lib]#ll /data/    查看新建在data下的mysql和binlog目录的所有者和所属组都是root , 需要修改为mysql属性。
total 16
drwxr-xr-x 2 root root 6 Nov 22 10:45 binlog
drwxr-xr-x. 150 root root 8192 Nov 17 16:03 etc2019-11-17
drwxr-xr-x 2 mysql mysql 4096 Nov 22 09:21 logbin
drwxr-xr-x 2 mysql mysql 44 Nov 20 09:29 logs
drwxr-xr-x 7 root root 193 Nov 22 10:56 mysql
drwxr-xr-x 2 root root 297 Nov 17 20:38 scripts
[root@centos7-1lib]#chown -R mysql.mysql /data/mysql /data/binlog  将两个数据库的属性都改为mysql

  8. 启动mysql服务：

    [root@centos7-1lib]#systemctl start mariadb  启动mysql服务

  9. 进入mysql客户端：

    [root@centos7-1lib]#mysql -pcentos  提示客户端的路径不对 , 需要修改mysql配置文件。
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    出现此错误问题 , 是由于自己的mysql数据库sock路径存在错误 , 需要指定自己新建的data数据库路径

    第一种就是在命令行指定数据库sock文件的路径

    [root@centos7-1lib]#mysql -S /data/mysql/mysql.sock -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]>

    第二种修改客户端mysql客户端配置文件:

    [root@centos7-1~]#vim /etc/my.cnf.d/mysql-clients.cnf
[client]
socket=/data/mysql/mysql.sock

    第三种方式：创建软链接 , 也可以进入到mysql数据库。

    [root@centos7-1~]#ln -s   /data/mysql/mysql.sock  /tmp/mysql.sock   给/data/mysql/mysql.sock 创建一个快捷方式 /tmp/mysql.sock
[root@centos7-1~]#ll /tmp/mysql.sock
lrwxrwxrwx 1 root root 22 Nov 22 11:21 /tmp/mysql.sock -> /data/mysql/mysql.sock    查看/tmp/mysql.sock指向的真实文件路径
[root@centos7-1~]#mysql -p123456     通过密码 此时已经可以登录
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]>

  10. 查看当前的二进制日志文件信息：

    [root@centos7-1~]#ll /data/binlog
total 20
-rw-rw---- 1 mysql mysql 264 Nov 22 11:09 mysql-bin.000001
-rw-rw---- 1 mysql mysql 264 Nov 22 11:10 mysql-bin.000002
-rw-rw---- 1 mysql mysql 264 Nov 22 11:17 mysql-bin.000003
-rw-rw---- 1 mysql mysql 245 Nov 22 11:17 mysql-bin.000004
-rw-rw---- 1 mysql mysql 120 Nov 22 11:17 mysql-bin.index

  11. 对数据库进行加锁处理 , 新开一个窗口 , 不要退出mysql , 否则锁就会解开。

    [root@centos7-1~]#mysql -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;  将数据库进行加锁
Query OK, 0 rows affected (0.00 sec)

  12. 将数据库原始的信息记录保存到一个文件中 , 方便后期恢复数据库

    [root@centos7-1~]#mysql -p123456  -e 'show master logs' > post.log  主要记录备份后的日志文件大小 , 方便后面的恢复

  13. 创建数据库快照文件

    [root@centos7-1~]#lvcreate -n mysql_snapshot -L 200M -s -p r /dev/vg0/mysql   创建快照文件
Logical volume "mysql_snapshot" created.

  14. 解锁数据库：

    MariaDB [hellodb]> unlock tables;

  15. 在数据库中插入内容

    MariaDB [hellodb]> insert students(name)values('e');
Query OK, 1 row affected, 1 warning (0.01 sec)
 
MariaDB [hellodb]> insert students(name)values('f');
Query OK, 1 row affected, 1 warning (0.00 sec)
 
MariaDB [hellodb]> insert students(name)values('g');
Query OK, 1 row affected, 1 warning (0.01 sec)

  16. 将快照挂载到临时文件上：

    [root@centos7-1~]#blkid
/dev/sr0: UUID="2018-11-26-14-22-58-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/sda1: UUID="4357cc0e-6ee7-4a8f-8064-d1a54bdbf17f" TYPE="xfs"
/dev/sda2: UUID="38dd5f68-4f30-411c-b80a-0f4a60b06c6f" TYPE="xfs"
/dev/sda3: UUID="eb4bf5e6-2645-4b1c-bda8-12c5831b81c2" TYPE="xfs"
/dev/sda5: UUID="b8c37e0b-3628-40b6-ac44-c36ca09b448f" TYPE="swap"
/dev/sda6: UUID="86lOOC-6LED-5egR-hHaq-COSf-GvV5-joolGR" TYPE="LVM2_member"
/dev/mapper/vg0-mysql: UUID="a4722f63-5099-4a81-a2c6-886b659e7e9b" TYPE="xfs"    此时的卷组和快照卷组UUID一致 , 无法挂载
/dev/mapper/vg0-binlog: UUID="6c50f626-172a-44e6-af04-8fd187db96d5" TYPE="xfs"
/dev/mapper/vg0-mysql_snapshot: UUID="a4722f63-5099-4a81-a2c6-886b659e7e9b" TYPE="xfs"   此时的快照和mysql卷组UUID一致 , 所以需要加上nouuid挂载
[root@centos7-1~]#mount -o nouuid,norecovery  /dev/vg0/mysql_snapshot  /mnt    由于之前创建逻辑卷时 , 显示的是只读文件 , 所以要进行norecovery进行挂载
mount: /dev/mapper/vg0-mysql_snapshot is write-protected, mounting read-only

  17. 备份数据库文件内容：

    [root@centos7-1~]#tar cvf /root/mysql.tar /mnt  将数据库文件备份到 root目录下,会忽略sock文件 , 不影响 , 因为启动mysql服务时就会自动生成新的sock文件

  18. 此时备份已经完成 , 需要将快照删除：

    [root@centos7-1~]#umount /mnt   卸载当前的挂载
[root@centos7-1~]#lvremove  /dev/vg0/mysql_snapshot  删除快照
Do you really want to remove active logical volume vg0/mysql_snapshot? [y/n]: y
  Logical volume "mysql_snapshot" successfully removed

  19. 此时模拟删库：

    [root@centos7-1~]#rm -rf /data/mysql/*

  20. 开始恢复数据库文件：

    [root@centos7-1~]#tar -xvf mysql.tar    将备份的数据库文件解压到当前目录 , 此时会解开的目录是mnt
[root@centos7-1~]#mv mnt/*  /data/mysql/   将mnt下的所有文件复制到/data/mysql目录下 , 此时完成数据库的数据恢复
[root@centos7-1~]#ll /data/mysql
total 28704
-rw-rw---- 1 mysql mysql    16384 Nov 22 11:17 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Nov 22 11:17 aria_log_control
drwx------ 2 mysql mysql     4096 Nov 20 15:52 hellodb
-rw-rw---- 1 mysql mysql 18874368 Nov 22 12:12 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Nov 22 12:12 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Nov 12 14:47 ib_logfile1
drwx------ 2 mysql mysql     4096 Nov 12 14:47 mysql
drwx------ 2 mysql mysql     4096 Nov 12 14:47 performance_schema
drwx------ 2 mysql mysql      128 Nov 14 21:20 studentdb
drwx------ 2 mysql mysql       20 Nov 12 15:17 testdb

  21. 此时我们查看到当前的数据只恢复了修改前的那些数据 , 我们想把所有的数据库全部还原 , 那怎么办呢？

    [root@centos7-1~]#mysql -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
Database changed
 
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|    38 | a             |   0 | F      |    NULL |      NULL |
|    39 | b             |   0 | F      |    NULL |      NULL |
|    40 | c             |   0 | F      |    NULL |      NULL |
|    41 | d             |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
40 rows in set (0.01 sec)

  22. 此时需要我们还原二进制日志文件 , 在还原之前不能允许所有用户访问数据库 , 可以通过防火墙去阻挡其他用户登录 , 恢复过程中不能关闭mysql数据库：

    首先在数据库中将日志生成开关临时关闭：

    set  sql_log_bin=off;

    然后再打开一个centos7窗口 , 将二进制日志文件导入到指定的目录中

    [root@centos7-1~]#cd /data/binlog    我们去切换到二进制日志目录下
[root@centos7-1binlog]#mysqlbinlog  --start-position=1365  mysql-bin.000001 > incr.sql   然后将修改前记录的日志文件1导入到incr.sql目录中
[root@centos7-1binlog]#mysqlbinlog  mysql-bin.000002 >> incr.sql   第二个二进制日志文件全部导入到incr.sql目录中
[root@centos7-1binlog]#ls
incr.sql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.index
[root@centos7-1binlog]#mysqlbinlog  mysql-bin.000003 >> incr.sql   第三个导入到日志中
[root@centos7-1binlog]#mysqlbinlog  mysql-bin.000004 >> incr.sql   第四个导入到日志中
[root@centos7-1binlog]#mysqlbinlog  mysql-bin.000005 >> incr.sql   第五个导入到日志中

    最后我们在未关闭的mysql窗口里进行导入二进制日志文件 , 恢复文件内容 , 此时基于LVM备份的基本操作全部完成。

    MariaDB [hellodb]> source /data/binlog/incr.sql  将二进制日志文件导入到mysql数据库中
MariaDB [hellodb]> use hellodb;   此时切换当前的数据库
MariaDB [hellodb]> select * from students;  查看数据库表内的内容 , 此时数据库全部还原
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    38 | a             |   0 | F      |    NULL |      NULL |
|    39 | b             |   0 | F      |    NULL |      NULL |
|    40 | c             |   0 | F      |    NULL |      NULL |
|    41 | d             |   0 | F      |    NULL |      NULL |
|    42 | e             |   0 | F      |    NULL |      NULL |
|    43 | f             |   0 | F      |    NULL |      NULL |
|    44 | g             |   0 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+

  23. 删除之前的所有物理组 , 卷组和逻辑卷：

    [root@centos7-1~]#lvremove /dev/vg0/mysql  删除mysql逻辑卷
Do you really want to remove active logical volume vg0/mysql? [y/n]: y
  Logical volume "mysql" successfully removed 
[root@centos7-1~]#lvremove /dev/vg0/binlog  删除binlog逻辑卷
Do you really want to remove active logical volume vg0/binlog? [y/n]: y
  Logical volume "binlog" successfully removed
[root@centos7-1~]#vgremove vg0  删除vg0卷组
  Volume group "vg0" successfully removed
[root@centos7-1~]#pvremove /dev/sda6 删除sda6物理卷

    删除多分出来的分区：

    [root@centos7-1~]#fdisk /dev/sda  删除磁盘分区
Welcome to fdisk (util-linux 2.23.2).
 
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
 
 
Command (m for help): d     删除
Partition number (1-6, default 6): 6  删除sda6分区
Partition 6 is deleted
 
Command (m for help): w  保存
The partition table has been altered!
 
Calling ioctl() to re-read partition table.
 
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@centos7-1~]#partprobe  同步之前删除的磁盘分区
Warning: Unable to open /dev/sr0 read-write (Read-only file system).  /dev/sr0 has been opened read-only.
[root@centos7-1~]#lsblk  查看此时的分区情况
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda      8:0    0  200G  0 disk
├─sda1   8:1    0    1G  0 part /boot
├─sda2   8:2    0  100G  0 part /
├─sda3   8:3    0   50G  0 part /data
├─sda4   8:4    0    1K  0 part
└─sda5   8:5    0    3G  0 part [SWAP]
sr0     11:0    1   10G  0 rom

4. mysqldump备份工具

mysqldump默认会从配置文件中的mysqldump段读取选项 , 配置文件读取的顺序为：

/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf

4.1 语法选项

mysqldump [OPTIONS] database [tables]    #支持指定数据库和指定多表的备份 , 但数据库本身定
义不备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]  #支持指定数据库备份 , 包含数据库本身定义也会备份
mysqldump [OPTIONS] --all-databases [OPTIONS]   #备份所有数据库 , 包含数据库本身定义也会备份

  • 连接选项

    • -u, --user=name : 指定用户名

    • -S, --socket=name : 指定套接字路径

    • -p, --password[=name] : 指定密码

    • -P, --port= : 指定端口

    • -h, --host=name : 指定主机名

    • -r, --result-file=name : 将导出结果保存到指定的文件中 , 在Linux中等同于覆盖重定向。在windows中因为回车符\r\n的原因 , 使用该选项比重定向更好

  • 筛选选项

    • --all-databases, -A : 指定dump所有数据库。等价于使用--databases选定所有库

    • --databases, -B : 指定需要dump的库。该选项后的所有内容都被当成数据库名；在输出文件中的每个数据库前会加上建库语句和use语句

    • --ignore-table=db_name.tbl_name : 导出时忽略指定数据库中的指定表 , 同样可用于忽略视图 , 要忽略多个则多次写该选项

    • -d, --no-data : 不导出表数据 , 可以用在仅导出表结构的情况。

    • --events, -E : 导出事件调度器

    • --routines, -R : 导出存储过程和函数。但不会导出它们的属性值 , 若要导出它们的属性 , 可以导出mysql.proc表然后reload

    • --triggers : 导出触发器 , 默认已开启

    • --tables : 覆盖--databases选项 , 导出指定的表。但这样只能导出一个库中的表。格式为--tables database_name tab_list

    • --where='where_condition', -w 'where_condition' : 指定筛选条件并导出表中符合筛选的数据 , 如--where="user='jim'"

  • DDL选项

    • --add-drop-database : 在输出中的create database语句前加上drop database语句先删除数据库

    • --add-drop-table : 在输出的create table语句前加上drop table语句先删除表 , 默认是已开启的

    • --add-drop-trigger : 在输出中的create trigger语句前加上drop trigger语句先删除触发器

    • -n, --no-create-db : 指定了--databases或者--all-databases选项时默认会加上数据库创建语句 , 该选项抑制建库语句的输出

    • -t, --no-create-info : 不在输出中包含建表语句

    • --replace : 使用replace代替insert语句

  • 字符集选项

    • --default-character-set=charset_name : 在导出数据的过程中 , 指定导出的字符集。很重要 , 客户端服务端字符集不同导出时可能乱码 , 默认使用utf8

    • --set-charset : 在导出结果中加上set names charset_name语句。默认启用。

  • 复制选项

    • --apply-slave-statements:在 CHANGE MASTER 语句之前包括 STOP SLAVE 并在输出结束时包括 START SLAVE

    • --delete-master-logs : 在复制源服务器上 , 执行转储操作后删除二进制日志

    • --dump-slave[=value] :该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时 , 将会以CHANGE MASTER命令输出到数据文件；设置为2时 , 在命令前增加说明信息。该选项将会打开–lock-all-tables , 除非–single-transaction被指定。该选项会自动关闭–lock-tables选项。默认值为0。

    • --include-master-host-port : 在-–dump-slave产生的’CHANGE MASTER TO…‘语句中增加’MASTER_HOST= , MASTER_PORT=’

    • --master-data[=value] : 该选项主要用来建立一个replication , 当值为1时 , 导出文件中记录change master语句； 当值为2时 , change master语句被写成注释语句 , 默认值为空。 该选项自动忽略--lock-tables , 当没有使用--single-transaction时自动启用--lock-all-tables。

  • 格式化选项

    • --compact : 简化输出导出的内容 , 几乎所有注释都不会输出

    • --complete-insert, -c : 在insert语句中加上插入的列信息

    • --create-options : 在导出的建表语句中 , 加上所有的建表选项

    • --tab=dir_name, -T dir_name : 将每个表的结构定义和数据分别导出到指定目录下文件名同表名的.sql和txt文件中 , 其中.txt文件中的字段分隔符是制表符。要求mysqldump必须和MySQL Server在同一主机 , 且mysql用户对指定的目录有写权限 , 并且连接数据库的用户必须有file权限。且指定要dump的表 , 不能和--databases或--all-databases一起使用。它的实质是执行select into outfile。

    • --fields-terminated-by=name : 指定输出文件中的字段分隔符

    • --fields-enclosed-by=name : 指定输出文件中的字段值的包围符 , 如使用引号将字符串包围起来引用

    • --fields-optionally-enclosed-by=name : 指定输出文件中可选字段引用符

    • --fields-escaped-by=name : 指定输出文件中的转义符

    • --lines-terminated-by=name : 指定输出文件中的换行符

    • -Q, --quote-names : 引用表名和列名时使用的标识符 , 默认使用反引号"`"

  • 性能选项

    • --delayed-insert : 对于非事务表 , 在insert时支持delayed功能 , 但在MySQL5.6.6开始该选项已经废弃

    • --disable-keys, -K : 在insert语句前后加上禁用和启用索引语句 , 大量数据插入时该选项很适合。默认开启

    • --insert-ignore : 使用insert ignore语句替代insert语句

    • --quick, -q : 快速导出数据 , 该选项对于导出大表非常好用。默认导出数据时会一次性检索表中所有数据并加入到内存中 , 而该选项是每次检索一行并导出一行

  • 加锁和事务相关选项

    • --add-locks : 在insert语句前后加上lock tables和unlock tables语句 , 默认已开启。

    • --flush-logs, -F : 在开始dump前先flush logs , 如果同时使用了--all-databases则依次在每个数据库dump前flush , 如果同时使用了--lock-all-tables,--master-data或者--single-transaction , 则仅flush一次 , 等价于使用flush tables with read lock锁定所有表 , 这样可以让dump和flush在完全精确的同一时刻执行。

    • --flush-privileges : 在dump完所有数据库后在数据文件的结尾加上flush privileges语句 , 在导出的数据涉及mysql库或者依赖于mysql库时都应该使用该选项

    • --lock-all-tables, -x : 为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁 , 一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项

    • --lock-tables, -l : 在dump每个数据库前依次对该数据库中所有表加read local锁(多次加锁 , lock tables...read local) , 这样就允许对myisam表进行并发插入。对于innodb存储引擎 , 使用--single-transaction比--lock-tables更好 , 因为它不完全锁定表。因为该选项是分别对数据库加锁的 , 所以只能保证每个数据库的一致性而不能保证所有数据库之间的一致性。该选项主要用于myisam表 , 如果既有myisam又有innodb , 则只能使用--lock-tables , 或者分开dump更好

    • --single-transaction 该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给服务端。该选项对于导出事务表如innodb表很有用 , 因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。该选项只能保证innodb表的一致性 , 无法保证myisam表的一致性。在使用该选项的时候 , 一定要保证没有任何其他连接在使用ALTER TABLE,CREATE TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE语句 , 因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥 , 因为lock tables会隐式提交事务。要导出大的innodb表 , 该选项结合--quick选项更好

    • --no-autocommit : 在insert语句前后加上SET autocommit = 0 , 并在需要提交的地方加上COMMIT语句

    • --order-by-primary : 如果表中存在主键或者唯一索引 , 则排序后按序导出。对于myisam表迁移到innobd表时比较有用 , 但是这样会让事务变得很长很慢

4.2 mysqldump导出示例

4.2.1 简单备份示例

创建示例数据库和表。

# 创建第一个数据库
CREATE DATABASE backuptest;
USE backuptest;
# 创建innodb表
CREATE TABLE `student` (
    `studentid` INT (11) NOT NULL,
    `sname` CHAR (30) NOT NULL,
    `gender` enum ('male', 'female') DEFAULT NULL,
    `birth` date DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE = INNODB DEFAULT CHARSET = latin1

INSERT INTO student
VALUES
    (1,'malongshuai','male',curdate()),
    (2,'gaoxiaofang','female',date_add(curdate(), INTERVAL - 2 YEAR)),
    (3,'longshuai','male',date_add(curdate(), INTERVAL - 5 YEAR)),
    (4,'meishaonv','female',date_add(curdate(), INTERVAL - 3 YEAR)),
    (5,'tun\'er','female',date_add(curdate(), INTERVAL - 4 YEAR));

# 创建myisam表 , 并且字符集设置为UTF8
CREATE TABLE teacher (
    tid INT NOT NULL PRIMARY KEY,
    tname VARCHAR (30),
    gender enum ('male', 'female'),
    classname CHAR (10)
) ENGINE = myisam DEFAULT charset = utf8;

INSERT INTO teacher
VALUES
    (1,'wugui','male','计算机网络'),
    (2,'woniu','female','C语言'),
    (3,'xiaowowo','female','oracle');

# 创建第二个数据库
CREATE DATABASE backuptest1;
USE backuptest1;
create table student1 as select * from backuptest.student;
create table teacher1 charset=utf8 engine=myisam as select * from backuptest.teacher;

mysqldump三种备份方式如下：

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

备份单个库 , 此处备份mysql库。重定向符号可以在Linux中等价于mysqldump的-r选项 , 所以下面的语句是等价的。

[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock mysql >/tmp/mysql.bak
[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -r /tmp/mysql1.bak mysql

查看备份文件 , 会发现dump单个库的时候不会在输出文件中记录建库语句和use语句。

[root@xuexi ~]# less /tmp/mysql.bak 
-- MySQL dump 10.13  Distrib 5.6.35, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.6.35-log

/*!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 `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
......

备份多个库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest backuptest1 >/tmp/mutil_db.bak

备份所有库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --all-databases >/tmp/all_db.bak

备份多个库或所有库时 , 会在dump文件中加入建库语句和use语句。实际上 , 只要使用--databases选项 , 即使只备份一个库也会加上建库语句和use语句。

[root@xuexi ~]# grep -C 2 -i 'use' /tmp/mutil_db.bak  
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest`;

--
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest1`;

--

所以使用mysqldump备份的时候 , 无论何时都建议--databases或者--all-databases选项二选一 , 这样就免去了连进数据库的过程。

4.2.2 使用DDL选项备份示例

DDL选项如下：

--add-drop-database
--add-drop-table   
--add-drop-trigger 
-n, --no-create-db  
-t, --no-create-info
--replace

  • --no-create-db将抑制建库语句 , 所以不建议使用。

  • --no-create-info将抑制建表语句。使用和不使用的对比如下:

    mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-create-info --databases backuptest >/tmp/backuptest1.bak
vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

    733013-20180509112252258-1933573632

  • --replace将会把insert语句替换为replace语句。

    shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --replace --databases backuptest >/tmp/backuptest2.bak
shell> grep -i 'replace' /tmp/backuptest2.bak                                                                     
REPLACE INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31');
REPLACE INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

4.2.3 使用字符集选项示例

dump数据的时候 , 客户端和数据库的字符集不一致的话会进行字符集转换 , 转换的过程是不可逆的 , 所以有可能会导致乱码。

例如 , 插入一个带有中文字符的记录到字符集为latin1的表student中。

insert INTO backuptest.`student` VALUES (6,'马','male','2017-03-31');

如果提示无法插入 , 则设置客户端字符集和连接字符集为latin1 , character_set_client、character_set_connection、character_set_results , 使用set names latin1即可 , 它会设置它们3个。

插入成功之后 , 其他会话连接数据库查询将会是乱码的。dump的时候也是乱码的 , 因为dump默认会使用utf8字符集 , 在latin1转码为utf8的过程中出现了乱码。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31'),(6,'马','male','2017-03-31');
INSERT INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

再使用乱码的文件来恢复的话 , 肯定是乱码的结果。

这时可以指定dump时的字符集为latin1来使得dump数据时无需转换字符集。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --default-character-set=latin1 --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31'),(6,'马','male','2017-03-31');

测试完成之后 , 将新插入的含有中文字符的记录删除。

delete from backuptest.student where studentid=6;

4.2.4 使用格式化选项示例

格式化选项如下：

--compact                   
--complete-insert, -c       
--create-options            
--tab=dir_name, -T dir_name 
--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name            
-Q, --quote-names

  • 选项--compact可以极大的简化输出 , 让输出变得更加简单明晰。但是却不安全 , 因为它不止会简化注释语句 , 还会简化部分非注释语句 , 如insert前后的lock语句。所以该选项仅用来调试使用。

    shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --compact --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

    733013-20180509114015433-1859703444

  • "--complete-insert, -c"选项会在每个insert语句中列出插入列列表 , 而默认情况下mysqldump备份时是不加的。

    shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -c --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

    733013-20180509114220544-1560117411

  • --tab选项是将表的结构定义和数据分开dump , 结构定义语句dump到表的同名.sql文件中 , 数据dump到表的同名.txt文件中。--tab指定这些文件的输出目录。但要求mysqldump和MySQL服务器必须在同一台服务器上 , 且mysql系统用户对指定的输出目录有写权限 , 连接数据库的用户还需要有file权限。而且该选项不能和--databases或--all-databases一起使用。因为它只能定义表的结构。

    一般和该选项一起使用的有：该部分内容见select ... into outfile。

    --fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name

4.2.5 使用筛选选项示例

--all-databases, -A  
--databases, -B  
--ignore-table=db_name.tbl_name  
-d, --no-data       
--events, -E  
--routines, -R   
--triggers  
--tables 
--where='where_condition', -w 'where_condition'

筛选数据库此处不再赘述。

  • --ignore-table : 表示忽略数据库中的某张表不被dump。要忽略多张表的时候多次写该选项即可。例如忽略backuptest.student和backuptest1.student1。

    shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --ignore-table backuptest.student --ignore-table backuptest1.student1 --databases backuptest backuptest1 >/tmp/backuptest.bak
shell> grep -i 'student' /tmp/backuptest.bak |wc -l
0

  • --no-data : 表示只dump表的结构 , 不dump数据。

    mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-data -d backuptest>/tmp/backuptest.bak

grep -i 'insert' /tmp/backuptest.bak | wc -l
0

  • --tables指定数据库来导出其中的某些表。只支持指定一个数据库。该选项会覆盖--databases选项 , 所以不会建库语句和use语句。该选项可以用在分开备份innodb表和myisam表。

    mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --tables backuptest student teacher>/tmp/backuptest.bak

  • --where指定某个表的筛选条件 , 格式为--where="condition"。如果指定了筛选列 , 则要导出的表中必须要含有该列。另外 , 显式指定表时 , 格式为"db_name tab_list"。以下格式都正确。

    # 导出某数据库下的一个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student>/tmp/backuptest.bak
# 导出某数据库下多个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student teacher>/tmp/backuptest.bak
# 导出多个数据库中的筛选数据
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --where="gender='male'" --databases backuptest backuptest1>/tmp/backuptest.bak

4.2.6 使用事务选项示例

  • --add-locks选项是在insert语句前后加上lock tables和unlock tables语句。

    733013-20180509115000422-1505116505

  • --no-autocommit : 选项是在insert语句前后加上set autocommit=0 , 并在事务结束的地方加上commit语句。这样插入数据时只需一次提交 , 可以大幅提升大量插入时的性能。

  • --flush-logs, -F : 在开始dump前先flush logs , 如果同时使用了--all-databases , 则依次在每个数据库dump前flush；如果同时使用了--lock-all-tables,--master-data或者--single-transaction , 则仅flush一次 , 因为这几个选项是dump前开启一个长事务或者全局锁定。等价于使用flush tables with read lock锁定所有表 , 这样可以让dump和flush在完全精确的同一时刻执行。

  • --flush-privileges : 在dump完所有数据库后在数据文件的结尾加上flush privileges语句 , 在导出的数据涉及mysql数据库或者依赖于mysql数据库时都应该使用该选项。

  • --lock-all-tables, -x : 为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁 , 一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项。

  • --lock-tables, -l : 在dump每个数据库前依次对该数据库中所有表加上read local锁(多次加锁 , lock tables ... read local) , 这样就允许对myisam表进行并发插入。对于innodb存储引擎 , 使用--single-transaction比--lock-tables更好 , 因为它不完全需要锁定表。因为该选项是分别对数据库加锁的 , 所以只能保证每个数据库之间的一致性而不能保证在所有数据库之间的一致性。 该选项主要用于myisam表 , 如果既有myisam又有innodb , 则只能使用--lock-tables了 , 或者分开dump更好。

  • --single-transaction : 该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给服务端。该选项对于导出事务表如innodb表很有用 , 因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。当使用该选项只能保证innodb表的一致性 , 对于myisam表是无法保证的。但是在使用该选项的时候 , 一定要保证没有任何其他数据库的连接在使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE语句 , 因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥 , 因为lock tables会隐式提交事务 , 要导出大的innodb表 , 该选项结合--quick选项更好。

4.3 使用方法演示

  • 备份多个库: mysqldump -uroot -padmin -B 库1 库2 库3 > /work/bak/xxx.sql

    [root@qiuhom ~]# mysqldump -uroot -padmin -B qiuhom qiuhom_gbk > /work/bak/qiuhom_qiuhom_gbk.sql
[root@qiuhom ~]# egrep -v "#|\*|--|^$" /work/bak/qiuhom_qiuhom_gbk.sql                         
USE `qiuhom`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
UNLOCK TABLES;
USE `qiuhom_gbk`;
DROP TABLE IF EXISTS `test_gbk`;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
LOCK TABLES `test_gbk` WRITE;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');
UNLOCK TABLES;　

  • 备份单个库 : mysqldump -uroot -padmin 库名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom_gbk  
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');

    mysqldump -uroot -padmin -B 库名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -B qiuhom_gbk
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `qiuhom_gbk` /*!40100 DEFAULT CHARACTER SET gbk */;
 
USE `qiuhom_gbk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');

    从以上两条命令返回的结果来看我可以得出一点 mysql备份加上-B的选项时 ，备份的sql文件里就会多一条create 备份数据库名 和use 备份数据库名，这两条条语句，所有加-B备份的库 在还原时可以不指定库名，也可以不用顾虑还原的库是否存在，直接还原即可。没有加-B选项备份的还原就需要考虑库是否存在，还要指定库名。这就是加-B的好处。

  • 备份多个表 : mysqldump -uroot -padmin 库名 表1名 表2名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom test test1 test2 >/work/bak/qiuhom_tables.sql
[root@qiuhom bak]# cat qiuhom_tables.sql
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;　　

  • 备份单个表 : mysqldump -uroot -padmin 库名 表名 > /work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom test >/work/bak/qiuhom__test.sql            
[root@qiuhom bak]# cat qiuhom__test.sql          
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

  • 备份表结构

    备份单表结构 : mysqldump -uroot -padmin -d 库名 表名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom test >/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

    可以看出加上-d选项后，备份到sql文件里没有insert into 语句插入数据，只有建表的语句。

    备份多表结构 : mysqldump -uroot -padmin -d 库名 表名1 表明2 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom test test1>/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

    备份某库里的所有表结构: mysqldump -uroot -padmin -d 库名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom >/work/bak/qiuhom__test.sql         
[root@qiuhom bak]# cat qiuhom__test.sql
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

    备份表结构和备份表都是一样的语法只是多-d的选项，也是在同一个库里面的，不能跨库的去备份。

  • 只备份数据

    单表备份数据 : mysqldump -uroot -padmin -t 库名 表名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom test>/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

    多表备份数据 : mysqldump -uroot -padmin -t 库名 表名1 表名2>/work/bak/xxx.sql　

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom test test1>/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO `test1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(1,'a'),(2,'b'),(3,'c'),(4,'d');

    备份整个库的表数据 : mysqldump -uroot -padmin -t 库名 >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom>/work/bak/qiuhom__test.sql               
[root@qiuhom bak]# cat qiuhom__test.sql
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO `test1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(1,'a'),(2,'b'),(3,'c'),(4,'d');
INSERT INTO `test2` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

    备份数据和备份表、备份表结构都是一样，必须跟库名 然后是表名，一个或多个，不能跨库备份。　

  • 压缩备份数据

    1. 将数据库进行备份并压缩：

      [root@qiuhom~]#mysqldump -p123456  -B hellodb |xz  > hello.B.sql.xz 压缩为xz格式的文件
第二步：删除数据库

    2. 删除数据库

      [root@qiuhom~]#mysql -p123456 -e 'drop database hellodb'

    3. 解压压缩备份文件并还原

      [root@qiuhom~]#unxz hello.B.sql.xz   解压备份的数据库
[root@qiuhom~]#xz -d  hello.B.sql.xz   此方法也是解药备份的数据库
[root@qiuhom~]#mysql -p123456  < hello.B.sql    将解压后的数据库导入导入到mysql中 完成数据库的还原
[root@qiuhom~]#mysql -p123456  -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| studentdb          |
| testdb             |
+--------------------+

  • -A 备份数据库里的所有库的内容 : mysqldump -uroot -padmin -A >/work/bak/xxx.sql

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact -A  >/work/bak/qiuhom__test.sql      
[root@qiuhom bak]# cat qiuhom__test.sql
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `mysql`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
 
由于内容过多。。只截取了部分作为展示

    加了-A选项 就表示备份整个数据库的内容，包括mysql系统的库都给一起备份下来了

  • -F 刷新bin-log

    这个参数的主要作用是刷新binlog文件，就是你每备份一次binlog文件就会自动刷新为一个新的文件

    [root@qiuhom bak]# ll /application/mysql/data/mysql-bin*
ls: cannot access /application/mysql/data/mysql-bin*: No such file or directory
[root@qiuhom bak]# mysqldump -uroot -padmin -B qiuhom >/work/bak/qiuhom.sql
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                   
ls: cannot access /application/mysql/data/mysql-bin*: No such file or directory
[root@qiuhom bak]# mysqldump -uroot -padmin -F -B qiuhom >/work/bak/qiuhom.sql
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                      
-rw-rw---- 1 mysql mysql 107 Oct  6 12:50 /application/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Oct  6 12:50 /application/mysql/data/mysql-bin.index
[root@qiuhom bak]# mysqldump -uroot -padmin -F -B qiuhom >/work/bak/qiuhom.sql
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                      
-rw-rw---- 1 mysql mysql 150 Oct  6 12:50 /application/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql 107 Oct  6 12:50 /application/mysql/data/mysql-bin.000002
-rw-rw---- 1 mysql mysql  38 Oct  6 12:50 /application/mysql/data/mysql-bin.index

  • --master-data 增加bin-log日志文件名及对应的位置点。（单纯备份用2，主从复制时用1）备份时，建议使用此选项，恢复时也可以快速定位恢复：

    mysqldump导出数据时，当这个选项的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是binlog文件和position的记录信息，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个选项的值是1;当这个选项的值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

    [root@qiuhom bak]# mysqldump -uroot -padmin --compact --master-data=1 qiuhom test >/work/bak/qiuhom.sql   
[root@qiuhom bak]# cat qiuhom.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
[root@qiuhom bak]# mysqldump -uroot -padmin --compact --master-data=2 qiuhom test >/work/bak/qiuhom.sql 
[root@qiuhom bak]# cat qiuhom.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

4.4 mysqldump使用建议

  1. 从性能考虑：在需要导出大量数据的时候 , 使用--quick选项可以加速导出 , 但导入速度不变。如果是innodb表 , 则可以同时加上--no-autocommit选项 , 这样大量数据量导入时将极大提升性能。

  2. 一致性考虑：对于innodb表 , 几乎没有理由不用--single-transaction选项。对于myisam表 , 使用--lock-all-tables选项要好于--lock-tables。既有innodb又有myisam表时 , 可以分开导出 , 又能保证一致性 , 还能保证效率。

  3. 方便管理和维护性考虑：在导出时flush log很有必要。加上--flush-logs选项即可。而且一般要配合--lock-all-tables选项或者--single-transaction选项一起使用 , 因为同时使用时 , 只需刷新一次日志即可 , 并且也能保证一致性。同时 , 还可以配合--master-data=2 , 这样就可以方便地知道二进制日志中备份结束点的位置。

  4. 字符集考虑：如果有表涉及到了中文数据 , 在dump时 , 一定要将dump的字符集设置的和该表的字符集一样。

  5. 杂项考虑：备份过程中会产生二进制日志 , 但是这是没有必要的。所以在备份前可以关掉 , 备份完后开启。set sql_log_bin=0关闭 , set sql_log_bin=1开启。

以下是备份不同存储引擎类型表的示例：

备份myisam表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --lock-all-tables --flush-logs --master-data=2 --tables backuptest teacher >/tmp/myisam.sql ;

set sql_log_bin=1

备份innodb表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

set sql_log_bin=1

4.5 mysqldump+二进制日志备份

mysqldump可以实现全备份 , 在mysqldump之后再二进制日志备份就相当于增量备份 , 这样就可以实现全备份之后的定时点还原。

假设要备份的是一张innodb表。使用下面的语句：

mysqldump  -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

因为dump前会flush二进制日志 , 所以之后对该表的操作会记录到新的滚动日志中。然后只需备份新的二进制日志即可。

然后在该表中插入一行记录。

insert into student select 10,'xiaolonglong','male','2015-01-02';

备份新的二进制日志。

mysqlbinlog mysql-bin.000002 >/tmp/new_binlog.sql

设计刚才备份的表误操作 , 如删除该表。

drop table student;

使用该表的完全备份和二进制日志恢复。因为备份时使用的是--tables选项 , 所以要恢复需要进入数据库指定数据库 , 然后使用source来加载sql文件。

use backuptest;
source /tmp/innodb.sql;
source /tmp/new_binlog.sql;

4.6 mysqldump工具评价

mysqldump备份的文件是逻辑SQL语句 , 总体来说 , 简单 , 便捷 , 在有些时候迁移数据的时候比较有用。另外 , 它的功能也很多 , 例如导出数据 , 导出表结构等。

但是缺点是恢复速度太慢 , 因为恢复数据时是通过insert不断插入记录的 , 它的恢复速度远不及load data infile导入数据。

在备份方式上 , mysqldump备份myisam表时因为要加--lock-all-tables , 这时要备份的数据库全部被上锁 , 可读不可写 , 所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction , 会自动将隔离级别设置为repeatable read并开启一个事务 , 这时mysqldump将获取dump执行前一刻的行版本 , 并处于一个长事务中直到dump结束。所以不影响目标数据库的使用 , 可读也可写 , 即实现的是热备。

5. 生产环境使用

常见通用选项

  • -A,--all-databases : #备份所有数据库，含create database

  • -B, --databases db_name… #指定备份的数据库，包括create database语句

  • -E,--events：#备份相关的所有event scheduler

  • -R, --routines：#备份所有存储过程和自定义函数

  • --triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器

  • --default-character-set=utf8 #指定字符集

  • --master-data[=#]： 此选项须启用二进制日志

    #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复 制多机使用

    #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction)

  • -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志

  • --compact : 去掉注释，适合调试，生产不使用

  • -d, --no-data : 只备份表结构

  • -t, --no-create-info : 只备份数据,不备份create table

  • -n,--no-create-db: 不备份create database，可被-A或-B覆盖

  • --flush-privileges : 备份mysql或相关时需要使用

  • -f, --force : 忽略SQL错误，继续执行

  • --hex-blob : 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码

  • -q, --quick 不缓存查询，直接输出，加快备份速度

5.1 备份策略

InnoDB建议备份策略

mysqldump –uroot -p –A –F –E –R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob 
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges  -
triggers  --default-character-set=utf8  --hex-blob 
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

5.2 特殊数据库备份脚本

[root@centos8 ~]#cat backup_hellodb.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=123456
mysqldump -uroot -p "$PASS" -F –E –R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB  | gzip  > ${DIR}/${DB}_${TIME}.sql.gz

5.3 分库备份并压缩

[root@centos8 ~]#for db in `mysql -uroot  -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot  -e 'show databases'|grep -Ev 
'^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /data/$db.sql.gz;done

[root@centos8 ~]#mysql -uroot  -e 'show databases'|grep -Ev 
'^(Database|information_schema|performance_schema)$' | sed -rn  's#(.*)#mysqldump -B \1 | gzip  > /data/\1.sql.gz#p' |bash

[root@centos8 ~]#mysql -uroot  -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 |  gzip  > /data/\1.sql.gz#p' |bash

对应脚本文件

[root@centos8 ~]#cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456
[ -d "$DIR" ]  || mkdir $DIR

for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ev "^Database|.*schema$"`;
    do mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB  | gzip  > ${DIR}/${DB}_${TIME}.sql.gz

done

5.4 完全备份与还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
 [mysqld]
 log-bin 
#备份
[root@centos8 ~]#mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz
 #还原
[root@centos8 backup]#dnf install mariadb-server
 [root@centos8 backup]#gzip -d all-2019-11-27.sql.gz
 [root@centos8 ~]#mysql
 MariaDB [(none)]> set sql_log_bin=off;
 MariaDB [(none)]> source /backup/all-2019-11-27.sql
 MariaDB [(none)]> set sql_log_bin=on;

5.5 mysqldump 和二进制日志结合实现增量备份

[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz

 #观察备份文件中的二进制文件和位置，将之后的二进制日志进行复制备份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup
[root@centos8 ~]#mysqlbinlog --start-position=389 /backup/mariadb-bin.000003 > 
/backup/inc.sql

5.6 删除数据库，还原数据库文件

  1. 新建一个二进制日志文件路径

    [root@centos7-1data]#mkdir /data/logbin
[root@centos7-1data]#chown -R mysql.mysql logbin

  2. 将二进制日志文件路径指定到新建的文件路径位置

    vim /etc/my.cnf
[mysqld]
log_bin=/data/logbin/mysql-bin                                                                           socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql

    先临时在mysql插入几条记录

    MariaDB [hellodb]> create table teacher(id int primary key auto_increment,name varchar(3));  创建表
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [hellodb]> insert teacher(id,name)values(1,'a'); 在表中插入内容a
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert teacher(id,name)values(2,'b');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert teacher(id,name)values(3,'c');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from teacher;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.01 sec)

  3. 打开一个新终端, 备份数据库

    mysqldump -uroot -123456 -A -F  --single-transaction --master-data=2  --default-character-set=utf8 | gzip > /data/all_bak_`date +%F`.sql.gz

    此时在数据库中再插入几条记录：

    [root@centos7-1data]#mysql -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> insert teacher(id,name)values(4,'d');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [hellodb]> insert teacher(id,name)values(5,'f');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [hellodb]> select * from teacher;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | f    |
+----+------+
5 rows in set (0.01 sec)

  4. 在新终端开始破坏数据库

    [root@centos7-1data]#rm -rf /var/lib/mysql/*

  5. 在新终端设置防火墙功能，阻止其他用户访问mysql数据库，并解压备份的数据库

    [root@centos7-1data]#gzip -d all_bak_2019-11-22.sql.gz

  6. CentOS 8需要事先生成数据库相关文件，CentOS7 不需要执行此步

    mysql_install_db  --user=mysql 
systemctl restart mariadb

  7. 在旧终端临时禁用二进制日志生产

    [root@centos7-1data]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show  variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+

  8. 在新终端将解压后的文件在数据库中直接执行，此时我们看到了数据库的文件已经恢复，但是二进制的日志文件未恢复

    MariaDB [hellodb]> source /data/all_bak_2019-11-22.sql   还原备份数据库的数据
MariaDB [testdb]> use hellodb
MariaDB [hellodb]> select * from teacher; 此时查看只恢复了备份数据库的数据
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

  9. 在新终端查看备份的二进制日志文件备份的时间点，可以看出最近备份的数据库文件中对应的二进制日志文件是000007后缀的文件。

    1769223-20191122224411436-1149382312

  10. 在新终端，将二进制日志的文件全部存放在一个文件中。

    [root@centos7-1data]#mysqlbinlog  /data/logbin/mysql-bin.000007 --start-position=245  > incr.sql
[root@centos7-1data]#mysqlbinlog  /data/logbin/mysql-bin.0000{08,09,10}  >> incr.sql

  11. 在旧终端，将数据库中恢复二进制日志文件：

    MariaDB [hellodb]> source /data/incr.sql   恢复二进制日志的文件信息
MariaDB [mysql]> use hellodb
Database changed
MariaDB [hellodb]> select * from teacher;  查看二进制日志备份的文件信息
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | f    |
+----+------+

  12. 在旧终端检查无误后，开启二进制日志功能，并打开防火墙，使其用户接入mysql数据库,此时所有的备份完成。

    set` `sql_log_bin=on;

5.7 误删除表实验：

  1. 新建一个二进制日志文件路径

    [root@centos7-1data]#mkdir /data/logbin
[root@centos7-1data]#chown -R mysql.mysql logbin

  2. 将二进制日志文件路径指定到新建的文件路径位置

    vim /etc/my.cnf
[mysqld]
log_bin=/data/logbin/mysql-bin                                                                                                        
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
binlog_format=ROW

    先临时在mysql插入几条记录：

    mysql -p123456  登陆数据库
MariaDB [hellodb]> use hellodb  切换到数据库
MariaDB [hellodb]> insert students(name)values('a');  插入a内容
Query OK, 1 row affected, 1 warning (0.01 sec)
 
MariaDB [hellodb]> insert students(name)values('b'); 插入b内容
Query OK, 1 row affected, 1 warning (0.00 sec)

  3. 打开一个新终端, 备份数据库

    mysqldump -uroot -123456 -A -F  --single-transaction --master-data=2  --default-character-set=utf8 | gzip > /data/all_bak_`date +%F`.sql.gz

  4. 开始删除数据库中表的内容

    MariaDB [hellodb]> drop table students;

    再在teachers表格中插入信息：

    MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
 
MariaDB [hellodb]> insert teachers(name)values('a');
Query OK, 1 row affected, 1 warning (0.00 sec)
 
MariaDB [hellodb]> insert teachers(name)values('b');
Query OK, 1 row affected, 1 warning (0.00 sec)

  5. 设置防火墙，阻止用户访问数据库，然后临时关闭二进制日志生成开关

    MariaDB [hellodb]> set sql_log_bin=off;

  6. 现在就可以利用上新终端备份的数据库，将数据库进行还原

    MariaDB [mysql]> source /data/incr.sql  恢复数据库
MariaDB [hellodb]> select * from students;  查看被删除的students表，此时已经查看到表格已经恢复
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    26 | a             |   0 | F      |    NULL |      NULL |
|    27 | b             |   0 | F      |    NULL |      NULL |

  7. 查看all_back.sql 确定二进制日志位置：

    [root@centos7-1data]#less all_back.sql
-- MySQL dump 10.16  Distrib 10.2.29-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.5.60-MariaDB
 
/*!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 */;
 
--
-- Position to start replication or point-in-time recovery from
--
 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;  可以确定，备份的二进制日志文件在000002（包含2）以后的文件全部都需要还原　

  8. 导出000002以后的二进制日志文件

    [root@centos7-1logbin]#mysqlbinlog mysql-bin.000002 > /data/incr.sql

  9. 查看导出的二进制日志文件里边的内容，被删除的DROP命令那行注释掉，或者删除　

    [root@centos7-1data]#vim incr.sql
#DROP TABLE IF EXISTS `students` /* generated by server */

  10. 开始恢复二进制日志文件

    MariaDB [hellodb]> source /data/incr.sql

  11. 此时可以看到，已经恢复到当前时间点插入的表格内容

    MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | a             |   0 | NULL   |
|   6 | b             |   0 | NULL   |
+-----+---------------+-----+--------+

  12. 最后恢复二进制日志开关和防火墙，让用户继续访问

    set  sql_log_bin=on;

5.8 MySQL数据库分库备份脚本

#!/bin/bash
BACK_TIME=`date +"%F-%H":%M:%S`  # 备份脚本时间戳，也可以使用${date +"%F-%H":%M:%S}
USER=root
PASS=centos
DATABASE=`mysql -u$USER  -p$PASS -e 'show databases' | grep -Ev '^(Database|information_schema|performance_schema)$'` # 将除了那些固定的数据库之外的进行备份。
for db in $DATABASE;do
      MYDIR=/data/backup/$db_${BACK_TIME}
      [ ! -d $MYDIR ] && mkdir -p ${MYDIR} # 判断目录不存在为真，就创建备份目录。注意：如果去掉！，就是存在为真了
           mysqldump  -u$USER -p$PASS  -B $db  |gzip > $MYDIR/${db}_$BACK_TIME.sql.gz
done

参考链接

技术分享|容灾备份|Linux备份策略 ( 完全备份、增量备份和差异备份 ) 详解|虚拟机备份专家云祺科技

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述 - 骏马金龙 - 博客园

MySQL之七---MySQL数据库的备份与恢复 - 一叶知秋~~ - 博客园


熊熊

site logo

Hi,Friend

© 2025 cbear小站