MySQL复制设置步骤（转）

转载自：丁丁：http://www.itpub.net/thread-831154-4-1.html

 

MySQL复制设置步骤

前言：
        镜像机制是一种能够让运行在不同计算机上的两个或更多个MySQL服务器保持同步变化的机制。目前MySQL只支持“主-从”镜像关系。即只有一台主控制系统（可读写），所有的数据修改操作都必须在这台MySQL 服务器上进行；有一台或多台从属系统（只读），它们有着与主控系统完全一样的数据，主控系统上的数据变化在经过一个短暂的延迟后也将会发生在它们身上。
        “主-从”镜像关系中的数据同步是通过主控系统的二进制日志文件实现的；主控系统把自己执行过的SQL命令记载到自己的二进制日志文件里，从属系统则通过从主控系统的二进制日志文件读出SQL命令，并加以执行的办法来同步它们自己的数据库数据。“主-从”镜像机制不要求主控与从属系统都必须使用同一种操作系统。
        MySQL复制功能在MySQL5.0以上版本比较稳定、而且性能也比较好。从其他用户测试与MySQL AB公司介绍可以获知，本人曾恶意地使用Update语句修改了一张表的所有数据（数据量：17万多条，主从机器都不在同一网段，主机-外网，从机为虚拟机（内网）），几乎没有感觉到时延。
        另外，若企业使用MySQL复制特性，主要是出于安全性与速度方面的考虑，若读操作是导致数据库系统变慢的主要原因，建议大家还是先进行SQL优化与服务器配置优化，或增加内存与CPU。至于安全性方面，大家关注的可能是担心硬盘损坏等造成数据丢失，这种情况可以考虑使用RAID系统进行冗余存储，即硬盘数据建立镜像。
建议大家不要首先就考虑采用MySQL的镜像机制，投入的资金与取得的实际效果并不是最优的（引用大师的话：Michael Kofler）。
那什么情况下可以考虑采用镜像机制？我个人认为至少是这几点的结合体（因为我们必须为老板省钱且做好事情，才可能为公司创造更多价值，才可能发更多奖金，嘿嘿！）。
1>        主要是读操作影响数据库服务器性能，并不是SQL语句性能差，也不是程序性能差与服务器配置等的问题，确实访问量太多（主要指网站类型的，发财了！），使用MySQL群集解决负载的问题，公司不想投入那么多资金。这个是时候你可以考虑使用MySQL复制进行折中。
2>        需要进行在线分析处理（OLAP），但是数据库又要影响大量的事务处理（OLTP），，并且是需要实时数据分析，而且在线事务分析处理后的数据对公司的经营决策非常重要，这个时候你可以考虑用一台服务器作为从服务器（比较适合游戏公司）。
3>        为了数据的安全性、备份、用于查询操作。因为使用RAID只可能解决因硬盘损坏造成的数据损失，而不能防止操作系统崩溃、电源故障或其他类似意外。出现问题，可以立即把从机升级为主服务器，若限制了从机只能读的话，需要修改（还有权限信息是否也镜像过来了或是否一致）。若限制从机不能修改，则建议不要把mysql数据库镜像过来，还有就是可以把一些查询操作放在从机上执行，以及数据库的备份操作也可以放在从机上进行（比较适合游戏公司与中小型企业）。
金融以及对数据安全、影响性能要求高的企业或国家部门，肯定会采用群集的！以上是本人根据学习与实际工作写的东东，只是我个人的观点噢，是否合理还有待大家的实际检验，有不正确的地方还请大家指定。下面介绍复制的过程。
一、测试环境
        主机：
                主控机操作系统：FreeBSD
                IP：10.21.0.5
                MySQL版本：5.0.27
                
从属机操作系统：Linux AS3.0（安装在虚拟机上）
                IP：192.168.0.229
                MySQL版本：5.0.27
                                                                                                                                                                        
二、主控机的my.cnf文件与复制相关的配置
[mysqld]
log_bin = ccxy_db
binlog-do-db = ccxyaccount
binlog-ignore-db = mysql,ccxydata_lz,ccxydata_ws,ccxydata_wy,test,ccxydata

三、从属机的my.cnf文件与复制相关的配置
[mysqld]
server-id            = 2
master-user          = eugene
master-password      = 2006eugene
master-port          = 3306
master-connect-retry = 120
read-only            = 1
relay-log-purge      = 1
replicate-do-db      = ccxyaccount
replicate-ignore-db  = mysql,test
report-host          = eugene
slave-skip-errors    = all
#master-log-file      = ccxy_db.000008

在主控服务器上进行权限设置：
grant replication slave on *.* to 'eugene'@'192.168.0.229' identified by '2006eugene'
对需要进行镜像的数据库进行备份，当然你也可以不备份直接使用通过镜像来完成（不建议使用这种方法）。由于我的数据库存储引擎为MyISAM，所以使用我的备份脚本接可以完成备份与打包并且传送到指定的服务器上。然后进行解压缩等相关操作。
先在主控服务器上执行：

mysql> show master  status;
+----------------+----------+--------------+---------------------------------------------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB                                        |
+----------------+----------+--------------+---------------------------------------------------------+
| ccxy_db.000008 |       98 | ccxyaccount  | mysql,ccxydata_lz,ccxydata_ws,ccxydata_wy,test,ccxydata | 
+----------------+----------+--------------+---------------------------------------------------------+
1 row in set (0.00 sec)

使用了master-log-file设置选项，就无法启动从属服务器上的mysqld，提示找不到日志文件。注释掉后就可以启动mysqld（加上master-log-pos也不行）。进入mysql命令模式下执行：
mysql> slave start;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
出现错误，然后再执行Change master语句就可以解决：
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
在从属服务器上执行：
mysql> change master to master_host='10.21.0.3',master_user='eugene',master_password='2006eugene',master_log_file='ccxy_db.000008' ,master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)

接着执行：
mysql> slave start;
Query OK, 0 rows affected (0.01 sec)
就可以解决，至于为什么会出现这样的问题，目前我无法解释（需要等待我找出更多证据后再告诉大家）。

在从属服务器上也可以设置某张或多张表的数据不进行复制，只要在my.cnf 文件中添加：
replicate-wild-ignore-table=dbname.tablename 
也可以限制不复制某个数据库：
replicate- ignore-db =dbname
注意：
        在主服务器上设置了binlog-ignore-db 就会把这些被列出来的数据库任何更改都不会写入二进制日志文件中的，若需要保留这些更改日志，请不要在主服务器上设置这个选项。而是在从属服务器的my.cnf文件中设置需要复制的数据库与不复制的数据库。
        另外：使用复制功能应该根据用途不同与实际情况，考虑服务器之间的物理位置关系（指是否在同一域或同网段内等），因为网络传送会影响到你复制的速度，从而可能抵消一部分你为了提供系统响应的速度。