When doing development, I need to synchronize MySQL databases. The two systems have the same installation, both are FreeBSD5.4. Apache 2.0.55 and PHP 4.4.0 are installed. The MySQL version is 4.1.15, which are the latest versions. .
1. Install and configure
two servers. Install MySQL separately and install them in the /usr/local/MySQL directory (the installation steps are omitted, please refer to the relevant documents). The IPs of the two servers are 192.168.0.1 and 192.168.0.2 respectively. We use 192.168.0.1 as the master database and 192.168.0.2 as the slave server. We use one-way synchronization, that is, the master's data is the master's data, and then the slave actively goes to the master to synchronize the data back.
The configurations of the two servers are the same. Let's copy the key configuration files. The default configuration files are in the /usr/local/MySQL/share/MySQL directory, which include my-large.cnf, my-medium.cnf, my -small.cnf and other writers, we are just testing, just use my-medium.cnf. After MySQL is installed, the default configuration file is specified in the database storage directory. We are using 4.1.X, so the configuration file should be in the /usr/local/MySQL/var directory, so copy the configuration file there. :
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 main MySQL server (master). Then we have to consider which database we need to synchronize and which user to use for synchronization. For the sake of simplicity, we use the root user for synchronization here, and Only database abc needs to be synchronized.
Open the configuration file:
vi /usr/local/MySQL/var/my.cnf
Find this 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 is master, 2 is salve
Add two lines:
sql-bin-update-same //Synchronization form binlog-do-db = abc //Database to be synchronized
Restart the MySQL server at 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 to the master server. We edit the configuration file:
vi /usr/local/MySQL/var/my.cnf
Find similar information below:
# 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
Change the server-id above to 2 and add some information:
server-id = 2 //This MySQL is a slave server master-host = 192.168.0.1 //The IP of the master server master-user = root //The user who connects to the master server master-password = '' //The password for connecting to the master server master -port = 3306 //Connect port master-connect-retry = 10 //Number of retries replicate-do-db = abc //Database to be synchronized log-slave-updates //Synchronization form
Restart the MySQL server at 192.168.0.2:
/usr/local/MySQL/bin/MySQLadmin shutdown /usr/local/MySQL/bin/MySQLd_safe --user=MySQL &
4. To test the installation,
first check the slave’s host log:
cat /usr/local/MySQL/var/xxxxx_err (xxx is the host name)
Check whether the connection is normal. If you see a message like this, you are 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 '[email protected]:3306' , replication started in log 'FIRST' at position 4
View information on Master
/usr/local/MySQL/bin/MySQL -u root
View master status:
MySQL> show master status;
Check the MySQL process information under Master:
MySQL> show processlist;
View information on slave:
/usr/local/MySQL/bin/MySQL -u root
View slave status:
MySQL> show slave status;
Check the MySQL process information under slave:
MySQL> show processlist;
You can then create a table structure in the master's abc library and insert data, and then check whether the slave has synchronized the data, and you can check whether the setting is successful.
Finally, if you are interested, you can study double-click hot backup, or the synchronization implementation of one master and multiple slaves.