MySQL主备配置方法

Database

MySQL

参考书目

《MySQL技术内幕（第5版）》530页设置复制服务器
《高性能MySQL》
官方文档：https://dev.mysql.com/doc/refman/5.6/en/replication-howto-existingdata.html

环境

阿里云服务器
centos 7
Mysql 5.6

一主库一备库

准备

两台阿里云服务器，均安装Mysql，并保证版本一致，安装此处不赘述
主服务器配置

给每个服务器分配一个ID，ID为1~2**32-1的整数，我们可以设置为服务器的ip地址

主服务器上需要启动二进制日志功能，因为更新通信是基于主服务器的二进制日志的，其中记录了需要发送到从服务器的各种更新。

配置事例如下：

master-server
```
[mysqld]
server-id = 174398997
log-bin = mysql-bin
```

从服务器配置

[mysqld]
log-bin = mysql-bin
server-id = 328631
# relay-log = /var/lib/mysql/mysql-relay-bin
# log-slave-updates = 1
skip-slave-start
# 备库只读
read-only = 1

主服务器配置账号，让从服务器能连接主服务器，并请求更新信息

# 创建用户, host为从服务器的ip，如果同一个安全组则写内网，不是则写外网ip
mysql> CREATE USER 'repl'@'10.44.36.24' IDENTIFIED BY 'slavepass';
# 配置权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.44.36.24';

获取主服务器的日志坐标，保持主从服务器的数据一致

大多数情况主服务器是有数据的，我们需要把主服务器的数据复制到从服务器，我们采用mysqldump方法

首先要将主服务器的mysql停止更新，知道slave启动，采用如下命令：
```
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
```
记录日志文件和当前行

如何复制数据：

在master上：
```
shell> mysqldump --all-databases --master-data > fulldb.dump
```
将fulldb.dump文件scp到slave服务器，执行：

配置--skip-slave-start 到/etc/my.cnf，重新启动服务，load数据
```
shell> service mysqld restart
shell> mysql < fulldb.dump
```

连接主服务器

最后的两个参数即上一步记录的数据

CHANGE MASTER TO MASTER_HOST='123.56.142.59',MASTER_USER='repl', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=544;

启动备库线程
```
mysql> START SLAVE;
```
解锁主库
```
mysql> UNLOCK TABLES;
```
测试

可以用mysql> show slave status来看有没有报错信息

然后再主库增加或删除，再看备库有没有更新

主动-被动模式下的主-主复制

慎用！！！！！

首先按照上面的步骤实现主备
重复上面的步骤，主备角色调换

在第七步启动备库线程时，可能会出现错误Slave failed to initialize relay log info structure from the repository，我们执行reset slave;来重置slave即可

这里涉及到主备切换，以实现高可用，以后再研究

读写分离

参考资料：http://www.jianshu.com/p/000dfd9bc3cf

现在一个主库，一个备库，主库负责写，备库负责读。一般是这么设计，因为只有一个备库，所以不用像多个备库一样，还要中间件来做负载均衡，把读操作分发到多个备库上，可以直接连备库。所以我觉得在程序代码上来做就行了，具体想法是：配置两个db_session, 一个write_db_session连主库，一个read_db_session连备库，在model里，如果是query操作，就用read_db_session,如果是add,update,delete，就用write_db_session。同时为了避免主备同步延迟，把一些客户需要即使生效的修改查询，用write_db_session去读主库

Flask应用里sqlalchemy读取不到更新

代码如下：

engine = create_engine(SQLALCHEMY_DATABASE_URI, convert_unicode=True)
slave_engine = create_engine(SLAVE_SQLALCHEMY_DATABASE_URI,
                             convert_unicode=True)

db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False,
                                         bind=engine))
slave_db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False,
                                               bind=slave_engine))

问题描述：

用db_session增加或删除一条数据之后，主备数据库都已同步更新，但是slave_db_session读取数据库还是更新前的数据，我们初步怀疑是事物执行之后没有commit，看到slave_db_session的配置autocommit=False，我们修改成True，测试成功