MySQL master-slave replication and dual master replication

Previously done a multi-instance MySQL on a single machine, this time separate to do, using two hosts.

The host addresses used here are:

master:192.168.214.135

slave:192.168.214.128

The two hosts already have MySQL installed, but two database data are different

Master-slave replication roughly step

Basic steps for configuring MySQL replication:

First, Master

1. Enable the binary log
Log-bin = Master-bin
Log-bin-index = Master-bin.index

2, choose a unique Server-id
Server-id = {0-2^32}

3. Create a user with copy permissions
REPLICATION SLAVE

Second, slave

1. Enable the relay log
Relay-log = Relay-log
Relay-log-index =

2, choose a unique Server-id
Server-id = {0-2^32}

3, connect to the master server, and start copying data;
Mysql> CHANGER MASTER to master_host= ', master_port= ', master_log_file= ', master_log_fiel_pos= ', master_user= ' , master_password= ';
Mysql> START SLAVE;

mysql> START SLAVE Io_thread;
mysql> START SLAVE Sql_thread;

Check the database configuration file for master server master below

[mysqld]port= 3306socket=/tmp/mysql.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet = 1Mtable_ Open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 64Mthread_ Cache_size = 8query_cache_size= 16mthread_concurrency = 8datadir=/mydata/datalog-bin=master-binlog-bin-index= Master-bin.indexbinlog_format=mixed#relay-log = Relay-mysql#relay-log-index = relay-mysql.index# Auto-increment-increment = 2#auto-increment-offset = 1server-id= 1

Configure the slave configuration file below

[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysqldatasocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0 server-id= 2relay-log = rel Ay-logrelay-log-index = Relay-log.index
The other configuration items for the master and slave databases are different, regardless of

One: After the configuration file of the master-slave library is set up, log on to the master library and create a user with copy permission

Grant Replication Slave on * * to ' Feng ' @ ' 192.168.%.% ' identified by ' fsz123 ';

Flush privileges;

Slave before adding the master information, look at the Bin-log logging location node of the master library and execute it in the Master library:

Mysql> Show master status;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
| master-bin.000029 | 107 | | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.01 sec)

II: Login To add master information from library slave

mysql> Change Master to  master_host= ' 192.168.214.135 ', master_user= ' Feng ', master_password= ' fsz123 ', master_ Log_file= ' master-bin.000029 ', master_log_pos=323; Query OK, 0 rows affected (0.15 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec)

Three: Start Copy from library:

Mysql> SLAVE START;

Execute: Show slave status\g view replication status

Mysql> Show slave status\g*************************** 1. Row ***************************               slave_io_state:waiting for Master to send event                  master_host:192.168.214.135< C2/>master_user:feng                  master_port:3306                connect_retry:60              master_log_file:master-bin.000029          Read_ master_log_pos:323               relay_log_file:relay-log.000002                relay_log_pos:254        relay_master_log_file: master-bin.000029             slave_io_running:yes            slave_sql_running:yes              
IO threads and SQL threads from the library are already open
Execute show databases below; View Database Synchronization Status

View data information from a library

Database           |+--------------------+| information_schema | | demo               | | demo222            | | demo333            | | mysql              | | Performance_schema | | Test     
The new demo222,demo333 in the main library has arrived from library

//////////////////////////////////////////////////////////////////////////////////////////////////////

Dual Master mode configuration for MySQL

　　

On the basis of the above, and then increase the implementation of dual-master replication, the implementation of dual-master mode, in the library to open the binary log file, while the main library also increased slave relay log function

Edit from Library my.cnf

Log-bin=master-bin
Log-bin-index=master-bin.index
Binlog_format=mixed

Relay-log = Relay-mysql
Relay-log-index = Relay-mysql.index
Auto-increment-increment = 2
Auto-increment-offset = 2

Edit Main Library my.cnf add slave log

Log-bin=master-binlog-bin-index=master-bin.indexbinlog_format=mixed
Auto-increment-increment = 2            #步进值auto_imcrement. Normally there are N main MySQL to fill n
Auto-increment-offset = 1               #步进值auto_imcrement. Normally there are N main MySQL to fill n

#binlog-ignore=mysql #忽略mysql库 "or other libraries that do not require synchronous replication"

# REPLICATE-DO-DB=AA #要同步的数据库, default all Libraries "or other libraries specified for replication"

Relay-log = Relay-mysqlrelay-log-index = Relay-mysql.index

Perform the change master to instruction in the main library

Change Master to  master_host= ' 192.168.214.128 ', master_user= ' Feng ', master_password= ' fsz123 ', master_log_file= ' master-bin.000001 ',master_log_pos=107;mysql> flush privileges;

Start the replication feature in the main library

Start slave;

At this point, MySQL dual master replication is complete.

MySQL master-slave replication and dual master replication