How to configure a two-way master-slave replication server for MySQL

1. Environment Description

Server A (master) 192.85.1.175

Server B (slave) 192.85.1.176

Mysql version: 5.1.61

System Version: System OS: ubuntu 10.10X86

(System installation and data environment setup, omitted)

2. Create a synchronization User:

Run the following command in service B:

      grant replication slave on *.* to 'replication'@'%' identified by 'mysqlsync';        flush privileges;        grant FILE,SELECT, replication slave on *.* to 'replication'@'192.85.1.175' identified by 'mysqlsync';        flush privileges;  

Run the following command on server:

Grant replication slave on *. * to 'replicase' @ '%' identified by 'mysqlsync ';
Flush privileges;
Grant FILE, SELECT, replication slave on *. * to 'replicase' @ '192. 85.1.176 'identified by 'mysqlsync ';
Flush privileges;

On server A and server B, check whether the master-slave replication account is normal:

(1) Run "show grants for 'replicase' @ '%'" on the MYSQL command lines of server A and server B respectively ';"

If the output result is similar to the following, it indicates that it is normal, and the others are exceptions.

mysql> show grants for 'replication'@'%';+--------------------------------------------------------------------------------------------------------------------------------------+| Grants for replication@%                                                                                                             |+--------------------------------------------------------------------------------------------------------------------------------------+| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '*47E2485DF0DBED84B9BD90AF25F48A36E7EAD57E' |+--------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

(2) Use the master-slave replication account to remotely log on to server B on server A and remotely log on to server B on server A. test. If the account can be properly logged on, you can use it.

If the following error message is displayed:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111)sxzichen@ubutun:/var/log/mysql$ ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.175' (111)

Or

ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111)sxzichen@ubutun:/var/log/mysql$ ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111) ERROR 2003 (HY000): Can't connect to MySQL server on '192.85.1.176' (111)

The cause is that the MYSQL database does not allow remote access. You need to modify the database configuration files my. cnf, comment out "bind-address = 127.0.0.1", that is, "# bind-address = 127.0.0.1"

Restart the MYSQL server separately. The test is normal.

3. Configure master data switch server A, modify the corresponding data configuration file ---- my. cnf, and add the following lines in [MYSQLD }:

#mysql replication  server-id = 1 log_bin = /var/log/mysql/mysql-bin.log  master-host = 192.85.1.176  master-user = replication master-password = mysqlsync master-port = 3306 master-connect-retry = 60 binlog-do-db = hrkip_zhhwd binlog-ignore-db = mysql   binlog-ignore-db = information_schema binlog-ignore-db = phpmyadmin replicate-do-db = hrkip_zhhwd    replicate-ignore-db = mysql,information_schema 

Brief Introduction to configuration information:

Server-id = 1 # host id, integer
Log_bin =/var/log/mysql/mysql-bin.log # Make sure this file is writable
Binlog-do-db = hrkip_zhhwd # You need to back up data and write multiple rows.
Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple write lines

Master-user = replication # Name of the remote database logon user
Master-password = mysqlsync # password for remotely logging on to the database
Master-port = 3306 # remote database server port number. You can use the show variables like 'Port' command to view the port number, which must be consistent with the port number of the target database server.

4. configure Server B for data master switch, modify its corresponding data configuration file ---- my. cnf, and add the following lines in [MYSQLD }:

#mysql replication  server-id = 2 log_bin = /var/log/mysql/mysql-bin.log  master-host = 192.85.1.175  master-user = replication master-password = mysqlsync master-port = 3306 master-connect-retry = 60 binlog-do-db = hrkip_zhhwd binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = phpmyadmin replicate-do-db = hrkip_zhhwd replicate-ignore-db = mysql,information_schema 

5. Check whether the configuration is normal (l check the two servers separately ):

Start the slave service and run it on the MYSQL command line.

mysql> slave start ;                Query OK, 0 rows affected, 1 warning (0.00 sec)

View the slave status and run the following command:

mysql> show slave status \G ;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.85.1.175                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000002          Read_Master_Log_Pos: 106               Relay_Log_File: ubutun-relay-bin.000005                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: hrkip_zhhwd          Replicate_Ignore_DB: mysql,information_schema           Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 106              Relay_Log_Space: 552              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

If you need to restart the slave server, first run slave stop, then run reset slave to delete my. mysql-bin under the log_bin directory (/var/log/mysql/) in the cnf file. ", and then run the slave start command.

6. perform A test to check whether the master and slave servers are working normally. When slave is started, both servers are started. Do not close the server and restart the server, for example, after server A is started.

Insert data in server

INSERT INTO ddgl_qdlx ( qdlxmc, lxjc, qdlxbz) VALUES('175a', '75a', '175a');

The corresponding record is displayed in server B.

Also insert in server B:

INSERT INTO ddgl_qdlx (qdlx_id, qdlxmc, lxjc, qdlxbz) VALUES(  '176b', '76b', '176b') ;

The corresponding record is displayed in server.

Test OK.