MySQL data mysqldump logical backup method

1. Why back up

1. Prevent data loss. Data is the most important asset of an enterprise, and data loss may cause huge losses. Regular backup of the database can be used as a temporary storage of data to prevent data loss due to unforeseen failures.
2. Disaster recovery. In the event of disasters such as fires and floods, as long as there is a backup of the database, the database can be rebuilt by restoring the backup.
3. Meet compliance requirements. In some industries, there are regulations that require companies to regularly back up their databases to meet data compliance requirements.

2. What you need to pay attention to when backing up MySQL data

Backup content: databases Binlog my.cnf
All backup data should be stored locally outside of the database, and multiple copies are recommended.
Do daily recovery drills (disaster recovery drills) in the test environment. Recovery is more important than backup.
Factors that must be considered during the backup process:
1. Data consistency
2. Availability of the Service

3. MySQL backup types

1. Physical backup

Directly copying database files is suitable for large database environments and is not restricted by storage engines, but it cannot be restored to different MySQL versions. That is to say, the consistency of the MySQL versions of both parties must be ensured when restoring data.
a. Hot backup
Online backup, the database is running. This backup method relies on the log file of the database; it has basically no impact on the application (but the performance will still decrease, so try not to make backups on the main database and do it on the slave database)
b. cold backup
Backing up data files requires stopping the service, which is done when the database is closed.

2. Logical backup

What is backed up is the SQL statements (DDL DML DCL) executed by operations such as table creation, database creation, and insertion. It is suitable for small and medium-sized databases and has relatively low efficiency.

3. The difference between physical and logical backup

Logical backup Physical backup The backup method backs up the database to create tables, databases, and insert SQL statements. Back up the physical files of the database. Advantages: The backup file is relatively small and only backs up the data and structure in the table. The recovery speed is faster. Disadvantages: The recovery speed is slow (needs to rebuild the index. Stored procedures, etc.) backup files are relatively large. Representative tools are mysqldump, ibbackup, and xtrabackup.

4. Introduction to MySQL backup tools

1.ibbackup
Official backup tools, charges, physical backup
?2.xtrabackup
Open source community backup tool, open source and free (old version has problems, the backed up data may have problems), physical backup
?3.mysqldump
Official built-in backup tool, open source, free, logical backup (slow speed)

5. mysqldump logical backup – recommended to be used first

mysqldump is MySQL’s own logical backup tool. Data consistency and service availability can be guaranteed.

#Create a test database, data table, and insert data (those already created do not need to be re-created)
mysql> create database company;

#Create a test table
mysql> \e
    CREATE TABLE company.employee5(
     id int primary key AUTO_INCREMENT not null,
     name varchar(30) not null,
     sex enum('male','female') default 'male' not null,
     hire_date date not null,
     post varchar(50) not null,
     job_description varchar(100),
     office int,
     dep_id int
     )
    -> ;
Query OK, 0 rows affected (0.01 sec)
#Insert data:
mysql> \e
insert into company.employee5(name,sex,hire_date,post,job_description,office,dep_id) values
    ('jack','male','20180202','instructor','teach',501,100),
    ('harry','male','20180202','hr',NULL,502,101),
    ('lili','female','20180206','sale','salecc',503,102),
 ('xiaoguo','male','20180205','sale','',503,102)
  -> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Remote backup syntax:
mysqldump -h server -P 3306 -u username -p password database name > backup file.sql
Local backup syntax:
mysqldump -u username -p password database name > backup file.sql

1. Common backup options

td>

Parameters	Explanation
-A, –all-databases	Back up all databases
-B, –databases	Back up multiple databases
-F, –flush-logs	Flush binlog logs before backup
–default-character-set	Specify which character set to use when exporting data. If the data table does not use the default latin1 character set, then this option must be specified when exporting, otherwise garbled characters will occur after importing the data again.
-d, –no-data	Do not export any data, only export the database table structure
–lock-tables	Lock all tables in the database before backup (note: locking tables also means that data cannot be written to these tables during the backup)
–single-transaction	When backing up, using this parameter can ensure data consistency and service availability (–single-transaction allows mysqldump to execute in a transactional manner , to obtain a data consistent database)
-f, –force	Even if you get an SQL error during a table export, continue

Note
Avoid table locks when using mysqldump to back up your database:
Please be careful when backing up a running database! ! If you must back up while the service is running, you can choose to add the --single-transaction option
This parameter can significantly reduce backup time, does not require table locking, and has little impact on the running database.
In contrast, although --lock-tables can obtain a completely consistent data backup, it requires locking the table, which has a greater impact.
Execution: mysqldump --single-transaction -u root -p123456 dbname > mysql.sql

2. Backup table

Back up the entire table structure and data
Syntax: mysqldump -u root -p'Password library name table name > /db1.t1.bak
Case:

[root@localhost ~]# mkdir /home/back //Create a backup directory
[root@localhost ~]# mysqldump -uroot -p'JiannLt@123' company employee5 > /home/back/company.employee5.bak

Back up multiple tables
Syntax: mysqldump -u root -p'Password library name table name 1 table name 2 > /db1.t1_t2.bak

3. Backup library (key)

Back up a library: It is equivalent to backing up all the tables in this library.
Syntax: mysqldump -u root -p'password' library name > /backup name.bak

[root@localhost ~]# mysqldump -uroot -p'JiannLt@123' company > /home/back/company.bak

Back up multiple specified libraries:
Syntax: mysqldump -u root -p'password' -B library name 1 library name 2 >/backup name.bak

[root@localhost ~]# mysqldump -uroot -p'JiannLt@123@123' -B company testdb > /home/back/more_database.bak

Back up all libraries:
Syntax: mysqldump -u root -p'password -A > /alldb.bak

[root@localhost ~]# mysqldump -uroot -p'JiannLt@123@123' -A > /home/back/allbase.bak
//Go to the directory and check:
[root@localhost ~]# ls /home/back/
allbase.bak company.bak company.employee5.bak more_database.bak

4.Restore database and tables

To ensure data consistency, you should stop the database’s external services and stop the binlog log before restoring the data; because using the binlog log to restore data will also generate a new binlog log.
For the experimental effect, delete the database and table just backed up.

[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'drop database company;'
//Check whether deletion is successful
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'show databases;'
 + -------------------- +
| Database |
 + -------------------- +
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
 + -------------------- +

5. Restore the entire database

//Before restoring data, you must create a library! ! !
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'create database company;'
//Check whether the creation is successful
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'show databases;'
 + -------------------- +
| Database |
 + -------------------- +
| information_schema |
| company |
| mysql |
| performance_schema |
| sys |
| testdb |
 + -------------------- +

//Check whether the binlog status is turned on. If it is turned on, stop it (ON means turned on, OFF means turned off)
mysql> show variables like 'sql_log_bin';
 + --------------- + ------- +
| Variable_name | Value |
 + --------------- + ------- +
| sql_log_bin | ON |
 + --------------- + ------- +
//Stop binlog log
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

//Restore:
[root@localhost ~]# mysql -uroot -p'JiannLt@123' company < /home/back/company.bak
//Check whether the recovery is successful
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'select id,name from company.employee5'
 + ---- + --------- +
| id | name |
 + ---- + --------- +
| 1 | jack |
| 2 | harry |
| 3 | lili |
| 4 | xiaoguo |
 + ---- + --------- +

6. Restore tables individually

//Log in to the database just restored and delete one of the tables
[root@localhost ~]# mysql -uroot -p'JiannLt@123'
mysql> show databases;
mysql> use company;
mysql> show tables;
 + ------------------- +
| Tables_in_company |
 + ------------------- +
| employee5 |
 + ------------------- +
mysql> drop table employee5;

#Start recovery:
//Check whether the binlog status is turned on. If it is turned on, stop it (ON means turned on, OFF means turned off)
mysql> show variables like 'sql_log_bin';
 + --------------- + ------- +
| Variable_name | Value |
 + --------------- + ------- +
| sql_log_bin | ON |
 + --------------- + ------- +
//Stop binlog log
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

1. Recovery method one:
mysql> source /home/back/company.employee5.bak; //Add path and backup file
mysql> select id,name from employee5; //Check whether the recovery is successful
 + ---- + --------- +
| id | name |
 + ---- + --------- +
| 1 | jack |
| 2 | harry |
| 3 | lili |
| 4 | xiaoguo |
 + ---- + --------- +
4 rows in set (0.00 sec)

2. Recovery method two:
[root@localhost ~]# mysql -uroot -p'JiannLt@123' company < /home/back/company.employee5.bak
                                                   Library name Backup file path

7. Backup and restore table structure

The backup does not include the data in the table, only the table structure is backed up.
Backup table structure:
Syntax: mysqldump -uroot -p'password -d database table > dump.sql
Case:

[root@localhost ~]# mysqldump -uroot -p'JiannLt@123' -d company employee5 > /home/back/emp.bak
// -d: --no-data only backs up the table structure

Restore table structure:
Syntax: mysql -u root -p'password' -D library name



//Log in to the database to create a library
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'create database t2;'
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -D t2 < /home/back/emp.bak
// -D specifies the database to be logged in/operated, or it can be omitted
//Check whether the table structure is imported successfully
[root@localhost ~]# mysql -uroot -p'JiannLt@123' -e 'desc t2.employee5;'
 + ------------------ + ----------------------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
 + ------------------ + ----------------------- + ------ + ----- + --------- + ---------------- +
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
 + ------------------ + ----------------------- + ------ + ----- + --------- + ---------------- +

8. Data import and export, no table structure
Table export and import only back up the records in the table, not the table structure. You need to back up the table structure through mysqldump.
 But when restoring, restore the table structure first, and then import the data! ! !
mysql> show variables like "secure_file_priv"; //Query the directory for import and export
 + ------------------ + ------- +
| Variable_name | Value |
 + ------------------ + ------- +
| secure_file_priv | NULL |
 + ------------------ + ------- +

Modify the security file directory:
 1. Create a directory: mkdir path directory
[root@localhost ~]# mkdir /sql

2. Modify permissions
[root@localhost ~]# chown -R mysql:mysql /sql

3. Edit the configuration file:
[root@localhost ~]# vim /etc/my.cnf //Append in [mysqld]
secure_file_priv=/sql

4. After restarting mysql and logging in, you will find that this value has changed.
mysql> show variables like "secure_file_priv";
 + ------------------ + ------- +
| Variable_name | Value |
 + ------------------ + ------- +
| secure_file_priv | /sql/ |
 + ------------------ + ------- +
1 row in set (0.00 sec)


Expand your knowledge:
 secure_file_priv
 This MySQL variable is used to limit the file directory written by SELECT INTO OUTFILE and LOAD DATA.
 Simply put, it is to limit the paths where these statements can be written.
 If this variable is set, these two statements can only write files to this directory; if not set, then they can write files to any directory.
 This variable is mainly for data and MySQL server security considerations.

for example:
 SELECT * FROM table INTO OUTFILE '/tmp/data.txt';
 If secure_file_priv is set to '/var/secure_file', then the above statement cannot be executed and must be modified to: SELECT * FROM table INTO OUTFILE '/var/secure_file/data.txt';
 In other words, statements can only be executed if they are written to the directory specified by secure_file_priv.
 1. Export data, log in to view data
mysql> show databases;
mysql> use company;
mysql> show tables;
mysql> select * from employee5 into outfile '/sql/out-employee5.bak';

[root@localhost ~]# ls /sql/
out-employee5.bak

2. Data import

//Clear the data in the original table first, leaving only the table structure
mysql> use company;
mysql> delete from employee5;
mysql> load data infile '/sql/out-employee5.bak' into table employee5;
mysql> select * from employee5;
 + ---- + --------- + -------- + ------------ + ------------ + ----------------- + -------- + -------- +
| id | name | sex | hire_date | post | job_description | office | dep_id |
 + ---- + --------- + -------- + ------------ + ------------ + ----------------- + -------- + -------- +
| 1 | jack | male | 2018-02-02 | instructor | teach | 501 | 100 |
| 2 | harry | male | 2018-02-02 | hr | NULL | 502 | 101 |
| 3 | lili | female | 2018-02-06 | sale | salecc | 503 | 102 |
| 4 | xiaoguo | male | 2018-02-05 | sale | | 503 | 102 |
 + ---- + --------- + -------- + ------------ + ------------ + ----------------- + -------- + -------- +
4 rows in set (0.00 sec)

Directory