记一次mysql双主配置

2016年11月04日 分类:Linux学习笔记mysql

在linode有一台vps,之前的博客在这台上,后来迁回国内,现在就用来翻墙。本来打算再开几台机器组建celery集群,用来计算,但是linode东京机房长期缺货,找了下发现conoha的配置很不错,2核1G,价格和linode差不多,所以celery集群放到了conoha。在跑大任务时celery集群非常耗资源,影响到了实时任务,所以打算把实时任务迁到linode机器,conoha跑大任务,两边各有一个mysql,使用双主模式,实现数据交换。

1. 准备

1.1 卸载mariadb

为了防止兼容问题,准备都用mysql 5.7,需要卸载linode上的mariadb。

#备份数据
mysqldump 数据库名 -uroot -p > xxx.sql
#停止mariadb
service mysql stop
#卸载
yum remove mariadb mariadb-server
#删除数据
rm -rf /var/lib/mysql
#删除配置文件 也可以留一份备用
rm /etc/my.cnf

1.2 安装mysql 5.7

具体安装教程可以在官方文档里找到,地址https://dev.mysql.com/doc/mysql-repo-excerpt/5.7/en/linux-installation-yum-repo.html

下载合适的yum仓库,http://dev.mysql.com/downloads/repo/yum/

yum localinstallmysql57-community-release-el6-9.noarch.rpm

选择5.7版本

yum repolist all | grep mysql
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community

安装mysql 5.7

yum install mysql-community-server

启动mysql

service mysqld start

修改root密码

grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxx';

1.3 环境

位置 ip mysql 数据 server-id
linode 106.185.xx.xx 5.7.16 1
conoha 150.95.xx.xx 5.7.16 2

注意,xx是我隐藏的数字。

2. mysql配置

2.1 my.cnf

mysql两边的配置应该尽可能保持一致,下面这些参数应该一样,server-id是同步的来源标记,同一个mysql集群必须唯一

server-id=1

log-bin=binlog
binlog_format=ROW
expire_logs_days=7
log-slave-updates
sync_binlog = 1
binlog-do-db=task
binlog-ignore-db=mysql,test,information_schema,performance_schema,sys
replicate-do-db=task
replicate-ignore-db=mysql,test,information_schema,performance_schema,sys
default_storage_engine = INNODB

下面这些可以根据服务器配置修改,仅供参考。

innodb_file_per_table = 1 #为每个表建立文件
innodb_adaptive_flushing = 1 # 自动刷脏页
innodb_max_dirty_pages_pct = 75 # 刷新比例
innodb_io_capacity = 200 # io能力

innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 2

transaction-isolation = READ-COMMITTED

2.2 建立同步账号

mysql主从需要REPLICATION,建立专门的repl账号,限制ip。

#linode上建立repl的账号,开放REPLICATION权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'150.95.xx.xx' IDENTIFIED BY 'xxxxxxxxxxx';
#同样在conoha上也建立repl账号
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'106.185.xx.xx' IDENTIFIED BY 'xxxxxxxxxxx';

3. 防火墙开放端口

因为mysql同步是通过外网,需要为对方开放3306端口,修改/etc/sysconfig/iptables,只对特定ip开放端口。

#这是linode机房的,为conoha机房开放3306
-A INPUT -s 150.95.xx.xx -p tcp -m tcp --dport 3306 -j ACCEPT
#相对应的conoha的就是这样
-A INPUT -s 106.185.xx.xx -p tcp -m tcp --dport 3306 -j ACCEPT
#还需要对内网开放所有端口,用于celery集群访问
-A INPUT -s 10.11.8.0/21 -j ACCEPT

重启防火墙

service iptables restart

4. 导数据

因为我的业务对实时性要求不高,而且数据写入可以控制,所以用简单的mysqldump来备份数据,如果数据写入很频繁的话可以用Xtrabackup做实时热备。

4.1 停止mysql写入

如果觉得不够保险,可以直接锁表。

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

4.2 记录master binlog信息

show master status;

记录File和Position信息。

4.3 mysqldump导出

mysqldump task -uroot -p > task.sql

task是库名,我只用到这个库。

现在可以开启mysql的写入了

4.4 导入数据

把上面导出的task.sql scp到linode机器上,导入mysql,task库需要提前建好。

#在终端里
mysql -u root -p task < /path/to/task.sql
#或者在mysql客户端里
use task
source /path/to/task.sql

5. 开启同步

这是最激动人心的时刻。

现在linode已经有一部分conoha的数据,数据导出后,conoha就开始写入了,linode的binlog落后

所有先让linode挂到conoha下,同步完所有数据。

5.1 linode同步conoha

CHANGE MASTER TO MASTER_HOST='150.95.xx.xx', MASTER_USER='repl', MASTER_PASSWORD='xxxxxxx', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=154;
start slave;

MASTER_LOG_FILE和MASTER_LOG_POS就是4.2 拿到的binlog信息。

show slave status\G

观察Slave_IO_Running和Slave_SQL_Running已经Yes。

这样conoha的mysql就开始往linode同步了,Seconds_Behind_Master就是两者的延时,注意两边的系统时钟要一致,不然会出现假延时。

试着在conoha的mysql里加测试数据,在linode能查到,done。

5.2 conoha同步linode

现在linode里的mysql是没有数据写入的,也就是没有binlog给conoha,所以可以随便选一个File和Position。

在linode的mysql里执行

show master status;

记录File和Position信息。

切换到conoha的mysql,和5.1的一样,再来一次。

CHANGE MASTER TO MASTER_HOST='106.185.xx.xx', MASTER_USER='repl', MASTER_PASSWORD='xxxxxxx', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1254;
start slave;
show slave status\G

观察Slave_IO_Running和Slave_SQL_Running已经Yes。

加测试数据,conoha能查到,done。

6. 总结

现在任务的配置和实时任务都在稳定的linode上跑,通过mysql同步到conoha,启动celery大任务,尽量发挥conoha双核的威力,防止两边互相影响。

作者:wuyuan 本文来自Wuyuan's Blog 转载请注明,谢谢! 文章地址: http://wuyuans.com/2016/11/mysql_replication_double_master