Brief summary of Mysql backup and recovery and tee command usage

Source: Internet
Author: User
Tags mysql backup

Backup Data method:
I. SQL statements.
Locks tables tablename READ; // READ lock
Before trying to LOCK a table, lock tables is not TRANSACTION-safe. It implicitly commits all active transactions and starts a TRANSACTION implicitly (for example, start transaction ),
Therefore, the correct way to use lock tables for transaction TABLES (such as InnoDB) is to set AUTOCOMMIT = 0
Flush tables,
SELECT * into outfile 'data _ bck. SQL 'FIELDS TERMINATED BY', 'FROM tablename;
Unlock tables;

Ii. mysqldump tool.
A table in the full backup database:
Mysqldump-h192.168.4.20-uusername-puserpswd dbname tablename> data_bck. SQL

Completely back up a database:
Mysqldump-h192.168.4.20-uusername-puserpswd dbname tablename> data_bck. SQL
Full backup of all databases:
Mysqldump-h192.168.4.20-uusername-puserpswd -- single-transaction -- all-databases> data_bck. SQL
After fully backing up all databases, stop all previous incremental logs and generate new logs:
Mysqldump-h192.168.4.20-uusername-puserpswd -- single-transaction -- flush-logs -- master-data = 2 -- all-databases> data_bck. SQL
After fully backing up all databases, delete all previous incremental logs and generate new logs:
Mysqldump-h192.168.4.20-uusername-puserpswd -- single-transaction -- flush-logs -- master-data = 2 -- all-databases -- delete-master-logs> data_bck. SQL
Incremental Backup:
Perform a full backup and then update the log:
Mysqladmin-h192.168.4.20-uusername-puserpswd flush-logs

3. copy the data directory directly.
Stop the mysql service or lock the database table, copy the data directory to the backup location, start the mysql service or unlock the database table.

Data Recovery Method:
I. SQL statements.
Load data infile 'data _ bck. SQL 'INTO TABLE tablename FIELDS TERMINATED ',';

Ii. mysql tool.
Full recovery:
Mysql-h192.168.4.20-uusername-puserpswd dbname <data_bck. SQL
Restore incremental logs:
Mysqlbinlog log-bin.000001 log-bin.000008 | mysql

In addition, there is an interesting and practical tips: tee.
It saves all the visual information you operate and output into a file, which can be used to generate table-based files, report production, and simple backup.
The usage is as follows:
Run the following command on the mysql command line:
> Tee E:/heihei. SQL
In this way, a record file is created, and all operations and output results are recorded as they are, just like photos.
> Select * from tablename;
This command and query results will be recorded in this file, which is very interesting. However, it is very careful, and even your wrong commands will be recorded. Don't get jokes. :)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.