Detailed description of MySQL configuration for real-time backup

I have mentioned in other articles that the company's database uses the free installation version and is on Windows. After a while, I will migrate the database to Linux.

Due to the large number of databases developed and operated by the company, if the database is not backed up in a timely manner, restoring the database may be difficult in case of Operation errors.

This article describes how to back up MySQL in real time.

 

Environment introduction

 

Two servers are used for master-slave synchronous replication (you can use google to query the relevant information or read the author's free installation version)

Back up the master server in real time using Windows scripts and scheduled tasks provided by Windows (as shown below)

Maseter MySQL: 192.168.1.253

Slave MySQL: 192.168.1.254

Enable the binary log function on the master server and set the unique server ID. These settings need to restart the MySQL service.

Set a unique server ID on the slave server. These settings need to restart the MySQL service.

On the master server, in order not to create a user from the server that can read the log files of the master server, or use the same unified user

Before data replication, you must record the location of the binary file on the master server.

Before data replication, ensure that the data on the slave server is consistent with that on the master server.

Back to top

 

Master server configuration

In the actual environment, there may be a large amount of data in the database before we deploy database synchronization. Therefore, remember to back up data when operating the database.

Mysqldump-u root-p -- all-databases -- lock-all-tables> G:/dbdump. SQL
Enter password :******

We need to enable the binary log on the master server and set the server number. The unique server number is an integer between the power of 32 and the power of 2 minus 1, which is set according to your actual situation.

To perform these settings, you need to close the MySQL database and edit the my. ini or my. cnf file. Then, add the corresponding configuration options in the [mysqld] section.

C: \ Users \ Administrator> net stop mysql
The MySQL service is stopping.
The MySQL service has been stopped successfully.
[Mysqld]
Log-bin = mysql-bin
Server-id = 1
C: \ Users \ Administrator> net start mysql
MySQL service is starting.
The MySQL service has been started successfully.
To enable synchronous replication on the slave server, we need to create a user for synchronous replication.

When performing data replication, all slave servers need to connect to the MySQL master server using the user and password. Therefore, at least one user and the corresponding password must exist on the master server to provide the slave server for connection.

However, this user must have the "replication slave" permission. Of course, you can create unnecessary users and passwords for different SLAVE servers, or use a unified user and password.

If the user is only used for database REPLICATION, the user only needs the "replication slave" permission.

C: \ Users \ Administrator> mysql-u root-p
Enter password :******
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1
Server version: 5.6.25 MySQL Community Server (GPL)
 
Copyright (c) 2000,201 5, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.
 
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> exit
Bye
Obtain the binary log information of the master server

First, let's take a look at the basic information about the binary file. This information is required for slave server settings. It includes the server binary file name and the current log record location, in this way, the server can know where to start the replication operation.

When we get the binary file name and the location of the current binary record, remember that it will be used on the slave server.

Slave server configuration

Like the master server, you need to configure the my. ini or my. cnf file. Note the slave server ID here.

C: \ Users \ Administrator> net stop mysql
The MySQL service is stopping.
The MySQL service has been stopped successfully.
[Mysqld]
Server-id = 2
C: \ Users \ Administrator> net start mysql
MySQL service is starting.
The MySQL service has been started successfully.

For replication, the binary function of MySQL slave server does not need to be enabled. Of course, you can also enable the binary function on the slave server for data backup and recovery.

• In the introduction, I have mentioned that before data replication, the data on the slave server and the master server must be consistent. Do you still remember to back up the MySQL on the master server just now ?! You can restore the backup to the slave server, so that the master and slave data are the same.

 

Mysql-u root-p <D:/dbdump. SQL

 

Enter password :******

 

• Configure the slave server to connect to the master server for data replication

 

1. In fact, the key operation of data replication is to configure the slave server to connect to the master server for data replication. We need to tell the slave server all the necessary information for establishing a network connection.

2. Use the change master to statement TO connect TO the MASTER server.

 

& Dagger; MASTER_HOST specifies the host name or IP address of the master server

 

& Dagger; MASTER_USER: The user created on the master server with the copy permission

 

& Dagger; MASTER_PASSWORD is the secret to change the user

 

& Dagger; MASTER_LOG_FILE specifies the name of the binary log file of the master server

 

& Dagger; MASTER_LOG_POS specifies the current location of the binary log file on the master server

1. start lsave to enable the slave server function for master-slave connection

2. View SLAVE server STATUS by SHOW SLAVE STATUS

Mysql> CHANGE MASTER
-> 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 values must be YES, indicating that the operation is normal. If IO is No, check the password or re-write the data.

When viewing the status, the following two values must be YES, indicating that the operation is normal. If IO is No, check the password or re-write the data.

Slave_IO_Running:

Slave_ SQL _Running:

Now, when we create a database on the master server, we can view it on the slave server. Now 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 |
+ -------------------- +
Windows timed backup database script

As mentioned above, you can use Windows scripts and built-in scheduled tasks to back up the master server in real time.

Here I provide code. If you have better script recommendations, please send them to my mailbox.

Create a new "mysql_auto_bak.bat"

@ Echo off
Cls
Color 3E
Title % date % time :~, 5% back up MySQL database By: vforobx
:::::::::: ::::::::::::::::::::::::::::::
 
Rem sets the password for the root account of the MySQL server. You need to add two special symbols before the special symbols ^
Sets MySQL_pw = 123456
Rem sets the database backup directory
SET BAK_dir = D: \ mysqlbak
Rem sets the myisam format database to be backed up
SET BAK_db_myisam = myisam_db
Rem sets the innodb format database to be backed up
SET BAK_db_innodb = innodb
Rem sets the path of WinRAR compression software
SET RAR_dir = "C: \ Program files (x86) \ WinRAR \ WinRAR.exe"
Rem sets the date in the format of as a subdirectory
SET BAK_dir2 = % date :~ 0, 4%-% date :~ 5, 2%-% date :~ 8, 2%
Rem sets backup file name
SET BAK_file = % I _ % BAK_dir2 %. SQL
Rem sets the rarfile path pointed to in the log file
SET BAK_file2 = % I _ % BAK_dir2 %
Rem sets the log file name
SET LOG_file = % BAK_dir % \ % BAK_dir2 % \ Mysql_bak.log
 
:::::::::::::::::::::::: The parameters to be configured are :::::::::: ::::::::::::::::::::::::::::::
 
:::::::::: ::::::::::::::::::::::::::::
 
If not defined MySQL_pw (echo MySQL_pw is not defined yet !)
If not defined BAK_dir (echo BAK_dir is not defined yet !)
If not defined RAR_dir (RAR_dir is not defined yet !)
 
::::::::::: :::::::::::::::::::::::::::::::
 
If not defined BAK_db_myisam (goto innodb)
Echo. Start to create a folder on the current date
If not exist % BAK_dir % \ % BAK_dir2 % md % BAK_dir % \ % BAK_dir2 %
Cd/d % BAK_dir % \ % BAK_dir2 %
Echo. Start creating (% BAK_dir2 %) backup
::::::::::::::::::::::::The core code of the backup :::::::::: ::::::::::::::::::::::::::::::
 
Echo backup time: % BAK_dir2 % time :~ 0, 8%> % LOG_file %
Echo ++ ++ ++> % LOG_file %
SETLocal DisableDelayedExpansion
For % 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, -42.16.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 %)
:::::::::::::::::::::::: The backup code in innodb format is ::::::::: :::::::::::::::::::::::::
 
If not defined BAK_db_innodb (goto exitbat)
Echo. Start to create a folder on the current date
If not exist % BAK_dir % \ % BAK_dir2 % md % BAK_dir % \ % BAK_dir2 %
Cd/d % BAK_dir % \ % BAK_dir2 %
Echo. Start to create backup for today (% BAK_dir2 %)
SETLocal DisableDelayedExpansion
For % 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, -42.16.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 have been created.
:::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::::::::: ::::
Rem clear variable
SET MySQL_pw =
SET BAK_dir =
SET RAR_dir =
SET BAK_dir2 =
SET BAK_file =
SET BAK_file2
SET LOG_file =