MySQL数据备份、还原、数据库迁移以及表的导出和导入

目录
  • 前言
  • 一、数据备份
    • 1、使用mysqldump命令备份
    • 2、直接复制整个数据库目录
    • 3、使用mysqlhotcopy工具快速备份
  • 二、数据还原
    • 1、使用mysql命令还原
    • 2、直接复制到数据库目录
  • 三、数据库迁移
    • 1、相同版本的MySQL数据库之间的迁移
    • 2、不同版本的MySQL数据库之间的迁移
  • 四、表的导出和导入
    • 1、用select…into outfile导出文本文件
    • 2、用mysqldump命令导出文本文件
    • 3、用mysql命令导出文本文件
    • 4、用load data infile方式导入文本文件
    • 5、用mysqlimport命令导入文本文件
  • 五、总结

前言

为了保证数据的安全,需要定期对数据进行备份。备份的方式有很多种,效果也不一样。如果数据库中的数据出现了错误,就需要使用备份好的数据进行数据还原。这样可以将损失降至最低。而且,可能还会涉及到数据库之间的数据导入与导出。

一、数据备份

备份数据是数据库管理中最常用的操作。为了保证数据库中数据的去安全,数据库管理员需要定期的进行数据库备份。一旦数据库遭到破坏,即通过备份的文件来还原数据库。因此,数据备份是很重要的工作。

1、使用mysqldump命令备份

mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个create语句。然后,将表中的所有记录转换成一条insert语句。这些create语句和insert语句都是还原时使用的。还原数据时就可以使用其中的create语句来创建表。使用其中的insert语句来还原数据。

1、备份一个数据库

使用mysqldump命令备份一个数据库的基本语法如下:

mysqldump -u username -p dbname table1 table2 ... > backupname.sql

其中,dbname参数表示数据库的名称;table1和table2参数表示表的名称,没有该参数时将备份整个数据;backupname.sql参数表示备份文件的名称,文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为sql的文件。

2、备份多个数据库

mysqldump命令备份多个数据库的语法如下:

mysqldump -u username -p --databases dbname1 dbname2 ... > backupname.sql

这里要加上“–databases”这个选项,然后后面跟多个数据库的名称。

3、备份所有数据库

mysqldump命令备份所有数据库的语法如下:

mysqldump -u username -p --all-databases > backupname.sql

使用“–all-databases”选项就可以备份所有的数据库了。

2、直接复制整个数据库目录

MySQL有一种最简单的备份办法,就是将MySQL中的数据库文件直接复制出来。这种方法最简单,速度也最快。使用这种方法时,最好将服务器先停止。这样,可以保证在复制期间数据库中的数据不会发生变化。如果在复制数据库的过程中还有数据写入,就会造成数据不一致。

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

3、使用mysqlhotcopy工具快速备份

如果备份时不能停止MySQL服务器,可以采用mysqlhotcopy工具。mysqlhotcopy工具的备份方式比mysqldump命令快。

mysqlhotcopy工具是一个Perl脚本,主要在Linux操作系统下使用。mysqlhotcopy工具使用lock tables、flush tables和cp来进行快速备份。其工作原理是,先将需要备份的数据库加上一个读操作锁,然后,用flush tables将内存中的数据写回到硬盘上的数据库中,最后,把需要备份的数据库文件复制到目标目录。使用mysqlhotcopy的命令如下:

mysqlhotcopy [option] dbname1 dbname2 ... backupDir/

其中,dbname1等表示需要备份的数据库的名称;backupDir参数指出备份到哪个文件夹下。这个命令的含义就是将dbname1、dbname2等数据库备份到backDir目录下。

二、数据还原

管理员的非法操作和计算机的故障都会破坏数据库文件。当数据库遭到这些意外时,可以通过备份文件将数据库还原到备份的状态。这样可以将损失降低到最小。

1、使用mysql命令还原

管理员通常使用mysqldump命令将数据库中的数据备份成一个文本文件。通常这个文件的后缀名是.sql。需要还原时,可以使用mysql命令来还原备份的数据。

备份文件中通常包含create语句和insert语句。mysql命令可以执行备份文件中的create语句和insert语句。通过create语句来创建数据库和表。通过insert语句来插入备份的数据。mysql命令的基本语法如下:

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

其中,dbname参数表示数据库名称。该参数是可选参数,可以指定数据库名,也可以不指定。指定数据库名时,表示还原该数据库下的表。不指定数据库名时,表示还原特定的一个数据库。而备份文件中创建数据库的语句。

下面使用root用户备份所有的数据库。命令如下:

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

2、直接复制到数据库目录

之前介绍过一种直接复制数据的备份方法。通过这种方式备份的数据,可以直接复制到MySQL的数据库目录下。通过这种方式还原时,必须保证两个MySQL数据库的主版本号是相同的。因为只有MySQL数据库主版本号相同时,才能保证这两个MySQL数据库的文件类型是相同的。而且,这种方式对MyISAM类型的表比较有效。对于InnoDB类型的表则不可用。因为InnoDB表的表空间不能直接复制。

三、数据库迁移

数据库迁移就是指将数据库从一个系统移动到另一个系统上。数据库迁移的原因是多种多样的。可能是因为升级了计算机,或者是部署开发的管理系统,或者升级了MySQL数据库。甚至是换用其他的数据库。根据上述情况,可以将数据库迁移大致分为3类。这3类分别是在相同版本的MySQL数据库之间迁移、迁移到其他版本的MySQL数据库中和迁移到其他类型的数据库中。

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

只有数据库表都是MyISAM类型的才能使用这种方式。

最常用和最安全的方式是使用mysqldump命令来备份数据库。然后使用mysql命令将备份文件还原到新的MySQL数据库中。这里可以将备份和迁移同时进行。假设从一个名为host1的机器中备份出所有的数据库,然后,将这些数据库迁移到名为host2的机器上。命令如下:

mysqldump -h name1 -u root --password1 --all-databases |
mysql -h host2 -u root --password=password2

其中,“|”符号表示管道,其作用是将mysqldump备份的文件送给mysql命令;“–password=password1”是name1主机上root用户的密码。同理,password2是name2主机上的root用户的密码。通过这种方式可以直接实现迁移。

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

高版本的MySQL数据库通常都会兼容低版本,因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。对于MySIAM类型的表可以直接复制,也可以使用mysqlhotcopy工具。但是InnoDB类型的表不可以使用这两种方法。最常用的办法是使用mysqldump命令来进行备份,然后,通过mysql命令将备份文件还原到目标MySQL数据库中。但是,高版本的MySQL数据库很难迁移到低版本的MySQL数据库。因为高版本的MySQL数据库可能有一些新的特性,这些特性是低版本MySQL数据库所不具有的。数据库迁移时要特别小心,最好使用mysqldump命令来进行备份,避免迁移时造成数据丢失。

四、表的导出和导入

MySQL数据库中的表可以导出成文本文件、XML文件或者HTML文件。相应的文本文件也可以导入MySQL数据库中。在数据库的日常维护中,经常需要进行表的导出和导入的操作。

1、用select…into outfile导出文本文件

MySQL中,可以使用select…into outfile语句将表的内容导出成一个文本文件。其基本语法形式如下:

select [列名] from table [where 语句] into outfile '目标文件'[option];

该语句分为两个部分。前半部分是一个普遍的select语句,通过这个select语句来查询所需要的数据;后半部分是导出数据的。其中,“目标文件”参数指出将查询的记录导出到哪个文件;“OPTION”参数是可以有常用的5个选项。

2、用mysqldump命令导出文本文件

mysqldump命令可以备份数据库中的数据。但是,备份时是在备份文件中保存了create语句和insert语句。不仅如此,mysqldump命令还可以导出文本文件。其基本的语法形式如下:

mysqldump -u root -pPassword -T 目标目录 dbname table [option];

其中,Password参数表示root用户的密码,密码紧挨着-p选项;目标目录参数是指导出的文本文件的路径;dbname参数表示数据库的名称;table参数表示表的名称;

3、用mysql命令导出文本文件

mysql命令可以用来登录MySQL服务器,也可以用来还原备份文件。同时,mysql命令也可以导出文本文件。其基本语法形式如下:

mysql -u root -pPassword -e "select 语句" dbname > C:/name.txt;

其中,Password表示root用户的密码;使用-e选项就可以执行SQL语句;“selec语句”用来查询记录。C:/name.txt表示导出文件的路径。

4、用load data infile方式导入文本文件

MySQL中,可以使用load data infile命令将文本文件导入到MySQL数据库中。其基本语法形式如下:

load data [local] infile file into table table [option];

其中,“local”是在本地计算机中查找文本文件时使用的;“file”参数指定了文本文件的路径和名称;“table”参数指标的名称;

5、用mysqlimport命令导入文本文件

MySQL中,可以使用mysqlimport命令将文本文件导入到MySQL数据库中。其基本语法形式如下:

mysqlimport -u root -pPassword [--LOCAL] dbname file [option]

其中,“Password”参数是root用户的密码,必须与-p选项紧挨着;“local”是在本地计算机中查找文本文件时使用的;“dbname”参数表示数据库的名称;“file”参数指定了文本文件的路径和名称;

五、总结

这里的相关内容还没有整理完毕,文章后面持续更新,建议收藏。

到此这篇关于MySQL数据备份、还原、数据库迁移以及表的导出和导入的文章就介绍到这了,更多相关MySQL数据备份还原内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL 备份还原数据库批处理

    1.备份数据库(单个表备份) bk_table.bat 复制代码 代码如下: mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database1 table1>table1.sql mysqldump -h127.0.0.1 -P3306 -uroot -proot --default-character-set=gbk -t database2 table2>table2.sql mysqld

  • mysql 数据库备份和还原方法集锦 推荐

    作/译者:叶金荣(Email: ),来源:http://imysql.cn/,转载请注明作/译者和出处,并且不能用于商业用途,违者必究. 日期:2006/10/01 本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22. 目前 MySQL 支持的免费备份工具有:mysqldump.mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INT

  • MySQL使用命令备份和还原数据库

    数据库在使用当中都会有数据库备份工作,当数据库发生严重错误无法启动,或者数据丢失时可以及时有效地恢复数据.文章简单介绍如何备份和还原MySQL数据库. 备份数据库 使用mysqldump命令备份数据库 复制代码 代码如下: # 如果要将game数据库进行备份: mysqldump -u root -p game > game_backup.sql # 如果希望备份所有的数据库: mysqldump -u root -p --all-databases > all_backup.sql 还原数据

  • MySQL数据库如何导入导出(备份还原)

    本文适用范围:全面阐述MySQL数据库的各种操作,分虚拟主机和服务器两种情况. 虚拟主机 1.通过PHPMyAdmin的导入导出功能,这个软件一般只支持几兆数据的导出,太大的数据可能会超时. 2.通过程序自带的数据库备份还原功能来操作,一些常见的PHP程序如DZ论坛等,后台都有数据库还原和备份的功能,方便我们转移空间数据. 3.如果您的数据库在朝暮数据购买,我们的管理面板支持一键备份和还原.点击备份按钮后,您可以到数据库对应的空间上通过FTP方式下载. 服务器或VPS 首先我们远程到服务器上(W

  • mysql数据库太大了如何备份与还原

    命令:mysqlhotcopy 这个命令会在拷贝文件之前会把表锁住,并把数据同步到数据文件中,以避免拷贝到不完整的数据文件,是最安全快捷的备份方法. 命令的使用方法是: mysqlhotcopy -u root -p<rootpass> db1 db2 - dbn <output_dir> 如果需要备份全部数据库,可以加上–regexp=".*"参数. Mysqlhotcopy命令可自动完成数据锁定工作,备份时不用关闭服务器. 它还可以刷新日志,使备份文件和日志

  • MySQL数据库备份和还原的常用命令小结

    备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库. mysqldump ---add-drop-table -uusername -ppassword databasename > backupfile.sql 直接将MySQL数据库压缩备份 m

  • MySQL数据备份、还原、数据库迁移以及表的导出和导入

    目录 前言 一.数据备份 1.使用mysqldump命令备份 2.直接复制整个数据库目录 3.使用mysqlhotcopy工具快速备份 二.数据还原 1.使用mysql命令还原 2.直接复制到数据库目录 三.数据库迁移 1.相同版本的MySQL数据库之间的迁移 2.不同版本的MySQL数据库之间的迁移 四.表的导出和导入 1.用select…into outfile导出文本文件 2.用mysqldump命令导出文本文件 3.用mysql命令导出文本文件 4.用load data infile方式

  • MySQL 数据备份与还原的示例代码

    一.数据备份 1.使用mysqldump命令备份 mysqldump命令将数据库中的数据备份成一个文本文件.表的结构和表中的数据将存储在生成的文本文件中. mysqldump命令的工作原理很简单.它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句.然后,将表中的所有记录转换成一条INSERT语句.然后通过这些语句,就能够创建表并插入数据. 1.备份一个数据库 mysqldump基本语法: mysqldump -u username -p dbname table1 table2

  • 详解MYSQL的备份还原(PHP实现)

    手把手教你实现MYSQL的备份还原示例代码用我比较熟悉的PHP,当然你看完并理解了其中的思路,相信你也可以快速地用你熟悉的语言自己写出来.一.新建dbBackup类,设置默认参数. 复制代码 代码如下: class dbBackup {    public $host='localhost';    //数据库地址    public $user='root';    //登录名    public $pwd='';    //密码    public $database;    //数据库名 

  • MySQL数据备份方法的选择与思考

    从事DBA的行业也有两年多了,在数据备份上无论是理论和实践上,都积累了一些经验,恰逢这两天又出现一些数据备份方面的问题,这里,我将之前遇到过的数据备份方法简单做个整理. 我们都知道,保障数据库的稳定.安全.高效运行,是DBA的工作职责所在.对于DBA来讲,要想实现数据安全,数据备份可能是至关重要的一个环节. 我曾经就遇到过一个案例,业务方误删数据引发故障,要求DBA恢复数据,结果发现当天的数据没有备份,场面一度十分尴尬,本来这个故障是业务引发的,但是由于没有数据备份,最后业务和DBA各打五十大板

  • MySQL数据备份之mysqldump的使用方法

     一.mysqldump 简介 mysqldump 是 MySQL 自带的逻辑备份工具.MySQLdump是一个数据库逻辑备份程序,可以使用对一个或者多个mysql数据库进行备份或者将数据传输到其他mysql服务器.执行mysqldump时需要账户拥有select权限才可以进行备份数据表,show view权限用于备份视图,trigger权限用于备份触发器等. mysqldump不是大数据备份的解决方案,因为mysqldump需要通过重建sql语句来实现备份功能,对于数据量比较大的数据库备份与还

  • MySQL数据备份之mysqldump的使用详解

    mysqldump常用于MySQL数据库逻辑备份. 1.各种用法说明 A. 最简单的用法: mysqldump -uroot -pPassword [database name] > [dump file] 上述命令将指定数据库备份到某dump文件(转储文件)中,比如: mysqldump -uroot -p123 test > test.dump 生成的test.dump文件中包含建表语句(生成数据库结构哦)和插入数据的insert语句. B. --opt 如果加上--opt参数则生成的du

  • 使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名

    MySQL中查询所有数据库名和表名 查询所有数据库 show databases; 查询指定数据库中所有表名 select table_name from information_schema.tables where table_schema='database_name' and table_type='base table'; 查询指定表中的所有字段名 select column_name from information_schema.columns where table_schema

  • Laravel5.5 数据库迁移:创建表与修改表示例

    数据库迁移是数据库的版本管理,要使用数据库迁移,需要在.env文件中连接好数据库(不多说).laravel本身已经存在user表和password_resets表的迁移了,因此,执行 php artisan migrate 便会在数据库中创建好user表.password_resets表和migrations表.migrations表是版本记录表. 命令执行的其实是 database\migration 下的迁移文件.迁移文件中调用的方法会替我们执行数据库操作(建表).每个文件的命名对应迁移创建

  • 史上最简单的MySQL数据备份与还原教程(上)(三十五)

    数据备份与还原第一篇分享给大家,具体内容如下 基础概念: 备份,将当前已有的数据或记录另存一份: 还原,将数据恢复到备份时的状态. 为什么要进行数据的备份与还原? 防止数据丢失: 保护数据记录. 数据备份与还原的方式有很多种,具体可以分为:数据表备份.单表数据备份.SQL备份和增量备份. 数据表备份 数据表备份,不需要通过 SQL 来备份,我们可以直接进入到数据库文件夹复制对应的表结构以及数据:在需要还原数据的时候,直接将备份(复制)的内容放回去即可. 不过想要进行数据表备份是有前提条件的,因为

  • 史上最简单的MySQL数据备份与还原教程(中)(三十六)

    数据备份与还原第二篇,具体如下 基础概念: 备份,将当前已有的数据或记录另存一份: 还原,将数据恢复到备份时的状态. 为什么要进行数据的备份与还原? 防止数据丢失:保护数据记录. 数据备份与还原的方式有很多种,具体可以分为:数据表备份.单表数据备份.SQL备份和增量备份. 单表数据备份 单表数据备份,每次只能备份一张表,而且只能备份数据,不能备份表结构. 通常的使用场景为:将表中的数据导出到文件. 备份方法:从表中选出一部分数据保存到外部的文件中, select */字段列表 + into ou

随机推荐