MySQL Primary master replication

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

Master-slave replication: To master the operation will be synchronized to slave, the slave do not sync to master;

Master replication: You can work with two MySQL, and you can sync to a different MySQL database.

I. Configuring Master-slave replication

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

Two. Configuring Primary master Replication

1. Modify the MySQL configuration file

Master: Turn on the trunk log

Edit/etc/my.conf Add relay-log=relay-log-binrelay-log-index=slave-relay-bin.index restart service [[Email protected]~]# Service mysqld restart

Slave: Open Binary Log

Edit/etc/my.conf Add log-bin=master-binlog-slave-update=true restart service [[email protected] ~]# service mysqld restart

2. Configure synchronization

The previous article has added an authorized account to master and configured the sync operation on Slave;

Only configure Slave to add authorized account, configure synchronization operation on master;

Slave

mysql> grant replication slave on *.* to  ' slave ' @ ' 192.168.0.% '   identified by  ' 123456 '; 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 Sync query ok, 0 rows affected  (0.00 sec) mysql> show slave  status \g  #查看同步状态 *************************** 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 for Yes to indicate that the synchronization was successful.

Three. Testing

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)

2. View on Master

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | AAA | | Master | | MySQL | | Test |+--------------------+5 rows in Set (0.00 sec)

3. Delete a database at 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. See above in slave

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | Master | | MySQL | | Test |+--------------------+4 rows in Set (0.00 sec)

#到这里, the MySQL master replication configuration is complete!

This article is from "Big Brother next Door" blog, please be sure to keep this source http://guoxh.blog.51cto.com/10976315/1922657

MySQL Primary master replication