当前位置: 澳门新濠3559 > 数据库 > 正文

因需求改变而需要把数据还原到改变以前,load

时间:2019-10-07 08:53来源:数据库
mysql的备份可以分为冷备份和热备份两种。 粗略介绍冷备,热备,温暖,及Mysqldump,mysqlpump,xtrabackup,innobackupex 全量,增量备份 恢复MySQL服务器上面的txt格式文件(需要FILE权限,各数据

mysql的备份可以分为冷备份和热备份两种。

粗略介绍冷备,热备,温暖,及Mysqldump,mysqlpump,xtrabackup,innobackupex 全量,增量备份

恢复MySQL服务器上面的txt格式文件(需要FILE权限,各数据值之间用"制表符"分隔)

目录

冷备份:停止数据库服务进行备份

--备份的目的

 

备份的分类

热备份:不停止数据库服务进行备份

灾难恢复:意外情况下(如服务器宕机、磁盘损坏等)对损坏的数据进行恢复和还原保证数据不丢失,最小程度地丢失
需求改变:因需求改变而需要把数据还原到改变以前
测试:测试新功能是否可用

1.导入数据库服务器上的txt文件

    物理备份与逻辑备份

mysql的存储引擎为MyIsam时,只支持冷备份,可以直接复制mysql的data目录下的数据库文件。这种方式需要注意mysql版本兼容性问题,同时,为了保证一致性,必须停机或者锁表进行备份。

--备份与恢复概述

mysql>load data infile '文件的绝对路径'           >into table 表名            > fields terminated by'分隔符'            > lines terminated by'换行符';
2.导出数据库信息 到txt文件
mysql>select ……           >into outfile '文件名'          > fields terminated by'分隔符'          > **lines terminated by'换行符';

    热备份与冷备份

在恢复时,首先关闭mysql服务,将备份的数据库文件复制到mysql的data目录下,然后启动mysql服务。

根据备份的方法可以分为:

若不指定目录,默认放在所在库,对应的数据库目录下
指定目录必须是绝对路径,确保mysql用户对目标文件具有写权限
linux 一般情况下载 var/lib/mysql  中**

    完全备份与增量备份

mysql的存储引擎为InnoDB时,支持热备份,因为InnoDB引擎是事务性存储引擎,可以根据日志来进行redo和undo,即将备份的时候没有提交的事务进行回滚,已经提交了的事务进行重做。

1、Hot Backup(热备)

 

    更多内容

mysql提供了mysqldump命令用于存储引擎为InnoDB时的备份。

2、Cold Backup(冷备)

 3.备份数据库
        用mysqldump命令(注意mysql的安装路径,即此命令的路径):        
备份与导出的区别:导出的文件只是保存数据库中的数据;而备份,则是把数据库的结构,包括数据、约束、索引、视图等全部另存为一个文件。

完全备份与恢复

 

3、Warm Backup(温备)

       mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT 等。

    以SQL语句格式导入导出数据库

一、备份

Hot Backup是指在数据库运行中直接备份,对正在运行的数据库基本无影响,但只能是InnoDB存储引擎的,表这种方式在MySQL官方手册中称为Online Backup(在线备份)。

 

          以SQL语句格式导出数据

mysqldump的语法和选项可以通过命令行输入mysqldump --help查看。

Cold Backup是指在数据库停止的情况下进行备份,这种备份最为简单,可是最可靠的,一般只需要拷贝相关的数据库物理文件即可。这种方式在MySQL官方手册中称为Offline Backup(离线备份)。

       1、备份数据和表结构:
mysqldump -u 用户名 -p密码 数据库名 > 备份文件名
#/usr/local/mysql/bin/   mysqldump -u root -p abc > abc.sql
敲回车后会提示输入密码

          导入SQL语句形式的文件

-- 运行帮助命令,查看语法及完整的选项列表
mysqldeump --help

Warm Backup备份同样是在数据库运行时进行,但是会对当前数据库的操作有所影响,例如加一个全局读锁以保证备份数据的一致性,其他的存储引擎就只能使用Warm Backup。

2、只备份表结构
mysqldump -u 用户名 -p密码 -d 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/   mysqldump -uroot -p -d abc > abc.sql

    以分隔符格式导入导出数据库

澳门新濠3559 1

备份方式分为:

注:/usr/local/mysql/bin/  --->  mysql的data目录

          以分隔符格式导出数据库

这里因为安装mysql时配置了环境变量,所以可以直接输入命令,否则需要进入mysql安装目录的bin目录下。(例如:C:softMySqlmysql-5.7.22-winx64bin)

物理备份:物理备份就是备份数据文件了,比较形象点就是cp下数据文件,但真正备份的时候自然不是的cp这么简单。

 

          导入分隔符格式的文件

 

逻辑备份:逻辑备份就是把数据库的结构定义语句,数据内容的插入语句,全部存储下来。然后恢复的时候,在另一个mysql服务器执行这些语句,就可以创建另一个与之前一样的数据库了。

        3.备份整个表
mysqldump -u root 数据库名 表名字>备份文件名; 

    mysqldump技巧

mysqldump命令备份指定数据库。

逻辑备份和物理备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大,逻辑备份速度比较慢,占用空间比较小。逻辑备份的恢复成本高。

 

           复制一个数据库的内容到另一个数据库

-- 备份指定数据库(demo)
mysqldump -u root -h 127.0.0.1 -p demo >c:UsersliufaDesktop2018-07-10.sql

--热备工具

 

           复制一个数据库到另一台主机上

澳门新濠3559 2

Ibbackup(收费)

4.恢复数据库 数据文件
         1、首先建空数据库
mysql>create database abc;

           导出已经存储的程序

 

ibbackup是oracle官方提供的热备工具,可以同时备份MyISAM存储引擎表和InnoDB存储引擎表,物理备份。

2、导入数据库

           分开导出数据库表的定义和数据内容

mysqldump命令备份指定数据库中的指定表。

XtraBackup(开源免费)

   方法一:
(1)选择数据库
mysql>use abc;
(2)设置数据库编码
mysql>set names utf8;
(3)导入数据(注意sql文件的路径)
mysql>source /home/abc/abc.sql;

           使用mysqldump来测试升级数据库的兼容性

-- 备份指定数据库(demo)中的指定表(student)
mysqldump -u root -h 127.0.0.1 -p demo student >c:UsersliufaDesktopStudent_2018-07-10.sql

XtraBackup支持MySQL数据库5.0版和5.1版,同时也支持InnoDB Plugin版本新增的Barracuda页格式,物理备份。

     方法二:
mysql -u用户名 -p密码 数据库名 < 备份文件
#mysql -uabc_f -p abc < abc.sql

增量备份与时间点恢复

澳门新濠3559 3

Mysqldump(官方自带)

建议使用第二种方法导入。

     操作日志文件

 

mysqldump工具是mysql官方自带的一个非常方便的一款小工具,逻辑备份,5.7加入了Mysqlpump。

 

     使用时间点来恢复数据

mysqldump命令备份多个数据库。

--Hot Backup(热备,逻辑备份)

 

     使用事件点来恢复数据

-- 备份多个数据库(demo、sys)
mysqldump -u root -h 127.0.0.1 -p --databases demo sys >c:UsersliufaDesktop2018-07-10.sql

逻辑备份就是把数据库的结构定义语句,数据内容的插入语句,全部存储下来。然后恢复的时候,在另一个mysql服务器执行这些语句,就可以创建另一个与之前一样的数据库了,适用:这种比较适合数据量少的数据库。

注意:有命令行模式,有sql命令

 

澳门新濠3559 4

热备的优点是:

 

备份的分类

 

1、占用空间比较小

恢复(导入)命令:

物理备份与逻辑备份

物理备份是由原始的数据库文件组成,这种类型的备份适用于大型且重要的数据库,因为可以在数据库发生问题时快速恢复。

逻辑备份是由数据库的逻辑语句组成(如CREATE DATABASE,CREATE TABLE,INSERT等语句)。这种类型的备份适用于少量的数据,尤其是在你可能会改变表结构的情况下。

物理备份有以下特征:

1. 备份文件是由数据库的目录和文件组成,完全就是mysql数据目录的一个复制副本

  1. 物理备份比逻辑备份快是因为物理备份单纯地复制文件,而没有进行逻辑转换

  2. 输出更加简洁,紧凑

4. 因为备份速度和简洁性对于重要,繁忙的数据库来说非常重要,所以mysql的企业版使用物理备份

  1. 备份的文件只适用于同一种架构的计算机

6. 根据数据库引擎来决定备份的粒度但一般不能细分到备份某个表,InnoDB可以细分到一个独立的文件

  1. 除了备份数据库,还可以备份相关的文件如日志和配置文件

8. 在mysql服务器停止运行期间可以执行物理备份,但在服务器运行期间需要先锁定服务器,为了防止在备份期间写入数据

  1. 备份工具包括mysqlbackup(mysql企业版专有),文件复制命令(cp, scp, tar, rsync)或者mysqlhotcopy(只适用于MyISAM表)

逻辑备份有如下特征:

  1. 通过查询mysql服务器,获得数据库结构和内容,来完成逻辑备份

2. 逻辑备份比物理备份速度慢是因为服务器要访问并转换数据库信息为逻辑语句形式

  1. 备份后的文件比逻辑备份文件大

4. 备份粒度包括服务器级别(所有的数据库),数据库级别(某个数据库所有的表)或者某个表

  1. 备份不包括与数据库无关的文件,如日志和配置文件

  2. 备份文件适用于所有的计算机架构

  3. 只能在mysql服务器运行的时候进行逻辑备份

  4. 逻辑备份的工具包括mysqldump和select … into outfile语句

mysqldump命令备份所有数据库。

2、恢复简单,可以使用管道将他们输入到MySQL

  导入所有库
 mysql命令行
 mysql>source all.sql;

-- 备份所有数据库
mysqldump -u root -h 127.0.0.1 -p --all-databases >c:UsersliufaDesktop2018-07-10.sql

3、与存储引擎无关,因为是从mysql服务器中提取数据而生成的,所以消除了底层数据存储的不同

 导入某些库
 mysql命令行
 mysql>source db1db2.sql;

澳门新濠3559 5

4、有助于避免数据损坏。若磁盘驱动器有故障而要复制原始文件时,此时将得到一个损坏的备份

导入某个库

热备份与冷备份

热(hot)备份指的是在运行mysql服务器的过程中备份,冷(cold)备份指的是在停止mysql服务器后备份,而暖(warm)备份是指在备份的手给正在运行的mysql服务器加锁,防止数据库文件改变

热备份的特征:

1. 不会严重干扰(intrusive)到其他客户端,因为在执行备份时,其他客户端可能会访问到数据库

2. 一定要记得给服务器加锁,否则在备份期间改变了数据库会影响到备份的完整性,mysql企业版会在备份时自动加锁

 

冷备份的特征:

1. 在备份期间,客户端不能访问到数据库,所以,通过会在从服务器(这里指的是配置了主从同步的从服务器)上进行备份

  1. 备份程序比较简单

 

 

热备的缺点是:

 系统命令行
 mysql -uusername -ppassword db1 < db1.sql;
 或mysql命令行
创建库、进入新建库、
mysql>source db1.sql;   (需要把 要还原的 数据放在 mysqldump 同一目录下)

备份的sql脚本文件中的一些知识点,简单介绍。

1、必须有数据库服务器完成逻辑工作,需要更多地cpu周期

  导入某些数据表

完全备份与增量备份

完全备份备份了某个时间点的mysql服务器的全部数据,而增量备份备份了在一个指定的时间点之后有变动的数据。增量备份是通过       mysql服务器的binary log(二进制日志,该日志记录了数据的变动情况)来执行的

 

备份调度是用来自动执行备份程序的,压缩备份文件可以减小备份文件的大小,加密备份可以提供更高的安全性。但是mysql本身并不提供这些功能,可以mysql企业版或者第三方的解决方案。

-- 表示当mysql的版本为4.01.03以上时,这里面的sql语句是会执行的
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- 在大批量导入时,先禁用索引,然后导入数据完成之后再开启索引,一次性重建索引的效率更高
-- 禁用表(student)的索引
alter table student disable keys;
-- 启用表(student)的索引
alter table student enable keys;

-- 为指定表设置读锁定,那么该线程和其它线程只能从该表中读数据,不能进行任何写操作
lock tables demo.student read;
-- 释放锁定
unlock tables;

-- 为指定表设置写锁定,那么该表只有拥有这个锁的线程才能读写,其它的线程被阻塞
lock tables demo.student write;
-- 释放锁定
unlock tables;

2、速度比较慢。因为获取了信息,然后组装成sql语句,如果调用命令是远程,还要把sql语句远程传输; 

 系统命令行
 mysql -uusername -ppassword db1 < tb1tb2.sql
 或mysql命令行
 mysql>
 user db1;
 source tb1tb2.sql;  

 

--mysqldump实例步骤:

  mysqldump字符集设置

 

二、恢复

语法:

 mysqldump -uusername -ppassword --default-character-set=gb2312 db1 table1 > tb1.sql

完全备份与恢复

这一部分的内容用来描述如何使用mysqldump命令来产生导出文件,和如何导入这些导出文件。

下面这张图显示了下面范例所用的数据库和数据库的内容

澳门新濠3559 6

 

mysqldump根据--tab选项来产生两种类型的输出文件:

1. 没有--tab选项,mysqldump命令把标准的SQL语句写入到输出文件中,该文件包括CREATE语句来创建导出对象(数据库,表等),  INSERT语句来往表中写入数据,这个文件可以使用mysql命令来重新创建导出的对象,可以通过选项来控制SQL语句格式和某个导出的对象。(参考下面的“以SQL语句格式导入导出数据库”)

澳门新濠3559 7

上图就是以SQL格式导出的文件

 

2. 使用--tab命令,mysqldump会为数据库中的每一个表产生两个输出文件,mysql服务器会以tab作为分隔符来写入一个文件(该文件名为tbl_name.txt),同时服务器会创建一个名为tbl_name.sql文件,该文件只包含建表语句(CREATE TABLE)(参考下面的“以分隔符格式导入导出数据库”)

澳门新濠3559 8

上图就是以分隔符形式导出的文件

 

mysql命令恢复至指定数据库。

[root@001 ~]# mysqldump

 

以SQL语句格式导出数据

默认地,mysqldump以SQL语句的方式写入到标准输出文件中,典型的用法如下(记得先开启mysql数据库):

shell > mysqldump [arguments] > file_name

注意:真正在shell中使用mysqldump命令时,需要显式地加上mysql连接信息,下面的所有命令为了简洁而省略了这些信息。

可以在shell终端中直接输入命令,就得到mysqldump的使用帮助(附件一)

[root@localhost ~]# mysqldump --help

下面举出几个常用的命令:

为了导出所有数据库,需要用--all-databases选项

shell > mysqldump --all-databases > dump.sql

示范命令:在shell中真正使用这条语句时是下面这样的(以日期的格式命名,

-uroot指的是以root的身份登录,-p'123456'指的是密码,注意不能有空格,后面的命令就不再一一示范了):

[root@localhost ~]# mysqldump --all-databases -uroot -p'123456' > /server/backup/bak_$(date  %F).sql

为了导出指定的数据库(一个或多个数据库),需要用--databases选项(或者-B)

shell > mysqldump --databases db1 db2 db3 > dump.sql

而导出指定数据库的哪一张表,则忽略掉--databases选项(db1数据库中的table1和table2)

shell > mysqldump db1 table1 table2> dump.sql

在导出单个指定的数据库时,除了像上面一样使用--databases以外,还可以这样:

shell > mysqldump db1 > dump.sql

但是,忽略了--databases选项后,需要注意这点:

输出文件中没有CREATE DATABASE和USE语句,所以当你重新导入这个导出文件时,你必须指定一个默认的数据库(或者自己创建一个数据库);与此同时,你也可以把这个导出文件导入到另一个数据库上

-- 恢复至指定数据库(Test)
mysql -u root -h 127.0.0.1 -p Test <c:UsersliufaDesktopStudent_2018-07-10.sql

Usage: mysqldump [OPTIONS] database [tables]     #备份表

 

澳门新濠3559 9

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]    #备份哪几个数据库

 

 

OR mysqldump [OPTIONS] --all-databases [OPTIONS]        #所有数据库

5.数据库备份 详细命令

导入SQL语句形式的文件

当使用上面的mysqldump语句导出文件的时候使用到了--all-databases或者

--databases选项后,文件中已经含有了CREATE DATABASE和USE语句,所以不需要再指定一个数据库,可以直接读入该文件

shell > mysql < dump.sql

示范语句:

[root@localhost ~]# mysql -uroot -p'123456' </server/backup/dump.sql

或者,先登录mysql,在mysql中进行导入

mysql > source /server/backup/dump.sql;

但是,如果导出文件中不包含CREATE DATABASE和USE语句,此时就要手动创建数据,再读入该文件。

shell > mysqladmin create db1

示范语句:

[root@localhost backup]# mysqladmin -uroot -p'123456' create db_test;

然后在导入文件时指定数据库名字:

shell > mysql db1 < dump.sql;

或者先登录mysql,然后导入文件

mysql > create database if not exists db1;

mysql > use db1;

mysql > source dump.sql;

 

如果已经登录mysql,则可以使用这种方式恢复至指定数据库,但只能在cmd界面下执行source命令,不能在mysql工具里面执行source命令。

For more options, use mysqldump --help

1.mysqldump
         是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

以分隔符格式导出数据库

在使用mysqldump命令时,如果加上了--tab=dir_name选项,就会在dir_name目录为数据库中的每个表生成两个文件,以txt为后缀的文件用来存储数据,以sql为后缀的文件用来存储CREATE TABLE的建表语句,下面的语句用来导出db1数据库的内容到/tmp目录下

shell > mysqldump --tab=/tmp db1

示范语句:

[root@localhost backup]# mysqldump -uroot -p'123456' --tab=/server/backup/tmp/ kylin_default

注意:如果看到了下面所示的错误

mysqldump: Got error: 1: Can't create/write to file '/server/backup/tmp/course.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'

是因为/server/backup/tmp目录的权限问题,导致mysql不能写入到该目录下,解决方法就是授权该目录

[root@localhost backup]# chmod a rwx tmp/

当使用--tab选项时,最好只导出本地的mysql服务器的数据库,如果要导出远程的数据库,那么导出的目录必须同时存在于两个服务器中,并且txt文件会写入到远程的服务器的目录中,而sql文件会写入到本地服务器目录中

可以通过选项自定义txt文件的导出格式:

  1. --fields-terminated-by=str 在txt文件中用来分隔每一列的内容(默认是tab)
  2. --fields-enclosed-by=char  在txt文件中用来封闭每一个值的内容(例如用双引号把所有的值括起来,默认为空)
  3. --fields-optionally-enclosed-by=char 在txt文件中用来封闭非数值的内容(默认为空)
  4. --fields-escaped-by=char 忽略特定的字符
  5. --lines-terminated-by=str 每一行的行终止符(默认是换行符)

譬如,导出的文件中,用逗号分隔每一列的值,并且用双引号括起来,在行末用rn(windows环境下的换行符)作为终止符

[root@localhost tmp]# mysqldump -uroot -p'123456' --tab=/server/backup/tmp/ --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a kylin_default

输出格式有前面的图变为下面这个

澳门新濠3559 10

-- 如果已经登录mysql,则可以使用这种方式恢复至指定数据库
-- 只能在cmd界面下执行source命令,不能在mysql工具里面执行source命令
use Test;
source c:UsersliufaDesktopStudent_2018-07-10.sql

如果是使用innodb为确保数据一致可以加参数 --single-transaction#一致性备份指的是当你备份的所有数据的时间点都是在你开始备份的那个时间点,即使表中的数据发生变化也不备份。

 

澳门新濠3559 11

--参数说明:

  1. mysqldump 是文本备份还是二进制备份

    全选 复制放进笔记

    de>它是文本备份,如果你打开备份文件你将看到所有的语句,可以用于重新创建表和对象。它也有 insert 语句来使用数据构成表。
    de>
    
  2. mysqldump 的语法是什么?

    全选 复制放进笔记

    de> mysqldump -u [uname] -p[pass] –databases [dbname][dbname2] > [backupfile.sql]
    de>
    
  3. 使用 mysqldump 怎样备份所有数据库?

    全选 复制放进笔记

    de>mysqldump -u root -p –all-databases > backupfile.sql
    de>
    
  4. 使用 mysqldump 怎样备份指定的数据库?

    全选 复制放进笔记

    de>mysqldump -u root -p –databases school hospital > backupfile.sql
    de>
    
  5. 使用 mysqldump 怎样备份指定的表?

    全选 复制放进笔记

    de>mysqldump –user=root –password=mypassword -h localhost databasename table_name_to_dump table_name_to_dump_2 > dump_only_two_tables_file.sql
    de>
    
  6. 我不想要数据,怎样仅获取 DDL?

    全选 复制放进笔记

    de>mysqldump -u root -p –all-databases –no-data > backupfile.sql
    de>
    
  7. 一次 mysqldump 备份花费多长时间?

    全选 复制放进笔记

    de>这依赖于数据库大小,100 GB 大小的数据库可能花费两小时或更长时间
    de>
    
  8. 怎样备份位于其他服务器的远程数据库?

    全选 复制放进笔记

    de>mysqldump -h 172.16.25.126 -u root -ppass dbname > dbname.sql
    de>
    
  9. –routines 选项的含义是什么?

    全选 复制放进笔记

    de>通过使用 -routines 产生的输出包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句用于重新创建 routines。如果你有 procedures 或 functions 你需要使用这个选项
    de>
    
  10. 怎样列出 mysqldump 中的所有选项?

    全选 复制放进笔记

    de>mysqldump –help
    de>
    
  11. mysqldump 中常用的选项是?

    全选 复制放进笔记

    de>All-databases
    Databases 
    Routines
    Single-transaction (它不会锁住表) – 一直在 innodb databases 中使用
    Master-data – 复制 (现在忽略了)
    No-data – 它将 dump 一个没有数据的空白数据库
    de>
    
  12. 默认所有的 triggers 都会备份吗?

    全选 复制放进笔记

    de>是的
    de>
    
  13. single transaction 选项的含义是什么?

    全选 复制放进笔记

    de>–singletransaction 选项避免了 innodb databases 备份期间的任何锁,如果你使用这个选项,在备份期间,没有锁
    de>
    
  14. 使用 mysqldump 备份的常用命令是什么?

    全选 复制放进笔记

    de>nohup mysqldump –socket=mysql.sock –user=user1 –password=pass –single-transaction –flush-logs –master-data=2 –all-databases –extended-insert –quick –routines > market_dump.sql 2> market_dump.err &
    de>
    
  15. 使用 mysqldump 怎样压缩一个备份?

    全选 复制放进笔记

    de>注意: 压缩会降低备份的速度
    Mysqldump [options] | gzip > backup.sql.gz
    de>
    
  16. mysqldump 备份大数据库是否是理想的?

    全选 复制放进笔记

    de>依赖于你的硬件,包括可用的内存和硬盘驱动器速度,一个在 5GB 和 20GB 之间适当的数据库大小。 虽然有可能使用  mysqldump 备份 200GB 的数据库,这种单一线程的方法需要时间来执行。
    de>
    
  17. 怎样通过使用 mysqldump 来恢复备份?

    • 使用来源数据的方法
    • Mysql –u root –p < backup.sql
  18. 在恢复期间我想记录错误到日志中,我也想看看恢复的执行时间?

    全选 复制放进笔记

    de>Time Mysql –u root –p < backup.sql > backup.out 2>&1
    de>
    
  19. 怎样知道恢复是否正在进行?

    全选 复制放进笔记

    de>显示完整的进程列表
    de>
    
  20. 如果数据库是巨大的,你不得不做的事情是?

    全选 复制放进笔记

    de>使用 nohup 在后台运行它
    de>
    
  21. 我是否可以在 windows 上使用 mysqldump 备份然后在 linux 服务器上恢复?

    全选 复制放进笔记

    de>是的
    de>
    
  22. 我怎么传输文件到目标服务器上去?

    • 使用 scp
    • 使用 sftp
    • 使用 winscp
  23. 如果我使用一个巨大的备份文件来源来恢复会发生什么?

    全选 复制放进笔记

    de>如果你的一个数据库备份文件来源,它可能需要很长时间运行。处理这种情况更好的方式是使用 nohup 来在后台运行。也可使用在 unix 中的 screen 代替
    de>
    
  24. 默认情况下,mysqldump 包含 drop 数据库吗?

    全选 复制放进笔记

    de>你需要添加 –add-drop-database 选项
    de>
    
  25. 怎样从一个多数据库备份中提取一个数据库备份(假设数据库名字是 test)?

    de>sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql
    de>
    

 

--all-databases , -A

 

导入分隔符格式的文件

先导入sql后缀的文件,再导入txt文件

shell > mysql db1 < t1.sql

shell > mysqlimport db1 t1.txt

示范语句:

[root@localhost tmp]# mysql -uroot -p'123456' kylin_default < test.sql

[root@localhost tmp]# mysqlimport -uroot -p'123456' kylin_default /server/backup/tmp/test.txt;

或者登陆mysql后操作

mysql > use db1;

mysql > load data infile ‘t1.txt’ into table t1;

注意:如果你导出数据库时使用了自定义的格式,在导入该文件到数据库时也必须指定同样的格式,否则会发生错误

如:

shell > mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

或者

mysql > use db1;

mysql > load data infile ‘t1.txt’ into table t1

> fields terminated by ',' fields enclosed by ' " '

> lines terminated by 'rb' ;

 

注意:通过mysqldump备份的数据库文件,如果用了(--all-databases)或(--databases)选项,则在备份文件中包含create database和use语句,故并不需要指定一个数据库名去恢复备份文件。

导出全部数据库。

         

mysqldump技巧

如果通过mysqldump备份的是单个数据库,且没有使用(--databases)选项,则备份文件中不包含create database和use语句,那么在恢复的时候必须先创建数据库并指定该数据库。

mysqldump -uroot -p --all-databases

 

复制一个数据库的内容到另一个数据库
shell > mysqldump db1 > dump.sql

shell > mysqladmin create db2

shell > mysql db2 < dump.sql

在使用mysqldump命令时只要不加上--databases选项,就会使得导出的SQL文件中没有USE db1语句,从而使得该文件可以导入到另一个不同名的数据库中

 

 

--all-tablespaces , -Y

参数

三、带有--tab=dir_name选项的备份与恢复

导出全部表空间。

参数说明

复制一个数据库到另一台主机上

在服务器1上执行:

shell > mysqldump --databases db1 > dump.sql

然后把dump.sql文件拷贝到服务器2上,在服务器2上执行:

shell > mysql < dump.sql

在使用mysqldump命令时加上--databases选项,就会使得导出的SQL文件中包含有CREATE DATABASE和USE db1语句,所以可以在没有db1的服务器上直接导入该文件(因为会自动生成db1)

当然,也可以不使用--databases选项,只要在服务器2上手动创建一个数据库,然后往该数据库导入该文件即可

 

调用mysqldump带有--tab=dir_name选项备份数据库,则dir_name表示输出文件的目录,在这个指定的目录中,需备份的每个表将会产生两个文件。

mysqldump -uroot -p --all-databases --all-tablespaces

--all-databases  , -A

导出已经存储的程序

mysqldump还可以处理存储的程序(stored programs)(存储进程,函数,触发器和事件(events)):

  1. --events:Dump Event Scheduler events
  2. --routines: Dump stored procedures and functions
  3. --triggers: Dump triggers for tables

--triggers选项是默认执行的,而其余两个选项是要自己手动添加的,如果要明确禁止导出这三个程序,就使用选项:--skip-events, --skip-routines, --skip-triggers

 

例如:对于一个名为t1的表,备份会生成两个文件,即t1.sql和t1.txt。

--no-tablespaces , -y

导出全部数据库。

分开导出数据库表的定义和数据内容

使用--no-data选项可以告诉mysqldump只导出数据库的表定义语句,而不导出数据内容

shell > mysqldump --no-data test > dump-defs.sql

示范语句:

[root@localhost backup]# mysqldump -uroot -p'123456' --no-data kylin_default > /server/backup/no_data.sql

澳门新濠3559 12

可以看到该文件只包含了创建表的语句

使用--no-create-info选项可以告诉mysqldump只导出数据库的数据操作语句,而不导出数据库的表定义

shell > mysqldump --no-create-info test > dump-data.sql

示范语句:

[root@localhost backup]# mysqldump -uroot -p'123456' --no-create-info kylin_default > /server/backup/no_create_into.sql

澳门新濠3559 13

可以看到该文件只包含了操作数据(这里仅仅是插入数据)的内容

 

t1.sql文件中包含create table语句,t1.txt文件中一行对应为数据表中的一条记录,列值与列值之间以‘tab’分隔。

不导出任何表空间信息。

mysqldump  -uroot -p --all-databases

使用mysqldump来测试升级数据库的兼容性

当打算升级mysql服务器时,应当首先测试新版本数据库。可以把旧服务器的数据导入到新服务器中,测试新版本的服务器是否能正确处理数据。

在旧的服务器中执行:

shell > mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在新的服务器中执行

shell > mysql < dump-defs.sql

因为导出的文件中没有数据,所以可以快速执行,你应当留意在执行过程中出现的所有警告和错误

在你确认了在新服务器中已经正确生成了数据库,此时就可以往新服务器中导入数据

在旧的服务器中执行:

shell > mysqldump --all-databases --no-create-info > dump-data.sql

在新的服务器中执行

shell > mysql < dump-data.sql

然后检查数据内容和运行一些测试程序

 

-- 带有--tab=dir_name选项的备份
mysqldump -u root -h 127.0.0.1 -p test --tab=c:UsersliufaDesktop

mysqldump -uroot -p --all-databases --no-tablespaces

--all-tablespaces  , -Y

增量备份与时间点恢复

时间点恢复指的是首先指定一个时间点,然后让mysql服务器执行该时间点之后的全部操作。通常,时间点恢复会在执行了一个全备份之后才能运行,因为在完成全备份的时候,服务器会记录当时的状态和时间(换个角度想,有a,b两个数据库,b首先全备份了a的数据库,然后a的数据库的内容发生了改变,这时候b数据库只要执行a数据改变的语句就能保持和a的一致)

澳门新濠3559 14

--add-drop-database

导出全部表空间。

操作日志文件

时间点恢复的要点:

时间点恢复需要用到一个二进制的日志文件,该文件是在执行全备份之后生成的,因此,服务器必须先启用--long-bin配置选项才能生成二进制文件。服务器默认会在数据存储的目录存放该日志,当然你可以指定另一个存放目录

修改你的my.cnf文件,在[mysqd]模块下增加内容:log-bin=mysql-bin,然后重启数据库。然后马上执行一次全备份,因为日志文件是记录全备份之后的SQL语句,如果不在每一次导入日志文件前先导入全备份的数据库,就会造成错误(譬如对于一条插入语句,如果直接导入日志文件,就会提示ERROR 1062 (23000) at line XX: Duplicate entry 'X' for key 'PRIMARY',因为这条数据在导入日志文件前就已经存在)。

所以,要测试下面的文件,每一次导入日志文件前,都必须先导入备份的数据库

登陆mysql,使用命令

mysql > show bianry logs;

可以看到二进制日志的列表(由于我清理了全部的日志,所以现在只有一个日志)

澳门新濠3559 15

判断当前的二进制文件

mysql > show master status G

澳门新濠3559 16

该二进制文件存放在mysql的数据目录中,如果直接查看该文件,会显示乱码。要使用mysqlbinlog命令才能正确显示该文件。

[root@localhost data]# mysqlbinlog mysql-bin.000001

澳门新濠3559 17

我截取了该文件的最后一部分,留意上面箭头所指的时间,表明数据库在2015年6月30日 15:47:28分执行了三条语句:

COMMIT;

DELIMITER;

ROLLBACK;

然后我登陆数据库,插入了一条数据,再查看这个日志内容:

澳门新濠3559 18

可以看到,日志文件增长了, 并在15:48:37产生了一条插入语句,而这条语句就是我使用的语句。因此可见,这个二进制日志完全记录了对数据库的所有操作。

 

使用mysqlbinlog命令来执行日志文件中的SQL语句

shell > mysqlbinlog binlog_files | mysql -u root -p

我在数据库里不小心写了一条删除语句,我现在要恢复执行这条语句之前的数据库状态,该怎样做呢?

首先,把日志文件内容另存为一个可阅读的文件

[root@localhost data]# mysqlbinlog mysql-bin.000002 > tmpfile

[root@localhost data]# vi tmpfile

然后找到该文件中的那条删除数据的语句并删除该语句

澳门新濠3559 19

在上图中可以看到在2015年6月30日15:49:38执行了一条delete语句,现在删除该语句,保存并退出

记住:首先导入之前的备份数据库,然后再把这个文件导入到mysql服务器中

shell > mysql < lastest-backup.sql

shell > mysql -uroot -p < tmpfile

就可以看到被删除的数据出现了

如果要导入多个二进制日志,最好不要单独到导入每一个日志,譬如要导入两个日志。

shell > mysqlbinlog mysql-bin.000001 | mysql -u root -p

shell > mysqlbinlog mysql-bin.000002 | mysql -u root -p

上面的做法是有风险的,假如第一个日志中含有CREATE TABLE语句,而第二个日志中含有USE语句,当第一个日志导入进程失败时也会导致第二个日志导入失败(因为建表语句执行失败,所以导入第二个日志时就没有表)

为了避免出现这种错误,可以把多个日志一次性导入

shell > mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

另一个方法如下

shell > mysqlbinlog mysql-bin.000001 > /tmp/tmpfile

shell > mysqlbinlog mysql-bin.000002 >> /tmp/tmpfile

shell > mysql -uroot -p -e “source /tmp/tmpfile;”

使用-e选项可以不登陆数据库,直接运行双引号里的语句

 

每个数据库创建之前添加drop数据库语句。

mysqldump  -uroot -p --all-databases --all-tablespaces

带有--tab=dir_name选项的备份在恢复时,需要先恢复表结构,然后恢复表中的数据。

mysqldump -uroot -p --all-databases --add-drop-database

--no-tablespaces  , -y

-- 恢复表结构
mysql -u root -h 127.0.0.1 -p test <c:UsersliufaDesktopstudent.sql

--add-drop-table

不导出任何表空间信息。

使用时间点来恢复数据

使用日志文件可以很明确地指出把数据恢复到哪个时间点。正如我上面举的例子,在上图中可以看到在2015年6月30日15:49:38执行了一条delete语句,但是我当时并没有发现,在我又执行了很多语句之后才发现了这个巨坑。此时,我该如何挽救呢?

  1. 首先,我要先导入之前备份的最新的数据库

    shell > mysql < lastest-backup.sql

  2. 把日志文件导出到一个可以阅读的文件中

    shell > mysqlbinlog mysql-bin.000002 > /tmp/tmpfile

  3. 打开该文件,找到delete语句执行时候的时间点(如上一张图所示)

  4. 然后在导入日志文件时,只执行到2015年6月30日15:49:38之前的语句

    shell > mysqlbinlog --stop-datetime="2015-06-30 15:49:37"

       mysql-bin.000002 | mysql -u root -p
    

注意,--stop-datetime要指向前一个时间

  1. 再一次导入日志文件,这次只执行从2015年6月30日15:49:38之后的语句

    shell > mysqlbinlog --start-datetime="2015-06-30 15:50:11"

       mysql-bin.000002 | mysql -u root -p
    

注意,--start-datetime要指向后一个时间

注意:

  1. 其实删除该日志文件的那条delete语句,然后导入到mysql中会更简单,不过这里为了演示如何指定使用时间点来恢复数据
  2. 指定时间点来恢复数据其实并不好用,因为可能会有多条语句在同一个时间里执行,这个方法就完全不管用了,可以参考下面使用事件点来恢复数据。

澳门新濠3559 20

每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用--skip-add-drop-table取消选项)

mysqldump  -uroot -p --all-databases --no-tablespaces

-- 恢复表中的数据
mysqlimport -u root -h 127.0.0.1 -p test c:UsersliufaDesktopstudent.txt

mysqldump -uroot -p --all-databases (默认添加drop语句)

--add-drop-database

使用事件点来恢复数据

澳门新濠3559 21

留意上图所指的at 508,这个文件中的所有跟在at后面的数字就是事件点,这个数字是递增且唯一的。

所以,先恢复到这个事件点之前

shell > mysqlbinlog --stop-position=411 

      mysql-bin.000001 | mysql -u root -p

同样地,往前移一个点

再恢复从这个事件点之后的数据

shell > mysqlbinlog --start-position=627 

      mysql-bin.000001 | mysql -u root -p

往后移一个时间点

澳门新濠3559 22

mysqldump -uroot -p --all-databases –skip-add-drop-table (取消drop语句)

每个数据库创建之前添加drop数据库语句。

 

 

--add-locks

mysqldump  -uroot -p --all-databases --add-drop-database

自动化备份

四、导出

在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项)

--add-drop-table

自动执行备份脚本

写好备份脚本后,通过把脚本添加到contrab中就能实现自动备份。

  1. 确认crond服务正在运行

    [root@FirstCentOS backup]# service crond status

澳门新濠3559 23

如果没有启动,则使用命令启动:

[root@FirstCentOS backup]# service crond start
  1. 然后把脚本添加到contrab中(每天01:00自动备份)

    [root@FirstCentOS backup]# crontab -e

    #backup all databases in mysql on 01:00 everyday by root

    00 01 * /bin/sh /server/backup/backup_automatically.sh >/dev/null 2>&1

把上面那行的内容添加进去,然后保存退出即可

使用select...into outfile命令导出。

mysqldump -uroot -p --all-databases (默认添加LOCK语句)

每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用--skip-add-drop-table取消选项)

 

-- select ... into outfile语法
select * from tablename where 1=1 into outfile 'filename.txt' [OPTIONS]

[OPTIONS]:
fields terminated by 'string'                -- 表示每个字段的值用指定的字符分割,可以为单个或多个字符
fields [OPTIONALLY]  ENCLOSED BY 'char'    -- 表示每个字段的值用指定的字符包起来,只能为单个字符
fields ESCAPED BY 'char'                    -- 表示将系统默认的转移字符替换为指定字符,只能为单个字符
lines STARTING by 'string'                    -- 表示每行数据开头的字符,可以为单个或多个字符,默认没有任何字符
lines terminated by 'string'                 -- 表示每行数据结尾的字符,可以为单个或多个字符,默认没有任何字符

-- 把查询出的数据导出到指定的文本文件
select * from demo.student into outfile 'C:\Users\liufa\Desktop\student.txt' 
fields 
terminated by ','    -- 表示字段之间用逗号分隔
optionally enclosed BY '''    -- 表示每个字段用单引号引起来,加上optionally关键字表示所有的值都放在引号之间,否则只有char和varchar等字符数据类型的字段被引号引起来
escaped BY '''    
lines 
terminated by 'rn';    -- 表示每行以回车换行符结尾,保证每一条记录占一行

mysqldump -uroot -p --all-databases –skip-add-locks (取消LOCK语句)

mysqldump  -uroot -p --all-databases  (默认添加drop语句)

上传到ftp

我们每天执行一次备份,时间长了之后就会产生大量的文件,浪费磁盘空间,所以可以在每天备份的时候就自动把备份文件上传到ftp上,然后删除本地磁盘的文件,就能节省本地磁盘的空间。

1. 我是参考这篇文章在windows上搭建的ftp服务器:

  1. 然后在linux上安装ftp

    [root@FirstCentOS backup]# yum install ftp -y

  2. 测试上传能否成功

    [root@FirstCentOS backup]# ftp -i -n <

    open 192.168.1.205 #ftp的ip地址

    user Administrator 123456 #用户名和密码

    cd mysql #上传到ftp的哪个文件夹上

    lcd /server/backup #要上传的文件的路径

    hash

    binary

    put 2015-07-07.tar.bz2 #上传的文件名

    prompt

    close

    bye

    EFO

注意上面的binary选项,非常重要,因为从linux上传文件到windows的服务器中会改变格式,而binary选项能保证不改变任何格式

下载文件:

[root@FirstCentOS backup]# ftp -i -n <<EFO

open 192.168.1.205              #ftp的ip地址

user Administrator 123456        #用户名和密码

cd mysql                      #要下载的文件位于ftp的哪个文件夹上

binary        

get 2015-07-07.tar.bz2            #要下载的文件名

close

bye

EFO

 

--allow-keywords

mysqldump  -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)

使用mysql命令导出。

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

--add-locks

-- 语法
mysql -u root -h 127.0.0.1 -p --execute="select 语句" dbname >filename.txt

mysqldump -uroot -p --all-databases --allow-keywords

在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态,
         使用--skip-add-locks      取消选项)

 

--apply-slave-statements

mysqldump  -uroot -p --all-databases  (默认添加LOCK语句)

使用mysql命令导出指定数据库中所查询的指定表的数据。

在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。

mysqldump  -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)
--allow-keywords

-- 导出数据库(demo)中表(student)的数据,导出的文件一行对应数据表中的一条记录,列与列之间以制表符(‘tab’)分割
mysql -u root -h 127.0.0.1 -p --execute="select * from student;" demo >c:UsersliufaDesktopstudent.txt

mysqldump -uroot -p --all-databases --apply-slave-statements

允许创建是关键词的列名字。这由表名前缀于每个列名做到。

澳门新濠3559 24

--character-sets-dir

mysqldump  -uroot -p --all-databases --allow-keywords

 

字符集文件的目录

--apply-slave-statements

-- 导出数据库(demo)中表(student)的数据,每个字段占一行,如下图
mysql -u root -h 127.0.0.1 -p --vertical --execute="select * from student;" demo >c:UsersliufaDesktopstudent.txt

mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets

在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。

澳门新濠3559 25

--comments

mysqldump  -uroot -p --all-databases --apply-slave-statements

澳门新濠3559 26

附加注释信息。默认为打开,可以用--skip-comments取消

--character-sets-dir

 

mysqldump -uroot -p --all-databases (默认记录注释)

字符集文件的目录

-- 导出数据库(demo)中表(student)的数据,生成html文件
mysql -u root -h 127.0.0.1 -p --html --execute="select * from student;" demo >c:UsersliufaDesktopstudent.html

mysqldump -uroot -p --all-databases --skip-comments (取消注释)

mysqldump  -uroot -p --all-databases  --character-sets-dir=/usr/local/mysql/share/mysql/charsets

澳门新濠3559 27

--compatible

--comments

 

导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,

附加注释信息。默认为打开,可以用--skip-comments取消

-- 导出数据库(demo)中表(student)的数据,生成xml文件
mysql -u root -h 127.0.0.1 -p --xml --execute="select * from student;" demo >c:UsersliufaDesktopstudent.xml

要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

mysqldump  -uroot -p --all-databases  (默认记录注释)

澳门新濠3559 28

mysqldump -uroot -p --all-databases --compatible=ansi

mysqldump  -uroot -p --all-databases --skip-comments   (取消注释)

 

--compact

--compatible

五、导入

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项:--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys

导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、
        postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等,

使用load data infile命令导入。

mysqldump -uroot -p --all-databases --compact

要使用几个值,用逗号将它们隔开。它并不保证能完全兼容,而是尽量兼容。

-- load data infile语法
-- 如果指定关键字LOW_PRIORITY,那么mysql将会等到没有线程读取当前指定表的时候才导入数据
-- 如果指定关键字CONCURRENT,则读取和导入可以同时发生
-- 如果指定关键字LOCAL,则表示导入本地的文件
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'filename.txt' 
-- REPLACE和IGNORE控制对现有表的唯一键记录重复的处理,当指定REPLACE时,新行将替换有相同的唯一键值的现有行;当指定IGNORE时,跳过有唯一键的现有行的重复行的输入。
-- 如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。
[REPLACE | IGNORE] 
INTO TABLE tablename 
[FIELDS 
    [TERMINATED BY 'string'] 
    [[OPTIONALLY] ENCLOSED BY 'char'] 
    [ESCAPED BY 'char' ] 
] 
[LINES 
    [STARTING BY 'string'] 
    [TERMINATED BY 'string'] 
] 
[IGNORE number LINES] -- 表示从开始忽略文件的行数,number表示忽略的行数
[(col_name_or_user_var,...)] 
[SET col_name = expr,...]

-- 导入数据
load data infile 'c:\Users\liufa\Desktop\student.txt' 
replace
into table test.student 
fields 
terminated by ','
optionally enclosed BY '''
escaped BY '''
lines 
terminated by 'rn';

--complete-insert, -c

mysqldump  -uroot -p --all-databases --compatible=ansi

 

使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。

--compact

mysqldump -uroot -p --all-databases --complete-insert

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项:--skip-add-drop-table  --skip-add-       locks --skip-comments --skip-disable-keys

--compress, -C

mysqldump  -uroot -p --all-databases --compact

在客户端和服务器之间启用压缩传递所有信息

--complete-insert,  -c

mysqldump -uroot -p --all-databases --compress

使用完整的insert语句(包含列名称)。这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败。

--create-options, -a

mysqldump  -uroot -p --all-databases --complete-insert

在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

--compress, -C

mysqldump -uroot -p --all-databases

在客户端和服务器之间启用压缩传递所有信息

--databases, -B

mysqldump  -uroot -p --all-databases --compress

导出几个数据库。参数后面所有名字参量都被看作数据库名。

--create-options,  -a

mysqldump -uroot -p --databases test mysql

在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

--debug

mysqldump  -uroot -p --all-databases

输出debug信息,用于调试。默认值为:d:t,/tmp/mysqldump.trace

--databases,  -B

mysqldump -uroot -p --all-databases --debug

导出几个数据库。参数后面所有名字参量都被看作数据库名。

mysqldump -uroot -p --all-databases --debug=" d:t,/tmp/debug.trace"

mysqldump  -uroot -p --databases test mysql

--debug-check

--debug

检查内存和打开文件使用说明并退出。

输出debug信息,用于调试。默认值为:d:t:o,/tmp/mysqldump.trace

mysqldump -uroot -p --all-databases --debug-check

mysqldump  -uroot -p --all-databases --debug

--debug-info

mysqldump  -uroot -p --all-databases --debug=” d:t:o,/tmp/debug.trace”

输出调试信息并退出

--debug-check

mysqldump -uroot -p --all-databases --debug-info

检查内存和打开文件使用说明并退出。

--default-character-set

mysqldump  -uroot -p --all-databases --debug-check

设置默认字符集,默认值为utf8

--debug-info

mysqldump -uroot -p --all-databases --default-character-set=utf8

输出调试信息并退出

--delayed-insert

mysqldump  -uroot -p --all-databases --debug-info

采用延时插入方式(INSERT DELAYED)导出数据

--default-character-set

mysqldump -uroot -p --all-databases --delayed-insert

设置默认字符集,默认值为utf8

--delete-master-logs

mysqldump  -uroot -p --all-databases --default-character-set=latin1

master备份后删除日志. 这个参数将自动激活--master-data。

--delayed-insert

mysqldump -uroot -p --all-databases --delete-master-logs

采用延时插入方式(INSERT DELAYED)导出数据

--disable-keys

mysqldump  -uroot -p --all-databases --delayed-insert

对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件,因为它是在插入所有行后创建索引的。该选项只适合MyISAM表,默认为打开状态。

--delete-master-logs

mysqldump -uroot -p --all-databases

master备份后删除日志. 这个参数将自动激活--master-data。

--dump-slave

mysqldump  -uroot -p --all-databases --delete-master-logs

该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时,将会以CHANGE MASTER命令输出到数据文件;设置为2时,会在change前加上注释。该选项将会打开--lock-all-tables,除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。

--disable-keys

mysqldump -uroot -p --all-databases --dump-slave=1

对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件,因为它是在插入所有行后创建索引的。该选项只适合MyISAM表,默认为打开状态。

mysqldump -uroot -p --all-databases --dump-slave=2

mysqldump  -uroot -p --all-databases 

 

--dump-slave

--master-data

该选项将导致主的binlog位置和文件名追加到导出数据的文件中。设置为1时,将会以CHANGE MASTER命令输出到数据文件;设置为2时,在命令前增加说明信息。该选项将会打开--lock-all-tables,除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。

该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1,将会输出CHANGE MASTER 命令;如果为2,输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项,除非--single-transaction也被指定(在这种情况下,全局读锁在开始导出时获得很短的时间;其他内容参考下面的--single-transaction选项)。该选项自动关闭--lock-tables选项。

mysqldump  -uroot -p --all-databases --dump-slave=1

mysqldump -uroot -p --host=localhost --all-databases --master-data=1;

mysqldump  -uroot -p --all-databases --dump-slave=2

mysqldump -uroot -p --host=localhost --all-databases --master-data=2;

--events, -E

--events, -E

导出事件。

导出事件。

mysqldump  -uroot -p --all-databases --events

mysqldump -uroot -p --all-databases --events

--extended-insert,  -e

--extended-insert, -e

使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用--skip-extended-insert取消选项。

使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用--skip-extended-insert取消选项。

mysqldump  -uroot -p --all-databases

mysqldump -uroot -p --all-databases

mysqldump  -uroot -p --all-databases--skip-extended-insert   (取消选项)

mysqldump -uroot -p --all-databases--skip-extended-insert (取消选项)

--fields-terminated-by

--fields-terminated-by

导出文件中忽略给定字段。与--tab选项一起使用,不能用于--databases和--all-databases选项

导出文件中忽略给定字段。与--tab选项一起使用,不能用于--databases和--all-databases选项

mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”

mysqldump -uroot -p test test --tab="/home/mysql" --fields-terminated-by="#"

--fields-enclosed-by

--fields-enclosed-by

输出文件中的各个字段用给定字符包裹。与--tab选项一起使用,不能用于--databases和--all-databases选项

输出文件中的各个字段用给定字符包裹。与--tab选项一起使用,不能用于--databases和--all-databases选项

mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”

mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#"

--fields-optionally-enclosed-by

--fields-optionally-enclosed-by

输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用,不能用于--databases和--all-databases选项

输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用,不能用于--databases和--all-databases选项

mysqldump  -uroot -p test test --tab=”/home/mysql”  --fields-enclosed-by=”#” --fields-optionally-enclosed-by  =”#”

mysqldump -uroot -p test test --tab="/home/mysql" --fields-enclosed-by="#" --fields-optionally-enclosed-by ="#"

--fields-escaped-by

--fields-escaped-by

输出文件中的各个字段忽略给定字符。与--tab选项一起使用,不能用于--databases和--all-databases选项

输出文件中的各个字段忽略给定字符。与--tab选项一起使用,不能用于--databases和--all-databases选项

mysqldump  -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”

mysqldump -uroot -p mysql user --tab="/home/mysql" --fields-escaped-by="#"

--flush-logs

--flush-logs

开始导出之前刷新日志。

开始导出之前刷新日志。

请注意:假如一次导出多个数据库(使用选项--databases或者--all-databases),将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。

请注意:假如一次导出多个数据库(使用选项--databases或者--all-databases),将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。

mysqldump  -uroot -p --all-databases --flush-logs

mysqldump -uroot -p --all-databases --flush-logs

--flush-privileges

--flush-privileges

在导出mysql数据库之后,发出一条FLUSH  PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

mysqldump  -uroot -p --all-databases --flush-privileges

mysqldump -uroot -p --all-databases --flush-privileges

--force

--force

在导出过程中忽略出现的SQL错误。

在导出过程中忽略出现的SQL错误。

mysqldump  -uroot -p --all-databases --force

mysqldump -uroot -p --all-databases --force

--help

--help

显示帮助信息并退出。

显示帮助信息并退出。

mysqldump  --help

mysqldump --help

--hex-blob

--hex-blob

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字
       段类型有       BINARY、VARBINARY、BLOB。

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。

mysqldump  -uroot -p --all-databases --hex-blob

mysqldump -uroot -p --all-databases --hex-blob

--host, -h

--host, -h

需要导出的主机信息

需要导出的主机信息

mysqldump  -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases

--ignore-table

--ignore-table

不导出指定表。指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。例如:--ignore-table=database.table1 --ignore-table=database.table2 ……

不导出指定表。指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。例如:--ignore-table=database.table1 --ignore-table=database.table2 ……

mysqldump  -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

--include-master-host-port

--include-master-host-port

在--dump-slave产生的'CHANGE  MASTER TO..'语句中增加'MASTER_HOST=<host>,MASTER_PORT=<port>'  

在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=<host>,MASTER_PORT=<port>'

mysqldump  -uroot -p --host=localhost --all-databases --include-master-host-port

mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port

--insert-ignore

--insert-ignore

在插入行时使用INSERT IGNORE语句.

在插入行时使用INSERT IGNORE语句.

mysqldump  -uroot -p --host=localhost --all-databases --insert-ignore

mysqldump -uroot -p --host=localhost --all-databases --insert-ignore

--lines-terminated-by

--lines-terminated-by

输出文件的每行用给定字符串划分。与--tab选项一起使用,不能用于--databases和--all-databases选项。

输出文件的每行用给定字符串划分。与--tab选项一起使用,不能用于--databases和--all-databases选项。

mysqldump  -uroot -p --host=localhost test test --tab=”/tmp/mysql”  --lines-terminated-by=”##”

mysqldump -uroot -p --host=localhost test test --tab="/tmp/mysql" --lines-terminated-by="##"

--lock-all-tables,  -x

--lock-all-tables, -x

提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项。

提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项。

mysqldump  -uroot -p --host=localhost --all-databases --lock-all-tables

mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

--lock-tables,  -l

--lock-tables, -l

开始导出前,锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,--single-transaction是一个更好的选择,因为它根本不需要锁定表。

开始导出前,锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,--single-transaction是一个更好的选择,因为它根本不需要锁定表。

请注意当导出多个数据库时,--lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

请注意当导出多个数据库时,--lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

mysqldump  -uroot -p --host=localhost --all-databases --lock-tables

mysqldump -uroot -p --host=localhost --all-databases --lock-tables

--log-error

--log-error

附加警告和错误信息到给定文件

附加警告和错误信息到给定文件

mysqldump  -uroot -p --host=localhost --all-databases  --log-error=/tmp/mysqldump_error_log.err

mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err

--master-data

--max_allowed_packet

该选项将binlog的位置和文件名追加到输出文件中。如果为1,将会输出CHANGE MASTER 命令;如果为2,输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项,除非--single-transaction也被指定(在这种情况下,全局读锁在开始导出时获得很短的时间;其他内容参考下面的--single-transaction选项)。该选项自动关闭--lock-tables选项。

服务器发送和接受的最大包长度。

mysqldump  -uroot -p --host=localhost --all-databases --master-data=1;

mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240

mysqldump  -uroot -p --host=localhost --all-databases --master-data=2;

--net_buffer_length

--max_allowed_packet

TCP/IP和socket连接的缓存大小。

服务器发送和接受的最大包长度。

mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024

mysqldump  -uroot -p --host=localhost --all-databases --max_allowed_packet=10240

--no-autocommit

--net_buffer_length

使用autocommit/commit 语句包裹表。

TCP/IP和socket连接的缓存大小。

mysqldump -uroot -p --host=localhost --all-databases --no-autocommit

mysqldump  -uroot -p --host=localhost --all-databases --net_buffer_length=1024

--no-create-db, -n

--no-autocommit

只导出数据,而不添加CREATE DATABASE 语句。

使用autocommit/commit 语句包裹表。

mysqldump -uroot -p --host=localhost --all-databases --no-create-db

mysqldump  -uroot -p --host=localhost --all-databases --no-autocommit

--no-create-info, -t

--no-create-db,  -n

只导出数据,而不添加CREATE TABLE 语句。

只导出数据,而不添加CREATE DATABASE 语句。

mysqldump -uroot -p --host=localhost --all-databases --no-create-info

mysqldump  -uroot -p --host=localhost --all-databases --no-create-db

--no-data, -d

--no-create-info,  -t

不导出任何数据,只导出数据库表结构。

只导出数据,而不添加CREATE TABLE 语句。

mysqldump -uroot -p --host=localhost --all-databases --no-data

mysqldump  -uroot -p --host=localhost --all-databases --no-create-info

--no-set-names, -N

--no-data, -d

等同于--skip-set-charset

不导出任何数据,只导出数据库表结构。

mysqldump -uroot -p --host=localhost --all-databases --no-set-names

mysqldump  -uroot -p --host=localhost --all-databases --no-data

--opt

--no-set-names,  -N

等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.

等同于--skip-set-charset

mysqldump -uroot -p --host=localhost --all-databases --opt

mysqldump  -uroot -p --host=localhost --all-databases --no-set-names

--order-by-primary

--opt

如果存在主键,或者第一个唯一键,对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效,但会使得导出工作花费很长时间。

等同于--add-drop-table,  --add-locks, --create-options, --quick, --extended-insert, --lock-tables,  
       --set-charset, --disable-keys 该选项默认开启,  可以用--skip-opt禁用.

mysqldump -uroot -p --host=localhost --all-databases --order-by-primary

mysqldump  -uroot -p --host=localhost --all-databases --opt

--password, -p

--order-by-primary

连接数据库密码

如果存在主键,或者第一个唯一键,对每个表的记录进行排序。在导出MyISAM表到InnoDB表
       时有效,但会使得导出工作花费很长时间。 

--pipe(windows系统可用)

mysqldump  -uroot -p --host=localhost --all-databases --order-by-primary

使用命名管道连接mysql

--password, -p

mysqldump -uroot -p --host=localhost --all-databases --pipe

连接数据库密码

--port, -P

--pipe(windows系统可用)

连接数据库端口号

使用命名管道连接mysql

--protocol

mysqldump  -uroot -p --host=localhost --all-databases --pipe

使用的连接协议,包括:tcp, socket, pipe, memory.

--port, -P

mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp

连接数据库端口号

--quick, -q

--protocol

不缓冲查询,直接导出到标准输出。默认为打开状态,使用--skip-quick取消该选项。

使用的连接协议,包括:tcp, socket, pipe, memory.

mysqldump -uroot -p --host=localhost --all-databases

mysqldump  -uroot -p --host=localhost --all-databases --protocol=tcp

mysqldump -uroot -p --host=localhost --all-databases --skip-quick

--quick, -q

--quote-names,-Q

不缓冲查询,直接导出到标准输出。默认为打开状态,使用--skip-quick取消该选项。

使用(`)引起表和列名。默认为打开状态,使用--skip-quote-names取消该选项。

mysqldump  -uroot -p --host=localhost --all-databases 

mysqldump -uroot -p --host=localhost --all-databases

mysqldump  -uroot -p --host=localhost --all-databases --skip-quick

mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names

--quote-names,-Q

--replace

使用(`)引起表和列名。默认为打开状态,使用--skip-quote-names取消该选项。

使用REPLACE INTO 取代INSERT INTO.

mysqldump  -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --replace

mysqldump  -uroot -p --host=localhost --all-databases --skip-quote-names

--result-file, -r

--replace

直接输出到指定文件中。该选项应该用在使用回车换行对(\r\n)换行的系统上(例如:DOS,Windows)。该选项确保只有一行被使用。

使用REPLACE INTO 取代INSERT INTO.

mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

mysqldump  -uroot -p --host=localhost --all-databases --replace

--routines, -R

--result-file,  -r

导出存储过程以及自定义函数。

直接输出到指定文件中。该选项应该用在使用回车换行对(\r\n)换行的系统上
        (例如:DOS,Windows)。该选项确保只有一行被使用。

mysqldump -uroot -p --host=localhost --all-databases --routines

mysqldump  -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

--set-charset

--routines, -R

添加'SET NAMES default_character_set'到输出文件。默认为打开状态,使用--skip-set-charset关闭选项。

导出存储过程以及自定义函数。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump  -uroot -p --host=localhost --all-databases --routines

mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset

--set-charset

--single-transaction

添加'SET NAMES  default_character_set'到输出文件。默认为打开状态,使用--skip-set-charset关闭选项。

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎,仅InnoDB。本选项和--lock-tables 选项是互斥的,因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。

mysqldump  -uroot -p --host=localhost --all-databases 

mysqldump -uroot -p --host=localhost --all-databases --single-transaction

mysqldump  -uroot -p --host=localhost --all-databases --skip-set-charset

--dump-date

--single-transaction

将导出时间添加到输出文件中。默认为打开状态,使用--skip-dump-date关闭选项。

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎,仅InnoDB。本选项和--lock-tables 选项是互斥的,因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。

mysqldump -uroot -p --host=localhost --all-databases

mysqldump  -uroot -p --host=localhost --all-databases --single-transaction

mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date

--dump-date

--skip-opt

将导出时间添加到输出文件中。默认为打开状态,使用--skip-dump-date关闭选项。

禁用–opt选项.

mysqldump  -uroot -p --host=localhost --all-databases

mysqldump -uroot -p --host=localhost --all-databases --skip-opt

mysqldump  -uroot -p --host=localhost --all-databases --skip-dump-date

--socket,-S

--skip-opt

指定连接mysql的socket文件位置,默认路径/tmp/mysql.sock

禁用–opt选项.

mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

mysqldump  -uroot -p --host=localhost --all-databases --skip-opt

--tab,-T

--socket,-S

为每个表在给定路径创建tab分割的文本文件。注意:仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用--tab不能指定--databases参数

指定连接mysql的socket文件位置,默认路径/tmp/mysql.sock

mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"

mysqldump  -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock

--tables

--tab,-T

覆盖--databases (-B)参数,指定需要导出的表名,在后面的版本会使用table取代tables。

为每个表在给定路径创建tab分割的文本文件。注意:仅仅用于mysqldump和mysqld
        服务器运行在相同机器上。

mysqldump -uroot -p --host=localhost --databases test --tables test

mysqldump  -uroot -p --host=localhost test test --tab="/home/mysql"

--triggers

--tables

导出触发器。该选项默认启用,用--skip-triggers禁用它。

覆盖--databases (-B)参数,指定需要导出的表名。

mysqldump -uroot -p --host=localhost --all-databases --triggers

mysqldump  -uroot -p --host=localhost --databases test --tables test

--tz-utc

--triggers

在导出顶部设置时区TIME_ZONE=' 00:00' ,以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。

导出触发器。该选项默认启用,用--skip-triggers禁用它。

mysqldump -uroot -p --host=localhost --all-databases --tz-utc

澳门新濠3559,mysqldump  -uroot -p --host=localhost --all-databases --triggers

--user, -u

--tz-utc

指定连接的用户名。

在导出顶部设置时区TIME_ZONE=' 00:00' ,以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。

--verbose, --v

mysqldump  -uroot -p --host=localhost --all-databases --tz-utc

输出多种平台信息。

--user, -u

--version, -V

指定连接的用户名。

输出mysqldump版本信息并退出

--verbose, --v

--where, -w

输出多种平台信息。

只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将条件引用起来。

--version, -V

mysqldump -uroot -p --host=localhost --all-databases --where=" user='root'"

输出mysqldump版本信息并退出

--xml, -X

--where, -w

导出XML格式.

只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将
     条件引用起来。

mysqldump -uroot -p --host=localhost --all-databases --xml

mysqldump  -uroot -p --host=localhost --all-databases --where=” user=’root’”

--plugin_dir

--xml, -X

客户端插件的目录,用于兼容不同的插件版本。

导出XML格式.

mysqldump -uroot -p --host=localhost --all-databases --plugin_dir="/usr/local/lib/plugin"

mysqldump  -uroot -p --host=localhost --all-databases --xml

--default_auth

--plugin_dir

客户端插件默认使用权限。

客户端插件的目录,用于兼容不同的插件版本。

mysqldump -uroot -p --host=localhost --all-databases --default-auth="/usr/local/lib/plugin/<PLUGIN>"

mysqldump  -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”

创建测试数据库:

--default_auth

mysql> create table x(a int auto_increment primary key,b int, unique key (b));     #创建一个数据库用于测试

客户端插件默认使用权限。

mysql> insert into x select null,1; #插入数据

mysqldump  -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”

mysql> insert into x select null,2;

 

mysql> insert into x select null,3;

mysqldump备份:

mysql> insert into x select null,4;

mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" --lock-all-tables > 路径

mysql> create table x1(a int auto_increment primary key,b int, unique key (b)); #创建另一个数据库用于测试

案例:

mysql> insert into x1 select null,1; #插入数据

mysqldump -uroot -p1234 -hlocalhost db1 a -w "id in (select id from b)" --lock-all-tables > c:aa.txt

mysql> insert into x1 select null,2;

mysqldump还原:

mysql> insert into x1 select null,3;

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

mysql> insert into x1 select null,4;

案例:

1、导出所有数据库

mysql -uroot -p1234 db1 < c:aa.txt

[root@001 ~]# mysqldump -uroot -p123456 --all-databases >/tmp/all.sql #该命令会导出包括系统数据库在内的所有数据库

mysqldump按条件导出:

备份得来的是一个文本文件,里面一堆sql语句

mysqldump -u用户名 -p密码 -h主机 数据库  a --where "条件语句" --no-建表> 路径

2、导出mysql、testmysql两个数据库的所有数据

mysqldump -uroot -p1234 dbname a --where "tag='88'" --no-create-info> c:a.sql

[root@001 ~]# mysqldump -uroot -p123456 --databases mysql mysqltest > /tmp/mysql_mysqltest.sql

mysqldump按导入:

3、导出mysqltest中的x、x1表

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

[root@001 ~]# mysqldump -uroot -p123456 --databases mysqltest --tables x x1 >/tmp/x_x1.sql

案例:

注意导出指定表只能针对一个数据库进行导出,且导出的内容中和导出数据库也不一样,导出指定表的导出文本中没有创建数据库的判断语句,只有删除表-创建表-导入数据

mysql -uroot -p1234 db1 < c:a.txt

4、条件导出,导出x表a=1的数据

 

如果多个表的条件相同可以一次性导出多个表

**6.启用二进制日志(binlog) 
采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。 
启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 my.cnf,加入以下几行: 
server-id = 1 
log-bin = binlog 
log-bin-index = binlog.index 
然后启动 mysqld 就可以了。运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是 mysqld 
记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog 的信息请查看手册。 
需要备份时,可以先执行一下 SQL 语句,让 mysqld 终止对当前 binlog 
的写入,就可以把文件直接备份,这样的话就能达到增量备份的目的了: FLUSH LOGS;如果是备份复制系统中的从服务器,还应该备份 
master.info 和 relay-log.info 文件。 
备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如: 
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001该工具允许你显示指定的数据库下的所有 
SQL 语句,并且还可以限定时间范围,相当的方便,详细的请查看手册。 
恢复时,可以采用类似以下语句来做到: /usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 
| mysql -uyejr -pyejr db_name把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它。 
如果你有空闲的机器,不妨采用这种方式来备份。由于作为 slave 的机器性能要求相对不是那么高,因此成本低,用低成本就能实现增量备份而且还能分担一部分数据查询压力,何乐而不为呢? 

字段是整形:

**

[root@001 ~]# mysqldump -uroot -p123456 --databases mysqltest --tables x --where='a=1' >/tmp/a1.sql     #字段是字符串,并且导出的sql中不包含drop table,create table

 

[root@001 ~]# mysqldump -uroot -p123456 --no-create-info --databases mysqltest --tables x --where="a='1'" >/tmp/a2.sql

5、生成新的binlog文件,-F

有时候会希望导出数据之后生成一个新的binlog文件,只需要加上-F参数即可

[root@001 ~]# mysqldump -uroot -p123456 --databases mysqltest -F >/tmp/f_mysql.sql

6、只导出表结构不导出数据,--no-data

[root@001 ~]# mysqldump -uroot -p123456 --no-data --databases mysqltest >/tmp/nodata.sql 

7、跨服务器导出导入数据

[root@001 ~]# mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中,db2的数据库必须存在否则会报错

[root@001 ~]# mysqldump --host=192.168.80.137 -uroot -proot -C --databases test | mysql --host=192.168.80.133 -uroot -proot test

#加上-C参数可以启用压缩传递

8、将主库的binlog位置和文件名追加到导出数据的文件中,--dump-slave

注意:--dump-slave命令如果当前服务器是从服务器那么使用该命令会执行stop slave来获取master binlog的文件和位置,等备份完后会自动执行start slave启动从服务器。但是如果是大的数据量备份会给从和主的延时变的更大,使用--dump-slave获取到的只是当前的从服务器的数据执行到的主的binglog的位置是(relay_mater_log_file,exec_master_log_pos),而不是主服务器当前的binlog执行的位置,主要是取决于主从的数据延时。

该参数在在从服务器上执行,相当于执行show slave status。当设置为1时,将会以CHANGE MASTER命令输出到数据文件;设置为2时,会在change前加上注释。

该选项将会打开--lock-all-tables,除非--single-transaction被指定。在执行完后会自动关闭--lock-tables选项。--dump-slave默认是1

[root@001 ~]# mysqldump -uroot -proot --dump-slave=1 --databases db1 >/tmp/db1.sql

[root@001 ~]# mysqldump -uroot -proot --dump-slave=2 --database db1 >/tmp/db1.sql

9、将当前服务器的binlog的位置和文件名追加到输出文件,--master-data

该参数和--dump-slave方法一样,只是它是记录的是当前服务器的binlog,相当于执行show master status,状态(file,position)的值。注意:--master-data不会停止当前服务器的主从服务

10、--opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.

[root@001 ~]# mysqldump -uroot -p --host=localhost --all-databases –opt

11、保证导出的一致性状态--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎(它不显示加锁通过判断版本来对比数据),仅InnoDB。本选项和--lock-tables 选项是互斥的,因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。

--quick, -q

不缓冲查询,直接导出到标准输出。默认为打开状态,使用--skip-quick取消该选项。

12、--lock-tables, -l

开始导出前,锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,--single-transaction是一个更好的选择,因为它根本不需要锁定表。

请注意当导出多个数据库时,--lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

13、导出存储过程和自定义函数--routines, -R

[root@001 ~]# mysqldump -uroot -p --host=localhost --all-databases –routines

14、压缩备份 

[root@001 ~]# mysqldump -uroot -p123456 --databases mysqltest | gzip > abc.sql.gz

还原

[root@001 ~]# gunzip < abc.sql.gz | mysql -uroot -p123456 mysqltest # 如报错1804 需设置  mysql> reset master;

错误处理 

1.unknown option '--no-beep' 

第一种方法:删除my.ini[client]下的 no-beep 参数;

第二种方法:在 mysqldump 后加--no-defaults参数。

总结:

文章中列举了一些常用的导出操作,还有很多其它的参数也会经常用到,包括"--add-drop-database","--apply-slave-statements","--triggers"等。客户端的导入导出功能也是不错的选择,比如workbench、navicat;其中navicat的导出向导中可以有很多文件格式可以选择。

--tab的快速导出导入数据是个不错的方法,它会在指定的目录下生成一个sql表结构文件和一个text数据文件

转自: 作者:pursuer.chen

--mysqlpump实例步骤:

MySQL5.7之后多了一个备份工具:mysqlpump,官方的说法是用mysqlpump代替mysqldump因为mysqldump导出非常慢是单线程的而mysqlpump是多线程的,在社区版本中mydumper是多线程的。 mysqlpump会有问题,要在5.7.11之后使用【因为MySQL 5.7.11 版本解决了一致性备份问题】,mysqlpump的语法与mysqldump高度兼容,支持基于库和表的并行导出,对比mysqldump速度提升非常明显。

mysqlpump和mysqldump一样,属于逻辑备份,备份以SQL形式的文本保存。逻辑备份相对物理备份的好处是不关心undo log的大小,直接备份数据即可。它最主要的特点是:

  • 并行备份数据库和数据库中的对象的,加快备份过程。

  • 更好的控制数据库和数据库对象(表,存储过程,用户帐户)的备份。

  • 备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到MySQL的系统数据库。

  • 备份出来直接生成压缩后的备份文件。

  • 备份进度指示(估计值)。

  • 重新加载(还原)备份文件,先建表后插入数据最后建立索引,减少了索引维护开销,加快了还原速度。

  • 备份可以排除或则指定数据库。

  • 详细介绍:

这里使用MySQL官方的数据库进行测试操作详见:

[root@001 ~]# mysqlpump -uroot -p123456 --single-transaction --databases employees > employees.sql

mysqlpump: [Warning] Using a password on the command line interface can be insecure.

mysqlpump: [ERROR] (1) A partial dump from a server that has GTIDs is not allowed. #报错

Dump process encountered error and will not continue.

关于GTID是5.6以后,加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力所以可能是因为在一个数据库里面唯一,但是当导入其他的库就有可能重复。所有会有一个提醒。可以通过添加--set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。个人认为是在导入库中重新生产GTID,而不用原来的。

[root@001 ~]# mysqlpump -uroot -p123456 --single-transaction --set-gtid-purged=off --databases employees > employees.sql

澳门新濠3559 29

澳门新濠3559 30

--Cold Backup(冷备,物理备份):

对InnoDB存储引擎的冷备非常简单,只需要备份MySQL数据库的frm文件、共享表空间文件、独立表空间文件(*.ibd)、重做日志文件。另外,定期备份MySQL数据库的配置文件my.cnf,这样有利于恢复操作,适用:这种比较适合数据量大的数据库。

冷备的优点是:

1、备份简单,只要拷贝相关文件即可。

2、备份文件易于在不同操作系统、不同MySQL版本上进行恢复。

3、恢复相当简单,只需要把文件恢复到指定位置即可。

4、恢复速度快,不需要执行任何SQL语句,也不需要重建索引。

冷备的缺点是:

1、需要停止数据库

2、InnoDB存储引擎冷备的文件通常比逻辑文件大很多,因为表空间中存放着很多其他数据,如Undo段、插入缓冲等信息。

3、冷备并不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感和浮点数格式都会成为问题。

--直接复制数据库目录:

[root@001 ~]# service mysqld stop    #必须停止数据库

[root@001 ~]# cp -r /data/mysqldata/ /tmp/        #直接拷贝数据目录

[root@001 ~]# cp /etc/my.cnf /tmp/mysqldata/my.cnf    #建议备份配置文件

前面介绍mysqldump,mysqlpump备份方式是采用逻辑备份,其最大的缺陷就是备份和恢复速度都慢,对于一个小于50G的数据库而言,这个速度还是能接受的,但如果数据库非常大,那再使用mysqldump备份就不太适合了。而使用lvm快照功能对数据库进行备份,可以实现几乎热备的功能,但备份过程较为复杂,不过现在倒是有个工具mylvmbackup可以实现自动化备份。

目前主流的有两个工具可以实现热备:ibbackup和xtrabackup;ibbackup是商业软件,非常昂贵。而xtrabackup功能比ibbackup还要强大,但却是开源的。因此我们这里就来介绍xtrabackup的使用。

Xtrabackup是由percona提供的mysql数据库备份工具,据官方介绍,这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点:

  • (1)备份过程快速、物理备份可靠;

  • (2)备份过程不会打断正在执行的事务(无需锁表);

  • (3)能够基于压缩等功能节约磁盘空间和流量;

  • (4)自动实现备份检验;

  • (5)还原速度快;

  • (6)可以流传将备份传输到另外一台机器上;

    xtrabackup包含两个主要的工具,即xtrabackup和innobackupex,二者区别如下:

    Xtrabackup:只能备份innodb和xtradb两种引擎的表,而不能备份myisam引擎的表;
    
    Innobackup:是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份;
    

    Xtrabackup 使用方法很简单,这里只做几个简单的介绍具体安装及介绍详见:

**--使用innobackupex备份employees
数据库**

employees是MySQL官方提供的测试数据库:http://www.cnblogs.com/imweihao/p/7286926.html


**1****、完全备份**:

[root@001
~]# innobackupex --defaults-file=/etc/my.cnf --user=root
--password=123456 --socket=/data/mysqldata/mysqld.sock /data/


170805 16:30:34 completed
OK! #备份成功

#备份后的文件:在备份的同时,备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件。

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185110053-444147120.png)

各文件说明:

(1)
backup-my.cnf —— 备份命令用到的配置选项信息;

(2)
ibdata1 —— 备份的表空间文件;

(3)
xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件;

(4) xtrabackup_binlog_info ——
mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置;

(5) xtrabackup_checkpoints ——
备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;

6)
xtrabackup_logfile —— 备份的重做日志文件。

**1.1恢复完全备份**

先删除数据库

mysql&gt;
drop database employees; 

[root@001
~]# service mysqld stop 

[root@001
~]# mv /data/mysqldata/ /tmp/mysqldata/ 

[root@001 ~]# innobackupex
--defaults-file=/etc/my.cnf --user=root --password=123456
--use-memory=4G --apply-log /data/2017-08-05_17-12-14/ #第1步是apply-log,为了加快速度,一般建议设置--use-memory,这个步骤完成之后,目录就准备就绪

170805 16:44:56 completed
OK! #成功

[root@001 ~]# innobackupex
--defaults-file=/etc/my.cnf --user=root --password=123456
--copy-back /data/2017-08-05_17-12-14/ 
#第2步是copy-back,即把备份文件拷贝至原数据目录下。注意:innobackupex 增量备份仅针对InnoDB这类支持事务的引擎,对于MyISAM等引擎,则仍然是全备。

170805 16:49:32 completed
OK! #成功

[root@001
~]# chown -R mysql.mysql /data/ 

[root@001
~]# service mysqld start 

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185110350-1994368939.png)
![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185110740-1959099508.png)

**2、增量备份**

在进行增量备份时,首先要进行一次全量备份,第一次增量备份是基于全备的,之后的增量备份是基于上一次的增量备份,以此类推。

全备份放在/data/backup/full,增量备份放在/data/backup/incremental


2.1全备

[root@001
~]# mkdir -p /data/backup/full

[root@001
~]# mkdir -p /data/backup/incremental

[root@001
~]# innobackupex --defaults-file=/etc/my.cnf --user=root
--password=123456 --socket=/data/mysqldata/mysqld.sock
/data/backup/full

170805 17:42:09 completed
OK! #成功

为了测试效果,新建数据库并在表中插入数据

mysql&gt;
create database test charset = utf8mb4;

mysql&gt;
use test;

mysql&gt;
create table student1(id int auto_increment primary key,name
varchar(20) not null);

mysql&gt;
insert into student1 values(null,'will');

mysql&gt;
insert into student1 values(null,'tom');

mysql&gt;
insert into student1 values(null,'jim');

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185111053-320180344.png)

2.2第一次增量备份

[root@001
full]# innobackupex --defaults-file=/etc/my.cnf --user=root
--password=123456 --socket=/data/mysqldata/mysqld.sock --incremental
/data/backup/incremental/
--incremental-basedir=/data/backup/full/2017-08-05_18-06-44/
--parallel=2 

170805 17:50:47 completed
OK! #成功

注意:第一次备份的--incremental-basedir参数应指向完整备份的时间戳目录。

在test种创建student2,并插入数据然后创建增量备份2

mysql&gt;
use test; 

mysql&gt;
create table student2(id int auto_increment primary key,name
varchar(20) not null); 

mysql&gt;
insert into student2 values(null,'laoli');

mysql&gt;
insert into student2 values(null,'laowang');

mysql&gt;
insert into student2 values(null,'laozhen');

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185111615-1496254802.png)

2.3第二次增量备份(这次是基于上次的增量备份)

[root@001
full]# innobackupex --defaults-file=/etc/my.cnf --user=root
--password=123456 --socket=/data/mysqldata/mysqld.sock --incremental
/data/backup/incremental/
--incremental-basedir=/data/backup/incremental/2017-08-05_18-14-10/
--parallel=2 

注意:第二次备份的--incremental-basedir参数应指向第一次增量备份的时间戳目录。

**3、增量备份恢复**

增量备份的恢复大体为3个步骤

*恢复完全备份

*恢复增量备份到完全备份(开始恢复的增量备份要添加--redo-only参数,到最后一次增量备份去掉--redo-only参数)

*对整体的完全备份进行恢复,回滚那些未提交的数据

恢复完全备份(注意这里一定要加--redo-only参数,该参数的意思是只应用xtrabackup日志中已提交的事务数据,不回滚还未提交的数据)

1、[root@001
~]# innobackupex --apply-log --redo-only
/data/backup/full/2017-08-05_18-06-44/ 

2、将增量备份1应用到完全备份

[root@001
~]# innobackupex --apply-log --redo-only
/data/backup/full/2017-08-05_18-06-44/
--incremental-dir=/data/backup/incremental/2017-08-05_18-14-10


170805 18:30:52 completed
OK! **#ok** 

3、将增量备份2应用到完全备份(注意恢复最后一个增量备份时需要去掉--redo-only参数,回滚xtrabackup日志中那些还未提交的数据)

[root@001
~]# innobackupex --apply-log --redo-only
/data/backup/full/2017-08-05_18-06-44/
--incremental-dir=/data/backup/incremental/2017-08-05_18-15-43


170805 18:30:52 completed
OK! **#ok**

4.把所有合在一起的完全备份整体进行一次apply操作,回滚未提交的数据:

[root@001
~]# innobackupex --apply-log
/data/backup/full/2014-04-07_23-37-20/ 

170805 18:30:52 completed
OK! **#ok**

5.把恢复完的备份复制到数据库目录文件中,赋权,然后启动mysql数据库,检测数据正确性

先删除数据库

mysql&gt;
drop database test; 

mysql&gt;
drop database employees; 

[root@001
~]# service mysqld stop 

[root@001
~]# mv /data/mysqldata/ /tmp/mysqldata/ 

[root@001
~]# mkdir /data/mysqldata 

 

[root@001
~]# innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync
/data/backup/full/2017-08-05_18-06-44/ 

[root@001
~]# chown -R mysql.mysql /data/ 

[root@001
~]# service mysqld start 

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185111928-130496691.png)
![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185112178-1662331375.png)
![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185112537-911353310.png)

![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185112756-152090374.png)
![](https://images2017.cnblogs.com/blog/1160590/201708/1160590-20170805185112959-1436758007.png)

以上证明备份成功

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

克隆slave


在日常工作中,我们有时候需要在线添加从库,比如线上有一主一从两个数据库,但是由于业务的需要,一台从库的读取无法满足现在的需求,这样就需要我们在线添加从库,由于出于安全考虑,我们通常需要在从库上进行在线克隆slave。

克隆slave时,常用参数--slave-info和--safe-slave-backup。

--slave-info会将master的binlog文件名和偏移量位置保存到xtrabackup_slave_info文件中

--safe-slave-backup会暂停slave的SQL线程直到没有打开的临时表的时候开始备份。备份结束后SQL线程会自动启动,这样操作的目的主要是确保一致性的复制状态。

下面的例子,将介绍一主一从情况下在线搭建新的从库,环境如下:

master
192.168.0.10 #主库

slave
192.168.0.20 #从库

newslave
192.168.0.100 # 新的从库

在上述示例中,newslave即为要新搭建的从库。在老的从库上面进行备份:

[root@MySQL-02
~]# innobackupex --user=root --password=12345
--socket=/tmp/mysqld.sock --defaults-file=/etc/my.cnf --slave-info
--safe-slave-backup --no-timestamp /data/cloneslave 

innobackupex:
Backup created in directory '/data/cloneslave'

innobackupex:
MySQL binlog position: filename 'mysql-bin.000022', position
107

innobackupex:
MySQL slave binlog position: master host '192.168.0.10', filename
'mysql-bin.000006', position 732

140413
23:25:13 innobackupex: completed OK!

这里的/data/cloneslave
目录要不存在,如果存在是会报错的。

查看目录下生成的文件:

[root@MySQL-02
~]# ll /data/cloneslave/

total
26668

-rw-r--r--
1 root root 261 Apr 13 23:24 backup-my.cnf

-rw-r--r--
1 root root 27262976 Apr 13 23:24 ibdata1

drwxr-xr-x
2 root root 4096 Apr 13 23:25 mysql

drwxr-xr-x
2 root root 4096 Apr 13 23:25 performance_schema

drwxr-xr-x
2 root root 4096 Apr 13 23:25 sakila

drwxr-xr-x
2 root root 4096 Apr 13 23:25 world_innodb

-rw-r--r--
1 root root 13 Apr 13 23:25 xtrabackup_binary

-rw-r--r--
1 root root 23 Apr 13 23:25 xtrabackup_binlog_info

-rw-r--r--
1 root root 79 Apr 13 23:25 xtrabackup_checkpoints

-rw-r--r--
1 root root 2560 Apr 13 23:25 xtrabackup_logfile

-rw-r--r--
1 root root 72 Apr 13 23:25 xtrabackup_slave_info

drwxr-xr-x
2 root root 4096 Apr 13 23:25 yayun

[root@MySQL-02
~]# 

查看xtrabackup_slave_info文件内容,这个内容就是为搭建从库时需要change
master to的参数:

[root@MySQL-02
~]# cat /data/cloneslave/xtrabackup_slave_info 

CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=732


在老的slave服务器上进行还原,即192.168.0.20


[root@MySQL-02
~]# innobackupex --apply-log --redo-only /data/cloneslave/ 

xtrabackup:
starting shutdown with innodb_fast_shutdown = 1 

140413
23:30:37 InnoDB: Starting shutdown... 

140413
23:30:37 InnoDB: Shutdown completed; log sequence number 12981048


140413 23:30:37 innobackupex:
completed OK! 

将还原的文件复制到新的从库newslave,即192.168.0.100


[root@MySQL-02
data]# rsync -avprP -e ssh /data/cloneslave/
192.168.0.100:/data/mysql/ 

在主库master上添加对新从库newslave的授权:

mysql&gt;
grant replication slave on *.* to 'repl'@'192.168.0.100'
identified by '123456';

Query
OK, 0 rows affected (0.00 sec)

 

mysql&gt;
flush privileges;

Query
OK, 0 rows affected (0.02 sec)

拷贝老的从库的配置文件到新的从库newslave,并且修改server-id参数,修改完毕后,启动新的从库;

[root@MySQL-02
data]# scp /etc/my.cnf 192.168.0.100:/etc/ 

root@192.168.0.100's
password: 

my.cnf
100% 4881 4.8KB/s 00:00 

[root@MySQL-02
data]# 

[root@newslave
mysql]# egrep 'log-slave|^server-id|skip_slave' /etc/my.cnf


server-id
= 3 

skip_slave_start


log-slave-updates=1


[root@newslave
mysql]# 

[root@newslave
mysql]# chown -R mysql.mysql . 

[root@newslave
mysql]# /etc/init.d/mysqld restart 

Shutting
down MySQL. [ OK ] 

Starting
MySQL.. [ OK ] 

[root@newslave
mysql]# 

查找老的从库备份后生成的xtrabackup_slave_info文件,提取其中的master_log_file和master_log_pos信息,然后在新的从库上进行change
master to操作:

在新的从库上进行同步:

mysql&gt;
CHANGE MASTER TO MASTER_HOST='192.168.0.10',MASTER_USER='repl',
MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=732;

Query
OK, 0 rows affected (0.09 sec)

mysql&gt;


启动io线程和sql线程,并观察复制是否正常:

mysql&gt;
start slave;

Query
OK, 0 rows affected (0.00 sec)

mysql&gt;
show slave status\G

*************************** 1.
row ***************************

Slave_IO_State:
Waiting for master to send event

Master_Host:
192.168.0.10

Master_User:
repl

Master_Port:
3306

Connect_Retry:
2

Master_Log_File:
mysql-bin.000006

Read_Master_Log_Pos:
1309

Relay_Log_File:
MySQL-02-relay-bin.000002

Relay_Log_Pos:
830

Relay_Master_Log_File:
mysql-bin.000006

Slave_IO_Running:
Yes

Slave_SQL_Running:
Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:
yayun.%

Replicate_Wild_Ignore_Table:

Last_Errno:
0

Last_Error:

Skip_Counter:
0

Exec_Master_Log_Pos:
1309

Relay_Log_Space:
989

Until_Condition:
None

Until_Log_File:

Until_Log_Pos:
0

Master_SSL_Allowed:
No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master:
0

Master_SSL_Verify_Server_Cert:
No

Last_IO_Errno:
0

Last_IO_Error:

Last_SQL_Errno:
0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:
1

1
row in set (0.00 sec)

查看主库,发现已经有两个线程(Binlog
Dump) 

mysql&gt;
show processlist\G

*************************** 1.
row ***************************

Id:
8

User:
slave

Host:
192.168.0.20:44251

db:
NULL

Command:
Binlog Dump

Time:
1088

State:
Master has sent all binlog to slave; waiting for binlog to be
updated

Info:
NULL

*************************** 2.
row ***************************

Id:
9

User:
root

Host:
localhost

db:
yayun

Command:
Query

Time:
0

State:
NULL

Info:
show processlist

*************************** 3.
row ***************************

Id:
10

User:
repl

Host:
192.168.0.100:45844

db:
NULL

Command:
Binlog Dump

Time:
124

State:
Master has sent all binlog to slave; waiting for binlog to be
updated

Info:
NULL

3
rows in set (0.00 sec)

正常工作,到此在线克隆slave就结束啦。

参考:

https://yq.aliyun.com/articles/45746

http://blog.csdn.net/heizistudio/article/details/23937935

Mysql5.7.18安装:http://www.cnblogs.com/imweihao/p/7201014.html


Mysql5.6.36安装:http://www.cnblogs.com/imweihao/p/7156754.html

编辑:数据库 本文来源:因需求改变而需要把数据还原到改变以前,load

关键词: 澳门新濠3559