CentOS6.5下实现Mysql5.5主从实时同步备份_shell学习笔记

发表评论
273

A+

所属分类：shell学习笔记

本文信息本文由方法SEO顾问发表于2017-02-1418:13:10，共 3335 字，转载请注明：CentOS6.5下实现Mysql5.5主从实时同步备份_【方法SEO顾问】

最近老遇到服务器被攻击情况，导致来不及备份网站数据及数据库数据，网站静态文件备份倒是简单，关键是数据库，更新频率高，如果来不及备份就被IDC关闭主机了，造成的损失恐怕是一天一个亿了：）

于是就想折腾一下Mysql数据库的自动实时备份，找到了一些参考资料，对照着弄，总算是成功了。过程曲折，记录一下，下次就不会这么折腾了。

于是就有了这篇《CentOS6.5下实现Mysql5.5主从实时同步备份》

前提

1、Mysql版本一致；

2、主、从服务器都存在相同的数据库，且初始状态一样；

系统环境

主服务器：CentOS6.5 64bit

从服务器：CentOS6.5 64bit

WEB环境

主服务器：oneinstack，Mysql版本：5.5.54

从服务器：lnmp，Mysql版本：5.5.54

第一步：保持版本一致

为保证同步成功，最好是主、从服务器的Mysql版本保持一样。如果不一致，升级一下低版本就行了。输入mysql -V查看版本。

第二步：开启远程访问

主服务器必须开启远程访问，否则从服务器无法连接主服务器来获取数据。oneinstack默认在iptables里关闭了Mysql的远程访问权限，所以需要打开。

iptables -I INPUT 4 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

service iptables save #保存iptables规则

第三步：主机新建同步账户

主服务器上新建一个远程访问用户，为了安全起见，不要用root账户来同步。

grant replication slave on *.* to 'slaveuser'@'%' identified by '123456' ;//新建用户名为slaveuser，密码为123456的同步专用用户，这是注释，不要复制执行

flush privileges;//刷新权限，这是注释，不要复制执行

第四步：修改主机my.cnf

Linux的my.cnf在/etc/my.cnf路径下

要保证以下项目必须得有，'//'后面是注释，实际使用时要去掉

[mysqld]

log-bin=mysql-bin

binlog_format=mixed//binlog保留天数，过期自动删除

server-id = 15 //主机的server-id，一般用IP地址最后一段

binlog-do-db = SEOfangfa.com // 需要同步的数据库

binlog-ignore-db = mysql // 不需要同步的数据库

第五步：修改从机my.cnf

server-id = 175 //主机的server-id，一般用IP地址最后一段
replicate-do-db = seofangfa.com// 需要同步的数据库
replicate-ignore-db = mysql// 不需要同步的数据库

第六步：查看主机情况

show master status;

MySQL [mysql]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |    97860 | seofangfa.com  | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

第七步：开始从服务器同步

mysql> change master to master_host='主服务器IP',master_user='slaveuser',master_password='123456', master_log_file='mysql-bin.000006',master_log_pos=97860;

#开始同步线程

mysql> start slave;

#查看开启情况

mysql> show slave status\G;

如果出现：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

表示主从同步进行中

如：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 8.8.8.8
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 97860
               Relay_Log_File: iZ25r8c8rg1Z-relay-bin.000002
                Relay_Log_Pos: 98006
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: seofangfa.com
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 97860
              Relay_Log_Space: 98169
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 15
1 row in set (0.00 sec)

主数据库或者从数据库挂掉情况下的mysql主从同步

1、当主数据库挂掉的时候，其实默认情况下从数据库会每60秒去访问主数据库进行同步，直到住数据库开启

2、当从数据库挂掉，直接使用命令start slave开始同步功能即可

mysql主从同步和操作系统无关，和mysql版本也基本无关。

mysql主从同步的速度基本是秒同以内，但是当然还是存在时间的差异，所以从数据库基本只做查询用，在修改，实时性要求高，事物的操作单位内，不用从库，用主库的链接。

参考资料：

1、http://blog.csdn.net/why7282299/article/details/10577025

补充更新

2017.2.16

从库在同步过程中遇到了问题，原因是我在两边同时登录，导致到两边数据库都记录一条同样的登录信息，于是在同步时就发生了Last_SQL_Error: Error 'Duplicate entry '64' for key 'PRIMARY'' on query. 这样的主键冲突的问题，解决办法如下：

修改从库的mysql的配置文件，/etc/my.cnf，在[mysqld]下面添加一行

slave_skip_errors = 1062

保存、重启mysql服务，再次查看主从复制，问题解决。

参考文章：http://blog.csdn.net/lylyf99/article/details/8984350

前提