• +86 400-9955-698
  • support@vinchin.com
logo
关于我们

技术分享

技术分享 mysqlbinlog备份和mysqldump备份

mysqlbinlog备份和mysqldump备份

2020-08-04

-bash : mysqldump: command not found

-bash : mysqlbinlog:command not found

首先得知道mysql命令或mysqldump命令的完整路径,可以使用find命令查找

 

find  / -name mysql -print
我的mysql的路径是:/usr/local/mysql/bin/mysql,然后映射一个链接到/usr/bin目录下,相当于建立一个链接文件
ln -fs /usr/local/mysql/bin/mysql /usr/bin
同理:ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin

5.7之前:
修改my.cnf文件,添加log-bin=my-bin(my.cnf的位置根据自己安装的路径查看)
# vi /usr/local/mysql/my.cnf
log-bin=my-bin            #my-bin就是bin-log文件的名字,可以根据各自情况自定义文件名

或自定义存放bin-log的位置,默认情况下是在mysql data目录下,如
log-bin=/mysql/data/my-bin
注意:/mysql/data文件夹的权限需要mysql的。
默认是在/mysql/data/ 下有mysql-bin.00001  ......
修改后需要重启mysql
# service mysql restart


查看mysql-bin.00001日志文件:
mysqlbinlog mysql0bin.00001 |less
比如:
'/*!*/;
# at 120
#180412  8:53:44 server id 1  end_log_pos 203 CRC32 0xb1a0533f  Query   thread_id=5153988       exec_time=0
     error_code=0
SET TIMESTAMP=1523494424/*!*/;
SET @@session.pseudo_thread_id=5153988/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 203
#180412  8:53:44 server id 1  end_log_pos 703 CRC32 0x04607463  Query   thread_id=5153988       exec_time=0
     error_code=0
use `zabbix`/*!*/;
SET TIMESTAMP=1523494424/*!*/;
insert into history (itemid,clock,ns,value) values (24103,1523494423,873193168,2.450000),(24283,1523494423,995677732,4.266667),(24163,1523494423,998621650,0.000000),(24523,1523494424,3255086,0.000000),(24164,1523494424,5611211,0.004164),(24104,1523494424,12912322,0.000000),(24344,1523494424,14527510,0.058611),(25064,1523494424,16363257,0.050000),(24284,1523494424,19002870,0.000000),(24524,1523494424,76535760,95.865357)


可以通过at,找到位置,时间等等
如果删除数据库库或者表,可以用mysql-bin.000012等恢复文档。

用mysqlbinlog工具查看
基于开始/结束时间
mysqlbinlog --start-datetime='2016-08-02 00:00:00' --stop-datetime='2016-08-03 23:01:01' -d hadoop /data/mysql/data/mysql-bin.000001


基于pos值,注:hadoop是库名,/var/lib/mysql/mysql-bin.000001是二进制文件路径
mysqlbinlog --start-position=2098 --stop-position=2205 -d hadoop /data/mysql/data/mysql-bin.000001

 

  • 标签:
  • 容灾备份

您可能感兴趣的新闻 换一批

现在下载,可享30天免费试用

立即下载