MySQL数据备份与恢复(十三)

Published on in MySQL with 0 views and 0 comments

数据备份与恢复

  • 保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误,可以使用备份的数据进行恢复,这样就尽可能地降低了意外原因导致的损失。

数据备份

使用mysqldump命令备份

  • mysqldump命令执行时,可以将数据库备份成一个文本文件,该文件实际上包含了多个CREATEINSERT语句,使用这些语句可以重新创建表和插入数据。

  • mysqldump备份单个数据库语句,基本语法格式如下:

    mysqldump -u user -h host -p dbname [tbname1 [tbname2...] ] > filename.sql
    

    tbnamedbname数据库中需要备份的数据表,可以指定多个需要备份的表,多个表名之间用空格隔开;

    右箭头符号 “>” 告诉mysqldump将备份的数据表的定义和数据写入备份文件;

    filename.sql为备份文件的名称。

  • mysqldump备份多个数据库语句,基本语法格式如下:

    mysqldump -u user -h host -p --databases [dbnane1 [dbname2...]] > filename.sql
    

    使用 --databases 参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开。

    另外,使用 --all-databases 参数可以备份系统中所有的数据库,语句如下:

    mysqldump -u user -h host -p --all-databases > filename.sql
    
  • 备份文件包含的信息:

    • 文件开头首先标明备份文件使用的mysqldump工具的版本号;

    • SET语句:这些语句将一些系统变量赋给用户定义变量,以确保被恢复的数据库的系统变量和原来备份时的变量相同;

    • 以 “--” 字符开头的语句为行为注释语句;

    • 以 “/*!” 开头、“*/” 结尾的语句为可执行的MySQL注释,这些语句可以被MySQL执行,但在其他数据库管理系统将被作为注释忽略,这可以提高数据库的可移植性。

    • 以数字开头的语句,这些数字代表了MySQL版本号,该数字表明这些语句只有在指定的MySQL版本或者比该版本高的情况下才能执行。

      例如:

      /*!40101 SET CHARACTER_SET_CLIENT = @OLD_CHARACTER_SET_CLIENT */
      

      表明这个语句只有在MySQL版本号为4.01.01或者更高的条件下才可以执行。

  • mysqldump提供许多选项,允许帮助命令 mysqldump --help,可以获得特定版本的完整选项列表。

  • 提示:

    • 如果在服务器上进行备份,并且表均为MyISAM表,应考虑使用mysqlhotcopy,因为可以更快地进行备份和恢复。

    • 如果使用最新版本的mysqldump程序备份数据,并用于恢复到比较旧版本的MySQL服务器中,则不要使用 --opt-e 选项。

      --opt:该选项是速记,等同于指定 --add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables-quick--set-charset。它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启,但可以用--skip-opt禁用。

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

直接复制整个数据库目录

  • 因为MySQL表保存为文件格式,所以可以直接复制MySQL数据库的存储目录及文件进行备份。
  • 备份前需要对相关执行LOCK TABLES操作,这样当复制数据库目录的中文件时,允许其他客户继续查询表;然后对表执行FLUSH TABLES操作,确保开始备份前将所有激活的索引页写入硬盘,或者也可以停止MySQL服务再进行备份操作。
  • 提示:在MySQL版本中,第一个数字表示主版本号,主版本号相同的MySQL数据库文件格式相同。

使用mysqlhotcopy工具快速备份

  • mysqlhotcopy是一个Perl脚本。它使用LOCK TABLESFLUSH TABLEScpscp 来快速备份数据库。它是备份数据库或单个表的最快途径,但它只能运行在数据库目录所在的机器上,并且只能备份MyISAM类型的表。mysqlhotcopyUNIX系统中运行。

  • mysqlhotcopy命令语法格式如下:

    mysqlhotcopy db_name_1,...db_name_n /path/to/new_directory
    

    /path/to/new_directory 指定备份文件目录。

  • 执行mysqlhotcopy,必须可以访问备份的表文件,具有那些表的SELECT权限、RELOAD权限(以便能够执行 FLUSH TABLES)和 LOCK TABLES 权限。

  • 提示:mysqlhotcopy只是将表所在的目录复制到另一个位置,只能用于备份MyISAMARCHIVE表。备份InnoDB类型的数据表时会出现错误信息。由于它复制本地格式的文件,因此也不能移植到其他硬件或操作系统下。

数据恢复

使用MySQL命令恢复

  • 备份的sql文件中包含CREATE、INSERT语句。MySQL命令可以直接执行文件中的这些语句。其语法如下:

    mysql -u user -p [dbname] < filename.sql
    

    执行该语句前,必须先在MySQL服务器中创建指定数据库。

  • 在已经登录MySQL服务器下,使用source命令导入sql文件。source语句语法如下:

    source filename
    

    执行该语句前,必须使用use语句选择数据库。且filename中路径分隔符用反斜杠 “\” 。

直接复制到数据库目录

  • 通过这种方式恢复时,必须保存备份数据的数据库和待恢复的数据库服务器的主版本号相同。而且这种方式只对MyISAM引擎的表有效,对于InnoDB引擎的表不可用。
  • 执行恢复操作以前关闭MySQL服务,将备份的文件或目录覆盖MySQL的data目录,启动MySQL服务。对于Linux/Unix操作系统来说,复制完文件需要将文件的用户和组更改为MySQL运行的用户和组,通常用户是MySQL,组也是MySQL。

mysqlhotcopy快速恢复

  • mysqlhotcopy备份后的文件也可以用来恢复数据库,在MySQL服务器停止运行时,将备份的数据库文件复制到MySQL存放数据的位置(MySQL的data文件夹),重新启动MySQL服务器即可。执行该操作前需要指定数据库文件的所有者,输入语句如下:

    chown -R mysql.mysql /var/lib/mysql/dbname
    

    mysqlhotcopy复制的备份恢复到数据库,输入语句如下:

    cp -R 备份文件所在路径 usr/local/mysql/data
    

    执行完该语句,重启服务器,MySQL将恢复到备份状态。

  • 提示:如果需要恢复的数据库已经存在,则需使用DROP语句输出已经存在的数据库之后恢复才能成功。另外,MySQL不同版本之间必须兼容,恢复之后的数据才可以使用。

数据库迁移

  • 数据库迁移就是把数据从一个系统移动到另一个系统上。数据迁移有以下原因:
    1. 需要按照新的数据库服务器。
    2. MySQL版本更新
    3. 数据库管理系统的变更(如从Microsoft SQL Server迁移到MySQL)

相同版本的MySQL数据库之间的迁移

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

  • A主机上的MySQL数据库全部迁移到B主机上的执行命令如下:

    mysqldump -h A -uroot -ppassword dbname |
    mysql -h B -uroot -ppassword
    

    mysqldump导入的数据直接通过管道符 “|” 传给MySQL命令导入到主机B数据库中;

    dbname为需要迁移的数据库名称,如果要迁移全部数据库,可使用参数 --all-databases

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

  • 由于数据库升级等原因,需要将较旧版本MySQL数据库中的数据迁移到较新版本的数据库中。MySQL服务器升级时,需要先停止服务,然后卸载旧版本,并安装新版的MySQL。
  • 如果想保留旧版本的用户控制访问信息,则需要备份MySQL中的MySQL数据库,在新版本MySQL安装完成之后,重新读入MySQL备份文件中的信息。
  • 旧版本与新版本的MySQL可能使用不同的默认字符集。如果数据库中有中文数据时,迁移过程中需要对默认字符集进行修改,不然可能无法正常显示结果。

不同数据库之间的迁移

  • 迁移之前,需要了解不同数据库的架构,比较它们之间的差异。不同数据库中定义相同类型数据的关键字可能会不同。
  • 另外,数据库厂商并没有完全按照SQL标准来设计数据库系统,导致不同的数据库系统的SQL语句有所差别,因此在迁移时必须对这些语句进行语句映射处理。
  • 数据库迁移可以使用一些工具。例如在Windows系统下,可以使用MyODBC实现MySQLSQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据库间进行数据迁移。

表的导出和导入

  • MySQL数据库中的数据可以导出成sql文本文件、xml文件或者html文件。同样这些导出文件也可以导入到MySQL数据库中。

使用SELECT...INTO OUTFILE导出文本文件

  • MySQL数据库导出数据时,允许使用包含导出定义的SELECT语句进行数据的导出操作。
  • 该文件被创建到服务器主机上,因此必须拥有文件写入权限(FILE权限)才能使用此语法。
  • SELECT... INTO OUTFILE ‘filename’ ”形式的SELECT语句可以把被选择的行写入一个文件中,filename不能是一个已经存在的文件。
  • SELECT...INTO OUTFILE语句基本格式如下:
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]

--OPTIONS 选项
		FIELDS TERMINATED BY 'value'
	FIELDS [OPTIONALLY] ENCLOSED BY 'value'
	FIELDS ESCAPED BY 'value'
	LINES STARTING BY 'value'
	LINES TERMINATED BY 'value'
  • [OPTIONS] 为可选参数选项。OPTIONS部分的语法包括FIELDSLINES子句,可能的取值有:

    • FIELDS TERMINATED BY 'value':设置字段之间的分隔符,可以为单个或多个字符,默认情况下为制表符 ’\t‘ (tab)。
    • FIELDS [OPTIONALLY] ENCLOSED BY 'value':设置字段的包围字符,只能为单个字符,如果使用了OPTIONALLY则只有CHARVARCHAR等字符数据字段被包括。
    • FIELDS ESCAPED BY 'value':设置如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认值为 ’\‘ 。
    • LINES STARTING BY 'value':设置每行数据开头的字符,可以为单个或多个字符,默认情况下不使用任何字符。
    • LINES TERMINATED BY 'value':设置每行数据结尾的字符,可以为单个或多个字符,默认值为 ’\n‘。
  • FIELDSLINES两个子句都是自选的,但是如果两个都被指定了,FIELDS必须位于LINES前面

  • SELECT...INTO OUTFILE 语句可以非常快速地把一个表转储到服务器上。如果想要在服务器主机之外的部分客户主机上创建结果文件,不能使用SELECT...INTO OUTFILE。在这种情况下,应该在客户主机上使用比如 “ mysqldump -T ” 的命令来生成文件。

  • 默认情况下,如果遇到NULL值,将会返回 “\N”。

使用mysqldump命令导出文本文件

  • mysqldump工具不仅可以将数据导出为包含CREATEINSERTsql文件,也可以导出为纯文本文件

  • mysqldump导出文本文件的基本语法格式如下:

    mysqldump -T path -u username -p dbname [tables] [OPTIONS] --文件路径需要加双引号
    
    --OPTIONS 选项 (value字符串不需要加引号)
    --fields-terminated-by=value
    --fields-enclosed-by=value
    --fields-optionally-by=value
    --fields-escaped-by=value
    --lines-terminated-by=value
    

    只有指定了 -T 参数才可以同时导出纯文本文件;

    path表示导出数据的目录;

    tables为指定要导出的表名称,如果不指定,将导出数据库中所有表;

    [OPTIONS]为可选参数选项,这些选项需要结合 -T 选项使用。

    使用OPTIONS常用的取值有:

    • --fields-terminated-by=value:设置字段之间的分隔符,可以为单个或多个字符。默认情况下为制表符 “\t”。
    • --fields-enclosed-by=value:设置字段的包围字符。
    • --fields-optionally-by=value:设置字段的包围字符,只能为单个字符,只能包括CHARVARCHAR等字符数据字段。
    • --fields-escaped-by=value:控制如何写入或读取特殊字符,只能为单个字符,即设置转义字符,默认值为反斜线 “\”。
    • --lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为 “\n”。

使用LOAD DATA INFILE方式导入文本文件

  • MySQL提供了一些导入数据的工具,这些工具有LOAD DATA语句和source命令。LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表。

  • LOAD DATA语句的基本格式如下:

    LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
    
    	--OPTIONS 选项
    		FIELDS TERMINATED BY 'value'
    	FIELDS [OPTIONALLY] ENCLOSED BY 'value'
    	FIELDS ESCAPED BY 'value'
    	LINES STARTING BY 'value'
    	LINES TERMINATED BY 'value'
    

    IGNORE number LINES选项表示忽略文件开始处的行数number表示忽略的行数。执行LOAD DATA语句需要FILE权限。

使用mysqlimport命令导入文本文件

  • 使用mysqlimport可以导入文本文件,并且不需要登录MySQL客户端。

  • mysqlimport命令的基本格式如下:

    mysqlimport -u username -p dbname filename.txt [OPTIONS] --文件路径不用加引号
    
    --OPTIONS 选项 (value字符串不需要加引号)
    --fields-terminated-by=value
    --fields-enclosed-by=value
    --fields-optionally-by=value
    --fields-escaped-by=value
    --lines-terminated-by=value
    --ignore-lines=n
    

    注意:mysqlimport命令不指定导入数据库的表名称,数据表的名称由导入文件名称确定,即文件名作为表名,导入数据之前该表必须存在。若只指定文件名,则该文件只能存放在指定数据库文件夹里。

注意事项

  • 使用mysqldumpLOAD DATA语句时需要注意文件所在文件夹的读写权限,而mysqlimport则不需要。

  • 如果在导出txt文件时指定了一些特殊分隔字符,则恢复语句也要指定这些字符,已确保恢复后数据的完整性和正确性。

  • 备份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它,则复制无效。备份文件时,最好关闭服务器。然后重新启动服务器。为了保证数据的一致性,需要在备份文件前执行以下SQL语句:

    FLUSH TABLES WITH READ LOCK;
    

    也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证复制过程中不会有新的数据写入。

  • mysqldump如果只指定数据库的名称,则该语句只备份了数据库下所有的表。所以在恢复时,如果指定数据库不存在,则需要创建后再进行恢复。


标题:MySQL数据备份与恢复(十三)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/18/1731943574555.html