1. Physical backup and recovery:
   Physical backup is divided into hot backup and cold backup. Compared with logical backup, physical backup has faster backup and recovery speed. The principle is based on the cp command.
   Cold standby: backup MySQL database shared table space,.frm, stand-alone table space (.ibd), redo log files.

   • Advantages: Simple backup, simple recovery, fast backup
   • Disadvantage: Not so easy to cross-platform, backup files are larger
   • Backup steps for cold backup: Stop MySQL service and backup MySQL data files and log files to backup directory at operating system level
   • Recovery steps for cold backup: Stop MySQL service, restore MySQL data files at the operating system level, and then restart MySQL service, using mysqlbinlog

2. Logical backup:
   a. mysqldump backup:
   Syntax: mysqldump [arguments] > file_name

    mysqldump -uuser -p --all-databases >file_name.sql  #Back up all databases
    mysqldump -uuser -p --databases db1 db2 db3 >file_name.sql  #Backup database db1,db2,db3
    mysqldump -uuser -p --single-transaction test > file_name.sql #Backup test architecture with consistency
    #Consistent backup opens a transaction at the beginning of the backup, so that it can backup without locking the table lock. If it does not add -- single-transaction, the backup will lock the table.
    mysqldump -uuser -p --where='a<100' --single-transaction Nums Nums >file.sql    #Backup Nums with field a less than 100 in Nums
    mysqldump -uuser -p --single-transaction sakila --tab="/var/lib/mysql-files"    #Backup sakila database in csv format to / var/lib/mysql-files directory

    b. select ... into outfile  
        Syntax: select column1, column2... into out file'file_name'where condition from table;
    fields
         Terminated by't': Interval character for each field
             enclosed by'': Represents an inclusion character for a string
         escaped by'\
    lines
        Start by'': Represents a line start symbol
        Terminated by'n': Represents the end of the line symbol
    For example:

select * into outfile '/var/lib/mysql-files/city.txt' fields terminated by '\t' enclosed by '' escaped by '\\' lines starting by '' terminated by '\n' from city limit 10;

Requirement:
        The path of the file must be mysql: MySQL
        This file cannot already exist
 The difference between mysqldump and select... into outfile:
    mysqldump can backup multiple tables at a time and ensure data consistency, while select in out file can backup only one table at a time, and can not guarantee data consistency.

The import of mysqldump:
    source file path + file name
load data infile : 
    Syntax: load data infile'File path + file name'into table_name;
    For example:

set @@foreign_key_checks=0; #Turn off foreign key checking
        load data infile '/var/lib/mysql-files/city.txt' into table  city;
        set @@foreign_key_checks=1; #Open Foreign Key Check
        #stayload data infile The efficiency of importing data can be improved by turning off foreign key checking before

mysqlimport tool:     
    Syntax: mysqlimport-uuser-p [parameter] file_name

mysqlimport -uuser -p --use-threads /var/lib/mysql-files/t.txt /var/lib/mysql-files/city.txt

    The connection and difference between mysqlimport and load data infile:
    Mysqlimport is the interface of load data infile, which consists of one or more load data infiles. That is to say, load data infile can only import one table at a time, but mysqlimport can import one or more tables.
Binary log backup and recovery:
    Backup log steps:
    1.flush logs;  refresh logs
    2. Logs before backup

#How to restore logs:
        mysqlbinlog lzg-Lenovo-G40-70m-bin.000001 |mysql -uuser -p db_name
        #Two logs can be restored at the same time, which is more efficient:
        mysqlbinlog lzg-Lenovo-G40-70m-bin.000001 lzg-Lenovo-G40-70m-bin.000002|mysql -uuser -p db_name

    The parameters of mysqlbinlog are:
    start-position: Start position
    stop-position: End position
    - start-datetime: start time
    stop-datetime: End time
 A little puzzled:
    That is, deleting data from a table can be recovered, but deleting a table can not be recovered.
    Solve jue: Baidu solved it later because binary recovery is divided into complete recovery, point-based recovery and point-based recovery. For the recovery of deleted tables, there are sql statements in the log, so we need to restore points based on time or location points.

    mysqlbinlog --start-position=560 --stop-position=650 lzg-Lenovo-G40-70m-bin.000001 | mysql -uuser -p 
    mysqlbinlog --start-datetime="2017-07-10 08:00:00" --stop-datetime="2017-07-10 09:00:00" lzg-Lenovo-G40-70m-bin.000001 | mysql -uuser -p 

    The use of xtrabackup is documented in the following article

It's cloudy in Hengyang today.