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

MySQL two-way master-slave replication server configuration method 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 'replication'@'%' identified by 'mysqlsync';flush privileges;grant FILE,SELECT, replication slave on *.* to 'replication'@'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 replicationserver-id = 1 log_bin = /var/log/mysql/mysql-bin.logmaster-host = 192.85.1.176master-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_zhhwdreplicate-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 replicationserver-id = 2 log_bin = /var/log/mysql/mysql-bin.logmaster-host = 192.85.1.175master-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 eventMaster_Host: 192.85.1.175Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 106 Relay_Log_File: ubutun-relay-bin.000005Relay_Log_Pos: 251Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: hrkip_zhhwdReplicate_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: 0Exec_Master_Log_Pos: 106Relay_Log_Space: 552Until_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: NoLast_IO_Errno: 0Last_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 nodes are working normally. when slave is started, it only needs to be started on one 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.