mysql 数据库的同步问题(双机热备)

1. 首先，你应该有两个或两个以上的 mysql 数据库服务器，版本最好是在 3.3 以上 ( 当然啦，两个服务器不一定是两台机器，一台机器上安装两个 mysql 服务是可以的，同时，如果你对 mysql replication 原理十分精通的话，你甚至可以在一个 mysql 服务的两个不同数据库 database 之间作同步，看有没有需要了) 
  说明： 这两个服务器一般设置一个为 主服务器，或叫源服务器， master mysql server, 另一台或其他多台就是 replication slave 同步从服务器了。一台 slave 与多台 slave 设置方法是一样的，这样你就可以作类似数据库集群了 


2. 设置可访问 mysql 帐号，操作以英文为准 
  说明： 2.1 mysql 帐号一般设置为限定 ip 访问，以保障安全性 
 2.2 mysql 帐号一般在 master 与 slave 设置为相同帐号
 2.3 特别注意，如果你用 linux / unix 操作系统，那一定要注意一下防火墙 firewall 
 有没有限制 mysql 远程访问，如果是，最好是打开远程访问端口，并作好访问 ip 限制 
 2.4 由于 my.ini 中要明文存储 mysql 帐号密码，请注意保护 my.ini 不让其他用户访问到 
 ( 看来要向 mysql 说明下以后用密码存 passwd ) 


3. 第三步，当然是设置两个服务器要同步的数据库为同样的数据库了 
  这里有一些技巧，包括 mysql 的 sql 指令说明下 
 3.1 方法之一，就是英文说明中说的，先锁定数据库读写功能 ( 其实最好是停止 mysqld 服务，再作备份) 然后用 tar 备份数据库目录，转到 slave 服务器相同数据目录中 


  3.2 方法之二，使用 mysql studio 这样的实用工具，直接使用 mysql studio 的 backup database 工具把数据库同步 


  3.3 以上两种方法都是 master 数据库中有不少数据记录，按上两 种方法得到 master 与 slave 有相同数据库与数据记录。 而第三种方法，则是适合于新建数据库的情况，特别适合于 master 与 slave 在 my.ini 已经设置好 replication 关系 （但 未指定 database 同步数据库 ) 的情况: 这就是使用mysql 的 sql 语句 load table from master 与 load data from master; 
  load table from master 可以从 master 数据库把表结构复制到 slave 数据库中，这样可以建立同步的表。 
  load data from master 是从 master 数据库把数据导入到 slave 数据表中，条件是 master 从一开始安装运行就使用了 bin-log 参数而保存有二进制日志 


4. 接下来就是配置 master 与 slave 的 my.ini 文件，使得 replcation 能有合适的启动参数以支持数据同步 
  技巧： 如果你使用 win2k 的 mysql 那么，你可以下载 mysql.com 出的官方 mysql administrator 实用程序，直接在 mysql administrator 中即可配置 master 与 slave ，同时也可以配置 query-cache 哟 


  在 master 的 my.ini增加 
  [mysqld] 
  log-bin = 路径
  server-id=1 


  注意，上面的 log-bin = 中的 等号 = 是不可少的 


 在 slave 的 my.ini 修改 
  [mysqld] 
  server-id=2 # 如果有多个 slave 就改为不重复的 id 就好，在mysql 4.1 中，这个已经取消了 
  master-host=10.10.10.22 
  master-user=backup #同步用户帐号 
  master-password=1234 
  master-port=3306 
  master-connect-retry=60 #预设重试间隔60秒 
  replicate-do-db=test # 告诉slave只做 test 数据库的更新 
  bin-log = 




5. 检查 master 与 slave 配置状态，使用 show master status; 与 show slave status; 这两个 SQL 指令即可在 master 与 slave 查看配置状态。这里有两个状态变量十分重要 


mysql > SHOW MASTER STATUS; 
+---------------+----------+--------------+------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+---------------+----------+--------------+------------------+ 
| mysql-bin.003 | 73| test | manual,mysql | 
+---------------+----------+--------------+------------------+ 


注意 File 是 mysql-bin.003 而 position 是 73 
这个文件是对 test 这个 数据库的二进制日志记录，记录数据变化的当前记录条数是 73 




前面我们说过，二进制日志记录着某个数据库所有数据记录变化的 sql 操作语句，如 insert / update / delete 等，正是因为如此，在 slave 同步 mysql 数据库操作时，其实是读取master 中这个 mysql-bin.003 二进制日志中的 sql 操作，同在 slave 中执行这些 sql 操作，所以，同步成功有以下几个条件 
  1. master 与 slave 有相同的数据库表结构，最好 database name 也一样 ( 可以设置为不一样的 database name) 
  2. master 与 slave 有相同的初始数据记录，保证同步操作开始后两者数据一致 
  3. master 必须使用 bin-log 二进制日志记录 ( 推荐 slave 也使用 bin-log ) 
  4. slave 是从 master 的 bin-log 是读取 sql 记录来同步，所以，从哪一条 log 开始读取很重要 ( 下面的第 7 条操作，就是保证 slave 能与 master 保持相同的记录读取，并读取正确的 bin-log 日志文件 ) 


6. 


7. 在 slave 执行下面 sql 操作 


mysql> CHANGE MASTER TO 
  -> MASTER_HOST='master_host_name', 
  -> MASTER_USER='replication_user_name', 
  -> MASTER_PASSWORD='replication_password', 
  -> MASTER_LOG_FILE='recorded_log_file_name', 
  -> MASTER_LOG_POS=recorded_log_position; 


这里，把 上面的 redcorded_log_file_name 改为 mysql-bin.003 而 recorded_log_position 改为 73 （特别注意，这是关系关键） 




最后，测试一下吧 




linux 下的 mysql 与 win2k 下的 mysql 作 replication 会有什么技巧: 




1. 注意 linux 下有大小写区分，而 win2k 下没有 
  所以最好使用 mysqldump 方法来让两个数据库初始化同步，而不能使用 tar 方式直接 copy 数据库表文件 
  使用 mysql studio 来作 database backup 也不错 


2. 注意 win2k 下的 mysql 配置文件是 c:\my.ini 
  你可以使用 mysql administrator 0.9 这个图型界面的实用工具来设置 


3. 如果 win2k 下的 mysql 为 master , 一定要注意 show master status 中显示出来的结果 
4. win2k 下的 mysql 为 slave 时，注意不要把同步时间周期设置太长。 
 
*******相关命令:
•  stop slave    #停止同步 
•  start slave    #开始同步，从日志终止的位置开始更新。 
•  SET SQL_LOG_BIN=0|1  #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误 
•  SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。 
•  RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER 
•  RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下， 
•  LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 
•  LOAD DATA FROM MASTER  #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 
•  CHANGE MASTER TO master_def_list  #在线改变一些主机设置，多个用逗号间隔,比如
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret' 
•  FLUSH MASTER; 
•  FLUSH SLAVE; 
•  MASTER_POS_WAIT() #从机运行 
•  SHOW MASTER STATUS #主机运行，看日志导出信息 
•  SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。 
•  SHOW SLAVE STATUS (slave) 
•  show status slave\G; 
•  SHOW MASTER LOGS (master) 
•  SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] 

•  PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'