MySQL establishes a bidirectional primary and standby replication server configuration method

1. Environmental description

Server A (Master) 192.85.1.175

Server B (from) 192.85.1.176

MySQL version: 5.1.61

System version: Os:ubuntu 10.10 X86

(System installation and Data Environment construction, omitted)

2. Create a sync User:

At the Service B command line, execute:

      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;  

On Server A command line execution:

      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 that the primary and standby replication accounts are normal on a and B servers:

(1) Execute "Show grants for ' replication ' @ '% ' on the MySQL command line on a and b servers respectively;"

If the output is similar to the following, it means normal, and the others are exceptions

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 a server remote login B, the B server Telnet A, test, if you can log in instructions, the account is normal, you can use.

If the following error message appears:

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 allow remote access, you need to modify the A and B server in the database configuration file my.cnf, commented out "bind-address = 127.0.0.1" is "#bind-address = 127.0.0.1 "

Restart MySQL server separately, test normal

3. Configure the Data master switch Server A, modify its corresponding data profile----MY.CNF, add the following line 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 = 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    

Configuration Information Brief Introduction:

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 #远程登录数据库用户名称
Master-password = Mysqlsync #远程登录数据库用户密码
Master-port = 3306 #远程数据库服务器端口号, you can use show variables like ' Port '; Command view, necessary to match the target database server port number

4. Configure the data master switch Server B, modify its corresponding data profile----MY.CNF, add the following line in [MYSQLD}

#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 execute it on the MySQL command line

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

To view the slave status, execute 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

If you need to restart the slave server, first perform slave stop, then perform reset slave, delete the Log_bin directory (/var/log/mysql/) in the my.cnf file corresponding to the MYSQL database Mysql-b In. " Start with all the files, and then execute the slave start command.

6. For testing, the primary and standby is working properly, slave boot, only need to start on a server, for example, after starting on Server A.

Inserting in Server a data

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

You can see the corresponding records in Server B.

Also insert in Server B:

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

You can see the corresponding record in Server A.

Test OK.