MYSQL/MAIRADB Dual Master replication

MYSQL/MAIRADB Dual Master replication

NODE5:172.16.92.5/16 MARIADB Primary server 1
NODE6:172.16.92.6/16 MARIADB Primary server 2
The above nodes are CentOS 7.1

Configuring the Environment
1. Configure the disc yum source
2. Turn off SELinux and iptables

============. Install Mariadb-server and configure the file ===========

NODE5:MARIADB Primary Server

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

###### #以下的内容为添加 ########
#二进制变更日志
Log-bin=mysql-bin
#二进制日志格式为混合模式
Binlog_format=mixed
#为主服务器node5的ID值
Server-id = 5
Relay-log=relay-bin
#第一个变量名 Auto_increment_offset refers to the starting value of the self-increment field.
Auto_increment_offset=1
#第二个变量名 auto_increment_increment refers to how much the field is incremented at a time;
auto_increment_increment=2
Log_slave_updates = 1

Port = 3306
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
Skip_name_resolve = On
###############################

###### The following content is optional ########
[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
#############################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d
############### End for My.cnf #################


NODE6:MARIADB Primary server 2

[Email protected] ~]# yum-y install Mariadb-server
[Email protected] ~]# VIM/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
# Settings user and group was ignored when Systemd was used.
# If you need to run mysqld under a different user or group,
# Customize your systemd unit file for mariadb according to the
# instructions in HTTP://FEDORAPROJECT.ORG/WIKI/SYSTEMD

########## Add the following content ##########
Log-bin=mysql-bin
Binlog_format=mixed
Server-id = 6
Relay-log = Relay-bin
#第一个变量名 Auto_increment_offset refers to the starting value of the self-increment field.
auto_increment_offset=2
#第二个变量名 auto_increment_increment refers to how much the field is incremented at a time;
auto_increment_increment=2
Log_slave_updates = 1

Port = 3306
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
Skip_name_resolve = On
###################################

######### The following content is optional ############
[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
####################################

[Mysqld_safe]
Log-error=/var/log/mariadb/mariadb.log
Pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the Config directory
#
!includedir/etc/my.cnf.d

############# End of My.cnf ###############


============ two. Turn on the MARIADB service and add an authorized user ===========

node5:172.16.92.5 Primary server 1
[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(None)]> grant replication client,replication slave on * * to ' repluser ' @ ' 172.16.%.% ' identified by ' Replpass ' ;
MariaDB [(None)]> flush privileges;
MariaDB [(None)]> Show Master Status\g
1. Row ***************************
file:mysql-bin.000003
position:506
binlog_do_db:
binlog_ignore_db:
##### note mysql-bin.000003 and 506, useful when setting up master server 2 trunk Log


node6:172.16.92.6 Primary server 2
[Email protected] ~]# systemctl start mariadb
[[email protected] ~]# MySQL
MariaDB [(None)]> grant replication client,replication slave on * * to ' repluser ' @ ' 172.16.%.% ' identified by ' Replpass ' ;
MariaDB [(None)]> flush privileges;
MariaDB [(None)]> Show Master Status\g
1. Row ***************************
file:mysql-bin.000003
position:506
binlog_do_db:
binlog_ignore_db:
##### note mysql-bin.000003 and 506, it is useful to set the primary server 1 trunk log #####


============ three. Set each other as the primary node and turn on the synchronization thread ===========
node5:172.16.92.5 Primary server 1
MariaDB [(none)]> change master to master_host= ' 172.16.92.6 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=506;
MariaDB [(None)]> start slave;

node6:172.16.92.6 Primary server 2
MariaDB [(none)]> change master to master_host= ' 172.16.92.5 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=506;
MariaDB [(None)]> start slave;



============ four. View the two node synchronization process status for primary master replication ===========
node5:172.16.92.5 Primary server 1
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.92.6
Master_user:repluser
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:506
relay_log_file:relay-bin.000002
relay_log_pos:529
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
...... The rest of the information is omitted ...

MariaDB [(None)]> show Processlist\g
3. Row ***************************
State:slave have read all relay log; Waiting for the slave I/O thread to update it
...... The rest of the information is omitted ...
4. Row ***************************
State:master have sent all binlog to slave; Waiting for Binlog to be updated
...... The rest of the information is omitted ...
#说明: Dual master nodes have sent relay logs to each other



node6:172.16.92.6 Primary server 2
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.92.5
Master_user:repluser
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:506
relay_log_file:relay-bin.000002
relay_log_pos:529
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
...... The rest of the information is omitted ...

MariaDB [(None)]> show Processlist\g
2. Row ***************************
State:master have sent all binlog to slave; Waiting for Binlog to be updated
...... The rest of the information is omitted ...
4. Row ***************************
State:slave have read all relay log; Waiting for the slave I/O thread to update it
...... The rest of the information is omitted ...



Finally, we test whether the two nodes of MySQL dual master model can synchronize data with each other.
NODE5 MARIADB1
MariaDB [(None)]> CREATE DATABASE mydb;
MariaDB [(None)]> use MyDB;
MariaDB [mydb]> CREATE TABLE tb1 (id int unsigned NOT NULL auto_increment primary key, name Char (30));
MariaDB [mydb]> INSERT into TB1 (name) VALUES (' Tom ');
MariaDB [mydb]> INSERT into TB1 (name) VALUES (' Jerry ');
MariaDB [mydb]> SELECT * from TB1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Tom |
| 3 | Jerry |
+----+-------+

Node6 MARIADB2
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
|        MyDB | #能看到node5创建的数据库
| MySQL |
| Performance_schema |
| Test |
+--------------------+
MariaDB [(None)]> use MyDB;
MariaDB [mydb]> INSERT into TB1 (name) VALUES (' Andy ');
MariaDB [mydb]> INSERT into TB1 (name) VALUES (' Allen ');
MariaDB [mydb]> SELECT * from TB1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Tom |
| 3 | Jerry |
| 4 | Andy |
| 6 | Allen |
+----+-------+

NODE5 MARIADB1
MariaDB [mydb]> SELECT * from TB1;
+----+-------+
| ID | name |
+----+-------+
| 1 | Tom |
| 3 | Jerry |
| 4 | Andy |
| 6 | Allen |
+----+-------+

From the above test, you can determine that two nodes can synchronize MySQL data with each other.

############# MySQL double master copy end ##############

This article is from the "8317626" blog, please be sure to keep this source http://8327626.blog.51cto.com/8317626/1711199

MYSQL/MAIRADB Dual Master replication