MySQL Dual master replication model

Experimental environment: centos6.6 MariaDB 10.0.102 Binary Installation

This is no longer an indication that the installation process directly begins configuring Master-slave nodes

Node 1:172.16.30.1

[Email protected] ~]# vim/etc/mysql/my.cnfthread_concurrency = 4datadir =/mydata/data #数据库存放目录innodb_file_per_table = 1 # #innodb引擎单表单文件log-bin=/mydata/binlogs/mysql-bin # #二进制文件存放目录relay-log=/mydata/relaylogs/relay-mysql # # Relay Log Server-id = 1 # #server ID used to differentiate each mysql server auto-increment-offset = 2 # # Auto-growing data type of step auto-increment-increment = 1 # #起始值

Node 2:172.16.30.2 only two points different from above

Server-id = 1auto-increment-increment = 2

Two hosts are authorized separately: Only the replication permissions are granted

Node 1 gives the Node 2 authorization:

MariaDB [(None)]> GRANT REPLICATION slave,replication CLIENT on *. * to ' mysql ' @ ' 172.16.30.2′identified by ' 112613 ';

Show binary log in front position

MariaDB [(None)]> SHOW MASTER status;+ —————— + ———-+ ———— –+ —————— +| File | Position | binlog_do_db | binlog_ignore_db |+ —————— + ———-+ ———— –+ —————— +|      mysql-bin.000006 |              536 |                  | |+ —————— + ———-+ ———— –+ —————— +1 row in Set (0.01 sec)

Node 2 gives the Node 1 authorization:

MariaDB [(None)]> GRANT REPLICATION slave,replication CLIENT on *. * to ' mysql ' @ ' 172.16.30.1′identified by ' 112613 ';

Displays the current location of the binary log

MariaDB [(None)]> SHOW MASTER status;+ —————— + ———-+ ———— –+ —————— +| File | Position | binlog_do_db | binlog_ignore_db |+ —————— + ———-+ ———— –+ —————— +|      mysql-bin.000003 |              541 |                  | |+ —————— + ———-+ ———— –+ —————— +1 row in Set (0.01 sec)

Node 1 connection Node 2:

MariaDB [(None)]> change MASTER to master_host= ' 172.16.30.2′,master_user= ' mysql ', master_password= ' 112613′,master _log_file= ' mysql-bin.000003′,master_log_pos=541; # #这里是节点2的二进制日志文件的位置

Node 2 connection Node 1:

MariaDB [(None)]> change MASTER to master_host= ' 172.16.30.2′,master_user= ' mysql ', master_password= ' 112613′,master _log_file= ' mysql-bin.000006′,master_log_pos=536;# #这里是节点1的二进制的日志文件的位置

On Node 1, open the replication thread and view

650) this.width=650; "Src=" http://linuxu.qiniudn.com/wp-content/uploads/2015/02/ Wpid-966d1d0b54ddb009f12d504d96739c4b_49807781.png "style=" font-size:10.5pt;line-height:1.5; "border=" 0 "/>

On Node 2, open the replication thread and view:

650) this.width=650; "Src=" http://linuxu.qiniudn.com/wp-content/uploads/2015/02/ Wpid-966d1d0b54ddb009f12d504d96739c4b_49903750.png "border=" 0 "/>

Now node 1 and Node 2 are already synchronized.

Create the database and table on Node 1:

MariaDB [(None)]> CREATE DATABASE Linux; Query OK, 1 row affected (0.07 sec) MariaDB [(none)]> use linux;database changedmariadb [linux]> CREATE TABLE des (ID INT UNSIGNED not NULL PRIMARY KEY auto_increment,year VARCHAR (30)); Query OK, 0 rows affected (0.14 sec)

In Node 2 view:

MariaDB [(None)]> SHOW databases;+ —————— –+| Database |+ —————— –+| Information_schema | | Linux | | MySQL | | Performance_schema | | Test |+ —————— –+5 rows in Set (0.01 sec) MariaDB [(none)]> use linux;database changedmariadb [linux]> SH OW tables;+ ————— –+| Tables_in_linux |+ ————— –+| Des |+ ————— –+1 Row in Set (0.01 sec)

It's already synced.

Create a table and insert data in Node 2

MariaDB [linux]> CREATE TABLE centos (id INT UNSIGNED not NULL PRIMARY KEY auto_increment,ver VARCHAR (20)); Query OK, 0 rows affected (0.17 sec) MariaDB [linux]> INSERT into CentOS (ver) VALUES (' 6.6′ '), (' 7.0′ '); Query OK, 2 rows affected (0.07 sec) records:2 duplicates:0 warnings:0mariadb [linux]> SHOW tables;+ ————— –+| Tables_in_linux |+ ————— –+| CentOS | | Des |+ ————— –+2 rows in Set (0.01 sec)

In Node 1 view:

MariaDB [linux]> SHOW tables;+ ————— –+| Tables_in_linux |+ ————— –+| CentOS | | Des |+ ————— –+2 rows in Set (0.00 sec) MariaDB [linux]> SELECT * from centos;+--+--+| ID |  Ver |+--+--+| 2 |  6.6 | | 4 | 7.0 |+--+--+2 rows in Set (0.00 sec)

It's already synced.

So the double master copy of MARIADB is ready to work.

This article is from the "Linuxu" blog, make sure to keep this source http://linuxu.blog.51cto.com/9471357/1613486

MySQL Dual master replication model