• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)

by admin

If you have set up binary logging and wish to restore a binary log, but want to omit one or more specific SQL statements from a data recovery, you can do so by using the mysqlbinlog utility and redirecting the results to a text file for editing. When you’re finished you can pass the edited file to MySQL for processing through the mysql client. To redirect the output from mysqlbinlog, you would enter something like this:

mysqlbinlog /var/log/mysql/bin.123456 \
    > /tmp/mysql_restore.sql

This will create a simple text file in the /tmp directory (you would adjust the path for your server). This accomplished by using the redirect (i.e., the greater-than sign). Incidentally, the backslash on the first line is to indicate that the command is spread over two lines. You can edit the text file created with a text editor like vi or notepad.exe. Don’t use a word processor because it may add binary formatting codes to the file, which will cause problems when you hand the file over to mysql. After you’ve deleted the unwanted SQL statements and saved the restore file, you can then run it through the mysql client like so:

mysql -u root -pmypwd \
   < /tmp/mysql_restore.sql

Notice that in this case, a less-than sign is used to redirect the standard input (i.e., STDIN).

Filed Under: mysql

Some more articles you might also be interested in …

  1. MySQL 8.0 : Persisted Variables
  2. Counting Rows Of A Table In MySQL Server
  3. Examples of mysqldump partial backups
  4. MySQL ‘show processlist’ statement
  5. How to use mysqlsh to execute addInstance in silent mode
  6. Configure MySQL Router to Auto Restart of Failure using systemd
  7. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  8. MySQL : What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged
  9. How to Generate Unique IDs For MySQL Cluster Backups
  10. MySQL: How to Set Account Resource Limits

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright