MySQL Learning Journey-Data migration-backup-restore

1. Data migration

Import data from/tmp/t3.txt into the T3 table with comma-delimited data

Load data infile '/tmp/t3.txt ' into table T3 fields terminated by ', ';

Export data from a table2 table to a file data.txt, comma delimiter

SELECT * into OUTFILE ' data.txt '

--TERMINATED by ', '

from Table2;

# # # #在mysql命令行内执行? Select to view Help for the Select command

2. Logical Backup

View Mysqldump's help, how to use

Mysqldump--help

Backup command

Mysqldump-uroot-p D1 >/tmp/d1.sql

Restore command, first create database D1

Mysql-uroot-p D1 </tmp/d1.sql

How SQL statements are executed in the Operating system Command window

Mysql-uroot-p d1-e ' show tables; '

3. Physical Backup

Stop service First

CP Data folder

If a problem arises, rename the backup folder to the location where the data is placed

To modify a folder's main genus group as the original genus Group, such as Mysql:mysql

Chown-r Mysql:mysql Data

The restart is no longer a problem, and the data has been restored

4, based on binlog recovery to set a point in time

For example, at 7, a mysqldump backup of the database

Mysqldump-uroot-p D1--master-data=2 > D1-7.sql

After that, two more tables were built in the D1 Library, T4 and T5

But at 9, some people accidentally removed the T4 watch.

The database is now required to revert to the state of T5,T4 when it was first created

Recovery

Where does the log go when I look at the dump backup first?

Master_log_file= ' mysql-bin-0000009 ' master_log_pos=325

Parsing mysql-bin-000009 with Mysqlbinlog

Find time at 325, split the log file

Mysqlbinlog mysql-bin-000009--start-position=325--stop-position=511 > Log1.txt

Check if the logs meet the requirements, use cat log1.txt

Using split log files for restore

MySQL Learning Journey-Data migration-backup-restore