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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.