Thursday, December 10, 2009

MySQL5.0 同步备份恢复的三种方法

第一种最简单的方法适用于数据库文件比较小,能在停止主数据库服务后几分钟内打完tar包的情况,这种情况与第一次做slave同步的方法一样:
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
记录状态后进入命令行将数据库原生数据文件打个tar包,再
mysql> UNLOCK TABLES;
将tar包重布署到MySQL slave上即可,注意将tar包中的日志文件,master.info,relay-log.info要删除之后再CHANGE MASTER TO。

第二种方法是利用mysql的二进制日志文件。
首先重启一次主数据库。
然后在slave上用以下命令查看二进制日志文件执行的最后SQL语句,其中start-datetime时间为此二进制日志文件最后的修改时间,或者提前30秒方便查看最后执行完成的SQL语句:
$> mysqlbinlog -S /tmp/mysql.sock hostname-relay-bin.000002 --start-datetime='2009-12-11 11:12:00'

然后在主数据库上用命令筛选出符合时间范围的日志:
$> mysqlbinlog --start-datetime='2009-12-11 11:12:00' -S /tmp/mysql.sock mysql-bin.000007 > /home/username/bin-log.sql
再用grep命令,查找到bin-log.sql中与之前在slave上查到的SQL行号,并用sed命令删除1到此行号的全部行(举例查到行号为1234):
$> grep -n "last executed sql statement" /home/username/bin-log.sql
$> sed '1,1234d' /home/username/bin-log.sql > /home/username/bin-log-sed.sql
将生成的bin-log-sed.sql文件传到slave数据库上用mysql命令行工具导入数据。
$> mysql -uroot -p < bin-log-sed.sql
如果碰到报错,用sed再删除sql文件的前几行导入。
导入完成后,进行mysql控制台,下面的master_log_file是同步中断的主数据库日志文件mysql-bin.000007的后继日志文件名mysql-bin.000008:
$> stop slave;
$> change master to master_log_file='mysql-bin.000008', master_log_pos=98;
$> start slave;
$> show slave status\G

第三种方法也是利用二进制日志文件恢复。
与第二种方法类似,只是在grep查到行号之后,用awk命令找到日志中断的位置,重新调整slave上的master_log_pos即可。
举例查到行号为1234:
$> cat /home/username/bin-log.sql |awk 'NR >= 1234 {print $0}' |more
可以找到最后执行完的那个SQL之后,服务器的二进制日志文件位置:
last executed sql statuement;
之后会看到类似:
# at 1023411738
这样的内容,用这个值更新slave上的master信息:
$> stop slave;
$> change master to master_log_file='mysql-bin.000007', master_log_pos=1023411738;
$> start slave;
$> show slave status\G
在slave的二进制日志或者是hostname-relay-log.info,hostname.err中也会有master上的end_log_pos,可以先尝试用一下,一般因为master/slave异常才导致同步失败,在slave上的这些信息已经不正确,所以需要用awk找到服务器上的二进制日志位置。


MySQL5 Replication fails with "log event entry exceeded max_allowed_packet"

配置文件中有二个参数设置需要注意,max_allowed_packet在主从服务器上的设置最好是设置相同,并且将read_buffer_size <= max_allowed_packet。 
如果这二个配置正确,仍然报错,则需要STOP SLAVE; 并CHANGE MASTER TO,调整master_log_pos的值,这个值与服务器的日志文件要对应上。
Reference: http://bugs.mysql.com/bug.php?id=15937
http://bugs.mysql.com/bug.php?id=8215

Thursday, December 03, 2009

mysql5中注意UUID函数的使用

NOW() 函数,因为在二进制日志里已经包括了时间戳,可以被正确复制到slave server上。
UUID() 函数,具有非确定性,所以不能被复制到slave server,所以在存储过程或者触发器中要慎用。
the UUID() function is nondeterministic (and does not replicate). You should be careful about using such functions in triggers. It is not safe.
SYSDATE() 函数也具有非确定性,与NOW()函数不一样,在同步复制时会与master上的时间不一致。官方文档说明如下:
SYSDATE()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context.

As of MySQL 5.0.13, SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.)


mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is non-deterministic and therefore unsafe for replication. If that is a problem, you can start the server with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). The non-deterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.

Wednesday, December 02, 2009

在64位的CentOS5中编译安装mysql5.0.88

$> wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.88.zip/from/http://mysql.byungsoo.net/
$> yum -y install gcc
$> yum -y install gcc-c++
$> yum -y install ncurses-devel

$> CC=gcc \
CFLAGS="-O3 -fno-omit-frame-pointer" \
CXX=gcc \
CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/usr/local/mysql --without-debug --with-big-tables --with-unix-socket-path=/tmp/mysql.sock --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --enable-assembler --with-extra-charsets=gbk,gb2312,utf8 --with-pthread --enable-thread-safe-client

$> make && make install

Reference: http://dev.mysql.com/doc/refman/5.0/en/linux-ia-64.html

.bashrc中rc的含义说明

rc (像是 ".cshrc" 或 "/etc/rc" 中的 rc 这两个字母) = "RunCom"

"rc" 是取自 "runcom", 来自麻省理工学院在 1965 年发展的 CTSS系统。相关文献曾记载这一段话:
具有从档案中取出一系列命令来执行的功能;这称为 "run commands" 又称为 "runcom",而这种档案又称为一个 runcom (a runcom)。