摘要: mysql 从 3.23.15 版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步，主从模式，互相备份模式的功能。 (2004-02-12 10:45:10) 设置 MySql 数据同步 摘要 mysql 从 3.23.15 版本以后提供数据库复制功能。利用该功能可以实现两个数据库同步
摘要: 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测试数据',
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 是發生什麼事了, 再來想辦法處理囉~
查看更多关于设置MySql数据同步及故障处理的详细内容...