MySQL Live Backup

Read Catalogue

• Environment Introduction
• Configuration of the primary server
• Configuration from the server
• Windows Scheduled Backup Database script

As I have said in other essays, the company database chooses the free install version and is on Windows, and so on, I will migrate the database to Linux

Because of the company's development operations database, such as not timely backup, in case of operational errors, recovery can become a problem

This essay will show you how to make a real-time backup of MySQL

Environment Introduction

1. Two servers for master-slave synchronous replication (installation can be through Google query related materials or see the author of this free installation version)
2. Real-time backup of the primary server with Windows scripting and Windows-brought scheduled Tasks (demo below)

• Maseter mysql:192.168.1.253
• Slave mysql:192.168.1.254

1. Turn on the binary logging feature on the primary server and set a unique server ID number that requires the MySQL service to be restarted
2. Setting a unique server ID number from the server, these settings require a restart of the MySQL service
3. On the primary server in order not to create a user from the server who can read the primary server log files, or to use the same unified user
4. Before data replication, you need to record the location of the binaries on the primary server
5. Ensure consistency of data from the server and the primary server prior to data replication

Back to Topconfiguration of the primary server

• In an actual environment, there is already a large amount of data in the database before we can synchronize the database. So, be sure to remember the backup when you operate the database

Mysqldump-u root-p--all-databases--lock-all-tables > G:/dbdump.sqlenter Password: ******

• We need to open the binary log on the primary server and set the server number, the server unique number is 2 of the 32 minus 1 of the integer, according to their actual situation and set

Making these settings requires that you close the MySQL database and edit the My.ini or my.cnf file, and then add the appropriate configuration options in [mysqld] refers to the segment

C:\users\administrator>net Stop Mysqlmysql service is stopping. The MySQL service has stopped successfully.

[Mysqld]log-bin=mysql-binserver-id=1

C:\users\administrator>net Start Mysqlmysql service is starting. The MySQL service has started successfully.

• To enable synchronous replication from the server, we need to create a synchronous replication user

2. But this user must have " REPLICATION SLAVE "Permissions, of course you can give different from the server to create unused users and passwords, you can also use a unified user and password
3. If the user is only used for database replication, the user needs only the "REPLICATION SLAVE" permission

C:\users\administrator>mysql-u root-penter Password: ******welcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 1Server version:5.6.25 mysql Community Server (GPL) Copyright (c), Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> CREATE USER ' slave ' @ '% ' identified by ' slaveadmin '; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE on * * to ' SLAVE ' @ '% '; Query OK, 0 rows Affected (0.00 sec) mysql> FLUSH privileges; Query OK, 0 rows Affected (0.00 sec) Mysql> Exitbye

• Get Primary server binary log information

1. First, let's take a look at the basic information of the binary, which is needed for the settings from the server, which includes the server binary file name and the current log record location so that the server can know where to start the copy operation
2. When we get the binary file name and the location of the binary current record, keep in mind that it is used on the slave server.

Mysql> FLUSH TABLES with READ LOCK; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW MASTER status;+------------------+----------+--------------+-------- ----------+-------------------+| File             | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |  9876212 | | | |    +------------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec) MySQL > UNLOCK TABLES; Query OK, 0 rows Affected (0.00 sec)

1. The file column displays the binary log file name, position the current log record location
2. The purpose of the FLUSH TABLES with read lock command is to perform a read-only lock on tables for all databases, and all database writes will be rejected when read-only locks, but read operations can continue
3. Performing a lock prevents the operation from being modified while viewing the binary log information, and finally using the UNLOCK TABLES command to perform an end operation on the global lock

Back to Topconfiguration from the server

• As with the primary server, you need to configure the My.ini or my.cnf file, note that the server ID number

C:\users\administrator>net Stop Mysqlmysql service is stopping. The MySQL service has stopped successfully.

[mysqld]server-id=2

C:\users\administrator>net Start Mysqlmysql service is starting. The MySQL service has started successfully.

For replication, MySQL slave server binary function is not required to open, of course, can also turn on the binary features from the server to achieve data backup and recovery

• In the introduction I have already said that before the data replication, to ensure that the data from the server and the master server consistent, remember I just a primary server MySQL backup?! This backup can be restored to the slave server, so that the master-slave data is the same

Mysql-u root-p < d:/dbdump.sqlenter password: ******

• Configure data replication from the server connection master server

1. In fact, the key operation of data replication is to configure the server to connect to the primary server for data replication, we need to tell the server to establish a network connection all necessary information
2. Use the Change master to statement to complete the connection to the primary server

†master_host Specify the host name or IP address of the primary server

†master_user the user who has the copy permission that was created on the primary server just now

†master_password to change the user's secret

†master_log_file specifying the primary server binary log file name

†master_log_pos Specify the current location of the primary server binary log file

1. Start Lsave turn on slave server function
2. Show SLAVE Status View from server state

mysql> change MASTER to    -master_host= ' 192.168.1.253 ',    master_user= ' slave ',    master_ Password= ' Slaveadmin ',    master_log_file= ' mysql-bin.000004 ',    master_log_pos=9876212; Query OK, 0 rows Affected (0.00 sec)

Mysql> START SLAVE; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW SLAVE status\g;

When viewing the status, the following two must indicate YES to the normal operating state, and if IO is no then check the password or write the new one

1. Slave_io_running:
2. Slave_sql_running:

• Now when we create a database on the primary server, we look at it from the server, then we can see that we have synchronized the newly created database on the master server.

mysql> CREATE DATABASE Vforbox; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               | | vforbox            |+--------------------+

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               | | vforbox            |+--------------------+

Back to TopWindows Scheduled backup database script

• Here I present the code, if there is a better script recommendation please send to the author's mailbox
• Create a new "Mysql_auto_bak.bat"

@echo Offclscolor 3Etitle%date%%time:~,5% Backup of the MySQL database by:vforobx::::::::::::..::..::::----------------------:        ::::-::::::::::-----------two--the password for the root account of the MySQL server, special symbols need to be added in front of the mysql_pw=123456 REM set database backup directory Set Bak_dir=d:\mysqlbak REM settings need to back up the MyISAM format database set bak_db_myisam=myisam_db REM settings need to back up InnoDB format Database Set Bak_db_innodb=innodb REM set WinRAR compression software path set rar_dir= "C:\Program files (x86) \WINRAR\WINRAR.E        Xe "Rem Set date in 2015-10-01 format for subdirectory set bak_dir2=%date:~0,4%-%date:~5,2%-%date:~8,2% REM setting backup file name        Set bak_file=%%i_%bak_dir2%.sql REM settings log file inside point to the RAR file path set bak_file2=%%i_%bak_dir2% rem set log file name SET Log_file=%bak_dir%\%bak_dir2%\mysql_bak.log::::::::::::::::--------------------------------: ::::::.::::::-:----:---. The following is a decision on whether a variable is defined by::::::::::::::::::::::::::: Defined (ECHO) MYSQL_PW not yet defined! ) if not defined Bak_dir (echo BAK_dir not yet defined! If not defined Rar_dir (Rar_dir is not yet defined!) )::::::::::::::--:--------------------------::-------------:---::::::::: Defined InnoDB) echo. Start creating a folder with the current date if not exist%bak_dir%\%bak_dir2% MD%bak_dir%\%bak_dir2%cd/d%bak_dir%\%bak_dir2%echo. Start building (%bak_dir2%) of a backup::.::::::::::::: The following are the core codes of a backup::::::::::::::::::::::-----%bak_ dir2%%time:~0,8% >>%log_file%echo ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++ >>%log_file%setlocal disabledelayedexpansionfor%%i in (%bak_db_myisam%) do (mysqldump-h 192.168.1.253-uroot-p%mysql_pw%--all-databases >%bak_file%%rar_dir% a%bak_file:~0,-4%.rar%bak_file%del/f/a/q% bak_file% echo Database "%%i format" has been backed up to:%bak_dir%\%bak_dir2%\%bak_file2%.rar >>%log_file%):::::::.:::: Start creating folder with current date if not exist%bak_dir%\%bak_dir2% MD%bak_DIR%\%BAK_DIR2%CD/D%bak_dir%\%bak_dir2%echo. Start building a backup of today (%bak_dir2%) setlocal disabledelayedexpansionfor%%i in (%bak_db_innodb%) do (mysqldump-h 192.168.1.253-uroot -p%mysql_pw%--all-databases >%bak_file%%rar_dir% a%bak_file:~0,-4%.rar%bak_file% del/f/a/q%BAK_file% Echo Database " %%i format "has been backed up to:%bak_dir%\%bak_dir2%\%bak_file2%.rar >>%log_file%) echo +++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++++++++++++++ >>%log_file%echo. All backups are established by::::.:::::::;:::----------:::.:::::::::-----:::----------:::----:.:-:. rem Clear Variable SET MYSQL_PW = Set bak_dir= set rar_dir= set bak_dir2= set bak_file= set bak_file2 set Log_fi le=

• If you feel a duplication problem-click Download

Then add the script to the Windwos to bring the scheduled task, here I will not show how to create a scheduled task, if you need help please Google　　

MySQL Live Backup