MySQL establishes a bidirectional primary and standby replication server configuration method

1. Environmental descriptive narrative

ServerA (Master) 192.85.1.175

ServerB (from) 192.85.1.176

MySQL version number: 5.1.61

System version number: Os:ubuntu 10.10 X86

(System installation and Data Environment construction, omitted)

2. Create a sync User:

Run on the Service B command line:

      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 on the ServerA command line:

      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;  

Check whether the primary and standby replication account is normal on a and bserver:

(1) Run "Show Grants for ' replication ' @ '% ' on the MySQL command line on a and bserver respectively";

Assuming that the output is similar to the following, which means normal, the other is an exception

Mysql> Show grants for ' replication ' @ '% '; +--------------------------------------------------------------------- -----------------------------------------------------------------+| Grants for [email protected]%                                                                                                             |+----------------------------------------------------------------------------- ---------------------------------------------------------+| GRANT SELECT, FILE, REPLICATION SLAVE on * * to ' REPLICATION ' @ '% ' identified by PASSWORD ' *47E2485DF0DBED84B9BD90AF25F48A3 6e7ead57e ' |+--------------------------------------------------------------------------------------------------- -----------------------------------+1 Row in Set (0.00 sec)

(2) using the primary and standby replication account in Aserver remote login b, in bserver telnet A, test, assuming that the normal login instructions, the account is normal, can be used.

Assume that an error message such as the following occurs:

Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.85.1.175 ' (111) [Email protected]:/var/log/mysql$ ERROR 2003 (HY0 XX): 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) [Email protected]:/var/log/mysql$ ERROR 2003 (HY0 XX): 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 reason is that the MySQL database does not agree to remote access, you need to change a and bserver in the database configuration file my.cnf, stare out "bind-address = 127.0.0.1" is "#bind-address = 127.0.0.1 "

Start MySQLServer again, test normal

3. Configure the ServerA of the data master switch, change its corresponding data configuration file----my.cnf, in the [MYSQLD} join for example the following line

#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 = binlog-do-db = Hrkip_zh Hwd binlog-ignore-db = mysql   binlog-ignore-db = information_schema binlog-ignore-db = phpMyAdmin replicate-do-db = hr Kip_zhhwd    

Introduction to configuration information:

Server-id = 1 #主机标示, integer
Log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写
Binlog-do-db =hrkip_zhhwd #须要备份数据, more than one write multiple lines
Binlog-ignore-db =mysql #不须要备份的数据库, more than one write multiple lines

Master-user = Replication #远程登录数据库username称
Master-password = Mysqlsync #远程登录数据库用户密码
Master-port = 3306 #远程数据库server端口号, able to use show variables like ' Port '; Command view, consistent with target database server port number

4. Configure the ServerB of the data master switch, change its corresponding data configuration file----my.cnf, in the [MYSQLD} join for example the following line

#mysql replication  Server-id = 2 Log_bin =/var/log/mysql/mysql-bin.log  master-host = 192.85.1.175  

5. Check that the configuration is normal (l check separately on both servers):

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)

To view the slave status, run the command:

Mysql> show slave status \g; *************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos  t:192.85.1.175 master_user:replication master_port:3306 connect_retry: 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_run Ning: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 Ma                Ster_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

Suppose you need to start slave server again, run slave stop first, then run reset slave, delete the Log_bin folder (/var/log/mysql/) in the corresponding my.cnf file of the MYSQL database, under "my Sql-bin. " Start the MySQL service again, and then run the slave Start command.

6. Test, the main preparation is working properly, slave start, two servers are started, do not shut down and restart after startup, such as ServerA on the boot.

Inserting in the ServerA data

INSERT into DDGL_QDLX (QDLXMC, LXJC, QDLXBZ) VALUES (' 175a ', ' 75a ', ' 175a ');

You can see the corresponding records in the ServerB.

Same as inserted in ServerB:

INSERT into DDGL_QDLX (qdlx_id, QDLXMC, LXJC, QDLXBZ) VALUES (  ' 176b ', ' 76b ', ' 176b ');

You can see the corresponding records in the ServerA.

Test OK.

MySQL establishes a bidirectional primary and standby replication server configuration method