标签 "mysqldump" 下的文章

今天飘云有在帮一个网友搬家网站过程中,习惯导出MySQL数据库的时候采用mysqldump命令,但是意外发生了出现"Warning: Using a password on the command line interface can be insecure."的错误提示,当然数据库肯定也没有能备份下来。这个问题应该是在MySQL5.6+版本的时候就有出现,可能是为了确保数据库的安全性采用的保护机制。

遇到问题那就去解决问题,大概搜索到国内的一些网站,大部分都是复制的,也没有讲的明白,于是还是找老外的信息,于是飘云就整理到下面比较全的方法且经过验证是没有问题的。

第一种方法、修改数据库配置文件

1、我们需要修改数据库配置文件,这个要看我们数据库的配置的,有些是在/etc/my.cnf,有些是/etc/my.conf、my.ini

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

我们需要在[mysqldump]部分添加脚本:(注:添加在[mysqldump]字段下面,安全性比较好一点,网上的有些是添加到:[client] 字段下面也可以)
mysql56-waring

host=localhost
user=数据库用户piaoyun
password='数据库密码piaoyuncc'

这里参数要修改成我们自己的。

2、采用命令导出和导入数据库

其实在这个时候,我们如果采用"详解使用mysqldump命令备份还原MySQL数据用法整理"介绍的方法也是可以使用的,虽然依旧有错误提示,但是数据库还是可以导出的。您肯定和飘云一样是追求细节的人,一点点问题都不能有,但我们可以用下面的命令导出和导入,就没有错误提示。

#导出数据库

"D:\xampps\mysql\bin\mysqldump.exe" --defaults-extra-file=D:\xampps\mysql\bin\my.ini--default-character-set=utf8 --databases piaoyuncc>E:\SQL\piaoyuncc.sql

#导入数据库

"D:\xampps\mysql\bin\mysqldump.exe" --defaults-extra-file=D:\xampps\mysql\bin\my.ini--default-character-set=utf8 --databases piaoyuncc < E:\SQL\piaoyuncc.sql

这里我们可以看到上面的命令和以前常用的快速导入和导入命令有所不同了,需要加载我们配置的MYSQL配置文件,这个红色部分要根据我们实际的路径修改。用这样的命令导出备份和导入是没有错误提示的。

第二种方法、利用mysql_config_editor

1、设置加密模式

mysql_config_editor set --login-path=local --host=localhost --user=db_user --password

红色部分是需要修改成我们自己数据库用户名的,回车之后会提示我们输入数据库密码,我们照样输入。

2、执行备份

mysqldump -u db_user -pInsecurePassword my_database | gzip > piaoyun.tar.gz

根据我们数据信息修改用户和用户名和数据库密码,执行备份,这里飘云测试还是有错误提示,但数据库是可以备份的。

总之,我们只要实现结果,可以选择以上2种方法其一操作,当然博主飘云也有看到其他的方法,这里就不多分享了,如果有兴趣的可以搜索其他解决方法。

需求:
有一台windows服务器上跑着mysql的一些应用,现在需要将mysql的数据每天备份,并通过ftp上传到指定的存储服务器上
要是在linux上,shell 脚本很容易就搞定了,在windows上习惯了点鼠标,哪里来的脚本,一时还真不知道该怎么弄.baidu,google得知可以通过批处理或者vbs脚本实现.
批处理是比较简单的,毕竟dos的常用命令还是知道的

我的服务器为Win2008,项目的数据库引擎采用的是MyIsam,数量大并不大,运营到现在3个月,数据库大小160M。为了防止出现意外,数据库每天都需要备份,当然,如果每天手工备份,那就要把人烦死。所以我就写了下面的批处理文件,通过Win2008系统的计划任务,在凌晨2点开始执行批处理,备份数据库。批处理中我采用了两种方法,一种是通过mysqldump命令备份,并通过gzip压缩数据。另一种是备份物理文件,通过调用rar命令压缩数据。至今,这个数据库备份批处理文件运行近1个月,目前看来效果符合预期。

解决思路:
使用批处理文件,实现备份和上传的功能
将批处理文件加到计划任务中实现每日定时执行脚本
完整的批处理文件如下 文件名dbbk.bat

@echo off
:: this batch is to backup the db files on windows to linux server
:: created by yahoon
:: 2008.4.9

::backup the mysql db
mysqldump -uroot –pxxxx demo>c:\demo %date:~0,10%.sql

::pause
:: wait press any key to continue
:: ready to delete

::form the ftp command file
echo open 服务器ip>c:\ftpdemo.txt
echo ftp用户名>>c:\ftpdemo.txt
echo ftp密码>>c:\ftpdemo.txt
echo put c:\ demo %date:~0,10%.sql >>c:\ftpdemo.txt
echo bye>>c:\ftpdemo.txt

::transfer the file to server via ftp
ftp -i -s:c:\ftpdemo.txt

::pause

::clean the tmp files
del c:\ftpdemo.txt
del c:\ demo %date:~0,10%.sql

说明:
1. 双冒号::后面的内容为注释,不会执行
2. 两处pause语句是调试的时候用的,运行pause的结果是屏幕上显示”按任意键继续”,脚本等着用户按键才会往下执行
3. demo %date:~0,10%.sql 是备份的文件名,最后的结果是demo20080409.sql ,其中%date:~0,10%表示的是date命令输出的字符串中从第0个开始的10个字符.这点很重要,因为我在英文的OS上,执行这个脚本在备份的时候报文件名错误.这是因为当你执行date命令的时候结果是
Tue 04/08/2008
要达到demo20080409.sql这样的效果文件名就要这么写demo%date:~10,4%%date:~4,2%%date:~7,2%.sql
最后提一下写的过程中解决的一个问题
由于脚本的思路是做备份,然后通过ftp上传
所以最开始写的时候没有::form the ftp command file 这一段.而是写好一个ftpdemo.txt放在那
这就出现了一个问题,由于生成的sql文件名每天都变,这样ftp命令也得改.写死的ftpdemo.txt文件肯定就实现不了.
想出了个笨主意是批处理文件里面生成的sql文件就定义死文件名为demo.sql,这样ftpdemo.txt文件也可以写死.然后等上传以后,再通过linux的脚本通过crontab来每天改名.

下面先看一下备份mySQL数据库的批处理代码:

    @ECHO OFF  
    set path=%SystemRoot%\system32;D:\PHP\MySql\MySQL Server 5.1\bin;C:\Program Files\WinRAR  
    set mktime=%date:~0,10%  
      
    ::设置数据库名称(两个数据库,则下面的for中的tokens值为1,2。如果有三个数据库,则tokens值为:1,2,3。数据库依次为%%a,%%b,%%c....按英文字母顺序排列)  
    ::这里备份三个数据库:DB_A、DB_B、DB_C,数据库间用半角逗号分隔  
    SET dbname=DB_A,DB_B,DB_C  
      
    ::数据库管理员权限及密码  
    SET dbuser=root  
    SET dbpasswd=123456  
      
    ::mySQL所在目录  
    SET mysqldir=D:\PHP\MySql\MySQL Server 5.1  
      
    ::备份gz文件所在目录  
    SET gz_backupdir=D:\mySQL_data_bak\sql_gz_bak  
    if not exist %gz_backupdir% md %gz_backupdir%  
      
    ::备份file文件所在目录  
    SET file_backupdir=D:\mySQL_data_bak\file_bak  
    if not exist %file_backupdir% md %file_backupdir%  
      
    ::::停止WWW网页WEB服务::::或者停止Apache2 命令:net stop Apache2  
    net stop w3svc  
      
    ::::延时15秒  
    ping -n 15 127.0.1>nul  
      
    ::::开始备份mySQL数据库::::  
    @ECHO Beginning backup of %dbname%...  
    for /f "tokens=1,2,3 delims=," %%a in ("%dbname%") do (  
      if not exist %gz_backupdir%\%%a md %gz_backupdir%\%%a  
      if not exist %gz_backupdir%\%%b md %gz_backupdir%\%%b  
      if not exist %gz_backupdir%\%%c md %gz_backupdir%\%%c  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%a | gzip > %gz_backupdir%\%%a\%mktime%.sql.gz  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%b | gzip > %gz_backupdir%\%%b\%mktime%.sql.gz  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%c | gzip > %gz_backupdir%\%%c\%mktime%.sql.gz  
    )  
      
    ::停止mySQL服务,以便拷备文件  
    net stop MySQL  
      
    ::延时10秒  
    ping -n 10 127.0.1>nul  
      
    ::开始将要备份的文件打包到仓库  
    d:  
    cd "%mysqldir%\data"  
    for /f "tokens=1,2,3 delims=," %%a in ("%dbname%") do (  
      if not exist %file_backupdir%\%%a md %file_backupdir%\%%a  
      if not exist %file_backupdir%\%%b md %file_backupdir%\%%b  
      if not exist %file_backupdir%\%%c md %file_backupdir%\%%c  
      @rar a %file_backupdir%\%%a\%mktime%.rar %%a  
      @rar a %file_backupdir%\%%b\%mktime%.rar %%b  
      @rar a %file_backupdir%\%%c\%mktime%.rar %%c  
    )  
      
    ::::启动MySQL::::  
    net start MySQL  
      
    ::::启动IIS::::如果WEB服务器是Apache,则去掉下面一行,使用: net start Apache2  
    net start w3svc  
      
    exit

对于上面的内容,做以下几点补充说明

1、由于采用两种方式备份,为了不混淆,所以以mysqldump方式备份的,由于通过gzip压缩,所以后缀名为.gz,这种文件我保存在D:\mySQL_data_bak目录下的sql_gz_bak目录下。以物理备份文件方式的,由于通过rar压缩,后缀名为.rar,这种文件我保存在D:\mySQL_data_bak目录下的file_bak目录下。备份的文件名是变量mktime决定的,mktime=%date:~0,10%,意思是把当前时间从位置0开始,截取10个字符,那么%data%为:2012-08-31 星期五 取得的结果就为:2012-08-31

2、设置备份数据库名称。上面的例子中备份了三个数据库。分别为DB_A、DB_B、DB_C,这个根据你实际情况备份你需要的数据库了。这里要注意的是,由于这在里统一配置要配备的数据库,那么在下面要通过批处理的for命令来遍历,依次读取需要备份的数据库名称。所以,如果你备份的数据库恰好不是3个,那么你要改动上面的代码。

备份一个数据库的情况:

    省略...  
    SET dbname=DB_A  
    省略...  
    ::::开始备份mySQL数据库::::  
    @ECHO Beginning backup of %dbname%...  
    for /f "tokens=1 delims=," %%a in ("%dbname%") do (  
      if not exist %gz_backupdir%\%%a md %gz_backupdir%\%%a  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%a | gzip > %gz_backupdir%\%%a\%mktime%.sql.gz  
    )  
    省略...  
    for /f "tokens=1 delims=," %%a in ("%dbname%") do (  
      if not exist %file_backupdir%\%%a md %file_backupdir%\%%a  
      @rar a %file_backupdir%\%%a\%mktime%.rar %%a  
    )  
    省略...

备份两个数据库的情况:

    省略...  
    SET dbname=DB_A,DB_B  
    省略...  
    ::::开始备份mySQL数据库::::  
    @ECHO Beginning backup of %dbname%...  
    for /f "tokens=1,2 delims=," %%a in ("%dbname%") do (  
      if not exist %gz_backupdir%\%%a md %gz_backupdir%\%%a  
      if not exist %gz_backupdir%\%%b md %gz_backupdir%\%%b  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%a | gzip > %gz_backupdir%\%%a\%mktime%.sql.gz  
      "%mysqldir%\bin\mysqldump" -u %dbuser% -p%dbpasswd% --default-character-set=utf8 --opt %%b | gzip > %gz_backupdir%\%%b\%mktime%.sql.gz  
    )  
    省略...  
    for /f "tokens=1,2 delims=," %%a in ("%dbname%") do (  
      if not exist %file_backupdir%\%%a md %file_backupdir%\%%a  
      if not exist %file_backupdir%\%%b md %file_backupdir%\%%b  
      @rar a %file_backupdir%\%%a\%mktime%.rar %%a  
      @rar a %file_backupdir%\%%b\%mktime%.rar %%b  
    )  
    省略...

3、在备份数据库前停止WEB服务,是为了确保备份数据库的一致性。
4、其它参数配置,根据实际情况改动。
5、通过gzip压缩,需要将gzip.exe放到mySQL目录下的bin目录中。

还原数据库

由于备份时采用两种方式备份数据库,所以还原数据库时,应用两种方法分开还原。所以还原数据库的批处理命令为两个批处理文件。

通过SQL文件(GZ)还原.bat

    @ECHO OFF  
    ::更改数据库名称  
    SET dbname=DB_A  
      
    ::更改欲还原的数据库备份文件  
    SET filename=xxxx-xx-xx.sql.gz  
      
    set path=%SystemRoot%\system32;D:\PHP\MySql\MySQL Server 5.1\bin  
      
    ::::停止WWW网页WEB服务::::或者停止Apache2 命令:net stop Apache2  
    net stop w3svc  
      
    ::::延时15秒  
    ping -n 15 127.0.1>nul  
      
    ::数据库管理员权限及密码  
    SET dbuser=root  
    SET dbpasswd=123456  
      
    ::要还原的gzip文件路径  
    SET filename=D:\mySQL_data_bak\sql_gz_bak\%dbname%\%filename%  
      
    @ECHO Beginning restore of %dbname%...  
    gunzip < %filename% | mysql -u %dbuser% -p%dbpasswd% %dbname%  
      
    ::::启动IIS::::如果WEB服务器是Apache,则去掉下面一行,使用: net start Apache2  
    net start w3svc

通过备份文件(RAR)还原.bat

    @ECHO OFF  
    set path=%SystemRoot%\system32;D:\PHP\MySql\MySQL Server 5.1\bin;C:\Program Files\WinRAR  
    set mktime=%date:~0,10%  
      
    ::设置数据库名称  
    SET dbname=DB_A  
      
    ::设置欲还原的压缩文件包文件名  
    SET filename=xxxx-xx-xx.rar  
      
    ::mySQL所在目录  
    SET mysqldir=D:\PHP\MySql\MySQL Server 5.1  
      
    ::备份的file文件所在目录  
    SET file_backupdir=D:\mySQL_data_bak\file_bak\%dbname%  
      
    if not exist "%mysqldir%\data\%dbname%" md "%mysqldir%\data\%dbname%"  
      
    ::::停止WWW网页WEB服务::::或者停止Apache2 命令:net stop Apache2  
    net stop w3svc  
      
    ::::延时15秒  
    ping -n 15 127.0.1>nul  
      
    ::停止mySQL服务,以便拷备文件  
    net stop MySQL  
      
    ::延时10秒  
    ping -n 10 127.0.1>nul  
      
    ::开始将要备份的文件打包到仓库  
    cd /d "%mysqldir%\data"  
    :: e表示解压 -o+ 表示替换原有文件  
    rar e -o+ %file_backupdir%\%filename% "%mysqldir%\data\%dbname%"  
      
    ::::启动MySQL::::  
    net start MySQL  
      
    ::::启动IIS::::如果WEB服务器是Apache,则去掉下面一行,使用: net start Apache2  
    net start w3svc  
    exit  

 

@echo on 

REM------------------------backup bugdb which is InnoDB-----------------------------   
del C:/backup/website/bugdb_*.sql   
cd F:/usr/wamp/mysql/bin   
set year=%date:~0,4%   
set month=%date:~5,2%   
set day=%date:~8,2%   
set filename=piaoyun_%year%%month%%day%.sql   
mysqldump.exe piaoyun -uroot -p123456 > F:/backup/piaoyun.cc/%filename%   
@echo off

mysqldump命令用法

MySQL数据库的备份工具有很多,不过这里介绍的是一个小巧好用的mysqldump工具,位于数据库目录下bin/mysqldump.exe.这几天用phpMyAdmin备份数据库的时候出现乱码,反复在本地跟远程的Dreamhost空间上测试了数据库,但是还是导出数据库会出现乱码,应该是phpMyAdmin出现的问题,数据库本身没有问题.扯远咯,呵呵.我Google到的关于mysqldump工具的相关用法.
如果主机主机支持 Shell 的话,可以 SSH 登陆主机,执行如下的命令实现备份与恢复,适合在Dreamhost,Bluehost等空间上.

导出数据库:
mysqldump -h HOSTNAME -uUSERNAME -p DBNAME > exported_db.sql

导入数据库:
mysql -h HOSTNAME -uUSERNAME -p DBNAME < exported_db.sql

HOSTNAME 是数据库主机名,USERNAME 和 DBNAME 分别是登陆 ID 和数据库名,导出的数据库存放在 exported_db.sql 文件里。 巧克力工厂(Beta5):Wordpress 超大数据库的导入.

使用mysqldump:
(mysqldump命令位于mysql/bin/目录中) //要专到mysql/bin/目录中才能使用,直接cmd运行命令窗口使用不了,专到数据库所在的mysql/bin/目录中使用.

基本用法是: shell> mysqldump [OPTIONS] database [tables]

如果你不给定任何表,整个数据库将被导出。

通过执行mysqldump –help,你能得到你mysqldump的版本支持的选项表。

注意,如果你运行mysqldump没有–quick或–opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题。

几个常用用例:

1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u root -p dataname >dataname.sql
这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中.

2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u root -p dataname users> dataname_users.sql

3.导出一个数据库结构
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:/wcnc_db.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.导入数据库
常用source 命令
进入mysql数据库控制台,
如mysql -u root -p

mysql>use 数据库

然后使用source命令,后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/wcnc_db.sql

mysqldump支持下列选项:
–add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。

–add-drop-table
在每个create语句之前增加一个drop table。

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

-c, –complete-insert
使用完整的insert语句(用列名字)。

-C, –compress
如果客户和服务器均支持压缩,压缩两者间所有的信息。

–delayed
用INSERT DELAYED命令插入行。

-e, –extended-insert
使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)

-#, –debug[=option_string]
跟踪程序的使用(为了调试)。

–help
显示一条帮助消息并且退出。

–fields-terminated-by=…

–fields-enclosed-by=…

–fields-optionally-enclosed-by=…

–fields-escaped-by=…

–fields-terminated-by=…

这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。
LOAD DATA INFILE语法。

-F, –flush-logs
在开始导出前,洗掉在MySQL服务器中的日志文件。

-f, –force,
即使我们在一个表导出期间得到一个SQL错误,继续。

-h, –host=..
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。

-l, –lock-tables.
为开始导出锁定所有表。

-t, –no-create-info
不写入表创建信息(CREATE TABLE语句)

-d, –no-data
不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!

–opt
同–quick –add-drop-table –add-locks –extended-insert –lock-tables。
应该给你为读入一个MySQL服务器的尽可能最快的导出。

-pyour_pass, –password[=your_pass]
与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。

-P port_num, –port=port_num
与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)

-q, –quick
不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。

-S /path/to/socket, –socket=/path/to/socket
与localhost连接时(它是缺省主机)使用的套接字文件。

-T, –tab=path-to-some-directory
对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和 –lines–xxx选项来定。

-u user_name, –user=user_name
与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。

-O var=option, –set-variable var=option
设置一个变量的值。可能的变量被列在下面。

-v, –verbose
冗长模式。打印出程序所做的更多的信息。

-V, –version
打印版本信息并且退出。

-w, –where=’where-condition’
只导出被选择了的记录;注意引号是强制的!
“–where=user=’jimf’” “-wuserid>1″ “-wuserid<1″

最常见的mysqldump使用可能制作整个数据库的一个备份:
mysqldump –opt database > backup-file.sql

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:
mysqldump –opt database | mysql –host=remote-host -C database
由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:

shell> mysqladmin create target_db_name
shell> mysql target_db_name < backup-file.sql
就是
shell> mysql 库名 < 文件名