mysql双机主从及互为主从同步配置

发表于 2016-04-20 14:57:15 最后修改于 2016-04-20 14:57:15

# 搭建 MySQL/MariaDB 的主从复制

# 主从复制原理

主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

# 共同配置

# install MySQL/MariaDB

yum install -y mysql-server mysql mysql-devel

# /etc/yum.repos.d/mariadb.repo
# MariaDB 10.1 CentOS repository list - created 2016-03-07 07:47 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

sudo yum install MariaDB-server

note: 如果CentOS 7

    yum install mariadb-server mariadb
    systemctl start mariadb
    systemctl enable mariadb

# static ip

DEVICE=eth0
BOOTPROT=static
IPADDR=192.168.2.100
GATEWAY=192.168.1.1
NETMASK=255.255.255.0
ONBOOT=yes

修改root用户口令，删除空口令

mysqladmin -uroot password 'passwd'
删除默认数据库和数据库用户

drop database test;

# hosts

/etc/hosts

192.168.2.100   Master
192.168.2.101    Slave

# startup configure

service mysqld start
chkconfig mysqld on

# 防火墙设置

CentOS 6:

iptables -A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
service iptables save

CentOS 7:

firewall-cmd --permanent --add-port=3306/tcp
# Reload firewall rules using command:
firewall-cmd --reload

# Master 配置

cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# as master
server-id=1    #主机id，整数
#开启二进制日志，并且名称为 /var/lib/mysql/mysql-bin.***
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=blog  #记录日志的数据库：需要的备份数据，多个写多行
# binlog-do-db=
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema

# as slave
replicate-do-db =blog #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # slave将复制事件写进自己的二进制日志

# 自增字段奇数递增，防止冲突（1, 3, 5, ...,）
auto-increment-increment = 2  # 每次递增的步长
auto-increment-offset = 1  # 初始值


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# Authorization on Master

create user 'backup' identified by  'Syncing';

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'Slave' IDENTIFIED BY 'Syncing';

Notice: show grants for 'backup'@'%'; 这里给了所有数据库的所有权限，请根据实际情况来更改。

# Slave

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# as slave
server-id=2
log-bin=mysql-bin
replicate-do-db =blog #只复制某个库，多个写多行
replicate-ignore-db=mysql #不复制某个库
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log=mysqld-relay-bin  # 开启日志中继
log-slave-updates  # 示slave将复制事件写进自己的二进制日志

# as master
#开启二进制日志，并且名称为 /var/lib/mysql/mysql-bin.***
log-bin=mysql-bin
read-only=0  #主机读写权限，读写都可以
binlog-do-db=blog   #记录日志的数据库：需要的备份数据，多个写多行
# binlog-do-db=hello2
binlog-ignore-db=mysql #不记录日志的数据库：不需要备份的数据库，多个写多行
binlog-ignore-db=test
binlog-ignore-db=information_schema


# 自增字段偶数递增，防止冲突（2, 4, 6, ...,）
auto-increment-increment = 2  # 每次递增的步长
auto-increment-offset = 2  # 初始值

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 配置从服务器

# 设置只读模式及获取Master信息

flush tables with read lock;
show master status\\G;

show master status\\G;
*************************** 1. row ***************************
            File: mysql-bin.000006
        Position: 5665
    Binlog_Do_DB: blog
Binlog_Ignore_DB: mysql,test,information_schema
1 row in set (0.00 sec)



unlock tables;

Notice: the Position vlaue is master_log_pos. the File vlaue is master_log_file

# Slave配置主服务器

change master to 
master_host='Master',
master_user='backup',
master_password='Syncing',
master_log_file='mysql-bin.000006',
master_log_pos=5665;

其中：

MASTER_HOST= //主服务器的IP地址
MASTER_USER=//同步数据库的用户
MASTER_PASSWORD=//同步数据库的密码
MASTER_CONNECT_RETRY=如果从服务器发现主服务器断掉，重新连接的时间差(秒)
MASTER_LOG_FILE=主服务器二进制日志的文件名(前面要求记住的 File 参数)
MASTER_LOG_POS=//日志文件的开始位置(前面要求记住的 Position 参数)

然后：

start slave;

show slave status\\G;

Slave_IO及Slave_SQL进程必须正常运行，即两个YES状态

# Test

在 Master 上执行：

create database example; 
create table example.dummy (`id` varchar(10)); 
show tables in example;

然后在 Slave 查看，这时候也有一样的表。

+-------------------+
| Tables_in_example |
+-------------------+
| dummy             |
+-------------------+
1 row in set (0.00 sec)

# 互为主从模式

其实这个很简单，就是主机既作为master，也作为slave.然后配置就ok了。

上面配置我已经加了，只需要重复上面的步骤就行了。

理论上这种模式适合做双机热备的，但数据太重要，生产环境最终没应用

# 遇到的错误

# server_id 错误

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). 

The slave I/O thread stops because master and slave have equal MySQL server ids;

通过下面命令查看

show variables like 'server_id';

检查/etc/my.cnf的id是否设置成一样了。

# 参考链接

版权属于： Linsir's Blog

原文地址： https://linsir.org/post/how_to_setup_mysql_master-slave_replication

转载时必须以链接形式注明原始出处及本声明。

--EOF--

>看不到评论？GFW!!!

搭建 MySQL/MariaDB 的主从复制
互为主从模式
- 遇到的错误
  - server_id 错误
- 参考链接