MySQL backup and restore

MySQL backup and restore

1. User management and authority management

☆ User management

1. Create a MySQL user

Note: In MySQL, the user cannot be described by the user name alone, the host must be added. Such as**[email protected]**

Basic syntax:

mysql> create user '用户名'@'被允许连接的主机名称或主机的IP地址' identified by '用户密码';
mysql> select user,host from mysql.user;

Case: Create a MySQL account, user name: tom, user password: 123

mysql> create user 'tom'@'localhost' identified by '123';
或
mysql> create user 'tom'@'127.0.0.1' identified by '123';

Case: Create a MySQL account (requires opening a remote connection), host IP address: 10.1.1.23, user name: harry, user password: 123

mysql> create user 'harry'@'10.1.1.23' identified by '123';

Test: On the host with IP address 10.1.1.23

# yum install mysql -y
# mysql -h 10.1.1.10 -P 3306 -uharry -p
Enter password:123

选项说明:
10.1.1.10 :MySQL服务器端的IP地址
yum install mysql: on behalf of the MySQL client is installed
yum installs mysql-server: it means the server side of MySQL is installed

Case: Create a MySQL account (requires to open a remote connection), host IP network segment: 10.1.1.0, user name: jack, user password: 123

mysql> create user 'jack'@'10.1.1.%' identified by '123';

Case: Create a MySQL account (requires to open a remote connection), which is required to be open to all hosts, user name: root, user password: 123

mysql> create user 'root'@'%' identified by '123';

2. Delete MySQL user

Basic syntax:

mysql> drop user '用户名'@'主机名称或主机的IP地址';
特别说明:
如果在删除用户时没有指定主机的名称或主机的IP地址,则默认删除这个账号的所有信息。

Case: Delete the tom account

mysql> drop user 'tom'@'localhost';

Case: Deleting jack account

mysql> drop user 'jack'@'10.1.1.%';

Case: Create two harry accounts (localhost/10.1.1.23), and then delete one of them

mysql> create user 'harry'@'localhost' identified by '123';
mysql> create user 'harry'@'10.1.1.23' identified by '123';

mysql> drop user 'harry'@'10.1.1.23';

Extension: Another way to delete MySQL accounts

mysql> delete from mysql.user where user='root' and host='%';
mysql> flush privileges;

3. Modify MySQL user

Special note: MySQL user renaming can usually change two parts, one is the user's name, and the other is the host name or the IP address of the host that is allowed to access.

Basic syntax:

mysql> rename user 旧用户信息 to 新用户信息;

Case: Change the user'root'@'%' to'root'@'10.1.1.%'

mysql> rename user 'root'@'%' to 'root'@'10.1.1.%';

Case: Renamed'tom'@'localhost' to'harry'@'localhost'

mysql> create user 'tom'@'localhost' identified by '123';
mysql> rename user 'tom'@'localhost' to 'harry'@'localhost';
Extension: Use the update statement to update user information
mysql> update mysql.user set user='tom',host='localhost' where user='harry' and host='localhost';
mysql> flush privileges;

☆ Permission management

1. Permission description

USAGE	无权限,只有登录数据库,只可以使用test或test_*数据库
ALL		所有权限

以下权限为指定权限
select/update/delete/super/replication slave/reload...

with grant option 选项表示允许把自己的权限授予其它用户或者从其他用户收回自己的权限
By default, if the with grant option is not specified when assigning permissions, it means that this user cannot issue permissions to other users, but this permission assignment cannot exceed its own permissions.

2. Where to save permissions (understand)

mysql.user				所有mysql用户的账号和密码,以及用户对全库全表权限(*.*)
mysql.db				非mysql库的授权都保存在此(db.*)
mysql.table_priv		某库某表的授权(db.table)
mysql.columns_priv		某库某表某列的授权(db.table.col1)
mysql.procs_priv		某库存储过程的授权

3. Authorize users

Basic syntax:

mysql> grant 权限1,权限2 on 库.表 to 用户@主机
mysql> grant 权限(列1,列2,...) on 库.表 to 用户@主机
Database. Table representation method: *.* represents all data tables in all databases, db_itheima.* represents all data tables in db_itheima database, db_itheima.tb_admin, represents tb_admin table in db_itheima database

Case: Assign the query permission of db_itheima to the tom account

mysql> grant select on db_itheima.* to 'tom'@'localhost';
mysql> flush privileges;

Case: Assign the permission of the db_itheima.tb_student data table to the tom account (requires that only the age field can be changed)

mysql> grant update(age) on db_itheima.tb_student to 'tom'@'localhost';
mysql> flush privileges;

Case: Add a [email protected]% account, and then assign all permissions

mysql> create user 'root'@'%' identified by '123';
mysql> grant all on *.* to 'root'@'%';
mysql> flush privileges;

4. Query user permissions

Query current user permissions

mysql> show grants;

Query other user permissions

mysql> show grants for '用户名称'@'授权的主机名称或IP地址';

5. With grant option

With grant option option function: it means that this account can issue permissions for other users, but the issued permissions cannot exceed its own permissions.

mysql> grant all on *.* to 'amy'@'10.1.1.%' identified by '123' with grant option;
mysql> grant all on *.* to 'harry'@'10.1.1.%' identified by '123'; 

如以上命令所示:
amy拥有下发权限的功能,而harry不具备下发权限的功能。
If the grant does not have the with grant option option during grant authorization, it cannot authorize other users.

6. Use grant to create users

Note: After 5.7Not recommended, Will be deprecated in the future!

Basic syntax:

mysql> grant 权限 on 数据库.数据表 to '新用户名称'@'授权主机名称或IP地址' identified by '用户的密码';

Case: Create a root account, the host is %, all permissions are granted, and the password is 123

mysql> grant all on *.* to 'root'@'%' identified by '123';

7, revoke recovery permission

Basic syntax:

revoke 权限 on 库.表 from 用户;

撤消指定的权限
mysql> revoke update on db01.tt1 from 'tom'@'10.1.1.1';
撤消所有的权限
mysql> revoke select on db01.* from 'tom'@'10.1.1.1';

Case: Recover select permission from tom account

mysql> revoke select on db_itheima.* from 'tom'@'localhost';
mysql> flush privileges;
mysql> show grants for 'tom'@'localhost';

Case: Recover the update permission from the tom account

mysql> revoke update(age) on db_itheima.tb_student from 'tom'@'localhost';
mysql> flush privileges;
mysql> show grants for 'tom'@'localhost';

Two, MySQL backup overview

1. What you need to know about data storage

**Thinking:** What is the difference between backup and redundancy?

Backup: It can prevent data loss due to mechanical failure and human misoperation , such as storing database files in other places.

Redundancy: There are multiple copies of data, but not waiting for backups, which can only prevent data loss caused by mechanical failures , such as active- standby mode, database clusters.

2. What exactly should be backed up

☆ What to backup

database:A collection of physical files; Log file (binary log) + data file + configuration file

① Data file

② Configuration file => my.cnf

③ Log files (mainly binary log files)

☆ MySQL architecture

Extension: MySQL architecture (MySQL => what layers of DBMS software are formed)

Insert picture description here

☆ Storage engine layer (MyISAM and InnoDB engines)

Storage engine layer: Simply put, it is the way data is stored. In MySQL, we can use to show enginesview which engines the current database version supports, common data storage engines: InnoDB, MyISAM, NDB...

Common interview questions: Please briefly describe the difference between MySQL's MyISAM engine and InnoDB engine

① MyISAM engine: good at data query, support full-text index

② InnoDB engine: Supports transactions, row-level locking, and foreign

Insert picture description here


keys, supports transaction processing, row-level locking, and supports foreign keys.

☆ Storage layer (how the data files are stored)

Question: How exactly does the storage engine save data files?

mysql> create database db_itheima default charset=utf8;
Ask the question: Can I just enter the MySQL terminal and create a database directly on the command line? -e

When the database is created, check the /mysql_3306/data folder:

There is also a file in the db_itheima folder db.opt, which is stored in the encoding format of the database.

MyISAM engine

mysql> use db_itheima;
mysql> create table tb_user(id int, name char(1)) engine=myisam default charset=utf8;

After the creation is complete, check the db_itheima directory information and find that 3 files are generated

Insert picture description here
*.frm: frame file, defining the data table structure *.MYI: INDEX index, mainly used to store index files *.MYD: data file

InnoDB engine

mysql> use db_itheima;
mysql> create table tb_user(id int, name char(1)) engine=innodb default charset=utf8;

After the creation is complete, check the db_itheima directory information and find that 2 files are generated

Insert picture description here
*.frm: frame file, which defines the structure of the data table *.ibd: index file + data file

In fact, the InnoDB engine will not only generate the above two files, it will also generate a file in the external data directory (to be precise, it cannot be called a generated file, but should be called a shared file)

Insert picture description here
Therefore, it can be seen that the data backup of the InnoDB engine cannot be achieved simply by copying, and a professional backup tool must be used.

☆ Log files (what logs do we need to know in MySQL)

Log typeInformation written to the log
error error logStore the start, stop or run-time error information of the database (look for ERROR)
binlog binary logAll changes to the databaseOperation (DDL/DML/DCL) does not contain statements such as select or show.
Error command rules and storage directory of error log: /data directory + host name.err

Change the storage location of the error log:

# vim my.cnf
[mysqld]
...
log_error=data数据目录/主机名称.err或mysql.err
Binlog binary log application scenarios:
Used forMaster-slave replicationIn, the master master server sends the change operations in the binary log to the slave slave server, and the slave server performs these changes in the same way as the master server.
Used forData recoveryoperating

How to open binlog binary log?

The default binlog log isshut downYes, it can be opened by modifying the configuration file, as follows:

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=data数据目录/binlog
When we change the my.cnf configuration file, we must remember to restart the MySQL server. service command

3. Factors to be considered during the backup process

  • Must be formulatedDetailed backup plan (strategy)(Backup frequency, time point, period)
  • The backup data should be placed inNon-database localAnd recommendHave multiple copies
  • == Data recovery exercises must be done == (Every time, the backup data is simulated and restored in the test environment to ensure that the data can be restored in time when there is a data disaster.)
  • According to data application occasions and characteristicsChoose the right backup tool.
  • Data consistency
  • Service availability

4. Types of backup

☆ Logical backup

  • SQL statements (DDL DML DCL) executed by operations such as table building, database building, and insertion are backed up .
  • Apply toSmall and medium-sized databases, relatively low efficiency. Generally , it is performed under the premise that the database normally provides services , such as: mysqldump , mydumper, into outfile (export and import of tables), etc.
  • The essence of backup: export the data to be backed up into .sql or .txt files

☆ Physical backup

  • Direct copyDatabase file
  • Apply toLarge databaseThe environment is not limited by the storage engine, but it cannot be restored to a different MySQL version.
  • Generally inThe database is completely shut down or cannot complete the normal provision of servicesUnder the premise of the backup); such as: tar, cp,xtrabackup(The database canProvide services normally), lvm snapshot, rsync, etc.
  • The essence of backup: copy data files + configuration files + log files

☆ Online hot backup (data redundancy, AB replication, master-slave replication)

  • MySQL replication architecture, such as MS|MSS|MMS, etc.
  • Real-time online backup

5. Backup tool

(1) The backup tool in the community edition installation package

① mysqldump (logical backup, onlyFull backup)

​ 1) Both Enterprise Edition and Community Edition include

​ 2) Essentially useSQL statement describes the databaseAnd data and export

​ 3) Lock the table on the MYISAM engine and lock the row on the Innodb engine

​ 4) Not recommended when the amount of data is large

② mysqlhotcopy (physical backup tool)

​ 1) Both Enterprise Edition and Community Edition include

​ 2) A script written by perl, essentially copying data after using the lock table statement

​ 3)onlySupport MYISAM data engine

(2) The backup tool in the enterprise version installation package

mysqlbackup

​ 1) Online backup

​ 2) Incremental backup

​ 3) Partial backup

​ 4) A backup of the consistency state at a specific time

(Iii) Third-party backup tools

① XtraBackup and innobackupex (physical backup)

​ 1) Xtrabackup is a tool for InnoDB data backup, supportOnline hot backup(It does not affect data reading and writing during backup). It is a good alternative to the commercial backup tool InnoDB Hotbackup .

​ 2) Xtrabackup has two main tools: xtrabackup, innobackupex

​ a, xtrabackup can only backupInnoDB and XtraDBTwo kinds of data tables, the myisam type table cannot be backed up.

​ b. Innobackupex is a Perl script that encapsulates Xtrabackup, so it can back up the storage engines of innodb and myisam at the same time, but it needs to add a read lock when processing myisam.

② mydumper (logical backup, backup SQL statement)

Multi-threaded backup tool

https://launchpad.net/mydumper/mydumper-0.9.1.tar.gz 2015-11-06 (last update time)

6. Backup method

  • Full backup (full backup)
  • Incremental backup (incremental backup is based on full backup)
Insert picture description here

Three, MySQL logical backup

##1, mysqldump basic backup

Essence : the exported sql statement file

Advantages : No matter what storage engine, you can use mysqldump to prepare SQL statements

Disadvantages : The speed is slow, and there may be unexpected situations of format incompatibility when importing.Cannot do incremental backup directly.

Provides three levels of backup, table level, library level and full library level

2. Basic syntax of mysqldump

表级别备份
mysqldump [OPTIONS] database [tables]
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

Prepare some data to be backed up:

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table tb_student(
	id int not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('male','female'),
	subject enum('ui','java','yunwei','python'),
	primary key(id)
) engine=innodb default charset=utf8;

mysql> insert into tb_student values (null,'刘备',33,'male','java');
mysql> insert into tb_student values (null,'关羽',32,'male','yunwei');
mysql> insert into tb_student values (null,'张飞',30,'male','python');
mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');
mysql> insert into tb_student values (null,'大乔',18,'female','ui');

3. mysqldump table-level backup and restore

☆ Backup

Case: Back up the tb_student data table in the db_itheima database

# mkdir /tmp/sqlbak
# mysqldump db_itheima tb_student > /tmp/sqlbak/tb_student.sql -p
Enter password:123

☆ Restore

# mysql 数据库名称 < .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> use db_itheima
mysql> source .sql文件的位置

4. mysqldump library-level backup and restore

☆ Backup

Case: Back up the db_itheima database

# mysqldump --databases db_itheima > /tmp/sqlbak/db_itheima.sql -p
Enter password:123

☆ Restore

# mysql < .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> source .sql文件的位置

5. mysqldump full database backup

In MySQL, if you want to use mysqldump for full database-level backup, you must turn on the binary log! ! !

Turn on binary log

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/mysql_3306/data/binlog

# service mysql_3306 restart

Description of mysqldump advanced options:

Common optionsDescription
--Flush-logs, -FRefresh the log (binary log) before starting the backup binlog.000001 => binlog.000002
–Flush-privilegesRefresh authorization table when backup contains mysql database => refresh user and authorization information
--Lock-all-tables, -xMyISAM consistency, service availability (for all tables in all libraries)
--Lock-tables, -lLock table before backup (for the library to be backed up)
–Single-transactionApplicable to InnoDB engine to ensure consistency and service availability
--Master-data=2Indicates that the binary log location and file name are written to the backup file and this line is commented out in the dump file
--Master-data=1Indicates that the binary log location and file name are written to the backup file, and this line is not commented in the dump file

Other descriptions of –master-data parameters:

1) It will be executed during recovery, the default is 1
2) RELOAD privilege is required and the binary file must be opened
3) This option will automatically turn on –lock-all-tables and turn off –lock-tables

☆ Backup

Prerequisite: Binary log must be turned on

# mysqldump --all-databases --master-data --single-transaction > /tmp/sqlbak/all.sql -p
Enter password:123

☆ Restore

# mysql < all.sql -p
Enter password:123

to sum up

  1. The mysqldump tool backs upSQL statement, So the backup does not need to stop the service
  2. Use backup filesrestoreWhen, to ensureThe database is running
  3. Can only achieve the full library, specified library, table levelBackup at a certain moment,itselfCannot backup incrementally
  4. Apply toSmall and mediumdatabase

Fourth, mysqldump + binlog realizes incremental backup

1. The core idea

Figure out one thing: what exactly is an incremental backup?

Answer: ① A full backup is required. ② Continue to add, delete and modify data. ③ When a backup is needed again, there is no need to perform a full backup, only the binlog log file needs to be backed up (because the binlog log records all SQL statements for addition, deletion, and modification operations)

2. Incremental backup experiment steps

The first step: prepare the data first (prerequisite)

Step 2: Turn on the binary, and then do a full backup (full database backup)

Step 3: Continue to add, delete and modify the database

Step 4: Suddenly a hardware failure occurred and the database was lost

Step 5: Restore the data exported from the full backup (incomplete, maybe only 90%)

Step 6: Back up the binary log, according to its information (import the remaining 10% of the data)

carry out

3. Specific practice of incremental backup

Step 1: Prepare the data

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table tb_student(
	id int not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('male','female'),
	subject enum('ui','java','yunwei','python'),
	primary key(id)
) engine=innodb default charset=utf8;

mysql> insert into tb_student values (null,'刘备',33,'male','java');
mysql> insert into tb_student values (null,'关羽',32,'male','yunwei');
mysql> insert into tb_student values (null,'张飞',30,'male','python');
mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');
mysql> insert into tb_student values (null,'大乔',18,'female','ui');

Step 2: Turn on the binary log, restart the service, and then perform a full database backup

# vim my.cnf
[mysqld]
...
server-id=10
log-bin=/mysql_3306/data/binlog

# service mysql_3306 restart
# rm -rf /tmp/sqlbak/*
# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > /tmp/sqlbak/all.sql -p

Step 3: Continue to add, delete and modify the database

mysql> insert into tb_student values (null,'小乔',16,'female','ui');
mysql> delete from tb_student where id = 3;

Step 4: Suddenly a hardware failure occurred and the database was lost

# mysql -e "drop database db_itheima;" -p
Enter password:123

…The story begins (delete library)

Step 5: Mobilize operation and maintenance engineers to start data recovery, and immediately back up the latest binary files

# cp /mysql_3306/data/binlog.000003 空格 /tmp/sqlbak/

Step 6: Perform full database recovery first

# mysql < /tmp/sqlbak/all.sql -p
Enter password:123

Step 7: Restore data to 100% through binlog incremental backup

Learn to read binary log files, you must use professional tools

# mysqlbinlog /tmp/sqlbak/binlog.000003  =>  重点找事故的临界点,如drop database
确认at位置
# mysqlbinlog --start-position=4 --stop-position=740 /tmp/sqlbak/binlog.000003 |mysql -p

Continue to add, delete and modify the database

mysql> insert into tb_student values (null,'小乔',16,'female','ui');
mysql> delete from tb_student where id = 3;

Step 4: Suddenly a hardware failure occurred and the database was lost

# mysql -e "drop database db_itheima;" -p
Enter password:123

…The story begins (delete library)

Step 5: Mobilize operation and maintenance engineers to start data recovery, and immediately back up the latest binary files

# cp /mysql_3306/data/binlog.000003 空格 /tmp/sqlbak/

Step 6: Perform full database recovery first

# mysql < /tmp/sqlbak/all.sql -p
Enter password:123

Step 7: Restore data to 100% through binlog incremental backup

Learn to read binary log files, you must use professional tools

# mysqlbinlog /tmp/sqlbak/binlog.000003  =>  重点找事故的临界点,如drop database
确认at位置
# mysqlbinlog --start-position=4 --stop-position=740 /tmp/sqlbak/binlog.000003 |mysql -p

Recover 100% data here