MySQL full backup and restore under Windows (Pro-Test available)

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)