Use MySql dual-system hot backup function under Linux system

Article Directory

Overview

Test purposes

Verify and test the MySQL dual-system hot backup function under the Linux system.

Test environment

Prepare two servers, install the operating system and database respectively, and have the same database version.

Environmental projectEnvironment configurationRemarks
Hardware environmentHuawei 2288H V5 serverServer 1: 192.168.200.1 Server 2: 192.168.200.2
operating systemUbuntu20.04 LTS
MySQL8.0.25-0-ubuntu0.20.04.1

Prepare the database

Create a new test database on the two servers:

CREATE DATABASE `mytest` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Prepare account

Create an account on server 1 that can only be logged in from server 2

The account in the example is synand the password is:123456
# 新建用户
CREATE USER `syn`@`192.168.200.2` IDENTIFIED WITH mysql_native_password BY '123456';
# 查看验证
SELECT user,host,plugin FROM mysql.user;
# 授予数据复制的权限
GRANT Replication Slave ON *.* TO `syn`@`192.168.200.2`;

Create an account on server 2 that can only be logged in from server 1

The account in the example is synand the password is:123456
# 新建用户
CREATE USER `syn`@`192.168.200.1` IDENTIFIED WITH mysql_native_password BY '123456';
# 查看验证
SELECT user,host,plugin FROM mysql.user;
# 授予数据复制的权限
GRANT Replication Slave ON *.* TO `syn`@`192.168.200.1`;

Test account

  • On server 1, use the account of server 2 to log in to the database on server 2
mysql -h 192.168.200.2 -u syn -p
  • On server 2, use the account of server 1 to log in to the database on server 1
mysql -h 192.168.200.1 -u syn -p
  • Problems
    when testing an account If a problem similar to the following occurs when testing an account:
  1. Please check whether /etc/mysql/mysql.conf.d/mysqld.cnfthe bind_addressparameters in the configuration file allow access to the IP address.
  2. Check whether the firewall of the accessed server has opened port 3306.
  3. Check hostwhether the account is set correctly.
这是因为用户名或者密码错误:

Modify the test configuration

Modify the configuration file

Modify the configuration files of the two servers separately/etc/mysql/my.cnf

sudo vim /etc/mysql/my.cnf

Add the following code at the end of the file:

[mysqld]
#其中的mytest是数据库名
# 主服务器唯一ID,服务器之间不能一样
server-id=1
# 启用二进制日志
log-bin=backuplog
sync_binlog=1
binlog_format=row
auto_increment_increment = 1
auto_increment_offset = 1 
max_binlog_size=512m
expire_logs_days=1
# 设置需要记录二进制日志的数据库(需要同步复制的都要记录,可设置多个,如果不配置,则全部记录)
binlog_do_db=mytest
# 设置不要复制的数据库(可设置多个)
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
# 设置需要复制的数据库(可设置多个,如果不配置,则全部同步)
replicate_do_db=mytest
replicate_wild_ignore_table=mytest.log
slave_skip_errors=all
If you only want master-slave replication, for example, server 1 is the master server, and server 2 is the slave server, and only copy data from the master server to the slave server, then the slave server only needs to fill in server-idone item.

Restart MySQL service

After the modification, remember to restart the MySQL service

sudo service mysql restart

Restart normally:

There is a problem when restarting, you need to check the configuration:

Check master service status

sudo mysql
mysql> show master status;
To check the server are 1 and 2 of the state master server, and remember Fileand Positionvalue, specify the following synchronization position when master_log_fileyou want to fill in Filethe value, master_log_posto fill in Postitionvalues.

Master-slave configuration

Configure master parameters

If you only want master-slave replication, for example, server 1 is the master server, server 2 is the slave server, and only replicates data from the master server to the slave server, then you only need to specify the synchronization location of server 2 here.
  • Server 1
mysql> stop slave;
mysql> change master to master_host='192.168.200.2',master_user='syn',master_password='123456', master_log_file='backuplog.000001',master_log_pos=156;
mysql> start slave;
  • Server 2
mysql> stop slave;
mysql> change master to master_host='192.168.200.1',master_user='syn',master_password='123456', master_log_file='backuplog.000001',master_log_pos=156;
mysql> start slave;

Check slave status

Check the slave status of the two servers separately:

Check whether the parameters Slave_IO_Running and Slave_SQL_Running are both yes, then the configuration is successful. If it is no or other, you need to check the corresponding Last_IO_Error or Last_SQL_Error abnormal value.
show slave status\G;

Synchronization test

Create table

  • Create table on server 1
Don't copy this code
mysql> use mytest;
mysql> CREATE TABLE tb_by_1(
	->id INT NOT NULL AUTO_INCREMENT,
	->dtext VARCHAR(20) NOT NULL,
	->PRIMARY KEY (id)
	->)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Check if there is this table on server 2:

	mysql> use mytest;
	mysql> show tables;


Have it already.

  • Create a table


on server 2 Query on server 1: all


can be queried.

Data test

  • Insert data into table 1 on server 1
INSERT INTO tb_by_1 (dtext) VALUES ('input by s1');


Query on server 2:


You can also find it.

  • Other tests slightly

references

Linux install Mysql8.0.20 and configure master-slave replication (one master and one slave, dual master and dual slave)
Windows configure MySQL dual hot standby