MYSQL-Data Synchronization and two-way hot standby

MYSQL ---- data synchronization, two-way hot standby (to) original address: www. iteye. comtopic153875? 1. Brief Introduction: mysql provides the database replication function after version 3.23.15. Using this function can achieve two Database Synchronization, master-slave mode (A-B), mutual backup mode (AB) function. Mysql Database Synchronization replication settings

MYSQL ---- data synchronization, two-way hot standby (to) original address: http://www.iteye.com/topic/153875? 1. Brief Introduction: mysql provides the database replication function after version 3.23.15. With this function, two databases can be synchronized, master-slave mode (A-B), mutual backup mode (A = B. Mysql Database Synchronization replication settings

MYSQL ---- data synchronization, two-way hot standby (switch)

Address: http://www.iteye.com/topic/153875

?

1. Brief Introduction: mysql provides the database replication function after version 3.23.15. This feature allows you to synchronize two databases, master-slave mode (A-> B), and backup mode (A <=> B.
The settings of the mysql Database Synchronization replication function are reflected in the mysql configuration file. In linux, the configuration file is usually in/etc/mysql/my. cnf or my. cnf, my. cnf is in/etc/my. cnf; in windows, you can find my In the mysql installation path. ini.

?

2. Let's see how to configure data synchronization (A-> B ):
(Mysql 5.0.26)
Assume that database A is the host (the synchronization service will be provided to database B, that is, data in database B comes from database ):
Machine:
IP = 192.168.1.101
Machine B:
IP = 192.168.1.102

?

(1). The database in machine A is as follows:

// Database

Create database backup_db; USE backup_db; Create table 'backup _ table '( ? 'Id' int (11) not null auto_increment, ? 'Name' varchar (20) character set utf8 not null, ? 'Sex 'varchar (2) character set utf8 not null, ? Primary key? ('Id ') ) ENGINE = InnoDB default charset = latin1;

# In my. cnf (or my. ini) of machine A, configure:

Server-id = 1 Log-bin = c: \ mysqlback # synchronize the log file of the event Binlog-do-db = backup_db # database that provides the data synchronization service

?

(2). The database in machine B is as follows:

// Database B

Create database backup_db; USE backup_db; Create table 'backup _ table '( ? 'Id' int (11) not null auto_increment, ? 'Name' varchar (20) character set utf8 not null, ? 'Sex 'varchar (2) character set utf8 not null, ? Primary key? ('Id ') ) ENGINE = InnoDB default charset = latin1;

Note: the database structure of database A and database B must be the same; otherwise, synchronization cannot be formed.

# In machine B's my. cnf (or my. ini), configure:

Server-id = 2 Master-host = 192.168.1.101 # address of host Master-user = ym # the user that host A provides to B needs to include database backup_db permissions. Master-password = ym # Access password Master-port = 3306 # port, MYSQL port of the host Master-connect-retry = 60 # retry Interval 60 seconds Replicate-do-db = backup_db # synchronized Database

?

(3) After completing the preceding configuration, grant the mysql DATA permission of A to B.
Machine:

Mysql> grant file on *. * TO ym @ '192. 168.1.102 'IDENTIFIEDBY 'ym ';

?

(4) restart the AB database, and then:
Machine B:

Mysql> slave start;

View synchronization configurations
Machine:

Mysql> show master status;

Machine B:

Mysql> show slave status;

?

(5) Insert some data into the backup_db.backup_table table in A to check whether the backup_db.backup_table table in B has synchronized data changes. If the data synchronization result is not displayed, that is, the synchronization fails, check the error (as shown below ).
When an error occurs *. err log file (you can find it in the mysql installation directory), the synchronization thread exits. Repeat Step 4 after the error is corrected ).

?

3. Bidirectional Hot Standby (A <=> B ):
The above (1)-(5) Steps by A-B bidirectional configuration.