Brief summary of Mysql backup and recovery and the use of Tee command _mysql

Source: Internet
Author: User
Tags flush mysql backup mysql command line

Backup Data method:
One: SQL statements.
LOCKS TABLES tablename read;//read lock
Lock tables are not transaction-safe until you attempt to lock the table, implicitly committing all active transactions, and implicitly starting a transaction (for example, using the start TRANSACTION).
Therefore, the correct way to use lock tables for a transaction table (such as InnoDB) is to set the autocommit=0
FLUSH TABLES,
SELECT * into outfile ' data_bck.sql ' FIELDS terminated by ', ' from TableName;
UNLOCK TABLES;

Two: Mysqldump tools.
To fully back up a table in the database:
MYSQLDUMP-H192.168.4.20-UUSERNAME-PUSERPSWD dbname Tablename>data_bck.sql

To back up a database completely:
MYSQLDUMP-H192.168.4.20-UUSERNAME-PUSERPSWD dbname Tablename>data_bck.sql
Fully back up all databases:
MYSQLDUMP-H192.168.4.20-UUSERNAME-PUSERPSWD--single-transaction--all-databases>data_bck.sql
After you have fully backed up all the databases, deactivate all previous incremental logs and generate a new log:
MYSQLDUMP-H192.168.4.20-UUSERNAME-PUSERPSWD--single-transaction--flush-logs--master-data=2--all-databases> Data_bck.sql
Delete all previous incremental logs and generate new logs after fully backing up all databases:
MYSQLDUMP-H192.168.4.20-UUSERNAME-PUSERPSWD--single-transaction--flush-logs--master-data=2--all-databases-- Delete-master-logs>data_bck.sql
Incremental backups:
Perform a full backup first, and then perform the update log:
MYSQLADMIN-H192.168.4.20-UUSERNAME-PUSERPSWD Flush-logs

Third: Directly copy the Data directory.
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.

Restore Data methods:
One: SQL statements.
LOAD DATA INFILE ' Data_bck.sql ' into the TABLE tablename FIELDS terminated by ', ';

Two: MySQL tools.
Full recovery:
MYSQL-H192.168.4.20-UUSERNAME-PUSERPSWD Dbname<data_bck.sql
To restore an incremental log:
Mysqlbinlog log-bin.000001 log-bin.000008 | Mysql

In addition, there is a very interesting and practical trick: tee.
It saves all visual information you manipulate and output into a single file that can be used to generate tabular files, report making, and simple backups.
The usage is this:
Under the MySQL command line, execute the following command:
>tee E:/heihei.sql
This creates a record file, and then all of our operations and all the output results are recorded as they are, just like photos.
>select * FROM TableName;
So the command and query results will be recorded in this file, it's fun. However, it is very careful, even your wrong orders will be recorded. But don't be laughed at. :)

Related Article

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.