How to synchronize MySQL Databases on two servers

The two servers, respectively, have installed Mysql in the usrlocalmysql directory (the installation steps are omitted, please refer to the relevant documentation). The IP addresses of the two servers are 192.1

Install Mysql on both servers in the/usr/local/mysql directory (the installation steps are omitted. Refer to the relevant documentation). The IP addresses of the two servers are 192.1.

Install Mysql on both servers in the/usr/local/mysql directory (the installation steps are omitted. Refer to the relevant documentation ), the IP addresses of the two servers are 192.168.0.1 and 192.168.0.2. We use 192.168.0.1 as the master database and 192.168.0.2 as the slave server. We adopt one-way synchronization, that is, the master data is the master data, then, slave takes the initiative to sync data back from the master. The configuration of the two servers is the same, we copy the key configuration file, the default configuration file is in the/usr/local/mysql/share/mysql directory, there are my-large.cnf, respectively, my-medium.cnf, my-small.cnf and so on several writers, we just test, use my-medium.cnf on the line. After mysql is installed, the default configuration file is specified in the database storage directory. We use 4.1.X, therefore, the configuration file should be in the/usr/local/mysql/var directory, so you can copy the configuration file:

Cp/usr/local/mysql/share/mysql/my-medium.cnf/usr/local/mysql/var/my. cnf
The two servers perform the same copy configuration file operation.

2. Configure the Master server
We need to configure 192.168.0.1 as the primary mysql Server (master), so we need to consider that we need to synchronize that database and use that user for synchronization. Here we want to be simple, the root user is used for synchronization, and only the database abc needs to be synchronized.

Open the configuration file:

Vi/usr/local/mysql/var/my. cnf
Find the information:

# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 1 // 1 is the master, 2 is the salve
Add two rows:

SQL-bin-update-same // synchronous format
Binlog-do-db = abc // database to be synchronized
Restart the mysql server of 192.168.0.1:

/Usr/local/mysql/bin/mysqladmin shutdown
/Usr/local/mysql/bin/mysqld_safe-user = mysql &
3. Configure the Slave server

Our slave server mainly takes the initiative to synchronize data back from the master server. We edit the configuration file:

Vi/usr/local/mysql/var/my. cnf
Find the following similar information:

# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 1
Modify the preceding server-id to 2 and add the following information:

Server-id = 2 // This Mysql is a slave server
Master-host = 192.168.0.1 // IP address of the master server
Master-user = root // the user who connects to the master server
Master-password = "// password used to connect to the master server
Master-port = 3306 // connection port
Master-connect-retry = 10 // Number of Retries
Replicate-do-db = abc // database to be synchronized
Log-slave-updates // synchronous form
Restart the mysql server of 192.168.0.2:

/Usr/local/mysql/bin/mysqladmin shutdown
/Usr/local/mysql/bin/mysqld_safe-user = mysql &
4. test and install

First, check the server log of slave:

Cat/usr/local/mysql/var/xxxxx_err (xxx is the host name)
Check whether the connection is normal. If such information is displayed, the connection is successful.

051031 11:42:40 mysqld started
051031 11:42:41 InnoDB: Started; log sequence number 0 43634
/Usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4. 1.15-log' socket: '/tmp/mysql. sock' port: 3306 Source distribution
051031 11:42:41 [Note] Slave SQL thread initialized, starting replication in log 'first'
At position 0, relay log'./new4-relay-bin.000001 'position: 4
051031 11:43:21 [Note] Slave I/O thread: connected to master 'root @ 192.168.0.1: 100 ′,
Replication started in log 'first' at position 4
View information on the Master

/Usr/local/mysql/bin/mysql-u root
View master Status:

Mysql> show master status;
View the mysql process information under the Master:

Mysql> show processlist;
View information on slave:

/Usr/local/mysql/bin/mysql-u root
View the slave status:

Mysql> show slave status;
View the mysql process information under slave:

Mysql> show processlist;
You can create a table structure and insert data in the abc library of the master, and then check whether the slave has synchronized the data to check whether the settings are successful.