Beginners Guide to MySQL Backup and Recovery

If you are into systems, you would’ve heard about backups. I think in all fields backup is one thing which is of utmost importance. This post touches upon the MySQL backup and recovery procedures. Before we dive into the particulars of MySQL backup and recovery lets take a look at the purpose of taking a backup and basic types of database backups.

Reasons for Backups

Complete system recovery: If a system fails, it is crucial to have a backup of the system so you can restore it. The backup and recovery strategy that you implement is determined by how complete and how current the recovered data needs to be.
Auditing capabilities: For certain systems and associated processes, you might need to audit or analyze the data in an environment separate from the primary production environment. You can use backups to create such a separate environment.
Typical DBA tasks: Use backups when you need to perform typical DBA tasks such as transferring data from one system to another, creating a development server based on a specific state of a production server, or recovering certain portions of the system to a state prior to a user error.

Types Of backups

Hot: These dynamic backups take place while the data is being read and modified with little to no interruption of your ability to interface with or manipulate data. With hot backups, the system remains accessible for operations that read and modify data.
Cold: These backups take place while the data is inaccessible to users so that you cannot read or make any modifications to the data. These offline backups prevent you from performing any activities with the data. These types of backups do not interfere with the performance of the system when it is up and running. However, having to lockout or completely block user access to data for an extended period of time is not acceptable for some applications.
Warm: These backups take place while the data is being read, but in most cases, the data itself cannot be modified while the backup is taking place. This middle-of-the-road backup type has the advantage of not having to completely lockout end users. However, the disadvantage of not being able to modify the data sets while the backup is taking place can make this type of backup not reasonable for certain applications. Not being able to modify data during backups can produce performance issues.
Disk: You can back up data directly to another disk by using processes such as replication or RAID mirroring, or by using external applications such as DRBD. These techniques provide live (or nearly live) backups and a quick means of recovering data.
Binary logs: A binary log records modifications to the data. The binary logs are therefore very useful for restoring those events that have occurred since the last full backup. The advantage to backing up binary logs is that they contain a record of all changes to the data over time, rather than a snapshot of the data. You can take multiple binary log backups in sequential order. Decide on the number of binary log backups to take between backups based on how much data is being modified and how often a full backup is completed. The disadvantage to binary logs is you must restore all sequential binary logs that were created from the last full backup in sequence. In addition, recovery from a system failure can be slow depending on the number of binary logs that must be restored.
Logical/textual backup: You can do a complete data dump by using mysqldump. These data dumps are based on a specific point in time but are the slowest of all the backup copies. The advantage to using mysqldump is that the file that is created is simply a SQL script containing statements that you can run on a MySQL server. The disadvantage is that mysqldump locks tables during the dump, which prevents users from reading or writing to the files during the backup.

Backups with MySQL

MySQL backups can be one of the following:

A logical backup that results in a text file containing SQL statements to reconstruct the database
A physical backup, which is a binary copy of the MySQL database files
A snapshot-based backup
A replication-based backup
An incremental backup (created by flushing the MySQL binary logs)

Logical (Textual) Backups

Logical backups convert databases and tables to SQL statements which are portable. Logical backups require that the MySQL server be running during the backup. It can back up both local and remote MySQL servers and are generally slower than raw (binary) backups. The size of a logical backup file may exceed the size of the database being backed up.

SQL Statements

Logical (or textual) backups dump the contents of the database into text files. These text files contain SQL statements and, as such, are very portable. These SQL statements contain all the information needed to rebuild the MySQL databases and tables. You can use the text file to reload the database on another host, running a different architecture.

Server Must Be Running

The MySQL server must be running when you create logical backups because the server creates the files by reading the structure and contents of the tables being backed up, and then converting the structure and data to SQL statements. Other applications can perform read operations during the logical backup.

Local and Remote Servers

With logical backups, you can back up local and remote MySQL servers. You can perform other types of backup (raw) only on the local MySQL server.

Generally Slower

Logical backups are generally slower than raw backups. This is because the MySQL server must read tables and interpret their contents. It must then translate the table contents to a disk file, or send the statements to a client program, which writes them out. A logical backup is also slower than a raw backup during recovery. This is because the method of recovery is to execute the individual CREATE and INSERT statements to re-create each backed-up table and row.

Logical backups may exceed the size of the database being backed up, because the statements themselves may take up more space than the data they represent. However, because InnoDB stores data in data pages, which can contain free space, the InnoDB data files often take considerably more space on disk than the data requires.

Physical (Raw or Binary) Backups

Physical backups make an exact copy of the database files. You can use standard commands such as tar, cp, cpio, rsync, or xcopy to take physical backup. Physical backup must be restored to the same database engine and can be restored across machine architectures. Physical backups are faster than logical backups and recoveries.

Exact Copy

Raw backups are binary copies of the MySQL database files. These copies preserve the databases in exactly the same format in which MySQL itself stores them on disk. Because they are exact copies of the originals, raw backups are the same size as the original

Raw binary backups are faster than logical backups because the process is a simple file copy, which does not need to know anything about the internal structure of the files. However, if you use a raw backup to transfer databases to another machine with a different architecture, the files must be binary portable. Because a raw backup is an exact representation of the bits in the database files, you must restore them to a MySQL server using the same database engine. When you recover a raw MySQL backup from an InnoDB table, it remains an InnoDB table on the target server.

MySQL Server Shutdown

With binary backup methods, you must make sure that the server does not modify the files while the backup is in progress. This can be accomplished in a variety of ways. One way is to shut down the MySQL server and then take the backup. This has obvious disadvantages. For some storage engines, a better approach is to temporarily lock the database, take the backup, and then unlock the database. You can also minimize the effect to MySQL and applications by using snapshots, replication, or proprietary methods.

Database files must not change during backup. The method to achieve this depends on the storage engine. – For InnoDB: MySQL server shutdown is required. – For MyISAM: Lock tables to allow reads but not changes.

Snapshot-Based Backups

Snapshot-based backups create a point-in-time “copy” of the data. It provides a logically frozen version of the file system from which you can take MySQL backups. Snapshot-based backups greatly reduce the time during which the database and applications are unavailable. A raw backup is typically performed against the snapshot copy.

External Snapshot Capability

Snapshot-based backups use a snapshot capability that is external to MySQL. For example, if your MySQL databases and binary logs exist on an LVM2 logical volume with an appropriate file system, you can create snapshot backups. Snapshot-based backups work best for transactional engines such as InnoDB. You can perform a hot backup of InnoDB tables; for other engines, you can perform a warm backup.

Scalable

Snapshot backups are scalable, because the time needed to perform the snapshot does not increase as the size of the database grows. In fact, the backup window (from the perspective of the application) is almost zero. However, a snapshot-based backup almost always includes a raw backup after the snapshot is taken.

Replication-Based Backups

MySQL replication can also be used for backups. The master is used for the production applications, whereas a slave is used for backup purposes. This eliminates the impact on production applications. The backup of the slave is either logical or raw.

One-Way, Asynchronous Replication

MySQL features support for one-way asynchronous replication, in which one server acts as the master while one or more other servers act as slaves. You can use replication to perform backups by using the replica or slave instead of the master. This has the advantage that the backup operation does not impact the performance of the master server

Disadvantages

This is a more expensive solution, since you must purchase additional hardware and network bandwidth. In addition, the slave’s copy of the database contains a delayed version of the data relative to the master because of the inherent latency of replication.

Binary Logging and Incremental Backups

Changes Made After Backup

A backup is only one of the components you need for data recovery after loss or damage. The other is the binary log, which contains a record of data changes. To recover databases, use the backups to restore them to their state at backup time. After the backup has been restored, apply the contents of the binary log to apply all data changes since the backup was created. Make sure that binary logging is enabled for all of the MySQL servers.

Control binary logging at the session level:

SET SQL_LOG_BIN = {0|1|ON|OFF}

Flush binary logs when taking logical or raw backups. Synchronize binary log to backups. To perform an incremental backup, copy binary logs. Binary logs can be used for fine-granularity restores. You can identify transactions that caused damage and skip them during restore.

SUPER Privilege

You must have the SUPER privilege to set this variable. If you attempt to set this variable without the SUPER privilege, you get the following error:

ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

Backup Tools: Overview

Backups can be made without the use of additional tools or utilities. Examples include - SQL statements that are used to perform logical backups and performing raw backups of a combination of SQL statements (for locking) along with operating system commands (for making the binary copy).

MySQL Enterprise Backup

The MySQL Enterprise Backup product performs hot backup operations for MySQL databases. The product is architected for efficient and reliable backups of tables created by the InnoDB storage engine. For completeness, it can also back up tables from other storage engines.

mysqldump

The mysqldump utility comes with the MySQL distribution. It performs logical backups and works with any database engine. It can be automated with the use of crontab in Linux and UNIX, and with the Windows Task Scheduler in Windows. There are no tracking or reporting tools for mysqldump.

mysqlhotcopy

The mysqlhotcopy utility also comes with the MySQL distribution. It performs a raw backup and is used for only those databases that use the MyISAM or ARCHIVE database engines. The name implies that mysqlhotcopy performs a “hot” backup, meaning that there is no interruption to database availability. However, because the database is locked for reading and cannot be changed during the backup, it is best described as a “warm” backup. There is no reporting or tracking provided with this script.

Third-Party Tools

Oracle commercial and community tools are the primary focus of this lesson. There are commercial and community third-party tools that you can incorporate into your backup strategies.

MySQL Enterprise Backup

MySQL Enterprise Backup facilitates taking backup in 3 modes as outlined below:

Hot Backup

Hot backups are performed while the database is running. This type of backup does not block normal database operations, and it captures even changes that occur while the backup is happening. mysqlbackup is the command-line tool of the MySQL Enterprise Backup product. For InnoDB tables, this tool performs a hot backup operation.

Warm Backup

For non-InnoDB storage engines, MySQL Enterprise Backup performs a warm backup in which the database tables can be read, but the database cannot be modified while the non- InnoDB backup runs.

Single-File Backup

Because the single-file backup can be streamed or piped to another process, such as a tape backup or a command such as scp, you can use this technique to put the backup on another storage device or server without significant storage overhead on the original database server.

MySQL Enterprise Backup

mysqlbackup Raw Files

The InnoDB-related data files that are backed up include:

ibdata* files that represent the system tablespace and possibly the data for some user tables.
.ibd files, which contain data from user tables.
Data extracted from the ib_logfile* files (the redo log information representing changes that occur while the backup is running), which is stored in a new backup file named ibbackup_logfile.

By default, mysqlbackup backs up all files in the data directory. If you specify the –only-known-file-types option, the backup includes only additional files with MySQL recognized extensions.

All files in the data directory to include:

.opt files: Database configuration information
.TRG files: Trigger parameters
.MYD files: MyISAM data files
.MYI files: MyISAM index files
.FRM files: Table data dictionary files

mysqlbackup

You can use mysqlbackup to take an online backup of your InnoDB tables and a snapshot of your MyISAM tables that correspond to the same binlog position as the InnoDB backup. In addition to creating backups, mysqlbackup can pack and unpack backup data, apply to the backup data any changes to InnoDB tables that occurred during the backup operation, and copy data, index, and log files back to their original locations.

Backup Procedure

mysqlbackup opens a connection to the MySQL server to perform the backups.
mysqlbackup then takes an online backup of InnoDB tables. This phase does not disturb normal database processing.
When the mysqlbackup run has almost completed, it executes the SQL command FLUSH TABLES WITH READ LOCK, and then it copies the non-InnoDB files (such as MyISAM tables and .frm files) to the backup directory. If you do not run long SELECT or other queries in the database at this time, and your MyISAM tables are small, the locked phase lasts only a couple of seconds. Otherwise, the whole database, including InnoDB type tables, can be locked until all long-running queries that started before the backup have completed.
mysqlbackup runs to completion and UNLOCKs the tables.

Basic usage of mysqlbackup:

mysqlbackup -u[user] -p[password] --backup_dir=[backup-dir] backup-and-apply-log

Here, backup: Performs the initial phase of a backup backup-and-apply-log: Includes the initial phase of the backup and the second phase, which brings the InnoDB tables in the backup up to date, including any changes made to the data while the backup was running.

Restoring a Backup with mysqlbackup

The restore operation copies the contents of [backup-dir], including InnoDB and MyISAM indexes, and .frm files to their original locations (defined by the [cnf-file] file).

Using the copy-back Option You must shutdown the database server before using mysqlbackup with the copy-back option. Using this option copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required post-processing on them. During the copy-back process, mysqlbackup cannot query its settings from the server, so it reads the standard configuration files for options such as the datadir. If you want to restore to a different server, you can provide a non-standard defaults file with the –defaults-file option.

Basic usage:

mysqlbackup --backup-dir=[backup-dir] copy-back

Here, [backup-dir]: Specifies where the backed up files are stored. copy-back: Tells mysqlbackup to perform a restore operation.

mysqlbackup Single-File Backups

Basic usage:

mysqlbackup -u[user] -p[password] --backup-image=[image-file] --backup_dir=[backup-dir] backup-to-image

Other scenarios: 1. Standard output:

mysqlbackup -u[user] -p[password] --backup-dir=[backup-dir] --backup-image=-backup-to-image > <image-file></image-file>

2. Convert an existing backup directory to a single file:

mysqlbackup -u[user] -p[password] --backup-dir=[backup-dir] --backup-image=[image-file] backup-dir-to-image

Restoring mysqlbackup Single-File Backups

Extract a selection of files:

mysqlbackup -u[user] -p[password]
--backup-image=[image-file]
--backup_dir=[backup-dir]

Other scenarios 1. List contents:

mysqlbackup -u[user] -p[password] --backup-image=[image-file] list-image

2. Convert an existing backup directory to a single file:

mysqlbackup -u[user] -p[password] --backup-image=[image_file]
--src-entry=[file-to-extract]
--dst-entry=[file-to-extract] extract

here, –src-entry: Identifies a file or directory to extract from a single-file backup. –dst-entry: Is used with single-file backups to extract a single file or directory to a user-specified path.

Privileges Required for mysqlbackup

The mysqlbackup client connects to the server with the –-user and –-password options. Specifying login details at the command line is not ideal, so consider including them in the [client] section of the mysqlbackup my.cnf file (or other configuration file). The account the connection uses needs certain privileges, as shown in the slide. The following script sets these permissions for the user backupuser:

GRANT RELOAD ON *.* TO 'backupuser'@'localhost';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backupuser'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'backupuser'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'backupuser'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backupuser'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost'; GRANT SUPER ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;

mysqlhotcopy

mysqlhotcopy is a perl script that can be used to backup MyISAM and ARCHIVE tables. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup.mysqlhotcopy runs on the same machine where the database directories are located and it is UNIX only utility.

Basic usage:

mysqlhotcopy -u[user] -p[password] [db_name] [backup-dir]

Options: –flush-log: Flushes logs after all tables are locked. –record_log_pos = db_name.tbl_name: Records master and slave status in the specified database db_name and table tbl_name.

LOCK TABLES

mysqlhotcopy connects to the local MySQL server and copies the table files. When it has finished the copy operation, it unlocks the tables.

MySQL Server Must Be Running

Run mysqlhotcopy on the server host so that it can copy table files while the table locks are in place. The server must be running so that mysqlhotcopy can connect to the server. Operation of mysqlhotcopy is fast because it copies table files directly rather than backing them up over the network.

Raw InnoDB Backups

Backup procedure:

1. Execute FLUSH TABLES…FOR EXPORT – Provide the names of all tables in the command. 2. Make a copy of each component: – A.frm file for each InnoDB table – A.cfg file for each InnoDB table – Tablespace files — System tablespace and Per-table tablespaces – InnoDB log files – my.cnf file 3. Execute UNLOCK TABLES to release all table locks.

Complete InnoDB backup

A raw backup operation that makes a complete InnoDB backup (a backup of all InnoDB tables) is based on making exact copies of all files that InnoDB uses to manage tablespaces. All InnoDB tables in all databases must be backed up together, because InnoDB maintains some information centrally, in the system tablespace.

The FLUSH TABLES…FOR EXPORT command ensures that all named tables are in a state ready for copying at the file system level. If you are backing up multiple tables at the same time, you must specify the names of all tables at the same time, for example:

FLUSH TABLES City, Country, CountryLanguage FOR EXPORT;

The command creates a .cfg file for each flushed table. This file allows you to import the table on another server that does not already contain the metadata for that table in the shared tablespace. InnoDB locks the tables so that other processes cannot write to them during the export process. To release those locks, issue the UNLOCK TABLES command after you have copied the required files.

Recovery

To recover InnoDB tables by using a raw backup, stop the server, replace all the components of which copies were made during the backup procedure, and restart the server.

Note: InnoDB stores table metadata in the shared tablespace, so you must either copy the shared tablespace and per-table tablespace files as a group (replacing the target shared tablespace) or import exported per-table tablespaces individually by using ALTER TABLE … IMPORT TABLESPACE.

Raw MyISAM and ARCHIVE Backups

Backup procedure:

1. While the server is running, lock the table to be copied:

mysql> USE mysql
mysql> FLUSH TABLES users WITH READ LOCK;

2. Perform a file system copy. 3. Start a new binary log file:

FLUSH LOGS;

4. Release the lock after the file system copy:

UNLOCK TABLES;

Locked Tables

To make a raw backup of a MyISAM or ARCHIVE table, copy the files that MySQL uses to represent the table. For MyISAM, these are the .frm, .MYD, and .MYI files. For ARCHIVE tables, these are the .frm and .ARZ files. During this copy operation, other programs (including the server) must not be using the table. To avoid server interaction problems, stop the server during the copy operation.

Note: Locking tables instead of shutting down the server works on Linux systems. On Windows, file-locking behavior is such that it might not be able to copy table files for tables that are locked by the server. In that case, stop the server before copying table files.

New Binary Log File

To start a new binary log file, use FLUSH LOGS (before UNLOCK TABLES). As an alternative, SHOW MASTER STATUS returns the current binary log file name and position. The new binary log file contains all statements that change data after the backup (and any subsequent binary log files).

Recovery

To recover a MyISAM or ARCHIVE table from a raw backup, stop the server, copy the backup table files into the appropriate database directory, and restart the server

LVM Snapshots

On systems that support LVM (such as Linux), you can create a logical volume to contain MySQL’s data directory. You can create a snapshot of that volume, and the snapshot behaves like an instantaneous copy of the logical volume. LVM uses a mechanism known as “copy-on-write” to create snapshots that initially contain no data. When you read files from a newly created snapshot, LVM reads those files from the original volume. As the original volume changes, LVM copies data to the snapshot immediately before it changes on the original, so that any data that has changed since taking the snapshot is stored in its original form in the snapshot. The result is that when you read files from the snapshot, it delivers the version of data existing at the instant the snapshot was created.

Backup procedure: 1. Take a snapshot of the logical volume containing MySQL’s data directory – Use FLUSH TABLES WITH READ LOCK if you are backing up non-InnoDB tables. 2. Perform a raw backup from the snapshot. 3. Remove the snapshot.

Because a snapshot is almost instantaneous, you can assume that no changes to the underlying data take place during the snapshot. This makes snapshots very useful for backing up InnoDB databases without shutting the server down.

To create a snapshot, use the following syntax:

# lvcreate -s -n [snapshot-name] -L [size] [original-volume]

The option -s instructs lvcreate to create a snapshot, with the other options specifying the new snapshot’s name and size, and the original volume’s location. For example, assuming a volume group VG_MYSQL and a logical volume lv_datadir:

# lvcreate -s -n lv_datadirbackup -L 2G /dev/VG_MYSQL/lv_datadir

The preceding statement creates a snapshot called lv_datadirbackup with a reserved size of 2 GB. If you only need the snapshot for a short while, the reserved size can be much less than the size of the original volume, because the snapshot’s storage contains only those blocks that change in the original. For example, if you are using the snapshot to perform a backup, then it stores only those changes made during the time it takes to perform the backup and drop the snapshot.

You can mount the snapshot as if it were a standard volume. Having mounted it, perform a raw backup from that volume as with any other raw backup (for example, by using tar or cp). As you are backing up from a snapshot, the database cannot change during the backup process. You are sure to get a consistent version of the data files as they existed at the time of the backup, without needing to stop the server.

Over time, the snapshot’s space requirement tends to grow to the size of the original volume. Also, each initial data change to blocks on the original volume causes two data writes to the volume group: the requested change and the copy-on-write to the snapshot. This can affect performance while the snapshot remains. For these reasons, you should remove the snapshot as soon as you have performed the backup. To remove the snapshot created by the preceding statement, use the following statement:

# lvremove VG_MYSQL/lv_datadirbackup

Raw Binary Portability

Binary databases can be copied from one MySQL server to another.

InnoDB – All tablespace and log files for the database can be copied directly. The database directory name must be the same on the source and destination systems.
MyISAM, ARCHIVE – All files for an individual table can be directly copied.
Windows compatibility – The MySQL server internally stores lowercase database and table names on Windows systems. For case-sensitive file systems, use an option file statement: lower_case_table_names=1.

Portability

Binary portability is useful when taking a binary backup made on one machine to use on a second machine that has a different architecture. For example, using a binary backup is one way to copy databases from one MySQL server to another.

InnoDB

For InnoDB, with binary portability you can copy tablespace files directly from a MySQL server on one machine to another server on another machine, and the second server accesses the tablespace.

MyISAM and ARCHIVE

For MyISAM and ARCHIVE, binary portability means that the files can be directly copied for a MyISAM or ARCHIVE table from one MySQL server to another on a different machine, and the second server accesses the table.

mysqldump

mysqldump dumps table contents to files. All databases, specific databases, or specific tables can be backed up my mysqldump. mysqldump allows back up of local or remote servers and it is independent of the storage engine. It is a good backup startegy for small exports but not for full backup solution.

Basic usage:

mysqldump --user=[user] --password=[password] --opt db_name > backup.file

Storage Location For SQL-format dump files that contain CREATE TABLE and INSERT statements for recreating the tables, the server sends the table contents to mysqldump, which writes the files on the client host.

Consistency with mysqldump

To ensuring consistency with mysqldump, below are few options which can be used with mysqldump command.

–master-data option alone: Locks tables during backup and records the binlog position in the backup file.
–master-data and –single-transaction options used together: Tables are not locked; only InnoDB table consistency is guaranteed.
–lock-all-tables: Satisfies consistency by locking tables.
–flush-logs - Starts a new binary log.

mysqldump Output Format Options

1. Drop Options: –add-drop-database adds a DROP DATABASE statement before each CREATE DATABASE statement, while –add-drop-table adds a DROP TABLE statement before each CREATE TABLE statement.

2. Create Options: –no-create-db suppresses the CREATE DATABASE statements, while –no-createinfo suppresses the CREATE TABLE statements. –no-data creates the database and table structures but does not dump the data. –no-tablespaces tells the MySQL server not to write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements to the output. –-quick retrieves single rows from a table, without buffering sets of rows.

3. MySQL Programming Components: –routines dumps stored routines (procedures and functions) from the dumped databases, while –triggers dumps triggers for each dumped table.

4. The –opt option: This is shorthand for the most common options to create an efficient and complete backup file.

Restoring mysqldump Backups

Reload mysqldump Backups

When using the mysql command for reloading mysqldump output, you must provide a name for the restored database if the dump file itself does not provide the name. If you create a dump file by invoking mysqldump with the –database or –all-databases option, you do not need to specify the target database name when reloading from that dump file. In that case, the dump file contains appropriate USE db_name statements.

Reload mysqldump backups with mysql:

mysql --login-path=[login-path]
[database] [ backup_file.sql

Copy from One Database to Another

You can use mysqldump output to restore tables or databases and to copy them. mysql can read from a pipe, so the use of mysqldump and mysql can be combined into a single command that copies tables from one database to another. The pipe technique also can be used to copy databases or tables over the network to another server:

shell> mysqldump -u[user] -p[password] [orig-db] [table] |
mysql --login-path=[login-path] [copy-db]

mysqlimport

To reload the table, change location to the backup directory, process the .sql file by using mysql, and load the .tsv file by using mysqlimport:

shell> cd [backup_dir]
shell> mysql --login-path=[login-path] [database] < table.sql
shell> mysqlimport -u[user] -p[password] [database] table.tsv

If you combine the –tab option with options such as –fields-terminated-by and –fields-enclosed-by, which perform format control, specify the same format-control options with mysqlimport so that it knows how to interpret the data files.

Privileges Required for mysqldump

You must have the following privileges to use mysqldump:

SELECT for dumped tables
SHOW VIEW for dumped views
TRIGGER for dumped triggers
LOCK TABLES (unless you use the –-single-transaction option)
Other options might require extra privileges.

To reload a dump file, you must have the following privileges:

CREATE on each of the dumped objects.
**ALTER **on the database, if the mysqldump output includes statements that change the database collation to preserve character encodings.

Some options require additional privileges. For example, you must have the RELOAD privilege to use the –flush-logs or –master-data options. Similarly, if you create a tabdelimited output with the –tab option, you must have the FILE privilege. If you want to back up stored functions and procedures by using the –routines option, you must have the SELECT privilege on the mysql.proc table.

Backing Up Log and Status Files

Binary Log Files

Binary logs store updates that have been made after the backup was made.

Option Files Used by the Server (my.cnf and my.ini files) These files contain configuration information that must be restored after a crash.

Replication Files

Replication slave servers create a master.info file that contains information needed for connecting to the master server, and a relay-log.info file that indicates the current progress in processing the relay logs.

Replication Slave Data Files

Replication slaves create data files for processing LOAD DATA INFILE statements. These files are located in the directory named by the slave_load_tmpdir system variable, which you can set by starting the server with the –slave-load-tmpdir option. If you do not set slave_load_tmpdir, the value of the tmpdir system variable applies. To safeguard replication slave data, back up the files beginning with SQL_LOAD-.

Replication as an Aid to Backup

If the MySQL server acts as a master in a replication setup, you can use a slave server to make backups instead of backing up the master. By using a slave server for backups, the master is not interrupted and the backup procedure does not add processing load on the master or require additional hard disk space or processing.

Stopping the Slave

Shut down the mysqld process, or issue a STOP SLAVE SQL_THREAD statement to stop the server from processing updates that it receives from the master. In the latter case, you must flush the tables to force pending changes to disk.

Back Up the Slave’s Databases

Make a backup of the slave’s databases. The allowable methods depend on whether the server is stopped or left running. For example, if the server is stopped, you cannot use tools such as mysqldump or mysqlhotcopy that connect to the server.

Start Server

Restart the server if it was stopped. If the server was left running, you can restart the SQL thread by issuing a START SLAVE SQL_THREAD statement.

Processing Binary Log Contents

After you restore the binary backup files or reload the text backup files, finish the recovery operations by reprocessing the data changes that are recorded in the server’s binary logs. To do this, you must determine which logs were written after the backup was made. The contents of these binary logs then need to be converted to text SQL statements with the mysqlbinlog program to process the resulting statements with mysql.

Convert the contents with mysqlbinlog:

mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql

Point-in-Time Recovery

If a given binary log file was in the middle of being written during backup, you must extract from it only the part that was written after the backup, plus all log files written after that. Also, if a table or database is dropped by accident (or if other data corruption occurs), restore the backup by using the incremental activity recorded in the binary logs. To avoid re-executing the problem statement, you can run everything up to that statement by capturing the binary log file only up to that point. To handle partial-file extraction, mysqlbinlog supports options that enable the time to be specified or the log position at which to begin extracting log contents:

–start-datetime option: Specifies the date and time at which to begin extraction, where the option argument is given in DATETIME format. Note that the granularity of –start-datetime is only one second, so it might not be accurate enough to specify the exact position to start with.
–start-position option: Can be used to specify extraction beginning at a given log position
There are also corresponding –stop-datetime and –stop-position options for specifying the point at which to stop extracting log contents.

Restoring partial binlogs example:

mysqlbinlog --start-position=23456 binlog.000004 | mysql

If you are not sure about the time stamp or position in a log file that corresponds to the point at which processing begins, use mysqlbinlog without mysql to display the log contents for examination:

shell> mysqlbinlog file_name | more

Beginners Guide to MySQL Backup and Recovery

Reasons for Backups

Types Of backups

Backups with MySQL

Logical (Textual) Backups

SQL Statements

Server Must Be Running

Local and Remote Servers

Generally Slower

Physical (Raw or Binary) Backups

Exact Copy

MySQL Server Shutdown

Snapshot-Based Backups

External Snapshot Capability

Scalable

Replication-Based Backups

One-Way, Asynchronous Replication

Disadvantages

Binary Logging and Incremental Backups

Changes Made After Backup

SUPER Privilege

Backup Tools: Overview

MySQL Enterprise Backup

mysqldump

mysqlhotcopy

Third-Party Tools

MySQL Enterprise Backup

Hot Backup

Warm Backup

Single-File Backup

MySQL Enterprise Backup

mysqlbackup Raw Files

mysqlbackup

Backup Procedure

Restoring a Backup with mysqlbackup

mysqlbackup Single-File Backups

Restoring mysqlbackup Single-File Backups

Privileges Required for mysqlbackup

mysqlhotcopy

LOCK TABLES

MySQL Server Must Be Running

Raw InnoDB Backups

Complete InnoDB backup

Recovery

Raw MyISAM and ARCHIVE Backups

Locked Tables

New Binary Log File

Recovery

LVM Snapshots

Raw Binary Portability

Portability

InnoDB

MyISAM and ARCHIVE

mysqldump

Consistency with mysqldump

mysqldump Output Format Options

Restoring mysqldump Backups

Reload mysqldump Backups

Copy from One Database to Another

mysqlimport

Privileges Required for mysqldump

Backing Up Log and Status Files

Binary Log Files

Replication Files

Replication Slave Data Files

Replication as an Aid to Backup

Stopping the Slave

Back Up the Slave’s Databases

Start Server

Processing Binary Log Contents

Point-in-Time Recovery

Latest Posts