MySql主从复制

        前面说的分区、分表都是基于同一数据库本事的一些结构优化，而当访问量巨大的情况单一的数据库服务器是无法满足压力要求的，MySQL为我们提供了复制功能，使得多台MySQL服务器共享数据，从而实现负载均衡。
        首先我们来看一张图，了解一下主从复制的物理结构：

 

        从图上我们可以直观的看出MySQL主从复制的机制就是：
        1.主服务器（Master）执行SQL语句成功后将执行结果记录在"Binary log"（二进制文件）中。
        2.从服务器（Slave）的I/O进程负责连接主服务器，连接成功后主服务器从指定位置（起初为开始位置）读取"Binary log"文件并返回，返回的内容包括"Binary log"改动内容，日志文件名及读取到的位置。
        3.从服务器接受内容之后将内容添加到"Relay log"（中继日志）尾部。将二进制文件名称及读取到的位置记录在"Master info"文件中以便下次利用。
        4.从服务器的SQL进程在实时监控着"Relay log"文件，当有新增内容出现时，SQL进程将文件内容转换成可执行的SQL内容并更新到数据中。

 

        Mysql为了解决这个风险并提高复制的性能，将Slave端的复制改为两个进程来完成。提出这个改进方案的人是Yahoo!的一位工程师“Jeremy Zawodny”。这样既解决了性能问题，又缩短了异步的延时时间，同时也减少了可能存在的数据丢失量。当然，即使是换成了现在这样两个线程处理以后，同样也还是存在slave数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事务中，这些问题都是会存在的。如果要完全避免这些问题，就只能用mysql的cluster来解决了。不过mysql的cluster是内存数据库的解决方案，需要将所有数据都load到内存中，这样就对内存的要求就非常大了，对于一般的应用来说可实施性不是太大。

        主从复制的架构绝大多数是“一主多从”，因为我们默认的认为读取数据的次数是远远大于更新的，事实证明绝大多数情况是这样的。这样做的好处就是将压力很好的分散，甚至我们可以通过购买廉价的服务器来扩充从服务器数量。
        当然这种结构并不是没有问题，读写不同步，数据丢失等等，这些问题我们会在最后去讨论，下面开始我们的主从服务器配置工作（条件有限，以一主一从为例）。

 

        配置主服务器：

        1.安装MySQL,安装过程就不说了，可以查看前面的文章。假定安装成功之后主服务器ip为192.168.0.1；从服务器ip为192.168.0.2.

 

        2.登录主服务器MySQL，建立一个供Slave端使用的账号，提供复制和连接权限，输入命令：

grant replication slave on *.* to slave1@192.168.0.2 identified by '123456';

 

        slave1@192.168.0.2:为用户名和ip
        '123456':为slave1的密码

 

        3.配置my.cnf,我这里的my.cnf是放在/etc/mysql/下的,找到以下两行内容：

文件内容

log-bin=mysql-bin
server-id=1

 有注释的将注释去掉，并修改为以上值。

 

        4.查看主服务器binary log日志名和复制到的位置，输入命令：

 mysql>  show master status;  
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000070 |      241 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

        5.锁定master服务器所有表的写入，防止我们在操作过程中数据被修改，SQL状态下输入命令：

flush tables with read lock;

        6.备份数据库，利用mysqldump工具，在shell状态下输入以下命令：

mysqldump -h127.0.0.1 -p3306 -uroot -p “数据库名” > /home/back.sql

        mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" --lock-all-tables > 路径

        详细用法可以参照我的另一篇文章。

        7.备份完成后解锁数据库，SQL状态下输入：

unlock tables;

 

 

        配置从服务器：

        1.修改从服务器的my.cnf文件：

文件内容

#服务器标示
server-id=2
#日志存放位置
log_bin = /var/log/mysql/mysql-bin.log
#主服务器ip
master-host =192.168.0.1
#用于复制的用户，也就是我们最开始创建的那个
master-user=slave1
#用户密码
master-pass=123456
#端口
master-port =3306
#连接超时时间，单位为“秒”，默认值为3600，即1小时
master-retry-count=10
#要复制的数据库，这句话谨慎使用，如果没有特殊情况最好不用，因为这句话有隐患会引起问题
replicate-do-db =test

 

         2.导入主服务器数据库的备份脚本back.sql,输入命令：

source /home/back.sql;

 

        3.开启从服务器同步服务，命令比较简单，SQL状态下输入：

start slave;

 

        4.查看Slave状态：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.2
Master_User: root
Master_Port: 3306
Connect_Retry: 4
Master_Log_File: mysql-bin.003
Read_Master_Log_Pos: 79
Relay_Log_File: mysql -relay-bin. 003
Relay_Log_Pos: 548
Relay_Master_Log_File: mysql -bin. 003
#Slave IO进程状态
Slave_IO_Running: Yes
#Slave SQL进程状态
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Last_Errno: 0

        参数也比较简单，上面都介绍过了，一看就懂了。 

 

        5.我们此时可以更新一下主服务器的数据库，再到从服务器中验证结果，下篇文章我们会讨论一下这种机制的问题及解决办法。

 

        主从复制一般会设置一台性能较好的主服务器，复制更新数据的操作，理论上我们认为查询操作的数量级远远大于更新的操作，所以需要准备多台从服务器组成负载均衡结构供外部查询。负载均衡可以使用mysql proxy或其他产品，这些内容我们之后会接触到。

评论

2 楼 tjjava 2016-07-12  

群主你的mysql系列结束了？

1 楼 fisherhe 2013-08-05  

mysql优化系列确实写得不错。。