mysql主从配置教程及容错方案详解

主从配置我们需要有两台mysql服务器,我们先要清楚两点

1、mysql配置文件my.cnf的位置

2、如何启动、停止mysql，找好启动文件

假设有两台机器，已经安装好了mysql（尽量同版本，且两台机器同一网络，可以ping通）

有朋友说：“从服务器，不能低于主服务器的版本”，不过我是低于的，没有出现问题。

主机A: 192.168.1.100

从机B:192.168.1.101

可以有多台从机

环境windows 2003系统

主服配置文件

server-id = 1
log-bin = mysql-bin
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-do-db = master_slave
//重启主服
service mysqld restart
//查看主服状态，并记下bin log pos，主服的所有操作均记录在Binlog中
show master status;
从服配置文件
server_id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-do-db = master_slave
//重启从服
service mysqld restart
//设定主服并启动同步
mysql> change master to master_host='192.168.0.1', master_user='root', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
mysql> start slave;
mysql> show slave status;

环境：
主服务器地址：192.168.239.2，windows XP
从服务器地址：192.168.239.129，windows XP

主从服务器的版本都是5.0.19（主从服务器的版本可以不一样，但是从服务器必须高于主服务器）

演习目标，让主服务器的test数据库在从服务器上生成一个镜像

1 主从服务器都已经正常安装MySQL，关闭主从服务器的MySQL服务
2 拷贝主服务器的test数据库的数据目录到从服务器的相应目录
3 修改主服务器的MySQL的配置，增加以下配置：

server-id=1
log-bin=D:/APMXE5/mysql50/logs/master
binlog-do-db=test
#只镜像test这个数据库

4 启动主服务器的MySQL，执行show master status;
应该能看到类似这样的两个值：

file：master.000001
position：128
记下来

5 在主服务器的MySQL创建一个新用户replicuser（你可以随便起，只要和下面的配置对应的上），从服务器就通过这个用户登录主服务器读取二进制日志

6 修改从服务器的MySQL配置，增加以下配置：

server-id=2
master-host=192.168.239.2
master-user=replicuser
master-password=123456

7 重新启动从服务器的MySQL，登录后执行以下命令：

change master to master_host='192.168.239.2';
change master to master_user='replicuser';
change master to master_password='123456';
change master to master_log_file='master.000001';
change master to master_log_pos=128;

start slave

8 如果正常的话，就已经配置完毕，下面测试：然后在主服务器上新增一条记录，到从服务器上能查到；从服务器先停掉，主服务器增加记录，从服务器启动后也能查到新增的记录。

更新：dump数据的时候可以使用这个命令

/usr/bin/mysqldump xxx --master-data=1 --default-character=utf8 -uroot -p > xxx.sql

加红的这个参数做两件事：1 把logfile、logpos写入到dump出来的SQL中；2 dump前读锁定全部表（可读不可写），dump后解锁全部表，就是

其它话题，MySQL主从服务器的一些技巧

问题：主从服务器表类型的选择

一般的共识是主服务器使用innodb，从服务器使用myisam，以便各尽其能。

问题：主从服务器字段类型的选择

字段类型对于分页等操作有很大影响。主服务器一般是innodb，因为不涉及查询，所以可以使用varchar等来存储字符串来节省空间，从服务器一般是 myisam，因为涉及查询，所以必须在char和varchar之间仔细权衡，没有varchar, text, blob字段的表是静态表，反之是动态表，静态表的检索效率要比动态表好若干倍，一般来说，所有涉及大结果集的查询都应该尽可能保证在静态表上完成，这里 说一个例子：比如说常见的articles表有title(varchar), body(text)等字段，在做文章列表的时候，因为不是静态表，所以查询不会很快，下面开始重构解决方案：把原来的articles表拆分成 subjects表和contents表，title字段设置为一个足够的char类型放在subjects表里，body字段还保持是text类型放到 contents表里，subjects和contents表之间的关系是一对多，这样，顺带着也方便的实现了多页文章的功能，而且更重要的是在查询文章 列表的时候，操作都是在subjects静态表里完成，效率肯定会比前一种方案提升很多。

问题：主从服务器NOW()函数造成数据不一致

假设在主服务器上执行一条INSERT …. VALUES ( …, NOW())，那么在从服务器上也会同样执行一条的SQL语句，但是一来主从服务器各自的时间设置可能就不一致，二来主从服务器间的SQL同步也可能存在 时间上的的延迟，这样，NOW()在两台服务器上的结果就可能不一致。解决方法是显而易见的，就是不要使用NOW()，时间的计算在应用程序里完成。这里 介绍一个额外的小技巧：在PHP里如果想获得当前时间的时间戳，不要用time()，而应该使用$_SERVER[‘REQUEST_TIME’] （PHP版本大于5.1有效），这样少做了一次系统调用，更有效率。

问题：主从服务器读写分离时读操作失败

先重现一下问题：比如说添加一条新数据，添加成功后根据last_insert_id跳转到新添加数据的浏览页面。在此过程中添加新数据的操作是在主服务 器上完成的，浏览新数据的操作实在从服务器上完成的，不过由于主从服务器间SQL同步存在延迟，所以当使用last_insert_id在从服务器上查询 的时候，从服务器很可能还没有还没来得及同步到此记录，所以读操作失败。解决思路也不复杂，在代码里加入一个缓存层（可以使用memcached），新添 加的数据都顺手放到缓存层里一份，新数据的读操作也先查询缓存层，这样就不会再有读操作失败的问题了，当然删除或者更新数据的时候也要顺带着处理好缓存数 据，可以使用观察者模式来搞定。不过这样缓存方案只限于读取单一的记录，对于读取列表的记录的情况，则是无效的。

问题：主从服务器索引是否有必要保持一致

一般都是利用主从服务器完成读写分离，从服务器上进行读操作，主服务器进行写操作，这样的话，主服务器上仅保留主键，外键，唯一索引等必要的索引即可，以 便保持数据合法性，而对于那些原本用于优化SELECT操作的索引，可以全部删除，如此的话主服务器的写操作效率会提升很多。