Brief summary of Mysql backup and recovery and tee command usage

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. :)