MySQL导入导出常用命令_MySQL

bitsCN.com

导出脚本1: 表元数据, 存储过程, 函数, 视图, 小表数据(不包含元数据)的导出

rem 每次导出到当前日期文件夹  @echo wscript.echo dateadd("d",0,date)>GetOldDate.vbs@for /f %%a in ('cscript /nologo GetOldDate.vbs') do @set OldDate=%%aecho %OldDate%@del GetOldDate.vbs@rem 替换/为-@set Current_Date=%OldDate:/=-%echo %Current_Date%set Prefix_Folder=D:/Database_Backup/Backup_Daily/sqlset Dump_IP="localhost"set User_Name="root"set Password=""set Database="db_name"mkdir %Prefix_Folder%/%Current_Date%/rem 整库元数据及存储过程,函数等mysqldump -h %Dump_IP% -u%User_Name% -p%Password% -d %Database% >%Prefix_Folder%/%Current_Date%/create_tables.sqlmysqldump -h %Dump_IP% -u%User_Name% -p%Password% -ntd -R -E %Database% --triggers=false >%Prefix_Folder%/%Current_Date%/sp_fn.sqlrem 某表的数据mysqldump -h %Dump_IP% -u%User_Name% -p%Password% -t %Database% table_name>%Prefix_Folder%/%Current_Date%/table_name.sql

导出脚本2: 每月月初导出上个月的数据, 数据量稍大的导出

@del GetOldDate.vbs@rem 替换/为-@set Current_Date=%OldDate:/=-%echo %Current_Date%set Prefix_Folder=D:/Database_Backup/Backup_Monthly/sqlset Dump_IP="localhost"set User_Name="root"set Password=""set Database="db_name"mkdir %Prefix_Folder%/%Current_Date%/rem 导出上个月的数据mysqldump -h %Dump_IP% -u%User_Name% -p%Password% -t %Database% article_detail --where="Extracted_Time between date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 1 month) and date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 0 month)" --skip-opt -e --max_allowed_packet=10240000 --net_buffer_length=16384 --triggers=false >%Prefix_Folder%/%Current_Date%/article_detail.sql

导入脚本:

rem 需要手工设置: 设置需要导入的数据库服务器IP, 在数据库服务器本机操作Dump_IP可用localhostset Dump_IP=localhostset User_Name=rootset Password=set Database=db_nameset Folder_Dir="D:/Database_Backup/Backup_Monthly/sql/2012-6-9"mysql -h %Dump_IP% -u%User_Name% -p%Password% %Database% bitsCN.com