Environment:
The database IP to be backed up is 192.168.28.1 account test1, password 123456
The local database IP is 192.168.28.2 account test1, password 123456
Full backup is divided into settings database master-slave synchronization and not set master-slave synchronization
To set up a master-slave synchronization database:
@echo off
Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:\Program files\mysql\mysql Server 5.6\bin\mysqldump"-utest1--password=123456-h192.168.28.1--flush_ Logs--single-transaction--master_data=2--all-databases--delete_master_logs > E:\mysqlbk\full_sql\Backup __%ymd%.sql
REM Parameter Description:
REM Flush_logs: Refreshes the binary log, and a binary log file is newly created and enabled
REM Single-transaction:innodb table when backing up, the option--single-transaction is usually enabled to guarantee the consistency of the backup, in fact it works by setting the isolation level for this session to: Repeatable READ, To ensure that this session (dump) does not see data that has been submitted by other sessions.
The REM master_data=2:master-data parameter is often used when establishing a slave database, because this is a good parameter, the default value is 1, and by default it will contain change master to, This statement contains the starting position of the record for file and position. master-data=2, the file that came out in mysqldump contains the change MASTER to this statement, which is in the commented state
REM all-databases: All databases
REM Delete_master_logs: Clean up the previous binary log file
REM %date:~,4%%date:~5,2%%date:~8,2% Date of the day
@echo on
Backup does not set the master-slave synchronization database:
@echo off
Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:\Program files\mysql\mysql Server 5.6\bin\mysqldump"-utest1--password=123456-h192.168.28.1 -- Single-transaction--all-databases > E:\mysqlbk\full_sql\backup_%ymd%.sql
REM Parameter Description:
REM Single-transaction:innodb table when backing up, the option--single-transaction is usually enabled to guarantee the consistency of the backup, in fact it works by setting the isolation level for this session to: Repeatable READ, To ensure that this session (dump) does not see data that has been submitted by other sessions.
REM all-databases: All databases
REM %date:~,4%%date:~5,2%%date:~8,2% date of day
@echo on
Data restore:
@echo off
Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:\Program files\mysql\mysql Server 5.6\bin\mysql"-utest1-p123456-h192.168.28.1--default-character-set=utf8-f < E:\mysqlbk\full_sql\backup_%ymd%.sql
REM parameter Description :
REM Default-character-set=utf8: sets the character set to UTF8, preventing garbled characters
Rem-f:Force, the error has been encountered directly skip and continue to execute the following
REM %date:~,4%%date:~5,2%%date:~8,2% Date of the day
@echo on
MySQL full backup and restore under Windows (Pro-Test available)