MySQL master master replication

MySQL master replication is actually a two-way synchronization based on master-slave replication.

Master-slave replication: operations on master will be synchronized to slave, operations on slave will not be synchronized to master

Master master replication: operations can be done to two MySQL, and both can be synchronized to another MySQL database.

one。 Configure master-slave replication

Please refer to the Master-Slave copy blog (http://guoxh.blog.51cto.com/10976315/1922643)

two。 Configure master master replication

1. Modify MySQL configuration file

Master: enable relay log

Edit / etc/my.conf add relay-log=relay-log-binrelay-log-index=slave-relay-bin.index restart Service [root@master~] # service mysqld restart

Slave: open binary log

Edit / etc/my.conf add log-bin=master-binlog-slave-update=true restart service [root@slave ~] # service mysqld restart

two。 Configure synchronization

In the previous article, you added an authorized account to master and configured synchronization on slave.

Here, you only need to configure slave to add authorized accounts, and configure synchronization operations on master.

Slave:

Mysql > grant replication slave on *. * to 'slave'@'192.168.0.%' identified by' 123456The query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000001 | 106 | | +-+ 1 row in set (0.00 sec)

Master:

Mysql > change master to master_host='192.168.0.135',master_user='slave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=106;Query OK, 0 rows affected (0.40 sec) mysql > start slave # start synchronous Query OK 0 rows affected (0.00 sec) mysql > show slave status\ G # View synchronization status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.135 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000005 Read_Master_Log_Pos: 106 Relay_Log_File: relay-log-bin.000012 Relay_Log_Pos: 252 Relay_Master_Log_File: master-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 551 Until_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

# Slave_IO and Slave_SQL indicate that synchronization is successful for YES.

three。 test

1. Create a new database on slave

Mysql > create database master;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | aaa | | master | | mysql | | test | +-+ 5 rows in set (0.00 sec)

two。 Check on master

Mysql > show databases;+-+ | Database | +-+ | information_schema | | aaa | | master | | mysql | | test | +-+ 5 rows in set (0.00 sec)

3. Delete a database in master

Mysql > drop database aaa;Query OK, 0 rows affected (0.00 sec) mysql > show databases;+-+ | Database | +-+ | information_schema | | master | | mysql | | test | +-+ 4 rows in set (0.00 sec)

4. Check on slave

Mysql > show databases;+-+ | Database | +-+ | information_schema | | master | | mysql | | test | +-+ 4 rows in set (0.00 sec)

# at this point, the MySQL master replication configuration is complete!