mysql/mairadb双主复制
node5: 172.16.92.5/16 mariadb主服务器1
node6: 172.16.92.6/16 mariadb主服务器2
以上节点均为CentOS 7.1
配置环境
1. 配置好光盘yum源
2. 关闭selinux和iptables
============ 一. 安装mariadb-server并配置好文件 ===========
node5: mariadb主服务器
[root@node5 ~]# yum -y install mariadb-server
[root@node5 ~]# 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 are ignored when systemd is 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 指自增字段的起始值。
auto_increment_offset=1
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
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
###############################
###### 以下的内容可选 ########
[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主服务器2
[root@node6 ~]# yum -y install mariadb-server
[root@node6 ~]# 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 are ignored when systemd is 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
server-id = 6
relay-log = relay-bin
#第一个变量名 auto_increment_offset 指自增字段的起始值。
auto_increment_offset=2
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
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
###################################
######### 以下内容可选 ############
[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 ###############
============ 二. 开启mariadb服务, 添加授权用户 ===========
node5 : 172.16.92.5 主服务器1
[root@node5 ~]# systemctl start mariadb
[root@node5 ~]# 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:
##### 记下mysql-bin.000003 和 506 , 设置主服务器2中继日志时有用 ####
node6 : 172.16.92.6 主服务器2
[root@node6 ~]# systemctl start mariadb
[root@node6 ~]# 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:
##### 记下mysql-bin.000003 和 506 , 设置主服务器1中继日志时有用 #####
============ 三. 相互设置对方为主节点, 并开启同步线程 ===========
node5 : 172.16.92.5 主服务器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 主服务器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;
============ 四. 查看主主复制的两个节点同步进程状态 ===========
node5 : 172.16.92.5 主服务器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
......其余信息省略........
MariaDB [(none)]> show processlist\G
*************************** 3. row ***************************
State: Slave has read all relay log; waiting for the slave I/O thread to update it
......其余信息省略........
*************************** 4. row ***************************
State: Master has sent all binlog to slave; waiting for binlog to be updated
......其余信息省略........
#说明: 双主节点已相互发送中继日志
node6 : 172.16.92.6 主服务器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
......其余信息省略........
MariaDB [(none)]> show processlist\G
*************************** 2. row ***************************
State: Master has sent all binlog to slave; waiting for binlog to be updated
......其余信息省略........
*************************** 4. row ***************************
State: Slave has read all relay log; waiting for the slave I/O thread to update it
......其余信息省略........
最后测试mysql双主模型的两个节点能否相互同步数据
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 |
+----+-------+
从上面的测试可确定两个节点能相互同步mysql的数据
############# mysql双主复制结束 ##############
node5: 172.16.92.5/16 mariadb主服务器1
node6: 172.16.92.6/16 mariadb主服务器2
以上节点均为CentOS 7.1
配置环境
1. 配置好光盘yum源
2. 关闭selinux和iptables
============ 一. 安装mariadb-server并配置好文件 ===========
node5: mariadb主服务器
[root@node5 ~]# yum -y install mariadb-server
[root@node5 ~]# 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 are ignored when systemd is 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 指自增字段的起始值。
auto_increment_offset=1
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
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
###############################
###### 以下的内容可选 ########
[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主服务器2
[root@node6 ~]# yum -y install mariadb-server
[root@node6 ~]# 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 are ignored when systemd is 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
server-id = 6
relay-log = relay-bin
#第一个变量名 auto_increment_offset 指自增字段的起始值。
auto_increment_offset=2
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
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
###################################
######### 以下内容可选 ############
[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 ###############
============ 二. 开启mariadb服务, 添加授权用户 ===========
node5 : 172.16.92.5 主服务器1
[root@node5 ~]# systemctl start mariadb
[root@node5 ~]# 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:
##### 记下mysql-bin.000003 和 506 , 设置主服务器2中继日志时有用 ####
node6 : 172.16.92.6 主服务器2
[root@node6 ~]# systemctl start mariadb
[root@node6 ~]# 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:
##### 记下mysql-bin.000003 和 506 , 设置主服务器1中继日志时有用 #####
============ 三. 相互设置对方为主节点, 并开启同步线程 ===========
node5 : 172.16.92.5 主服务器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 主服务器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;
============ 四. 查看主主复制的两个节点同步进程状态 ===========
node5 : 172.16.92.5 主服务器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
......其余信息省略........
MariaDB [(none)]> show processlist\G
*************************** 3. row ***************************
State: Slave has read all relay log; waiting for the slave I/O thread to update it
......其余信息省略........
*************************** 4. row ***************************
State: Master has sent all binlog to slave; waiting for binlog to be updated
......其余信息省略........
#说明: 双主节点已相互发送中继日志
node6 : 172.16.92.6 主服务器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
......其余信息省略........
MariaDB [(none)]> show processlist\G
*************************** 2. row ***************************
State: Master has sent all binlog to slave; waiting for binlog to be updated
......其余信息省略........
*************************** 4. row ***************************
State: Slave has read all relay log; waiting for the slave I/O thread to update it
......其余信息省略........
最后测试mysql双主模型的两个节点能否相互同步数据
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 |
+----+-------+
从上面的测试可确定两个节点能相互同步mysql的数据
############# mysql双主复制结束 ##############
本文转自 zhuhc1988 51CTO博客，原文链接：http://blog.51cto.com/changeflyhigh/1711199，如需转载请自行联系原作者
