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