mysql怎么复制备份表 mysql备份表sql

在MySQL中如何复制表

在工作中需要把MySQL环境某一个库里一个表复制一份，故整理记录方法如下：复制整个表create table new_table select * from old_table;

创新互联公司主要从事成都网站设计、网站制作、网页设计、企业做网站、公司建网站等业务。立足成都服务玛纳斯,10余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:13518219792

复制表,不复制数据create table new_table select * from old_table where 0;

主键，索引，自增等其他额外特征不会被带到新表中。这点和其他的数据库产品类似。

如何将mysql的一个完整数据库全部复制到另外一个数据库?

如果从库上表 t 数据与主库不一致，导致复制错误，整个库的数据量很大，重做从库很慢，如何单独恢复这张表的数据？通常认为是不能修复单表数据的，因为涉及到各表状态不一致的问题。下面就列举备份单表恢复到从库会面临的问题以及解决办法：

场景 1

如果复制报错后，没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新，从库数据停滞在报错状态（假设 GTID 为 aaaa:1-100）。

修复步骤：

在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000）；

恢复到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:101，从库上表 t 的数据状态是领先其他表的。aaaa:101-10000 这些事务中只要有修改表 t 数据的事务，就会导致复制报错 ，比如主键冲突、记录不存在（而 aaaa:101 这个之前复制报错的事务必定是修改表 t 的事务）

解决办法：启动复制时跳过 aaaa:101-10000 这些事务中修改表 t 的事务。

正确的修复步骤：

1. 在主库上备份表 t （假设备份快照 GTID 为 aaaa:1-10000），恢复到从库；

2. 设置复制过滤，过滤表 t：

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db_name.t');

3. 启动复制，回放到 aaaa:10000 时停止复制（此时从库上所有表的数据都在同一状态，是一致的）;

START SLAVE UNTIL SQL_AFTER_GTIDS = 'aaaa:10000';

4. 删除复制过滤，正常启动复制。

注意事项：这里要用 mysqldump --single-transaction --master-data=2，记录备份快照对应的 GTID

场景 2

如果复制报错后，使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。

修复步骤：

在主库上备份表 t （假设备份快照 GTID为 aaaa:1-10000）；

停止从库复制，GTID为 aaaa:1-20000；

恢复表 t 到从库；

启动复制。

这里的问题是复制起始位点是 aaaa:20001，aaaa:10000-20000 这些事务将不会在从库上回放，如果这里面有修改表 t 数据的事务，从库上将丢失这部分数据。

解决办法：从备份开始到启动复制，锁定表 t，保证 aaaa:10000-20000 中没有修改表 t 的事务。

正确修复步骤：

对表 t 加读锁；

在主库上备份表 t；

停止从库复制，恢复表 t；

启动复制；

解锁表 t。

如果是大表，这里可以用可传输表空间方式备份、恢复表，减少锁表时间。

mysql备份表的几种方式

基于之前的文章方法，加入批处理命令即可实现自动备份。只是由于批处理命令中对于备份文件的名字按照时间命名比较特别，所以特别整理一文。

1、复制date文件夹备份

============================

假想环境：

MySQL 安装位置：C:\MySQL

论坛数据库名称为：bbs

数据库备份目的地：C:\db_bak\

============================

新建db_bak.bat，写入以下代码

*******************************Code Start*****************************

net stop mysql

xcopy c:\mysql\data\bbs\*.* c:\db_bak\bbs\%date:~0,10%\ /S /I

net start mysql

*******************************Code End *****************************

然后使用Windows的“计划任务”定时执行该批处理脚本即可。（例如：每天凌晨3点执行back_db.bat）

解

释：备份和恢复的操作都比较简单，完整性比较高，控制备份周期比较灵活，例如，用%date:~0,10%。此方法适合有独立主机但对mysql没有管理

经验的用户。缺点是占用空间比较多，备份期间mysql会短时间断开（例如：针对30M左右的数据库耗时5s左右）,针对%date:~0,10%的用法

参考 。

2、mysqldump备份成sql文件

==============

假想环境：

MySQL 安装位置：C:\MySQL

论坛数据库名称为：bbs

MySQL root 密码：123456

数据库备份目的地：D:\db_backup\

脚本：

rem *******************************Code Start*****************************

@echo off

set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"

C:\MySQL\bin\mysqldump --opt -u root --password=123456 bbs D:\db_backup\bbs_%Ymd%.sql

@echo on

rem *******************************Code End*****************************

将以上代码保存为backup_db.bat

然后使用Windows的“计划任务”定时执行该脚本即可。（例如：每天凌晨5点执行back_db.bat）

说明：此方法可以不用关闭数据库，并且可以按每一天的时间来名称备份文件。

通过%date:~5,2%来组合得出当前日期，组合的效果为yyyymmdd,date命令得到的日期格式默认为yyyy-mm-dd(如果不是此格式可以通过pause命令来暂停命令行窗口看通过%date:~,20%得到的当前计算机日期格式)，所以通过%date:~5,2%即可得到日期中的第五个字符开始的两个字符，例如今天为2009-02-05,通过%date:~5,2%则可以得到02。（日期的字符串的下标是从0开始的）

3、利用WinRAR对MySQL数据库进行定时备份。

对于MySQL的备份，最好的方法就是直接备份MySQL数据库的Data目录。下面提供了一个利用WinRAR来对Data目录进行定时备份的方法。

首先当然要把WinRAR安装到计算机上。

将下面的命令写入到一个文本文件里

*******************************Code Start*****************************

net stop mysql

c:\progra~1\winrar\winrar a -ag -k -r -s d:\mysql.rar d:\mysql\data

net start mysql

*******************************Code End*****************************

保存，然后将文本文件的扩展名修改成CMD。进入控制面版，打开计划任务，双击“添加计划任务”。在计划任务向导中找到刚才的CMD文件，接着为这个任务指定一个运行时间和运行时使用的账号密码就可以了。

这种方法缺点是占用时间比较多，备份期间压缩需要时间，mysql断开比第一种方法更多的时间，但是对于文件命名很好。