最近公司服务核心数据由mongodb转到mysql中来,顺理成章的就要解决热备以及灾难恢复等问题,而且 上来直接用mysql5.7所以也遇到了不少的版本问题,这篇我们我们就简单介绍一下 安装和互为主从配置的问题。

安装

公司服务器采用Ubuntu Server系统,而mysql5.7到现在也并没有更新到sources里面去,所以我们只有通过官网文档来寻找解决方法

下载MySQL APT Repository文件

1
wget http://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb

这个地址可在 官网找到

将MySQL APT Repository添加到系统sources中

1
sudo dpkg -i /PATH/version-specific-package-name.deb

安装软件的基本操作

1
sudo apt-get update
suo apt-get install mysql-server

这样mysql 5.7就顺利安装成功了

配置文件

互为主从 配置才是核心,一开始网上找材料全是 编辑my.cnf什么的,结果我一看就懵逼了,安装的mysql 配置文件my.cnf跟之前出入太大了

1
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

# The MySQL Community Server configuration file.
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

这个配置跟之前版本的mysql my.cnf出入明显太大了,后来修改了半天也没修改出个好歹,最终还是在StackOverFlow上找到了答案

1
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

这两句表示多余的配置放在 /etc/mysql/conf.d/ /etc/mysql/mysql.conf.d/这两个文件夹里面
所以mysql真正的配置文件是

1
/etc/mysql/mysql.conf.d/mysqld.cnf

配置互为主从

互为主从配置其实也只是 先主A从B 后主B从A这样配置两遍而已
这里我们有两台机器

1
A 172.16.63.129
B 172.16.63.140

添加同步账户

给两个机器的Mysql都添加数据同步账号,这一步也是必须的

1
#A数据库
mysql> grant replication slave on *.* to 'slave'@'172.16.63.140' identified by 'password';
#B数据库
mysql> grant replication slave on *.* to 'slave'@'172.16.63.129' identified by 'password';

编辑配置文件

1
[root~]# vim /etc/mysql/mysql.conf.d/mysqld.cnf
log-bin=mysql-bin                 #开启二进制日志
server-id=1
relay-log=mysql-relay-bin         #开启中继日志
log_slave_updates = on            #从服务器将时间记录到二进制日志中
auto_increment_increment=2        #自动增长的步长
auto_increment_offset=1           #自动增长的起始数值
#A服务器的配置

[root~]# vim /etc/my.cnf
server-id=2
#log-bin=mysql-bin
#log_slave_updates = on
auto_increment_increment=2
auto_increment_offset=2
#B服务器的配置,先关闭二进制日志

配置完成之后 重启数据库服务

在两数据库上创建同样的数据库及表结构

让B服务器成为slave

1
mysql> change master to master_host='172.16.63.129',master_user='slave',master_password='password',master_port=3306,MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1068;

其中MASTER_LOG_FILE 和 MASTER_LOG_POS 可以在A服务器上 通过

1
show master status;

来获取

开启B服务器二进制日志并重启

1
[root~]#  vim /etc/mysql/mysql.conf.d/mysqld.cnf
#将刚刚注释掉的参数生效
log-bin=mysql-bin
log_slave_updates = on

让A服务器成为slave

1
mysql> change master to master_host='172.16.63.140',master_user='slave',master_password='password',master_port=3306,MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1068;
1
start slave;

查看AB服务器状态

A服务器

1
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.63.140
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

B服务器

1
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.63.129
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2783
               Relay_Log_File: ubuntu-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

只要最后显示的Slave_SQL_RunningSlave_IO_Running都是YES则说明配置成功
这时候你就可以进行的操作,来验证了

参考

http://svenman.blog.51cto.com/6867097/1363110
http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/