MySQL binlog (backup,archive,restore)

#my.ini 開啟 binlog，保留 7 天 (WAMP 2.5)
log-bin=L:\/
expire_logs_days = 7

#login root
mysql -u root -p
pcPxyncjC8hCbUn

#主機名稱
select @@hostname;
show variables where Variable_name like '%host%';

#目前連線
select host from information_schema.processlist;
show processlist;


#顯示檔名
show master status;
SHOW BINARY LOGS;

#binlog 參數
show variables like '%bin%';

#查看 binlog 内容
show binlog events;
show binlog events in '.000002';

#重播資料庫變動過程
mysqlbinlog hostname-bin.001

#還原 binlog
mysqlbinlog -H --set-charset="utf8" --start-datetime="2017-03-08 15:22:00" --stop-datetime="2017-03-09 10:00:00" L:\.000001 L:\.000002 > L:\binary.sql

#backup.bat(完整備份，清除 binlog)
set x=%date:~0,4%%date:~5,2%%date:~8,2%
mysqldump --flush-logs -u root -pLpcPxyncjC8hCbUn chair_flow > L:\backup%x%.sql

#http://forum.slime.com.tw/thread208729.html
#http://g9677602.blogspot.tw/2010/01/mysql-binary-log-mysql-bin-log.html
#https://blog.wu-boy.com/2007/03/mysql-mysqlbinlog-%E8%B3%87%E6%96%99%E5%BA%AB%E8%99%95%E7%90%86%E4%BA%8C%E9%80%B2%E5%88%B6%E6%97%A5%E8%AA%8C%E6%AA%94%E6%A1%88%E7%9A%84%E5%AF%A6%E7%94%A8%E5%B7%A5%E5%85%B7/

#取代欄位字串
UPDATE 表格
SET 欄位名稱 = REPLACE(欄位名稱, '123\', '')
WHERE ID = 4