现对mysql数据库进行二进制备份还原
备份:mysqldump -uroot -p"XXXXXX" --single-transaction --flush-logs --master-data=2 --opt jianghu > /data/backup/data/jianghu/backup_sunday_1_PM.sql
恢复:
先对其进行完全恢复:mysql -uroot -p"XXXXXX" jianghu</data/backup_sunday_1_PM.sql
再进行增量恢复:
1: mysqlbinlog -s -r mysql-bin.sql mysql-bin.000002
2: mysql -uroot -p"XXXXXX" jianghu </data/mysql/data/mysql-bin.sql
此时提示错误
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
部分二进制代码为:/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090715 18:20:57 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.34-log created 090715 18:20:57
# at 106
#090715 18:21:00 server id 1 end_log_pos 177 Query thread_id=980 exec_time=3 error_code=0
use jianghu/*!*/;
SET TIMESTAMP=1247653260/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C utf8 *//*!*/; 红色表示12,13行
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN/*!*/;删除12行后再次操作:mysql -uroot -p"XXXXXX" jianghu </data/mysql/data/mysql-bin.sql
提示:
ERROR 1064 (42000) at line 185: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use
near '' at line 1部分二进制日志内容为:
SET /*!*/; 红色表示185,186行
SET @@session.sql_mode=2097152/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653420/*!*/;
update team_info set money=15242, wood=15295, ore=15309, shengw=2919, addmoney=0.22432222222138498, addwood=0.22432222222138498, addore=0.22432222222138498, addshengw=0.33616888888889, time=1247653420747 where id=65628/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653422/*!*/;
update tools_list_info set typesel=0, tools_id=3, team_id=65628, idec=-1, ideceq=-1, num=1, level=0, wugong=1, neigong=0, jinggu=0, shenfa=0, neili=1, strong=8, lockeq=null, member_id=215636 where id=2010150/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653422/*!*/;
update tools_list_info set typesel=1, tools_id=22, team_id=null, idec=-1, ideceq=-1, num=1, level=0, wugong=0, neigong=0, jinggu=0, shenfa=3, neili=2, strong=24, lockeq=null, member_id=215636 where id=2092592/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
就以上问题,不知道有什么解决办法,是不是我的mysql配置有问题吗?感觉也不是的呀啊
mysql版本为:5.1.34-log Source distribution
备份:mysqldump -uroot -p"XXXXXX" --single-transaction --flush-logs --master-data=2 --opt jianghu > /data/backup/data/jianghu/backup_sunday_1_PM.sql
恢复:
先对其进行完全恢复:mysql -uroot -p"XXXXXX" jianghu</data/backup_sunday_1_PM.sql
再进行增量恢复:
1: mysqlbinlog -s -r mysql-bin.sql mysql-bin.000002
2: mysql -uroot -p"XXXXXX" jianghu </data/mysql/data/mysql-bin.sql
此时提示错误
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
部分二进制代码为:/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090715 18:20:57 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.34-log created 090715 18:20:57
# at 106
#090715 18:21:00 server id 1 end_log_pos 177 Query thread_id=980 exec_time=3 error_code=0
use jianghu/*!*/;
SET TIMESTAMP=1247653260/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C utf8 *//*!*/; 红色表示12,13行
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
BEGIN/*!*/;删除12行后再次操作:mysql -uroot -p"XXXXXX" jianghu </data/mysql/data/mysql-bin.sql
提示:
ERROR 1064 (42000) at line 185: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use
near '' at line 1部分二进制日志内容为:
SET /*!*/; 红色表示185,186行
SET @@session.sql_mode=2097152/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653420/*!*/;
update team_info set money=15242, wood=15295, ore=15309, shengw=2919, addmoney=0.22432222222138498, addwood=0.22432222222138498, addore=0.22432222222138498, addshengw=0.33616888888889, time=1247653420747 where id=65628/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653422/*!*/;
update tools_list_info set typesel=0, tools_id=3, team_id=65628, idec=-1, ideceq=-1, num=1, level=0, wugong=1, neigong=0, jinggu=0, shenfa=0, neili=1, strong=8, lockeq=null, member_id=215636 where id=2010150/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
SET TIMESTAMP=1247653422/*!*/;
update tools_list_info set typesel=1, tools_id=22, team_id=null, idec=-1, ideceq=-1, num=1, level=0, wugong=0, neigong=0, jinggu=0, shenfa=3, neili=2, strong=24, lockeq=null, member_id=215636 where id=2092592/*!*/;
COMMIT/*!*/;
SET TIMESTAMP=1247653422/*!*/;
BEGIN/*!*/;
就以上问题,不知道有什么解决办法,是不是我的mysql配置有问题吗?感觉也不是的呀啊
mysql版本为:5.1.34-log Source distribution
source data/mysql/data/mysql-bin.sql
次恢复时可以进行的 也有错误 但是可以继续执行 不过这样恢复的速度实在是奇慢无比,难道二进制恢复不可以快点吗、?
请求高手解答!!
你两个数据库的版本是否一致?将#开头的语句跳过,直接到MYSQL中试逐一试一下你的语句看有什么问题。另外 -p不应该加引号。
mysql -uroot -p"XXXXXX" jianghu
再进行增量恢复: ”完全恢复后,只导出一张表的增量数据进行恢复,看报不报错,缩小问题范围。
/*!\C utf8 *//*!*/; <------------------错误行
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
.
.
SET /*!*/; <------------------错误行
SET @@session.sql_mode=2097152/*!*/;
他们在日志中有多处
导致ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
我不能在 “mysql-bin.000002”格式中把他删除的呀,他只能用mysqlbinlog工具才能打开,基于时间点和位置的恢复只能是二进制格式,要是能在配置中对
sql_mode进行设置可能能解决此问题 但是找不到在哪里进行设置,烦请大家指点
mysql> /*!\C utf8 *//*!*/;
Charset changed
Query OK, 0 rows affected (0.00 sec)mysql>
Charset changed
Query OK, 0 rows affected (0.00 sec)mysql>
这样是没有问题的将二进制用这种方法恢复不是要搞死人呀,
[root@jh6 var]# mysqlbinlog mysql-bin.000002 |mysql -uroot -p
Enter password:
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
我回复了的,在lz贴上也说个的:就是本库上面,mysql版本为:5.1.34-log Source distribution
但是还有 SET /*!*/; 不能通过,将其删除后一切正常
但是发现此恢复是在是太慢,难道二进制的恢复都这这么慢吗?
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;