mysql 备份表数据_MySQL备份数据库

很多公司没有专业的DBA，很多时候开发人员在开发时不得不手动在生产环境下操作数据库，总所周知，在生产环境下操作数据非常危险，如果 update 更新或 delete 删除语句忘写了 where 条件或者写错了 where 条件，那么只能跑路了。

为了保证万无一失，笔者在操作数据库前会进行数据备份，如此一来，即使手抖出错，数据也能及时从备份中恢复。我总结了mysql几种备份数据的方法。

1. 锁表备份

这种方法最简单，也是笔者备份小表常用的方式。对需要备份的表加读锁，避免备份期间数据被修改，然后建立一张和原表一样结构的表，然后将原表数据复制到新表中，数据备份完成之后需要将将锁释放。

备份为什么要加锁？

细想一下，备份为什么要加锁呢？我们来看一下不加锁会有什么问题。

1. 假设你现在要维护账户余额表和用户课程表，现在发起一个逻辑备份。

2. 假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。

3. 这个备份结果里，用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课”。如果后面用这个备份来恢复数据的话，用户 A 就发现，自己赚了。

也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

1、表锁

表锁的语法是 lock tables … read/write。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。

同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

方式一、复制表结构、数据

方式1：

create  table 新表  as  select * from 旧表；

这种方法会将 旧表 中所有的内容都拷贝过来，包括表结构、数据。

缺点：新表中没有了旧表的primary key、Extra（auto_increment）等属性。需要自己用"alter"添加。

方式2：

– 复制表结构，包含主键、索引

create table 新表 like 旧表。 

– 将原表数据插入新表

insert into 新表 select * from 旧表;

数据恢复：

rename table  旧表  to  新表；

2.事务控制

在清洗过程中，使用事务来确保操作的原子性。这样即使操作失败，也可以通过回滚避免数据丢失或损坏：

START TRANSACTION;
-- 清洗操作
COMMIT;
-- 或 ROLLBACK 在出现问题时