preface

Every time the data is backed up completely, that is, the whole database, database structure and file structure are backed up. What is saved is the database at the time when the backup is completed. It is the basis of differential backup and incremental backup. The backup and recovery operations of full backup are very simple and convenient, but the data has a lot of duplication and will occupy a lot of disk space, and the backup time is also very long
Every time a full backup is performed, the backup file will occupy a huge space and have a large amount of duplicate data. When restoring, you can directly use the full backup file

1, Introduction to MySQL full backup

• It is a backup of the entire database, database structure and file structure

• Save the database at the completion time of backup

• It is the basis of differential backup and incremental backup

• advantage:
  Backup and recovery operations are simple and convenient

• Disadvantages:
  There is a lot of duplication in the data
  Takes up a lot of backup space
  Long backup and recovery time

2, Train of thought

• Close MySQL database
• Use the tar command to package the database folder directly
• Simply replace the existing MySQL directory

3, Environmental preparation

mysql> use test;
mysql> create table if not exists info1 ( id int(4) not null auto_increment, name varchar(10) not null, age char(10) not nul
l, hobby varchar(50), primary key (id));

mysql> insert into info1 values(1,'user1',20,'running');
mysql> insert into info1 values(2,'user2',30,'singing');

mysql> select * from info1;
+----+-------+-----+---------+
| id | name  | age | hobby   |
+----+-------+-----+---------+
|  1 | user1 | 20  | running |
|  2 | user2 | 30  | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

4, Physical backup and recovery (cold standby)

mysql> select * from info1;		//View original data
+----+-------+-----+---------+
| id | name  | age | hobby   |
+----+-------+-----+---------+
|  1 | user1 | 20  | running |
|  2 | user2 | 30  | singing |
+----+-------+-----+---------+
2 rows in set (0.00 sec)

[root@c7-2 ~]#systemctl stop mysqld 		// Stop mysql
[root@c7-2 ~]#yum install -y xz 			// Install XZ
#Compressed data backup
[root@c7-2 ~]#tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

##Simulated fault
[root@c7-2 ~]#cd /usr/local/mysql/
[root@c7-2 /usr/local/mysql]#rm -rf data/ 		// Delete data directory

[root@c7-2 ~]#systemctl start mysqld
[root@c7-2 ~]#mysql -uroot -p123456 			// You can't log in again
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


##Decompress recovery data
[root@c7-2 ~]#systemctl stop mysqld
[root@c7-2 ~]#tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data/
[root@c7-2 ~]#cd /usr/local/mysql/data
[root@c7-2 ~]#mv /usr/local/mysql/data/* ./

5, mysqldump backup and recovery (warm backup)

• MySQL has its own backup tool, which can facilitate the backup of MySQL
• You can export the specified libraries and tables as SQL scripts
• Use the command mysq| to import backed up data
• Cold backup is packaged with tar. You need to shut down the database first because the database is still writing continuously. If you don't shut down, you will lose data

1. Fully back up one or more complete libraries (including all tables therein)

###Format:
mysqldump -u root -p[password] --databases Library name 1 [Library name 2] ... > /Backup path/Backup file name.sql    #The exported is the database script file

#####Example:
mysqldump -u root -p --databases zone > /opt/zone.sql       #Back up a zone Library
mysqldump -u root -p --databases mysql zone > /opt/mysql-zone.sql    #Backup mysql and zone Libraries 

2. Fully back up all databases in the MySQL server

####Format:
mysqldump -u root -p[password] --all-databases > /Backup path/Backup file name.sql

example:
mysqldump -u root -p --all-databases > /opt/all.sql

3. Fully back up some tables in the specified library

mysqldump -u root -p[password] Library name [Table name 1] [Table name 2] ... > /Backup path/Backup file name.sql
 example:
mysqldump -u root -p [-d] zone info1 info2 > /opt/zone_info1.sql
#Use the "- d" option to explain that only the table structure of the database is saved 
#If the "- d" option is not used, the table data will also be backed up
#As a table structure template

4. View backup files

grep -v "^--" /opt/zone_info1.sql | grep -v "^/" | grep -v "^$"

6, Full recovery

• Method 1: restore the database using source
  1. Log in to MySQL database
  2. Execute the path of source backup sql script

###Simulated fault
use zone;
delete from dsj; 

###recovery
source /opt/zone.sql

• Method 2: use the MySQL command without logging into the MySQL database
  Use - e to delete the data table and execute the path of mysql backup sql script

[root@c7-2 ~]#mysqldump -uroot -p123456 zone > /opt/zone.sql
[root@c7-2 ~]#mysql -uroot -p123456 -e 'drop table zone.dsj;'
[root@c7-2 ~]#mysql -uroot -p123456 -e 'show tables from zone;'
[root@c7-2 ~]#mysql -uroot -p123456 zone < /opt/zone.sql 
[root@c7-2 ~]#mysql -uroot -p123456 -e 'show tables from zone;'

summary

• Cold backup (offline backup): it is performed when the database is shut down (tar)
• Warm backup: perform backup operation (mysqldump) in the state of database locking table (not writable but readable)