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

摘要: 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.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 是發生什麼事了, 再來想辦法處理囉~