Mariadb or MySQL master/Master synchronous Backup Settings (windows)

Mariadb or MySQL master/Master synchronous Backup Settings (windows)
Abstract: This article only describes the configuration of master-master synchronous backup in a dual database. If you need to implement real failover and load balancing, you also need to configure other software such as Keepalived (using Keepalived, you must use the linux system. This software is only available in linux)

Preparations

First, prepare two virtual machines with MariaDB or MySQL installed. I have two win2003 virtual machines with MariaDB, and the two are 192.168.100.217 and 192.168.100.218, which have the same basic configurations, one of them is my. in the INI file, configure server_id = 2 (that is, the server_id of the two machines cannot be the same) my. in the INI file, add the following parameters to the [mysqld] node:

Log-bin = binlog # enable the binlog Function

Log-bin-index = binlog. index

Sync_binlog = 0

Server_id = 1 # the two machines cannot be the same. One is the same as the other.

In the my. ini file of 192.168.100.218, add the following parameters for the [mysqld] node:

Log-bin = binlog # enable the binlog Function

Log-bin-index = binlog. index

Sync_binlog = 0

Server_id = 2 # the two machines cannot be the same. One is the same as the other.

1. Run the following statement on MariaDB on both machines to add a user named testsync as the slave server, and the password is "abc123 ,":

GRANT replication slave ON *. * TO 'testsync' @ '%' identified by 'abc123, '; flush privileges;

2. Set synchronization for 218. The master server is 217
1. Run the following command on 192.168.100.217:

Flush tables with read lock; show master status; after executing show, remember several parameters, including the File name and Position value of the File field, use unlock tables in the future;

2. Run the following command on 192.168.100.218:

Run the Add mster command first. Note that the value of master_log_file must be the name of the File obtained when show is executed on 217, and the value of master_log_pos must be the Position value, otherwise, an error occurs.

Change master to master_host = '192. 168.100.217 ', master_port = 192, master_user = 'testsyn', master_password = 'abc123,', master_log_file = 'binlog. 100', master_log_pos = 3306;

Start slave;

Show slave status;

# After executing this command, observe the following two parameters. All the parameters must be yes.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If one of them is no, observe the descriptions in the subsequent field Last_IO_Error or Last_ SQL _Error, and modify the configuration parameters according to the error.

Which of the following errors may occur:

1. If server_id is the same, I/O errors occur.

2. master_log_file and master_log_pos errors, resulting in IO errors

3. Configure synchronization for 217. The master server is 218.
1. Run the following command on 192.168.100.218:

Flush tables with read lock; show master status; after executing show, remember several parameters, including the File name and Position value of the File field, use unlock tables in the future;

2. Run the following command on 192.168.100.217:

Run the Add mster command first. Note that the value of master_log_file must be the name of the File obtained when show is executed on 217, and the value of master_log_pos must be the Position value, otherwise, an error occurs.

Change master to master_host = '192. 168.100.218 ', master_port = 192, master_user = 'testsyn', master_password = 'abc123,', master_log_file = 'binlog. 100', master_log_pos = 3306;

Start slave;

Show slave status;

# After executing this command, observe the following two parameters. All the parameters must be yes.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If one of them is no, observe the descriptions in the subsequent field Last_IO_Error or Last_ SQL _Error, and modify the configuration parameters according to the error.

Which of the following errors may occur:

1. If server_id is the same, I/O errors occur.

2. master_log_file and master_log_pos errors, resulting in IO errors

4. test whether the synchronization configuration is complete.

You can test it, create a new database in 217, and create a table in the newly created database in 218. If the content of both parties is the same, verify that the configuration is complete.

If there are no errors in the process, the master-slave synchronization backup is complete.