mysqlbinlog使用
版本号:MySQLbinlog Ver 3.4 for linux-glibc2.5 at x86_64 公司主营业务:做网站、网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。成都创新互联公司是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。成都创新互联公司推出福清免费做网站回馈大家。
使用方式:mysqlbinlog --option --option --option .. mysqlbinlog.00001 mysqlbinlog.00002.... Usage: mysqlbinlog [options] log-files -?, --help Display this help and exit. --base64-output=name 当日志格式为row模式时,需要加此参数转义,才能看得懂, 注意:此选项只是用于查看,排查等,真正恢复数据时不可加此参数 一般和 -vv配合使用 --bind-address=name IP address to bind to. --character-sets-dir=name Directory for character set files. -d, --database=name List entries for just this database (local log only). --debug-check Check memory and open file usage at exit . --debug-info Print some debug info at exit. --default-auth=name Default authentication client-side plugin to use. -D, --disable-log-bin Disable binary log. This is useful, if you enabled --to-last-log and are sending the output to the same MySQL server. This way you could avoid an endless loop. You would also like to use it when restoring after a crash to avoid duplication of the statements you already have. NOTE: you will need a SUPER privilege to use this option. -F, --force-if-open Force if binlog was not closed properly. (Defaults to on; use --skip-force-if-open to disable.) -f, --force-read Force reading unknown binlog events. -H, --hexdump Augment output with hexadecimal and ASCII event dump. -h, --host=name Get the binlog from server. -l, --local-load=name Prepare local temporary files for LOAD DATA INFILE in the specified directory. -o, --offset=# Skip the first N entries. -p, --password[=name] Password to connect to remote server. --plugin-dir=name Directory for client-side plugins. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). -R, --read-from-remote-server 从远程服务器读取binlog信息,不加此选项,一切 -h -u -p 等等远程连接选项都被忽略,将变成从本地读取那一堆你自己定义的mysql-binlog...... Read binary logs from a MySQL server. This is an alias for read-from-remote-master=BINLOG-DUMP-NON-GTIDS. --read-from-remote-master=name Read binary logs from a MySQL server through the COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by setting the option to either BINLOG-DUMP-NON-GTIDS or BINLOG-DUMP-GTIDS, respectively. If --read-from-remote-master=BINLOG-DUMP-GTIDS is combined with --exclude-gtids, transactions can be filtered out on the master avoiding unnecessary network traffic. --raw Requires -R. 当远程备份binlog时需要开启,生成二进制文件Output raw binlog data instead of SQL statements, output is to log files. -r, --result-file=name 当远程备份时,指定备份于本地的目录 /backup/mysql-bin Direct output to a given file. With --raw this is a prefix for the file names. --secure-auth Refuse client connecting to server if it uses old (pre-4.1.1) protocol. (Defaults to on; use --skip-secure-auth to disable.) --server-id=# Extract only binlog entries created by the server having the given id. --server-id-bits=# Set number of significant bits in server-id --set-charset=name Add 'SET NAMES character_set' to the output. -s, --short-form Just show regular queries: no extra info and no row-based events. This is for testing only, and should not be used in production systems. If you want to suppress base64-output, consider using --base64-output=never instead. -S, --socket=name The socket file to use for connection. --start-datetime=name 开始时间点,从指定的第一个文件开始扫描,》=这个时间点的日志 Start reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly). -j, --start-position=# 开始pos点,第一个binlog文件的pos点 Start reading the binlog at position N. Applies to the first binlog passed on the command line. --stop-datetime=name 终止时间点 Stop reading the binlog at first event having a datetime equal or posterior to the argument; the argument must be a date and time in the local time zone, in any format accepted by the MySQL server for DATETIME and TIMESTAMP types, for example: 2004-12-25 11:25:56 (you should probably use quotes for your shell to set it properly). --stop-never 用于远程备份binlog Wait for more data from the server instead of stopping at the end of the last log. Implicitly sets --to-last-log but instead of stopping at the end of the last log it continues to wait till the server disconnects. --stop-never-slave-server-id=# The slave server_id used for --read-from-remote-server --stop-never. --stop-position=# 最后一个指定的文件pos点 Stop reading the binlog at position N. Applies to the last binlog passed on the command line. -t, --to-last-log 源于远程备份或者,将远程服务器的binlog下载到本地,此参数代表终点 binlog file ,如 mysql-binlog.00234 Requires -R. Will not stop at the end of the requested binlog but rather continue printing until the end of the last binlog of the MySQL server. If you send the output to the same MySQL server, that may lead to an endless loop. -u, --user=name Connect to the remote server as username. -v, --verbose Reconstruct pseudo-SQL statements out of row events. -v -v adds comments on column data types. -V, --version Print version and exit. --open-files-limit=# Used to reserve file descriptors for use by this program. -c, --verify-binlog-checksum Verify checksum binlog events. --binlog-row-event-max-size=# The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. This value must be a multiple of 256. --skip-gtids
Do not print Global Transaction Identifier information (SET GTID_NEXT=... etc). --include-gtids=name Print events whose Global Transaction Identifiers were provided. --exclude-gtids=name Print all events but those whose Global Transaction Identifiers were provided. Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- base64-output (No default value) bind-address (No default value) character-sets-dir (No default value) database (No default value) debug-check FALSE debug-info FALSE default-auth (No default value) disable-log-bin FALSE force-if-open TRUE force-read FALSE hexdump FALSE host (No default value) local-load (No default value) offset 0 plugin-dir (No default value) port 0 read-from-remote-server FALSE read-from-remote-master (No default value) raw FALSE result-file (No default value) secure-auth TRUE server-id 0 server-id-bits 32 set-charset (No default value) short-form FALSE socket /tmp/mysql.sock start-datetime (No default value) start-position 4 stop-datetime (No default value) stop-never FALSE stop-never-slave-server-id -1 stop-position 18446744073709551615 to-last-log FALSE user (No default value) open-files-limit 64 verify-binlog-checksum FALSE binlog-row-event-max-size 4294967040 skip-gtids FALSE include-gtids (No default value) exclude-gtids (No default value) |
示例:
1、从远程服务器 读取 数据库test 开始时间 2017-09-26 12:05:00' 的binlog
mysqlbinlog -uroot -p123456 -h292.168.1.100 -R -d test --start-datetime='2017-09-26 12:05:00' --base64-output=decode-rows -vv mysql-binlog.000004 mysql-binlog.000004 | more
2、从远程服务器同步binlog
mysqlbinlog -uroot -p123456 -h292.168.1.100 -R --raw --stop-never -r mysql mysql-binlog.000001
如果是mysql服务器上执行,可以去除 -uroot -p123456 -h292.168.1.100 -R
分享名称:mysqlbinlog使用
转载源于:http://azwzsj.com/article/ghhjgo.html