Configure mysql5.0 dual-machine Synchronization

Https://forum.eviloctal.com/thread-30618-1-1.html

 

Platform: RedHat as4.0 Mysql-max-5.0.16-linux-i686 (original)
Author: jiang313hua MSN: jiang313hua@hotmail.com
You are welcome to point out the error! Reprinted!
This article has been tested by the author.

Requirements:
1. First install the system RedHat as4.0;

2. Mysql-max-5.0.16-linux-i686 decompression does not need to compile, directly install can:
Shell> groupadd MySQL
Shell> useradd-G MySQL
Shell> Cd/usr/local
Shell> tar zxvf/data/soft/mysql-max-5.0.16-linux-i686-glibc23.tar.gz
Shell> ln-s mysql-max-5.0.16-linux-i686-glibc23 MySQL
Shell> Cd MySQL
Shell> scripts/mysql_install_db -- user = MySQL
Shell> chown-r root.
Shell> chown-r Mysql Data
Shell> chgrp-r MySQL.
Shell> bin/mysqld_safe -- user = MySQL &
The specific operation is in the install-binary file in the package;

3. If iptables is set, open port 3306;

4. IP addresses of the two servers: 192.168.1.56
192.168.1.57

Specific process:
1. Create the user to be synchronized and the data to be synchronized; log on to the database of the 192.168.1.56 Server:
Shell> mysql-uroot-p123456
Mysql> Create Database bak; // create a database
Mysql> grant replication slave on *. * To tongbu @ '192. 168.1.57 'dentified by '20140901'; // create a synchronized MySQL account

Tongbu with a password of 123456. It specifies that you can only log on from 192.168.1.57 using tongbu and synchronize any database in MySQL.
Mysql> flush privileges;

2. log on to the database of the 192.168.1.57 Server:
Shell> mysql-uroot-p123456
Mysql> Create Database bak;
Mysql> grant replication slave on *. * To tongbu @ '192. 168.1.56 'dentified by '20140901 ';
Mysql> flush privileges;

3. Copy the MySQL configuration file under MySQL/support-files to the/etc directory,
# Cp/usr/local/MySQL/support-files/my-large.cnf/etc/My. CNF
The. CNF documentation is as follows:
My-small.cnf memory is less than or equal to 64 m, providing only a small number of database services
The my-medium.cnf exists between 32m--64m and is used with other services, such as web
My-large.cnf memory has m mainly to provide database services
My-huge.cnf memory has 1g to 2G, mainly provide database services

4. Modify the my. CNF file on 192.168.1.56. Here we name it Server 1.
# Vi/etc/My. CNF
Add the following content in [mysqld:
Log-bin = mysql-bin
Master-host = 192.168.157
Master-user = tongbu
Masters-Password = 123456
Master-Port = 3306
BINLOG-do-DB = Bak

Because MySQL is the master server by default, you do not need to specify server-id = 1.

5. Modify the my. CNF file on 192.168.1.57. Here we name it Server 2.
# Vi/etc/My. CNF, first find the server-ID in the my. CNF file, and then modify it:
Server-id = 2
Add the following content in [mysqld:
Master-host = 192.168.1.56
Master-user = tongbu
Masters-Password = 123456
Master-Port = 3306
Master-connect-retry = 30
Replicate-do-DB = Bak
Log-bin
BINLOG-do-DB = Bak

Explanation:
Master-host = 192.168.1.57 indicates that the master is 192.168.1.57 when the host is set to Server 1;
Master-user = username indicates an authorized user opened on Server 2, so that the user can connect to and copy the user from Server 1;
Master-Password = PASSWORD indicates the password of the authorized user;
Master-Port = port 3306 of MySQL service listen3306 on the master;
Master-connect-retry = 30 synchronization interval: 30 seconds;
Replicate-do-DB = Bak indicates synchronizing the Bak database;
Log-bin open the logbin option to write to the slave I/O thread;
BINLOG-do-DB = Bak indicates that other machines can synchronize the local Bak database.

6. Create or import the same table for the Bak databases in the two databases.

7. Restart MySQL.
#/Usr/local/MySQL/support-files/MySQL. Server restart

8. This step is very important. As a result, I am overwhelmed by the many mistakes I have made!
Log on to the 192.168.1.56 database:
Mysql> change master
Master_host = '1970. 168.1.57 ',
Master_user = 'gbu ',
Master_password = '000000'

Log on to the 192.168.1.57 database:

Mysql> change master
Master_host = '1970. 168.1.56 ',
Master_user = 'gbu ',
Master_password = '000000'

Check MySQL at 192.168.1.56:
Mysql> show processlist;
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + ---------------------------------------------------

-------------------- + ------------------ +
| ID | user | host | dB | command | time | state

| Info |
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + -------------------------------------------------------

---------------- + ------------------ +
| 1 | system user | null | connect | 71 | waiting for Master to send event

| Null |
| 2 | system user | null | connect | 1150 | has read all relay log; waiting for the slave I/O

Thread to update it | null |
| 3 | root | localhost | null | query | 0 | null

| Show processlist |
| 4 | tongbu | 192.168.1.57: 32787 | null | BINLOG dump | 16 | has sent all BINLOG to slave; waiting for BINLOG to be

Updated | null |
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + -------------------------------------------------------

---------------- + ------------------ +
4 rows in SET (0.00 Sec)

Check MySQL at 192.168.1.57:
Mysql> show processlist;
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + -------------------------------------------------------

---------------- + ------------------ +
| ID | user | host | dB | command | time | state

| Info |
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + -------------------------------------------------------

---------------- + ------------------ +
| 1 | system user | null | connect | 37 | connecting to master

| Null |
| 2 | system user | null | connect | 37 | has read all relay log; waiting for the slave I/O

Thread to update it | null |
| 3 | tongbu | 192.168.1.56: 32829 | null | BINLOG dump | 33 | has sent all BINLOG to slave; waiting for BINLOG to be

Updated | null |
| 4 | root | localhost | null | query | 0 | null

| Show processlist |
+ ---- + ------------- + -------------------- + ------ + ------------- + ------ + -------------------------------------------------------

---------------- + ------------------ +
4 rows in SET (0.00 Sec)

If this information is displayed, the bidirectional synchronization is successful. Otherwise, check the preceding steps!

9. Now you can insert records in the Bak database. Check synchronization status!

Reference:
MySQL official documentation
I have made an article about MySQL replication. You are welcome to correct it...Http://www.chinaunix.netAuthor: pig with double eyelid