# 搭建 MySQL/MariaDB 的主从复制
# 主从复制原理
主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。
# 共同配置
# install MySQL/MariaDB
yum install -y mysql-server mysql mysql-devel
or
# /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是否设置成一样了。