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