设置 MySql 数据同步及故障处理

windychan

摘要:mysql从3.23.15版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步，主从模式，互相备份模式的功能。(2004-02-12 10:45:10)
设置 MySql 数据同步
摘要
　　mysql从3.23.15版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步，主从模式，互相备份模式的功能。(2004-02-12 10:45:10)
--------------------------------------------------------------------------------
By lanf, 出处：CHINAUNIX
　　mysql从3.23.15版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步，主从模式，互相备份模式的功能。
　　数据库同步复制功能的设置都在mysql的设置文件中体现。mysql的配置文件（一般是my.cnf）
　　在unix环境下在/etc/mysql/my.cnf 或者在mysql用户的home目录下面的my.cnf。
　　window环境中，如果c:根目录下有my.cnf文件则取该配置文件。当运行mysql的winmysqladmin.exe工具时候，该工具会把c:根目录下的my.cnf 命名为mycnf.bak。并在winnt目录下创建my.ini。mysql服务器启动时候会读该配置文件。所以可以把my.cnf中的内容拷贝到my.ini文件中，用my.ini文件作为mysql服务器的配置文件。
设置方法：
设置范例环境：
　　操作系统：window2000 professional
　　mysql：4.0.4-beta-max-nt-log
　　A ip:10.10.10.22
　　B ip:10.10.10.53
A:设置
　　1.增加一个用户最为同步的用户帐号：
GRANT FILE ON *.* TO backup@'10.10.10.53' IDENTIFIED BY ‘1234’
　　2.增加一个数据库作为同步数据库：
create database backup
B:设置
　　1.增加一个用户最为同步的用户帐号：
GRANT FILE ON *.* TO backup@'10.10.10.22' IDENTIFIED BY ‘1234’
　　2.增加一个数据库作为同步数据库：
create database backup
　　主从模式：A->B
　　A为master
　　修改A mysql的my.ini文件。在mysqld配置项中加入下面配置：
server-id=1
log-bin
#设置需要记录log 可以设置log-bin=c:mysqlbakmysqllog 设置日志文件的目录，
#其中mysqllog是日志文件的名称，mysql将建立不同扩展名，文件名为mysqllog的几个日志文件。
binlog-do-db=backup #指定需要日志的数据库
　　重起数据库服务。
　　用show master status 命令看日志情况。
　　B为slave
　　修改B mysql的my.ini文件。在mysqld配置项中加入下面配置：
server-id=2
master-host=10.10.10.22
master-user=backup #同步用户帐号
master-password=1234
master-port=3306
master-connect-retry=60 预设重试间隔60秒
replicate-do-db=backup 告诉slave只做backup数据库的更新
　　重起数据库
　　用show slave status看同步配置情况。
　　注意：由于设置了slave的配置信息，mysql在数据库目录下生成master.info
　　所以如有要修改相关slave的配置要先删除该文件。否则修改的配置不能生效。
　　双机互备模式。
　　如果在A加入slave设置，在B加入master设置，则可以做B->A的同步。
　　在A的配置文件中 mysqld 配置项加入以下设置：
master-host=10.10.10.53
master-user=backup
master-password=1234
replicate-do-db=backup
master-connect-retry=10
　　在B的配置文件中 mysqld 配置项加入以下设置：
log-bin=c:mysqllogmysqllog
binlog-do-db=backup
　　注意：当有错误产生时*.err日志文件。同步的线程退出，当纠正错误后要让同步机制进行工作，运行slave start
　　重起AB机器，则可以实现双向的热备。
　　测试：
　　向B批量插入大数据量表AA（1872000）条
　　A数据库每秒钟可以更新2500条数据。
相关 命令,
h. show processlist; // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看
他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show slave status;
slave stop;
reset slave;
slave start;
如果你的数据不重要可以让这条记录的同步跳过去
slave stop;
set sql_slave_skip_counter=1;
slave start;
或直接把辅库的那条记录删掉
on slave上运行
load data from master;
show slave statusG
看看状态是否正常.有什么错误提示
*******************************************************
mysql 双机热备实战 --安恒网管员手记
2005-04-20 刘世伟
打印自: 安恒公司
地址: /news/article.php?articleid=625
mysql 双机热备实战 --安恒网管员手记
mysql实现双机热备，原理是通过更新日志,用mysql的SELECT的文件操作功能，备机实时抓取主机的更新日志，当然这只是其原理，实际上并不需要我们自己去处理日志，明白了原理，实施就比较容易理解了。
这样，在主机端需要开一个账号，这个账号是备机用来抓取主机的更新日志。需要有文件访问权限，在早期，刚开始实现双机热备时，就是用的文件权限。
从mysql4 开始，添加了一个专门的权限，用来做热备，这个权限本质应该还是文件读取权限，但是应该只能用来读取日志，防止一些漏洞。
对于客户端来说，就是设置这个账号，密码，主服务器地址，还有要同步的数据库名。这只是单向的，再配置一个对等的同步通道，就支持双向的热备了。
通过热备，还可以备端从主端load全部数据。这个在同步出错时可以使用。load权限是一个单独的mysql权限,这样跟热备有关的mysql的权限有2个，日志抓取和数据载入(REPLICATION SLAVE, REPLICATION CLIENT )
在服务器端开备份账号
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO "backup"@ "192.168.1.2"IDENTIFIED BY "*****"WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ;
在服务器端打开日志功能
/etc/mysql/my.cnf
[mysqld]
server-id = 1
log-bin
binlog-do-db = pa
max_binlog_size = 104857600
-------------------------------------------- 这样，mysql会在数据目录放置pa这个库的更新日志。等待备机来抓取。
客户端设置:
/etc/mysql/my.cnf
master-host=192.168.1.1
master-user=backup
master-password=12345
master-port=3306
master-connect-retry=60
replicate-do-db=pa
客户端会到服务器抓取pa库的更新日志，来更新本地的pa库。
几个跟热备有关的mysql命令:(需要在mysql命令行界面或query ）
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'
MASTER_POS_WAIT() #从机运行
SHOW MASTER STATUS #主机运行，看日志导出信息
SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'
--------------------------------------------------------------------------------
下面是Q&A时间:
双机热备怎么配置？照上面再配置一个反向的更新就行了。
不用担心本机的更改会回环回来，因为server_id就是识别这个用的.
多机热备怎么做,几台mysql服务器就像首尾相连的蛇,组成一个环装,就可以了,而且还可以作几个单向的更新,用以分担select这样的读取操作的压力,因为mysql操作中大部分是
select操作.如下图所示:

补充:
MySQL Replication Status
MySQL 設好 Replication 後, 常會用以下指令來觀察 Master, Slave status, 但是秀出來的資訊代表的意思到底是如何呢??

• show master status
  
• show slave status

以下是 Master 機器上, show master status 出來的 欄位 和 說明:

• Master_Host: dbm1.domain_name
  
• Master_User: repl
  
• Master_Port: 3306
  
• Connect_retry: 60 , 這個 mysql server 重啟動到現在已經 connect 幾次了(自己 restart 會歸零)
  
• Master_Log_File: dbm1-bin.009 , 目前 Master 上已經寫到第幾個了
  
• Read_Master_Log_Pos: 991863990 , Slave讀到 Master 這個 log file 的第幾筆了(master 上的 file)
  
• Relay_Log_File: dbs1-relay-bin.008 , Slave目前正在寫入的 binary log (slave 上的 file)
  
• Relay_Log_Pos: 303654057 , 寫到第幾筆了
  
• Relay_Master_Log_File: dbm1-bin.009 , Slave目前傳到 Master 上的第幾個(目前正在抓哪一個過來), 目前 Master上, 已經讀到哪個 log file(relication) binary log(一堆 SQL 指令執行的記錄, 可用 mysqlbinlog 讀取)
  
• Slave_IO_Running: Yes , 這個 process 有在 run(抓 binary log), 抓 log 回來 (No: 可能原因有 網路斷, 權限問題, master stop)
  
• Slave_SQL_Running: Yes , 是否有在執行 binary log (error)
  
• Replicate_do_db:
  
• Replicate_ignore_db:
  
• Last_errno: 0 , 停掉前發生什麼事情, error number, 可用 perror 查詢
  
• Last_error: (error message)
  
• Skip_counter: 0 (set db slave skip counter = 1, start slave) 跳過這一筆
  
• Exec_master_log_pos: 991863990 (要與 Read_Master_Log_Pos 一樣, 代表沒有 delay)
  
• Relay_log_space: 303654057 目前有多少空間可以寫

平常最主要就是看 Slave_IO_Running, Slave_SQL_Running 是否是 Yes, 是 Yes 的話, 應該就都是正常在跑的狀況, 若是 No 的話, 就趕快去看一下 Last_error 是發生什麼事了, 再來想辦法處理囉~


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/9951/showart_59076.html