MySQL Data Backup under Window

Today, I had a friend who asked me to help him back up the MySQL database on the windowserver server, so it took a day to complete a daily scheduled backup of the database, a small part here for everyone to record:

First, for the MySQL command line import and export as a simple comb

First, import

①mysql-h Host-u account-p password db < db.sql[storage path, same as]--Import data for DB database/mysqljump-h Address-u account-p password db < db.sql--import data for DB database

②mysql-h Host-u account-p password DB table1 < db.table1.sql--import data for DB Database table1 table/mysqljump-h Host-u account-p password db table1 < Db.table1 . SQL--Import data for DB database table1 table

③source database file path (. sql)

Second, export

①mysqldump Tool Export

Mysqldump-h Host-u account-p password database > Storage path (db.sql--> by default in the Bin directory under MySQL installation path)------------Export the database

Mysqldump-h Host-u account-p password database data sheet > Storage path------------a table below the export datasheet

　　　　　　

REM means that only the results are displayed while the batch file is executing, not the commands executed. Echo on is obviously the opposite of echo off
@echo off
REM Open delay variable expansion setlocal enabledelayedexpansion
REM set is defined as assignment variable assignment variable = cannot have space before or after REM and:: All acts as a comment set mysql_data_path=D:\mysqldata2\dataset back_path=D:\data_bak\[email protected] yy=%date:~0,4%@set mm=%date:~5,2%@set dd=%date:~8,2%@set datedir=%yy%-%mm%-%dd% if not exist%back_path%%datedir%(Echo mkdir%back_path%%datedir%MD%back_path%%datedir%) Set "Filename1=%date:~0,4%-%date:~5,2%-%date:~8,2%\contractunit.sql"Set "Filename2=%date:~0,4%-%date:~5,2%-%date:~8,2%\esbdb.sql"Set "Filename3=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_centerbookdb.sql"Set "Filename4=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_contractunit.sql"Set "Filename5=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_customerdb.sql"Set "Filename6=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_esbdb.sql"Set "Filename7=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_membercomponentdb.sql"Set "Filename8=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_stockdb.sql"Set "Filename9=%date:~0,4%-%date:~5,2%-%date:~8,2%\g_systemcomponentdb.sql"Set "Filename10=%date:~0,4%-%date:~5,2%-%date:~8,2%\jccrmdb.sql"Set "Filename11=%date:~0,4%-%date:~5,2%-%date:~8,2%\jddasynreqdb.sql"Set "Filename12=%date:~0,4%-%date:~5,2%-%date:~8,2%\mysql.sql"Set "Filename13=%date:~0,4%-%date:~5,2%-%date:~8,2%\shopdb.sql"Set "Filename14=%date:~0,4%-%date:~5,2%-%date:~8,2%\stockdb.sql"Set "Filename15=%date:~0,4%-%date:~5,2%-%date:~8,2%\tcontractunit.sql"Set "Filename16=%date:~0,4%-%date:~5,2%-%date:~8,2%\tjccrmdb.sql"Set "Filename17=%date:~0,4%-%date:~5,2%-%date:~8,2%\tjddasynreqdb.sql"Set "Filename18=%date:~0,4%-%date:~5,2%-%date:~8,2%\tshopdb.sql"Set "Filename19=%date:~0,4%-%date:~5,2%-%date:~8,2%\tstockdb.sql" "C:\Program files\mysql\mysql Server 5.6\bin\ Mysqldump.exe "-uqtsa [email protected]--opt--default-character-set=utf8-e--triggers-r--hex-blob-- Flush-logs-x contractunit >%back_path%!filename1! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x esbdb >%back_path%!filename2! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_centerbookdb >%back_path%!filename3! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_contractunit >%back_path%!filename4! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt--Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_customerdb >%back_path%!filename5! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_esbdb >%back_path%!filename6! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_membercomponentdb >%back_path%!filename7! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_stockdb >%back_path%!filename8! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x g_systemcomponentdb >%back_path%!filename9! "C:\Program Files\mysql\mysql Server 5.6\bIn\mysqldump.exe "-uqtsa [email protected]--opt--default-character-set=utf8-e--triggers-r--hex-blob-- Flush-logs-x jccrmdb >%back_path%!filename10! " C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x jddasynreqdb >%back_path%!filename11! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x mysql >%back_path%!filename12! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x shopdb >%back_path%!filename13! "C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe"-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x stockdb >%back_path%!filename14! " C:\ProgramFiles\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt--default-character-set=utf8-e-- Triggers-r--hex-blob--flush-logs-x tcontractunit >%back_path%!filename15! " C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x tjccrmdb >%back_path%!filename16! " C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x tjddasynreqdb >%back_path%!filename17! " C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x tshopdb >%back_path%!filename18! " C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x tstockdb &Gt %back_path%!filename19!rem d:rem cd%mysql_data_path% rem for/r%%s in (., *) do (REM if exist percentS\nul (rem if "%%s" NEQ "%mys ql_data_path%. " (rem set a=%%~nxs rem Set "filename=%date:~0,4%-%date:~5,2%-%date:~8,2%\!a!. SQL " REM" C:\Program files\mysql\mysql Server 5.6\bin\mysqldump.exe "-uqtsa [email protected]--opt-- Default-character-set=utf8-e--triggers-r--hex-blob--flush-logs-x!dbname! >%back_path%!filename! REM) REM) REM) REM does not know why this for loop export data is problematic, eh! You know, friends, leave you a word. Echo backup Success#pause        

-----------------This bat programming is learned today, not too much to understand.

② Copy Data folder backup

　　　　　　　　　You must close MySQL service net stop MySQL (if you join the system service) before operation

　　　　　　

Code start****************************    net stop MySQL    xcopy c:\mysql\ Data\bbs\*.* c:\db_bak\bbs\%date:~0,10%\/S/I    net start Mysql*******************************code End******* **********************

③ using WinRAR to perform scheduled backups of MySQL database

The best way to backup MySQL is to back up the data directory of the MySQL database directly. The following provides a way to use WinRAR to perform a scheduled backup of the data directory.

Code start*****************************net stop Mysqlc:\progra~1\winrar\winrar A-ag-k-R-s D:\mysql.rar d:\mysql\datanet start Mysql*******************************code end******************** *********

Tips: The above all the path is the path of the use of small series of environment, if you use please according to their own path to rewrite the program, at the same time for the BAT programming please Baidu study, because I also study today, slightly understand one or two!

For the last timer, we can use the Task Scheduler to set a daily scheduled task for Windows, and for Linux we can use crontab to handle it. And there's Linux. You can also use mysqldump source to export the import database

　　　　　　　　

　　　　　　

MySQL Data Backup under Window