mysql 数据库备份(如何备份整个mysql数据库)

老铁们，大家好，相信还有很多朋友对于mysql 数据库备份和如何备份整个mysql数据库的相关问题不太懂，没关系，今天就由我来为大家分享分享mysql 数据库备份以及如何备份整个mysql数据库的问题，文章篇幅可能偏长，希望可以帮助到大家，下面一起来看看吧！

MySql数据库备份的几种方式

备份数据库中的某个表

$> mysqldump-u root-h host-p dbname tbname1, tbname2> backdb.sql

备份多个数据库

$> mysqldump-u root-h host-p--databases dbname1, dbname2> backdb.sql

备份系统中所有数据库

$> mysqldump-u root-h host-p--all-databases> backdb.sql

直接复制整个数据库目录(对于InnoDB存储引擎不适用)备份

windowns: installpath/mysql/data

linux:/var/lib/mysql

在复制前需要先执行如下命令：

MYSQL> LOCK TABLES;

#在复制过程中允许客户继续查询表，

MYSQL> FLUSH TABLES;

#将激活的索引页写入硬盘。

mysqlhotcopy工具备份

备份数据库或表最快的途径，只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。

要使用该备份方法必须可以访问备份的表文件。

$> mysqlhotcopy-u root-p dbname/path/to/new_directory;

#将数据库复制到new_directory目录。

mysql命令导入sql文件还原

$> mysql-u root-p [dbname]< backup.sql

#执行前需要先创建dbname数据库，如果backup.sql是mysqldump创建的备份文件则执行是不需要dbname。

MYSQL> source backup.sql;

#执行source命令前需要先选择数据库。

直接复制数据库目录还原

注：该方式必须确保原数据库和待还原的数据库主版本号一致，并且只适用于MyISAM引擎的表。

关闭mysql服务。

将备份的文件或目录覆盖mysql的data目录。

启动mysql服务。

对于linux系统，复制完文件后需要将文件的用户和组更改为mysql运行的用户和组。

mysqlhotcopy快速恢复

停止mysql服务，将备份数据库文件复制到存放数据的位置（mysql的data文件夹），重先启动mysql服务即可(可能需要指定数据库文件的所有者)。

$> cp-R/usr/backup/test/usr/local/mysql/data

#如果恢复的数据库已经存在，则使用DROP语句删除已经存在的数据库之后，恢复才能成功，还需要保证数据库版本兼容。

相同版本数据库之间迁移

$> mysqldump-h www.abc.com-uroot-p password dbname|

$> mysqldump-h www.bcd.com-uroot-p password

#将服务器www.abc.com的数据库dbname迁移到服务器www.bcd.com的相同版本数据库上。

不同版本的mysql数据库之间的迁移

备份原数据库。

卸载原数据库。

安装新数据库。

在新数据库中还原备份的数据库数据。

数据库用户访问信息需要备份mysql数据库。

默认字符集问题，MySQL4.x中使用latin1作为默认字符集，mysql5.x使用utf8作为默认字符集。如果有中文数据需要对默认字符集进行更改。

不同数据库之间的迁移

MyODBC工具实现MySQL和SQL Server之间的迁移。

MySQL Migration Toolkit工具。

表的导出和导入

SELECT...... INTO OUTFILE导出文本文件,该方法只能导出到数据库服务器上，并且导出文件不能已存在。

MYSQL> SELECT...... INTO OUTFILE filename [OPTIONS]

MYSQL> SELECT* FROM test.person INTO OUTFILE"C:\person0.txt";

#将表person里的数据导入为文本文件person0.txt。

mysqldump文件导出文本文件(和INTO OUTFILE不一样的是该方法所有的选项不需要添加引号)

$> mysqldump-T path-u root-p dbname [tables] [OPTIONS]

#-T参数表明导出文本文件。path导出数据的目录。

$> mysqldump-T C:\test person-u root-p

#将test表中的person表导出到文本文件。执行成功后test目录下会有两个文件，person.sql和person.txt

mysql命令导出文本文件

MYSQL> mysql-u root-p--execute="SELECT* FROM person;" test> C:\person3.txt;

#将test数据库中的person表数据导出到person3.txt文本文件中。--vartical参数可以将一行分为多行显示。

MYSQL> mysql-u root-p--vartical--execute="SELECT* FROM person;" test> C:\person3.txt;

#--html将表导出为html文件，--xml文件将表导出为xml文件

LOAD DATA INFILE导入文本文件

MYSQL> LOAD DATA INFILE‘filename.txt‘ INTO TABLE tablename [OPTIONS] [IGNORE number LINES];

# [IGNORE number LINES]表示忽略行数

MYSQL> LOAD DATA INFILE‘C:\person0.txt‘ INTO TABLE test.person;

mysqlimport导入文本文件

$> mysqlimport-u root-p dbname filename.txt [OPSTONS]

#导入的表名有文件名决定，导入数据之前表必须存在

$> mysqlimport-uroot-p test C:\backup\person.txt

#将数据导入到test数据库的person表中。

使用mysqlbinlog恢复数据

$> mysqlbinlog [option] filename| mysql-u user-p password

# filename为二进制日志文件，

$> mysqlbinlog--stop-date="2013-03-30 15:27:47" D:\MySQL\log\binlog\binlog.000008| mysql-u root-p password

#根据日志文件binlog.000008将数据恢复到2013-03-30 15:27:47以前的操作。

启动二进制日志

log-bin= path/filename#日志文件存储目录和文件名

expire_log_days= 10#日志自动删除时间

max_binlog_size= 100M#日志文件最大大小

查看二进制日志

MYSQL> SHOW VARIABLES LIKE‘log_%‘;

MYSQL> SHOW BINARY LOGS;

$> mysqlbinlog filename

# filename为二进制日志文件名。

删除二进制日志

MYSQL> RESET MASTER;#删除所有二进制日志

MYSQL> PURGE{MASTER| BINARY} LOGS TO‘log_name‘;#删除文件编号小于log_name编号的文件

MYSQL> PURGE{MASTER| BINARY} LOGS BEFORE‘date‘;#删除指定日期以前的文件

暂时停止二进制日志（不需要重启mysql服务）

MYSQL> SET sql_log_bin={0|1}#暂停或启动二进制日志。MySql数据库备份的几种方式

标签：userbaseload datamaster服务器reset表数据sql存储

mysql数据库备份的几种方法

数据备份是数据容灾的最后一道防线，即便有着两地三中心的架构，备份也依然重要。如果备份出问题，备份时影响了交易业务，备份数据无法恢复，这些也是企业难以承受的。所以选择合适的备份工具尤为重要。

每个企业级数据库都会有配套的备份工具，MEB(MySQL Enterprise Backup)就是MySQL企业版中非常重要的工具之一，是为企业级客户提供的数据备份方案。

Xtrabackup一直作为MEB开源版备胎而存在，从MySQL 8.0开始情况可能会变得有所不同。

在 MySQL 8.0的Backup Lock、Redo Log Archiving、Page Tracking等新特性的加持下，MEB备份/恢复体验会更好，目前xtrabackup还不支持这些特性。

MySQL企业版还有哪些功能？

特性1：Backup Lock

8.0之前使用xtrabackup或MEB做物理备份，为了保证备份时InnoDB引擎表与其他引擎数据文件、及binlog日志的一致性会上全局读锁，再拷贝非InnoDB文件，这期间MySQL会变成只读，数据无法写入。表数量越多，可能加上时间越长，如果使用的xtrabackup不小心没加rsync参数，逐个拷贝frm文件，锁定时间会更长，对业务影响较大。

我曾遇到过部署在虚拟机的实例有12000多张表，当时使用的xtrabackup，备份脚本中没加rsync参数，结果锁了十几分钟，而MEB就没有这样的问题。

MySQL 8.0支持轻量级备份锁 LOCK INSTANCE FOR BACKUP，数据字典也重构了由InnoDB存储。若不创建非InnoDB表，MEB默认使用备份锁获取binlog日志一致性位置，并阻止DDL操作，但不影响DML操作。

只有InnoDB表，仅上备份锁

请点击输入图片描述

若有非InnoDB表，上全局锁

请点击输入图片描述

特性2：Redo Log Archiving

MEB能做到在线热备，备份时不影响数据库读写，这是利用了InnoDB事务日志，在备份期间持续监视redo log的变化，读取增量变化，写入到ibbackup_logfile，也就不需要上锁来保障备份一致性。(对非InnoDB的文件需要上读锁拷贝)

如果备份期间数据库写入负载特别大，而写入ibbackup_logfile速度较慢，redo log size也不大，很可能会出现ibbackup_logfile的写入速度跟不上redo log记录生成速度，redo log空间不够时需要覆写日志文件，那么来不及写入ibbackup_logfile的记录会丢失，导致备份失败。

MEB 4.1对此做了优化，将redo log处理线程拆分成多线程分工合作，提高处理redo log的效率，降低了redo log覆写造成备份失败的概率，但redo log新增速度和ibbackup_logfile写入速度悬殊太大，问题依然会发生。

MySQL 8.0.17支持了redo log archiving彻底解决了此问题，备份前设置innodb_redo_log_archive_dirs，指定redo log归档目录。MEB备份时自动开启日志归档，当checkpoint时会将旧记录归档到此目录，后续从归档文件中读取redo日志记录，避免了覆写可能导致的redo记录丢失。

请点击输入图片描述

注意：innodb_redo_log_archive_dirs不能在数据目录下，目录权限要求是700

特性3：Page Tracking

Page Tracking是为优化增量备份效率，减少不必要的数据页扫描。

增量备份当前有3种扫描模式：

page-track：利用LSN精确跟踪上次备份之后被修改页面，仅复制这些页面，效率最快。

optimistic：扫描上次备份之后被修改的InnoDB数据文件中，找出并拷贝修改的页面。依赖系统时间，使用存在限制。

full-scan：扫描所有InnoDB数据文件，找出并拷贝自上次备份之后修改的页面，效率最慢

1、利用page-track增量备份，需先安装备份组件

2、在全备前开启page-track

3、全备之后，做增量备份时指定若满足page tracking条件，默认会使用page-track模式，否则会使用full-scan模式，也可以指定--incremental=page-track。

incremental-base有3种选择

last_backup：基于前一次备份做增备，前一次备份可能是增备，也可能是全备。这种方式全备之间可能会有多个增备，每次增量可能比较小，但恢复时需要逐个合并。

last_full_backup：基于前一次全备做增备。这种方式增备会越往后体积可能越大，但恢复时只需要合并最后一次增量备份。

dir：基于前一次的备份目录，前一次备份可能是增备，也可能是全备。

测试对比full-scan和page-track，在变更页小于总体50%的情况下，备份效率至少能有1倍的速度提升。

page-track模式磁盘读写均衡，说明读写的都是修改页面。

请点击输入图片描述

full-scan模式磁盘读写差别很大，说明读了很多未修改的页面。

请点击输入图片描述

如何备份整个mysql数据库

1、首先打开mysql数据库软件进入软件主界面。

2、然后再左侧树里打开自己的的数据库。

3、然后需要点击需要备份的数据库名。

4、如图所示为打开数据库后界面。

5、然后需要点击转储sql文件选项。

6、然后需要打开选择存储文件路径并选择保存。

7、点击保存即可在路径备份好格式为sql的数据库文件。

mysql 数据库备份和如何备份整个mysql数据库的问题分享结束啦，以上的文章解决了您的问题吗？欢迎您下次再来哦！