mysql bin mysqldump_mysqlbinlog备份和mysqldump备份

-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