晓夏

新手菜鸟Linux学习之路

不怕从零开始,只怕从未启程

Mysql-主从复制配置详解

浏览量:796

 一、关于Mysql主从复制的一些必备知识

    1、首先在配置Mysql主从复制之前,需要开启Mysql中的binlog日志功能.

    2、binlog里的日志其实就是sql语句+时间+一个标识符

    3、主从复制,相当于把主的bilog日志中的sql语句放到从服务器上再执行一遍而已

    4、如果需要重新配置mysql主从的话,别忘了删除从服务器上的Relaylog(这个文件会记录你的binlog上次记录到了第几行,以及你使用到了第几个binlog)

 

二、Mysql主从复制技术所解决的问题

MySQL复制技术有以下一些特点:
(1)    数据分布 (Data distribution )
(2)    负载平衡(load balancing)
(3)    备份(Backups) 
(4)    高可用性和容错行 High availability and failover

 

三、Mysql主从复制工作原理

整体上来说,复制有3个步骤:   
(1)    master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
(2)    slave将master的binary log events拷贝到它的中继日志(relay-log);
(3)    slave重做中继日志中的事件,将改变反映它自己的数据。

Mysql主从复制过程图:

Mysql.gif

      该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。

       下一步就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

       SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

        此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

 

四、Mysql主从复制配置

 

环境:

操作系统:CentOS 6.3

Mysql版本:5.6(源码包安装)

IP地址:192.168.1.197 192.168.1.199

 

 创建Mysql复制账号

# mysql -u root -p    #进入mysql数据库
mysql> grant replication slave,reload,super on *.* to backup@'%' identified by '123456';
#赋权给backup
mysql> flush privileges;

 


 

配置Master并拷贝数据

# vim /etc/my.cnf   #配置Master包括打开二进制,指定唯一的server ID
[mysqld]
server-id=1        #主服务器ID值
log-bin=mysql-bin    #开启bin-log

:wq!    #保存退出
# service mysqld restart    #重启mysql服务
# mysql -u root -p    #进入mysql数据库
mysql> flush tables with read lock;        #先把master表锁了,不让其再次操作
Query OK, 0 rows affected (0.19 sec)
这一步很重要,关停Master服务器,将Master中的数据拷贝到B服务器中,使得Master和slave中的数据同步,并且确保在全部设置操作结束前,禁止在Master和slave服务器中进行写操作,使得两数据库中的数据一定要相同!
这里不要Ctrl—C退出mysql服务器
另开一个ssh将数据进行打包,打包到/home/sql/下,因为就一个库,所以只备份这个库,如果有其他库请自行备份,备份完成后把数据导入Slave的那台数据库中,这里不再演示如何导入。
mysqldump -h127.0.0.1 -p3306 -uroot -p ywcf > /home/sql/ywcf.sql
mysql> show master status;    #查看binlog日志
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     106  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> unlock tables;            #把表的锁解开
Query OK, 0 rows affected (0.28 sec)

 

配置Slave

Slave上的配置与master类似,配置如下:
[mysqld]
log_bin           = mysql-bin    #开启binlog,slave上是可以不开的,但是有可能我们要做一主多从,所以保险起见还是开开的好.
server_id         = 2    #这个id是唯一的,可以使用ip最后一位
relay_log=mysql-relay-bin    #配置中继日志
log_slave_updates = 1    #slave将复制时间写进自己的二进制日志中
read_only         = 1    #只读
binlog-do-db=ywcf    #是表示只备份mysql中的ywcf库。
binlog_ignore_db=mysql    #表示忽略备份mysql中的mysql库。
注意:不加binlog-do-db和binlog_ignore_db,那就表示备份全部数据库。
注意: 这里也可以使用replicate-ignore-db = mysql代替。因为网上说使用binlog_ignore_db,会导致些问题。

:wq!    #保存退出

# service mysqld restart    #修改完配置文件必须重启mysql(对应下面的报错1)

mysql> change master to master_host='192.168.1.199',        #主的mysql的ip地址
    -> master_user='backup',                             #在主的mysql上面创建的用来主从复制的用户
    -> master_password='123456',        #backup用户的密码
    -> master_log_file='mysql-bin.000001',    #使用的哪个binlog,就是主的mysql的那个show master status; 命令中的File
    -> master_log_pos=106;    #从那个position开始,就是主的那个show master status; 命令里面的position

 

报错:

mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

这里我在启动slave的时候报错了.

排查过程:
1、
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |        #之前明明设置的是2,这里未生效
+---------------+-------+
1 row in set (0.00 sec)

2、
mysql> show  slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.199       #masterip
                  Master_User: backup              #用户backup
                  Master_Port: 3306                #端口3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001    #binlog是000001
          Read_Master_Log_Pos: 106                #与position一致
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No            #没变成YES    因为slave没起来
            Slave_SQL_Running: No            #没变成YES    因为slave没起来

            

3、
# mysql -u backup -h 192.168.1.199 -p 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.199' (113)

解决方法:
1、一般出现server_id未写入成功这个情况,有两种,第一种是没有重启slave的mysql服务器,网上说不用重启slave那是错误的,已验证.

2、出现IO和SQL进程为NO的情况,请查看日志看哪里的错误,SQL进程一般是server_id未写入.这个可以通过修改my.cnf后重启mysql服务解决.IO问题,通常是网络问题或者sql用户权限或者防火墙问题.

3、通过查看mysql错误日志,我们了解到错误原因是连接不上mysql主服务器.尝试连接显示失败.最终发现是我的防火墙设置问题.


重启Mysql的Slave服务

mysql> stop slave;    #关闭slave进程
mysql> reset slave;    #在修改my.cnf配置文件后必须要执行此命令,因为中途修改过my.cnf配置文件,重启mysql之后,binlog文件名和relay-log文件名已经改变,所以不能再从之前的relay-log复制了.
mysql> start slave;    #重新开启slave进程
mysql> show slave status \G;    #重新查看主从复制是否成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


测试Mysql主从是否成功

主mysql服务器:
mysql> select * from ywcf.user_balance where id = 'guoxin';
+--------+---------+---------+--------------+----------+---------------------+
| id     | user_id | balance | frozen_money | hf_money | time                |
+--------+---------+---------+--------------+----------+---------------------+
| guoxin | guoxin  |       0 |            0 |        0 | 2016-03-28 13:55:55 |
+--------+---------+---------+--------------+----------+---------------------+

Slave上的mysql服务器:
mysql> select * from ywcf.user_balance where id = 'guoxin';
+--------+---------+---------+--------------+----------+---------------------+
| id     | user_id | balance | frozen_money | hf_money | time                |
+--------+---------+---------+--------------+----------+---------------------+
| guoxin | guoxin  |       0 |            0 |        0 | 2016-03-28 13:55:55 |
+--------+---------+---------+--------------+----------+---------------------+

修改主Mysql服务器sql:
mysql> update ywcf.user_balance set balance = '10000' where id = 'guoxin';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0


此时我们再去Slave上查看:
mysql> select * from user_balance where id = 'guoxin';
+--------+---------+---------+--------------+----------+---------------------+
| id     | user_id | balance | frozen_money | hf_money | time                |
+--------+---------+---------+--------------+----------+---------------------+
| guoxin | guoxin  |   10000 |            0 |        0 | 2016-03-28 13:55:55 |
+--------+---------+---------+--------------+----------+---------------------+

到此MYSQL主从复制搭建完成!


提示:如果想要重新配置主从复制,必须删除Master和Slave中的relay-log.info日志,否则再重新配置后就悲催了!会报错!

分享:

支付宝

微信