MySQL dual-machine hot standby implementation solution [testable]

1. Concept

1. The difference between hot backup and backup

Hot backup refers to: High Available (HA), while backup refers to Backup, a type of data backup. These are two different concepts, and the products they correspond to are also two completely different products in terms of functions. Hot backup mainly ensures business continuity, and the method of implementation is to transfer the failure point. The main purpose of backup is to prevent data loss by making a copy, so backup emphasizes data recovery rather than application failover.

2. What is hot standby?

In a broad sense, hot standby means using two servers to back up each other and jointly perform the same service for important services. When a server fails, another server can take over the service task, thus automatically ensuring that the system can continue to provide services without the need for human intervention.

In a narrow sense, hot standby is the use of two servers that back up each other to jointly perform the same service, one of which is the working server (Primary Server) and the other is the backup server (Standby Server). When the system is normal, the working machine provides services for the application system, and the backup machine monitors the operation of the working machine (usually through heartbeat diagnosis, and the working machine also detects whether the backup machine is normal). When the working machine becomes abnormal and cannot support the operation of the application system, the backup machine actively takes over the work of the working machine and continues to support key application services to ensure uninterrupted operation of the system. Hot standby is a high-availability solution for failures in IT core servers, storage, and network routing switches.

2. Environmental Description

1. master

System: Windows 7

Database: mysql5.5

ip:192.168.0.123

2. Slave

System: Windows 7

Database: mysql5.5

ip:192.168.0.105

(Note: The version of the master server cannot be higher than that of the slave server, and the two servers must be in the same LAN)

3. Master-slave hot standby implementation

1. Account preparation

① On the master server, create a connection account for the slave server. The account must be granted REPLICATION SLAVE permissions. Enter the MySQL operation interface and enter the following SQL:

grant replication slave on *.* to 'replicate'@'192.168.0.105' identified by '123456';
flush privileges;

The operation is as shown in the figure:

②Verify the connection account

Use the replicat account on the slave server to access the master server database to see if the connection is successful.

Open a command prompt from the server and enter the following command:

mysql -h192.168.0.123 -ureplicate -p123456

If the following result appears, it means that the login is successful, which means that the two servers can be operated in dual-machine hot standby.

2. Master configuration

①Modify the mysql configuration file. After finding the my.ini configuration file and opening it, modify it under [mysqld]:

[mysqld]

server-id = 123 #Master ID, cannot be the same as slave ID

log-bin=mysql-bin#Set the generated log file name

binlog-do-db = test_db #Set the synchronization database name

replicate-do-db=test_db # Synchronize database name from server

binlog-ignore-db = mysql#Avoid syncing mysql user configuration

②Restart mysql service

Open the command prompt and enter the following two commands to complete the restart:

net stop mysql
net start mysql

③ Check the master server status

show master status;

④ Lock table

The purpose is to prevent new data from entering the environment so that the slave server can locate the synchronization position. After the initial synchronization is completed, remember to unlock

flush tables with read lock;

Steps ③④ are as shown below:

3. Slave configuration

①Modify the my.ini configuration file

log-bin=mysql-bin #Set the generated log file name
server-id=105# Slave ID, cannot be the same as the master ID
binlog-do-db=test_db #Set the synchronization database name
binlog-ignore-db=mysql #Avoid syncing mysql user configuration
replicate-do-db=test_db# Synchronize database name from server
replicate-ignore-db = mysql,information_schema,performance_schema

②Restart mysql service

③Use the change master statement to specify the synchronization position

After entering the MySQL operation interface, enter the following command:

stop slave;
reset slave;
change master to master_host='192.168.0.123',master_user='replicate',master_password='123456',master_log_file='mysql-bin.000124',master_log_pos=107;
start slave;

Note: The master_log_file and master_log_pos here must be consistent with the previous show master status query results

The operation is as shown in the figure:

4. Unlock the master table

unlock tables;

At this point, the master-slave hot standby is complete and can be tested.

Summarize

The above is the MySQL dual-machine hot standby implementation solution introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!