Mysql database backup (logical backup)

Prepare three test forms

Tablecompany.employee

create database company;

use company;

create table employee(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int);

insert into employee(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);

select * from employee;

Tablecompany.department

create table department(
dept_id int,
dept_name varchar(100)
);

insert into department values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');

select * from department;

Table school.student

create database school;

use school;

create table student(
id int,
name varchar(30),
datevarchar(10)
);

insert into student values
(1,'jack','Monday'),
(2,'tom','Tuesday'),
(3,'alice','Wednesday');

select * from student;

Logical backup

What is backed up is the SQL statements executed by operations such as table creation, database creation, and insertion.

Regardless of the storage engine, you can use mysqldump to prepare SQL statements.

The speed is slow, format incompatibility may occur during import, and incremental backup and cumulative incremental backup cannot be performed.

1.1 Common backup options

-A, –all-databases Back up all databases

-B, –databases bbs test mysql Back up multiple databases

–no-data, -d Do not export any data, only export the database table structure

1.2 Backup table

Note: Operate from Linux command line

Create backup directory

mkdir /opt/back

Back up a single table

mysqldump -uroot -p'Linyunxi@123' company employee > /opt/back/company.employee.back

Back up multiple tables

mysqldump -uroot -p'Linyunxi@123' company employee department > /opt/back/company.emp_dep.back

1.3 Backup library

Back up a single library

mysqldump -uroot -p'Linyunxi@123' company > /opt/back/company.back

Back up multiple libraries

mysqldump -uroot -p'Linyunxi@123' -B company school > /opt/back/company_school.back

Back up all libraries

mysqldump -uroot -p'Linyunxi@123' -A > /opt/back/allbase.back

1.4 Delete databases and tables

To ensure data consistency, the external services of the database should be stopped before data recovery, and the binlog log should be stopped because binlog logs will also be generated when binlog uses binlog logs to restore data

Delete the company and school databases for experimental results

show databases;

drop database company;

drop database school;

show databases;

1.5 Recovery Library

Log in to mysql to create a library

create database company;

Restore from Linux command line

mysql -uroot -p'Linyunxi@123' company < /opt/back/company.back

Log in to mysql to view

1.6 Recovery table

Log in to the database you just restored and delete one of the tables.

drop table employee;

show tables;

Start recovery

Method 1 (sql statement recovery)

source /opt/back/company.employee.back

show tables;

Method 2 (Linux command line recovery)

mysql -uroot -p'Linyunxi@123' company < company.employee.back

1.7 Backup and restore table structure

Backup table structure

mysqldump -uroot -p'Linyunxi@123' -d company employee > /opt/back/emp.back

Log in to the database to create a library

create database t1;

Restore table structure

mysql -uroot -p'Linyunxi@123' -D t1 < /opt/back/emp.back

Log in to the database to view

1.8 Data import and export (no table structure)

Exporting and importing tables only backs up the records in the table and does not back up the table structure. The table structure needs to be backed up through mysqldump. When restoring, restore the table structure first and then import the data

Query the imported and exported directories

show variables like "secure_file_priv";

Modify security file directory

①Create a directory

mkdir /sql

②Modify permissions

chown mysql.mysql /sql

③Edit configuration file

vim /etc/my.cnf

Add the following content to [mysqld]:

secure_file_priv=/sql

④Restart mysql

systemctl restart mysqld

export data

select * from employee;

select * from employee into outfile '/sql/test.emp.back' fields terminated by ',' lines terminated by '\\
';

Explanation:

fields terminated by ‘,’ fields are separated by commas

lines terminated by ‘\\
‘

Import Data

delete from employee;

load data local infile "/sql/test.emp.back" into table employee fields terminated by ',' lines terminated by '\\
';

Note: If you import data into another table, you need to create this table and create the corresponding table structure.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 77208 people are learning the system