How to synchronize MySQL database data _ MySQL

MySQL database synchronization is required during development. the two systems with the same installation are FreeBSD5.4 and Apache2.0.55 and PHP4.4.0. the MySQL version is 4.1.15, which is the latest version. 1. install and configure the two servers and install MySQL respectively in the usrlocalmysql Directory (the installation steps are omitted. refer to the relevant documentation.

MySQL database synchronization is required during development. the two systems with the same installation are FreeBSD5.4, and Apache 2.0.55 and PHP 4.4.0 are installed. the MySQL version is 4.1.15, which is the latest version.

1. install the configuration

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 will use the root user for synchronization for the sake of simplicity, in addition, you only need to synchronize the database abc.

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 omittedserver-id = 1 // 1 for Master, 2 is salve

Add two rows:

SQL-bin-update-same // synchronous form 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 omittedserver-id = 1

Modify the preceding server-id to 2 and add the following information:

Server-id = 2 // This MySQL is the slave server Master-host = 192.168.0.1 // IPMaster-user of the Master server = root // Master-password ='' // password for connecting 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 started051031 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 distribution051031 11:42:41 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './new4-relay-bin.000001' position: 4051031 11:43:21 [Note] Slave I/O thread: connected to Master 'root@192.168.0.1:3306', 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.

Finally, if you are interested, you can study the synchronization implementation of multiple slave instances on double-click hot backup or one Master.