MySQL Dual master-slave synchronization and Bidirectional synchronization

The first part: master-slave relationship synchronization master end  192.168.0.1slave end   192.168.0.21. Master side A. Enter MySQL to create a database abc:      create database abc;b. Create a user to synchronize with, Specify only the 192.168.0.2 login:       grant replication slave on *.*  to  ' ha ' @ ' 192.168.0.2 '  identified by  ' hapwd '; c. Modify the/etc/my.cnf file on the master side        server_id = 1  (for 1 means master,2 represents slave)        binlog-do-db = abc  (ABC indicates the database to be synchronized), if there are multiple databases, one row per database        binlog-ignore-db= mysql     set up a database that does not need to be synchronized, one row per database   (this can not be written in general)        log-bin = mysql-bind. Restart MYSQL2. Slave end A. Modify the/etc/my.cnf file on the slave side       server_id = 2       log-bin = mysql-bin      master-host =  192.168.0.1      master-user = ha      master-password =   ' Hapwd '   (two single quotes if empty)       master-port = 3306  ( Based on the specified port number)       master-connect-retry = 10  Connection retry Count        replicate-do-db = abc  the database name to receive, if there are multiple databases, one row per database        replicate-ignore-db= mysql    set the database not to be received, one row per database     (This can not be written in general) b. Restart the mysql       configuration will generate Master.info in the MySQL directory if you want to change the slave settings. To remove Master.info first, it will work. C. Go to MySQL, execute:      slave start;    View synchronization with show slave status  or show master status  Part II: Synchronous Master End of bidirectional relationship   192.168.0.1slave Terminal   192.168.0.21. Master side A. Enter MySQL to create a database abc:      create database abc;b. Create a user to synchronize with, Specify only the 192.168.0.2 login: &NBsp;     grant replication slave on *.* to  ' ha ' @ ' 192.168.0.2 '  identified by  ' hapwd '; c. Modify the/etc/my.cnf file on the master side       Log-bin     server-id = 1     sql-bin-update-same   Sync mode &nbsp, in the version above MYSQL5 do not need this sentence, otherwise it will not start the MySQL service       binlog-do-db= abc      set up a synchronization database, if there are multiple databases, one row per database      binlog-ignore-db =  mysql  set up a database that you do not synchronize, such as multiple databases, one row per database      master-host=192.168.0.2      master-user=ha     master-password= ' Hapwd '       master-port=3306     master-connect-retry=10      replicate-do-db=abc    set the database to be received, if there are multiple databases, one row per database       replicate-ignore-db= mysql    set not to receive the database,One row per database     (generally this can not be written)      log-slave-updates     This sentence is not required for versions above MYSQL5 D. Restart Mysqle. Go to MySQL, execute:      slave start;2. Slave end A. Go to MySQL and create a database abc:      create database abc;b. Create a user to synchronize with, Specify only the 192.168.0.1 login:       grant replication slave on *.*  to  ' ha ' @ ' 192.168.0.1 '  identified by  ' hapwd '; c. Modify/etc/my.cnf files on the slave side       log-bin     server-id       =  2     binlog-do-db= abc     set up a synchronization database, if there are multiple databases, one row per database      binlog-ignore-db = mysql  set up databases that do not synchronize, such as having multiple databases, one row per database       sql-bin-update-same    sync mode &nbsp, this sentence is not required for versions above MYSQL5, otherwise it will not start MySQL service        master-host=192.168.0.1     master-user= ha     master-password= ' Hapwd '       master-port=3306     master-connect-retry=10      replicate-do-db=abc    set up a database to receive, such as multiple databases, one row per database       replicate-ignore-db= mysql    set not to receive a database, one row per database     (generally this can not be written)       log-slave-updates    does not need this sentence for versions above MYSQL5 D. Restart Mysqle. Go to MySQL, execute:      slave start; Note: 1. If one end modifies the data and the other end is not received, manually executing the command load data from master; can be 2. If it is impossible to synchronize because the binary log file was mistakenly deleted, then execute: (These steps are useful, you can even delete the ABC library on the slave, he will automatically copy it from master to rebuild) slave stop;reset slave; Slave start;3. We often find that when resynchronization occurs after Reset slave, there is a case where duplicate data cannot be poured, such as:                Last_Errno: 1062                  Last_Error: Error  ' duplicate entry  ' 1472533 '  for key  1 '  on query. Default database:  ' EPG ' . query:  ' insert into  Boot_info (stbid,begintime)                  values (' zk124070c819 ', ' 1259128442 ') ' At this point, you can skip this line of conflict: Slave stop;set global sql_slave _skip_counter=1;  slave start;

This article is from the "Linux_config" blog, make sure to keep this source http://jialiang1026.blog.51cto.com/10119067/1632258

MySQL Dual master-slave synchronization and Bidirectional synchronization