I have made a MySQL replication document...

I don't think you are familiar with it, so it is best to take a look at it and help me to correct the mistakes.

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;
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.
Part 3 Grant Permissions
Use MySQL to log on to 192.168.37.188 as follows:
(1) mysql> grant all privileges on backup. * To 'backup '@ '192. 168.37.189' identified by '123 ';
Grant the replication permission to the backup user connected with 192.168.37.189...
(2) mysql> flush privileges;
Refresh permission settings;

Log on to slave using MySQL
(1) mysql> grant all privileges on backup. * To 'backup '@ '192. 168.37.188' identified by '123 ';
(2) mysql> flush privileges;
Refresh permission settings;

Note: The above all privileges version 4.0 should be replication slave, that is, grant replication slave on ........ on 3.23, It is file, that is, grant file on ........ but I am afraid of other troubles, just give all the permissions.
After the above settings, we can see that after 192.168.37.189 is set and MySQL is restarted, MySQL will generate a master.info file and relay-log.info under the Data Directory (/var/lib/MySQL, relay-log.index files. if you want to change the master server, you need to delete this file (that is, you have changed/etc/My. the master information in CNF) in my. the CNF file is reconfigured and MySQL is restarted.

Part 4 view the working status
1) create a new backup database on the master
Mysql> Create Database Backup;
2) create a table:
Mysql> Create Table Jintao (ID int (10), name varchar (20 ));
3) Check MySQL on 192.168.37.189;
Mysql> Use Backup;
Mysql> show tables;
Mysql> DESC Jintao;
Mysql> select * From Jintao;
If you see the same information as the master, it proves that the operation was successful.
At the same time, you can change the existing database to determine whether synchronization has been achieved... Just verify that the database is synchronized... In this case, regardless of the Master/Slave, the Server Load balancer will be updated on the master, but the Server Load balancer will also be updated on the master.
Part 5 troubleshooting

On the master node, you do not need to set anything. You only need to open log-bin, write server-id = 1, and write the database to be backed up. The master mode is automatically used, as a result, the problem is mainly concentrated on slave. so how does slave work?
MySQL replication on slave has two threads: I/O thread and SQL thread, i/O is used to retrieve its BINLOG from the master port 3306 (after the master has modified anything, write the modified information to your BINLOG for slave update) and write it to the local relay-log, while the SQL thread reads the local relay-log, the conversion cost is what MySQL can understand, So synchronization is completed step by step. decide I/O thread is/var/lib/MySQL/master.info, and decide the SQL thread is/var/lib/MySQL/relay-log.info.
Note that the content in BINLOG is changed, rather than the content in the future. Therefore, you must ensure that the two databases are identical before synchronization, otherwise, an error may occur. for example. on Machine A, there is a table with 2 tuples, and the operation is minus one, then BINLOG only records the operation minus one. If there is no such operation on machine B, the data cannot be synchronized, because machine B does not have this field, it does not know what operations one minus.
For fault diagnosis, all of my methods are completed in the MySQL (client) of slave (Master/Slave is relative.

Method 1: Show slave status;
The correct conditions should be similar to the following:
Mysql> show slave status;
+ ---------------- + ------------- + --------------- + Hour + -------------- + ------------ + hour
| Master_host | master_user | master_port | connect_retry | master_log_file | delimiter | relay_log_file | delimiter | last_error | skip_counter | delimiter | relay_log_space |
+ ---------------- + ------------- + --------------- + Hour + -------------- + ------------ + hour
| 192.168.37.188 | Backup | 3306 | 5 | Server-bin.020 | 79 | Jintao-relay-bin.001 | 45 | Server-bin.020 | Yes | Backup | 0 | 0 | 79 | 45 |
+ ---------------- + ------------- + --------------- + Hour + -------------- + ------------ + hour
1 row in SET (0.00 Sec)
The above Jintao and server are the host names of the two machines, so the actual situation should be different. Note Yes | yes, which is the working status of the local I/O thread and SQL thread, make sure all are yes. If not yes, check whether MySQL is running normally.

Method 2: Show processlist;
If it is correct, it should be as follows:
Mysql> show processlist;
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
| ID | user | host | dB | command | time | state | info |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
| 4 | system user | null | connect | 398 | waiting for Master to send event | null |
| 5 | system user | null | connect | 398 | has read all relay log; waiting for the I/O slave thread to update it | null |
| 6 | root | localhost | null | query | 0 | null | show processlist |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
3 rows in SET (0.00 Sec)

Note that it is correct if it is similar to the character recorded in the mark. It should look like this in case of an error:
Mysql> show processlist;
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
| ID | user | host | dB | command | time | state | info |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
| 4 | system user | null | connect | 454 | reconnecting after a failed master event read | null |
| 5 | system user | null | connect | 454 | has read all relay log; waiting for the I/O slave thread to update it | null |
| 7 | root | localhost | null | query | 0 | null | show processlist |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------- + ------------------ +
3 rows in SET (0.00 Sec)
Of course, if reconnecting is only incorrect, it may be ing, indicating that the connection is in progress. Check the following:
1. Is MySQL daemon running on the master node normal?
2. Is the network connection between the master and slave normal?
3. Is my. CNF correctly configured?
4 have you deleted master.info after modifying the configuration? (One will be automatically generated after deletion. Do not worry about deleting it.) because if it is not deleted, the original configuration will still be used.
5. Have you restarted MySQL daemon after modifying the configuration? After restarting, you must confirm that MySQL has started properly.
6. Are the replication user permissions assigned to slave and slave on the master node correct? The Master host name and DNS settings
7. Are the two databases identical in the current situation.

Method 3: show Master status;
Mysql> show Master status;
+ ---------------- + ---------- + -------------- + ------------------ +
| File | position | binlog_do_db | binlog_ignore_db |
+ ---------------- + ---------- + -------------- + ------------------ +
| Server-bin.021 | 79 | Backup |
+ ---------------- + ---------- + -------------- + ------------------ +
1 row in SET (0.00 Sec)
Note that the position cannot be 0. If it is 0, it indicates a problem. Check the server-ID in/etc/My. CNF and whether log-bin is enabled.
Mysql> show processlist;
+ ---- + -------- + ------------------- + ------ + ------------- + ------ + ---------------------------------------------------------------- + -------------------- +
| ID | user | host | dB | command | time | state | info |
+ ---- + -------- + ------------------- + ------ + ------------- + ------ + ---------------------------------------------------------------- + -------------------- +
| 1 | Backup | 192.168.37.189: 1067 | null | BINLOG dump | 284 | has sent all BINLOG to slave; waiting for BINLOG to be updated | null |
| 3 | root | localhost | null | query | 0 | null | show processlist |
+ ---- + -------- + ------------------- + ------ + ------------- + ------ + ---------------------------------------------------------------- + -------------------- +
2 rows in SET (0.00 Sec)
If this is not the case on the master, the master configuration may be faulty.

Method 4 View error logs
There is a hostname. Err file under/var/lib/MySQL. All errors are recorded in the file, as shown below:
041210 12:54:51 mysqld started
041210 12:54:51 warning: asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file./ibdata1 did not exist:
InnoDB: a new database to be created!
041210 12:54:51 InnoDB: setting file./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: Wait...
041210 12:54:54 InnoDB: Log File./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: Wait...
041210 12:54:55 InnoDB: Log File./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: Wait...
InnoDB: doublewrite buffer not found: creating new
InnoDB: doublewrite buffer created
InnoDB: creating foreign key constraint system tables
InnoDB: foreign key constraint system tables created
041210 12:54:58 InnoDB: started
/Usr/sbin/mysqld: Ready for connections.
Version: '4. 0.20-standard-log' socket: '/var/lib/MySQL. Sock' port: 3306
041210 12:54:58 slave SQL thread initialized, starting replication in log 'first' at position 0, relay log'./Jintao-relay-bin.001 'position: 4
041210 12:54:58 slave I/O thread: connected to master 'backup @ 192.168.37.188: 3306 ', replication started in log 'first' at position 4
There is no error in the above logs. This is just an example. If an error occurs during database synchronization, the BINLOG records cannot be understood by slave, so errors may occur. /var/lib/MySQL/will not stop generating files like hostname-bin.001 and hostname-relay-bin.001, in this way, a new relay-log will be used to replace the original one every time the Master/Slave is restarted. so the directory will not stop generating similar files, and the hostname-relay-bin.index to control which is currently used relay-log. the entire synchronization process has been clearly stated at the beginning of the fifth part.
BTW: if you do not know the host name of the local machine, you can enter
# Hostname