mysql主从

原创

自己定义 2016-04-19 22:24:59 博主文章分类：数据库 ©著作权

文章标签 mysql 其他 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者自己定义的原创作品，谢绝转载，否则将追究法律责任

1》mysql主从的工作原理：

主服务器将更新写入二进制日志文件（bin_log），并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。

每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

从服务器设置为复制主服务器的数据后，它连接主服务器并等待更新过程。如果主服务器失败，或者从服务器失去与主服务器之间的连接，从服务器保持定期尝试连接，直到它能够继续帧听更新。由--master-connect-retry选项控制重试间隔。默认为60秒。

每个从服务器跟踪复制时间。主服务器不知道有多少个从服务器或在某一时刻有哪些被更新了。

mysql主从的工作原理其实就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。因此在主服务器上必须开启Binary Log功能。

2》mysql主从的工作过程

2.1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容；

2.2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置；

2.3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”

2.4. Slave 的 SQL 线程检测到 Relay Log (中继日志)中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

3》mysql支持复制的类型

（１）：基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
　　（２）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
　　（３）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

4》主从服务器的设置。

主服务器，主机名master，ip地址为192.168.1.113

从服务器，主机名slave，ip地址为192.168.1.114

首先在主服务器上安装mysql，安装过程见：

首先在主服务上操作

打开mysql的配置文件，作如下修改

server-id =1  /设置为1，主要不要和从服务器重复
log-bin=mysql.bin   /启用二进制日志文件，名字可以自己定义
binlog-do-db=db1,db2   /白名单，即需要同步的数据库
binlog-ignore-db=db1,db2/黑名单，指定忽略不同步的数据库

修改完配置文件后，注意要重新启动mysql服务。

然后设置授权给slave一个用来同步数据的用户repl

 >grant replication slave on *.* to 'repl'@'192.168.1.114' identified by '123123';
 >flush privileges;
 >flush tables with read lock;
 >show master status;
 +------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 |      335 | db1,db2      | db1,db2          |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

然后设置从，在从服务器上操作

修改主配置文件作如下修改：

server-id =2 /注意不要和主服务器一样
replication-do-db=db1,db2   
replication-ignore-db=db1.db2 /这两家意思和主服务器加上的那两句一个意思，如果在那里设置了，此处就不用设置。

然后登录从服务器，执行如下指令

mysql> slave stop; 《1》
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.1.113',master_port=3306,master_user='repl',master_password='123123',master_log_file='mysql_bin.000025',master_log_pos=335;   《2》
Query OK, 0 rows affected (0.16 sec)
mysql> slave start;    《3》
Query OK, 0 rows affected (0.01 sec)

mysql>

然后在从上查看主从是否搭建成功

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.113
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000027
          Read_Master_Log_Pos: 106
               Relay_Log_File: shiyan-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000027
             Slave_IO_Running: Yes           /有两个yes
            Slave_SQL_Running: Yes     
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           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: 106
              Relay_Log_Space: 407
              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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

这一步可能出现如下问题：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.113
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000025
          Read_Master_Log_Pos: 335
               Relay_Log_File: shiyan-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql_bin.000025
             Slave_IO_Running: No
            Slave_SQL_Running: Yes

可采取如下方法解决：

在master那边，执行：

flush logs;
show master status;

记下File, Position。

在slave端，执行：

slave stop;

CHANGE MASTER TO MASTER_LOG_FILE='《上面出现的》',MASTER_LOG_POS=《上面出现的》;
slave start;
show slave status \G

一切正常。

5》mysql主从的优点

在slave服务器上执行查询操作，降低master服务器的访问压力

当master服务器上出现了问题可以切换到slave服务器上，不会造成访问中断等问题

在slave服务器上进行备份，以避免备份期间影响master服务器的服务使用及日常访问