Use the master and slave functions of MySQL for Real-time Data Synchronization

Implement MySQL Replication
After MySQL 3.23.15, MySQL provides the database replication function to synchronize two databases in real time, enhance the stability of MySQL databases, and implement Cluster at the database layer of enterprise-level applications...

Condition:
1 RedHat 9
2 Mysql 4.0.20
3. The ip addresses of the two machines are 192.168.37.188 192.168.37.189.
Objectives:
1. Bidirectional Database Replication
2. The network between the master and slave is disconnected, but the system returns to normal again later. The data on the master can also be updated on the slave, and vice versa.

This article consists of the following parts:

Part 1 install MySQL
The second part configures/etc/my. cnf (check the/etc/init. d/mysql script to determine whether it is/etc/my. cnf)
Part 3 Grant Permissions
Part 4 check the working status, test and verify whether synchronization can be performed.
Part 5 Troubleshooting

Part 1 install MySQL
1. Obtain the MySQL RPM installation package. The list is as follows:
MySQL-server-4.0.20-0
MySQL-client-4.0.20-0
MySQL-shared-4.0.20-0
2. Install the SDK as root
# Rpm-ivh MySQL-*-4.0.20-0
There will be a progress bar prompting the installation progress;

After the installation is complete, the MySQL database configuration file is in/var/lib/mysql/, and the default configuration files are in/usr/share/mysql/. The following files are available:
My-hug.cnf
My-large.cnf
My-medium.cnf
My-small.cnf
As the name suggests, it is designed for different applications. It mainly optimizes some database parameters. For specific optimization, see [mysqld] Statements in my. cnf.

Part 2 Configure/etc/my. cnf
Mysql installed in the/etc/init. d will generate a mysql shell script file. in Redhat, when we use service mysql start, it is actually passed to the script start parameter and executed. You need to view the file, which configuration file is called? Here we find this line...
Conf =/etc/my. cnf
The configuration file is/etc/my. cnf.
Suppose we have medium-sized applications:
# Copy/usr/share/mysql/my-medium.cnf/etc/my. cnf
Copy to/etc/my. after cnf, you can configure it. MySQL reads the configuration file and starts it according to the configuration method each time it starts, because the database needs two-way replication, then, each machine must be both master and slave,
1. First, modify it in [mysqld] Under/etc/my. cnf of 192.168.37.188, and set the machine as master as follows:
Server-id = 1
Log-bin
Binlog-do-db = backup

Explanation:
1) server-id = 1 indicates that the serial number of the local machine is 1, which is generally the meaning of the master.
2) log-bin indicates that binlog is enabled. This option can be used to write logs to Slave relay-log through I/O, which is also a prerequisite for replication;
3) binlog-do-db = backup indicates that the database to be backed up is backup,
4) if you need to back up multiple databases, write multiple rows as follows:
Binlog-do-db = backup1
Binlog-do-db = backup2
Binlog-do-db = backup3

2. Modify the Server Load balancer instance in this region.
Master-host = 192.168.37.189
Master-user = backup
Masters-password = 1234
Master-port = 3306

3. Configure my. cnf on 192.168.37.189.
In/etc/my. cnf, modify the following in [mysqld:
Server-id = 2
Master-host = 192.168.37.188
Master-user = username
Master-password = password
Master-port = 3306 # master server port
Master-connect-retry = 60 # synchronization interval: 60 seconds
Replicate-do-db = backup
Log-bin
Binlog-do-db = backup

Explanation:
1) server-id = 2 indicates the serial number of the machine;
2) master-host = 192.168.37.188 indicates that the master of the Local Machine for slave is 192.168.37.188;
3) master-user = username indicates a user with permissions open on the master, so that the user can connect to the master from the slave and perform replication;
4) master-password = password indicates the password of the authorized user;
5) master-port = port 3306 of MySQL service Listen3306 on the master;
6) master-connect-retry = 60 synchronization interval;
7) replicate-do-db = backup indicates synchronizing the backup database;
8) log-bin open the logbin option to write to the slave I/O thread;
9) binlog-do-db = backup indicates that other machines can synchronize the backup database of the local machine.
Restart mysql on 192.168.37.188 and 192.168.37.189.

1 2 3 Next Page