MariaDB, MySQL dual master interoperability Model synchronization data

MySQL Dual master Interoperability is the two MySQL server is the primary server, but also the other side of the slave server, so as to share a part of the master server write requests, because if it is a single master model, the data write can only be written to a MySQL server, while the dual master model can be written on both servers, Effectively speeds up writes, and also provides backup capabilities for data.

The environment is as follows:

Server1:centos 6.5 x86_64 mariadb 10.0.10 ip:192.168.10.204

Server2:centos 6.5 x86_64 mariadb 10.0.10 ip:192.168.10.205

One: MY.CNF configuration of the server:

SERVER1:MY.CNF are all configured as follows:

[Email protected] ~]# grep-v "#"/etc/mysql/my.cnf

[Client]

Port= 3306

Socket=/tmp/mysql.sock

[Mysqld]

Port= 3306

Socket=/tmp/mysql.sock

Skip-external-locking

Key_buffer_size = 256M

Max_allowed_packet = 1M

Table_open_cache = 256

Sort_buffer_size = 1M

Read_buffer_size = 1M

Read_rnd_buffer_size = 4M

Myisam_sort_buffer_size = 64M

Thread_cache_size = 8

Query_cache_size= 16M

Thread_concurrency = 4

DataDir =/data/mydata

Innodb_file_per_table = on #每一条事物完成后就写入二进制

Log-bin=/data/binlogs/master-bin #二进制日志目录

Binlog_format=mixed

Server-id= #两台服务器的server-id must never be the same

auto_increment_increment=2 # defines each growth interval as 2, or step, with a starting value of 1 steps to 2 for each increase of 2, 1, 3, 5, 7, 9, for and from the server to differentiate values

Auto_increment_offset=1 # define the primary key auto-grow starting value

Skip_slave_start #跳过slave备份线程, that is, service Qidong time does not open automatically

Relay_log =/data/relaylogs/relay-bin #中继日志路径

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeou

Server2 my.cnf All configurations:

[Email protected] ~]# grep-v "#"/etc/mysql/my.cnf

[Client]

Port= 3306

Socket=/tmp/mysql.sock

[Mysqld]

Port= 3306

Socket=/tmp/mysql.sock

Skip-external-locking

Key_buffer_size = 256M

Max_allowed_packet = 1M

Table_open_cache = 256

Sort_buffer_size = 1M

Read_buffer_size = 1M

Read_rnd_buffer_size = 4M

Myisam_sort_buffer_size = 64M

Thread_cache_size = 8

Query_cache_size= 16M

Thread_concurrency = 4

Innodb_file_per_table = On

DataDir =/data/mydata

Log-bin=/data/binlogs/master-bin

Binlog_format=mixed

Server-id= 4

auto_increment_increment=2

auto_increment_offset=2

Skip_slave_start

Relay_log =/data/relaylogs/relay-bin

[Mysqldump]

Quick

Max_allowed_packet = 16M

[MySQL]

No-auto-rehash

[Myisamchk]

Key_buffer_size = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

Then start the MYSQLD service

Two: Authorize replication and initiate replication:

1, Server1:

Mysql> Grant Replication Slave,file on * * to ' tom ' @ ' 192.168.%.% ' identified by ' 123456 ';

mysql> flush Privileges;

2, Server2:

Mysql> Grant Replication Slave,file on * * to ' tom ' @ ' 192.168.%.% ' identified by ' 123456 ';

mysql> flush Privileges;

3, in each server point to each other when the primary server:

Server1:

mysql> change MASTER to master_host= ' 192.168.10.205 ', master_user= ' Tom ', master_password= ' 123456 ', master_log_file = ' master-bin.000004 ', master_log_pos=1160;

Note:master_log_file= ' master-bin.000004 ', master_log_pos=1160; is the other main server 192.168.10.205 view Show Master status know

Server2:

mysql> change MASTER to master_host= ' 192.168.10.204 ', master_user= ' Tom ', master_password= ' 123456 ', master_log_file = ' master-bin.000003 ', master_log_pos=526;

Note: master_log_file= ' master-bin.000004 ', master_log_pos=1160; is the other main server 192.168.10.204 view Show Master status know

4. Start slave Thread:

Server1:

mysql> start slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

Server2:

mysql> start slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

5. View Status:

Serever1:

Mysql> Show Slave Status\g

*************************** 1. Row ***************************

slave_io_state:waiting for Master to send event

master_host:192.168.10.205

Master_user:tom

master_port:3306

connect_retry:60

master_log_file:master-bin.000004

read_master_log_pos:1160

relay_log_file:relay-bin.000002

relay_log_pos:536

relay_master_log_file:master-bin.000004

Slave_io_running:yes #

Slave_sql_running:yes #此两个线程一定要启动

Server2:

Mysql>show slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.10.204

Master_user:tom

master_port:3306

Connect_retry:60

master_log_file:master-bin.000003

read_master_log_pos:843

relay_log_file:relay-bin.000002

relay_log_pos:853

relay_master_log_file:master-bin.000003

Slave_io_running:yes

Slave_sql_running:yes

6. Create a library and table in any server and insert the content difference may be able to synchronize:

Server1:

mysql> CREATE DATABASE Linux;

Query OK, 1 row Affected (0.00 sec)

Server2:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Linux |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

5 rows in Set (0.04 sec)

mysql> use Linux;

Database changed

mysql> CREATE TABLE t1 (id int);

Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO T1 values (1), (2), (3);

Query OK, 3 rows affected (0.08 sec)

Records:3 duplicates:0 warnings:0

Mysql> select * from T1;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 Rows in Set (0.00 sec)

Server1:

mysql> INSERT INTO T1 values (4), (5), (6);

Query OK, 3 Rows Affected (0.00 sec)

Records:3 duplicates:0 warnings:0

Mysql> select * from T1;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

+------+

6 rows in Set (0.00 sec)

This article is from the "Linux" blog, so be sure to keep this source http://zhangshijie.blog.51cto.com/806066/1606725

MariaDB, MySQL dual master interoperability Model synchronization data