Archives for January 2021

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 \ > … [Read more...] about how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)

Sometimes you may need to restore a specific database, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only a specific database. Using the --one-database option allows you to restore a single database from a backup of multiple databases. In order to restore a single database, use the --one-database or -o option when replaying the backup: mysql -uroot -p[pwd] --one-database [db1] < … [Read more...] about mysqldump – How to Restore a Specific Database From a Backup of All Databases

Depending on whether you want a subset of databases or tables within a single database, there are various options to create the backup. Selecting the Databases and Tables to Include In the Backup In general the following is included in the backup: The CREATE TABLE statements for each table included in the backup INSERT statements for the data Triggers on the included tables If more than one database is backed up a CREATE DATABASE and a USE are included; but not for single database … [Read more...] about Examples of mysqldump partial backups

Question: How to restore a specific table from a backup dump file containing all databases and tables? Sometimes you may need to restore specific tables, but the only available backup contains all databases. Restoring everything from a backup may not be an option. Instead, you will need the ability to restore only specific databases or tables. The following procedures can be used to effectively filter the backup as it is applied: 1. The first step is to create a new user account, … [Read more...] about How to Restore a Specific Database or Table (MySQL)