侧边栏壁纸
博主头像
coydone博主等级

记录学习，分享生活的个人站点

  • 累计撰写 306 篇文章
  • 累计创建 51 个标签
  • 累计收到 0 条评论
标签搜索

目 录CONTENT

文章目录
数据库

数据库备份与恢复

coydone
coydone
2022-01-19 / 0 评论 / 0 点赞 / 397 阅读 / 8,541 字 / 正在检测是否收录...
温馨提示：
本文最后更新于 2022-04-07，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

物理备份与逻辑备份

在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用xtrabackup工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

mysqldump实现逻辑备份

# 基本语法
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

基本操作

# 备份一个数据库
# 使用root用户备份testdb数据库
mysqldump -uroot -p testdb>testdb.sql #备份文件存储在当前目录下
mysqldump -uroot -p testdb> /var/lib/mysql/testdb.sql

# 备份全部数据库
# 使用 --all-databases 或 -A 参数
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql

# 备份部分数据库
# 使用 --databases 或 -B 参数，该参数后面跟数据库名称，多个数据库间用空格隔开。
#如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定则不存在
mysqldump -uroot -p --databases testdb testdb2 > two_database.sql
mysqldump -uroot -p -B testdb testdb2 > two_database.sql

# 备份部分表
# 备份testdb数据库下的book表
mysqldump -uroot -p testdb book > book.sql
#备份多张表
mysqldump -uroot -p testdb book account > 2_tables_bak.sql

# 备份单表的部分数据
# --where，where后面附带需要满足的条件
# 备份student表中id小于10的数据
mysqldump -uroot -p testdb student --where="id < 10 " > student_part_id10_low_bak.sql

# 排除某些表的备份
mysqldump -uroot -p testdb --ignore-table=testdb.student > no_stu_bak.sql
# 通过如下指定判定文件中没有student表
grep "student" no_stu_bak.sql

# 只备份结构
# 使用 --no-data 简写为-d选项
mysqldump -uroot -p testdb --no-data > testdb_no_data_bak.sql

# 只备份数据
mysqldump -uroot -p testdb --no-create-info > testdb_no_create_info_bak.sql
#可以使用 --no-create-info 简写为-t选项

# 备份中包含存储过程、函数、事件
#mysqldump备份默认是不包含存储过程，自定义函数及事件的。
#使用 --routines 或 -R 选项来备份存储过程及函数
#使用 --events 或 -E 参数来备份事件。
# 查看当前库有哪些存储过程或者函数
SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA FROM
information_schema.Routines WHERE ROUTINE_SCHEMA="testdb";
# 备份testdb库的数据，函数以及存储过程
mysqldump -uroot -p -R -E --databases testdb > fun_testdb_bak.sql

mysqldump常用选项

mysqldump其他常用选项如下：

–add-drop-database：在每个CREATE DATABASE语句前添加DROP DATABASE语句。

–add-drop-tables：在每个CREATE TABLE语句前添加DROP TABLE语句。

–add-locking：用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

–all-database, -A：转储所有数据库中的所有表。与使用–database选项相同，在命令行中命名所有数据库。

–comment[=0|1]：如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。–skipcomments与–comments=0的结果相同。默认值为1，即包括额外信息。

–compact：产生少量输出。该选项禁用注释并启用–skip-add-drop-tables、–no-set-names、–skipdisable-keys和–skip-add-locking选项。

–compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、
MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者
no_field_options。

–complete_insert, -c：使用包括列名的完整的INSERT语句。

–debug[=debug_options], -#[debug_options]：写调试日志。

–delete，-D：导入文本文件前清空表。

–default-character-set=charset：使用charsets默认字符集。如果没有指定，就使用utf8。

–delete–master-logs：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。

–extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可
以加速插入。

–flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。

–force，-f：在表转储过程中，即使出现SQL错误也继续。

–lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关
闭–single-transaction和–lock-tables。

–lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例
如InnoDB和BDB），–single-transaction是一个更好的选项，因为它根本不需要锁定表。

–no-create-db，-n：该选项禁用CREATE DATABASE /!32312 IF NOT EXIST/db_name语句，如果给出-
-database或–all-database选项，就包含到输出中。

–no-create-info，-t：只导出数据，而不添加CREATE TABLE语句。

–no-data，-d：不写表的任何行信息，只转储表的结构。

–opt：该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，
但可以用–skip-opt禁用。

–password[=password]，-p[password]：当连接服务器时使用的密码。

-port=port_num，-P port_num：用于连接的TCP/IP端口号。

–protocol={TCP|SOCKET|PIPE|MEMORY}：使用的连接协议。

–replace，-r –replace和–ignore：控制替换或复制唯一键值已有记录的输入记录的处理。如果指定–
replace，新行替换有相同的唯一键值的已有行；如果指定–ignore，复制已有的唯一键值的输入行被跳过。如果不
指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。

–silent，-s：沉默模式。只有出现错误时才输出。

–socket=path，-S path：当连接localhost时使用的套接字文件（为默认主机）。

–user=user_name，-u user_name：当连接服务器时MySQL使用的用户名。

–verbose，-v：冗长模式，打印出程序操作的详细信息。

–xml，-X：产生XML输出。

运行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。

mysql命令恢复数据

# 基本语法
mysql –u root –p [dbname] < backup.sql

# 单库备份中恢复单库
# 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < testdb.sql
# 否则需要指定数据库名称
mysql -uroot -p testdb1< testdb.sql

# 全量备份恢复
mysql –u root –p < all.sql
mysql -uroot -pxxxxxx < all.sql

# 从全量备份中恢复单库
sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_database.sql
 > testdb.sql
#分离完成后我们再导入testdb即可恢复单个库

# 从单库备份中恢复单表
cat testdb.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >
class_structure.sql
cat testdb.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

use testdb;
mysql> source class_structure.sql;
mysql> source class_data.sql;

物理备份：直接复制整个数据库

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同：

  • 在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQLServer 8.0\Data ”或者其他用户自定义目录；

  • 在Linux平台下，数据库目录位置通常为/var/lib/mysql/；

  • 在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”。

但为了保证备份的一致性。需要保证：

  • 方式1：备份前，将服务器停止。

  • 方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表 ，而且这种方法 对InnoDB存储引擎 的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。

注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。

此外，还可以考虑使用相关工具实现备份。比如， MySQLhotcopy 工具。MySQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之前。

表的导出与导入

表的导出

mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

# 查询secure_file_priv值
SHOW GLOBAL VARIABLES LIKE '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /var/lib/mysql-files/ |
 #导出目录
+--------------------------+-----------------------+

# 1、使用SELECT…INTO OUTFILE导出文本文件
use testdb;
select * from account;
select * from account;
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

# 2、使用mysqldump命令导出文本文件
# 在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件
mysqldump -uroot -p -T "/var/lib/mysql-files/" testdb account

# 3、使用mysql命令导出文本文件
mysql -uroot -p --execute="SELECT * FROM account;" testdb > "/var/lib/mysqlfiles/account.txt"
# 将testdb数据库account表中的记录导出到xml文件，使用--xml参数
mysql -uroot -p --xml --execute="SELECT * FROM account;" testdb >"/var/lib/mysqlfiles/account_3.xml"
# 如果要将表数据导出到html文件中，可以使用--html选项。然后可以使用浏览器打开

表的导入

# 1、使用LOAD DATA INFILE方式导入文本文件
SELECT * FROM testdb.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';
DELETE FROM testdb.account;
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE testdb.account;

# 2、使用mysqlimport方式导入文本文件
# 导出文件account.txt，字段之间使用逗号"，"间隔，字段值用双引号括起来
SELECT * FROM testdb.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS
TERMINATED BY ',' ENCLOSED BY '\"';
# 删除account表中的数据
DELETE FROM testdb.account;
# 使用mysqlimport命令将account.txt文件内容导入到数据库testdb的account表中
mysqlimport -uroot -p testdb '/var/lib/mysql-files/account.txt' --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

数据库迁移

概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL数据迁移方案大致可以分为 物理迁移和逻辑迁移两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

迁移方案

1、物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。

物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

2、逻辑迁移

逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。

迁移注意点

1、相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

  • 方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。

  • 方式2： 最常见和最安全的方式是使用 mysqldump命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。

2、不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，因此需要把数据库升级到8.0版本。

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。

高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。

3、不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构， 比较它们之间的差异 。不同数据库中定义相同类型的数据的 关键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQLServer数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容。

不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用 MyODBC 实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后，手动更改 sql文件中的CREATE语句。

小结

0
MySQL
  1. qrcode alipay
  2. qrcode weixin
版权归属： coydone
本文链接： https://www.coydone.com/archives/156
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区