• 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. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  2. How to reset MySQL database root password
  3. MySQL Shell: Using External Python Modules
  4. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  5. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  6. What are Reserved User Accounts in MySQL
  7. How to Create a MySQL Docker Container for Testing
  8. How to take Logical Backups on a MySQL Replication Slave using shell script
  9. MySQL Backup stuck at “Starting to lock all the tables”
  10. Excluding a table or database from MySQL replication

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright