Shulou(Shulou.com)06/01 Report--

The importance of data backup 1. In the production environment, the security of data is very important. Any loss of data may have serious consequences. 2. The causes of data loss are program errors, computer failures, disk failures, disasters and theft of database backups. From a physical and logical point of view, backup can be divided into:

1. Physical backup: backup of physical files (such as data files, log files, etc.) of the database operating system

Physical backup can be divided into offline backup (cold backup) and online backup (hot backup):

Cold backup: when the database is closed

Hot backup: the database is running, and this backup method depends on the log files of the database

2. Logical backup: backup of database logical components (such as tables and other database objects)

From the point of view of the backup strategy of the database, backup can be divided into:

Full backup: make a full backup of the data each time

Differential backup: back up files that have been modified since the last full backup

Incremental backup: only those in. Files that were modified after the last full or incremental backup will be backed up.

Note: differences and increments complement each other

MySQL full backup

1. Full backup is the backup of the whole database, database structure and file structure.

2. A full backup saves the database at the time when the backup is completed.

3. Full backup is the basis of incremental backup.

(1) the advantages of full backup

● backup and recovery operation is simple and convenient.

(2) the disadvantage of full backup

There are a lot of duplications in ● data.

● takes up a lot of backup space.

● backup and recovery time is long

Mysqldump backup library 1. The backup of MySQL database can be directly packaged by ● in a variety of ways. For example, / usr/local/mysql/data ● uses the special backup tool mysqldump2, the mysqldump command ● MySQL comes with the backup tool, which is quite convenient to backup MySQL. Through this command tool, you can export specified libraries, tables or all libraries to SQL scripts. Data recovery can be carried out when you need to restore 3. Mysqldump command makes a full backup of a single library mysqldump-u user name-p [password] [option] [database name] > / backup path / backup file list library backup example mysqldump-u root-p auth > / backup/auth.sqlmysqldump-u root-p mysql > / bakcup/mysql.sql4, The mysqldump command makes a full backup of multiple libraries mysqldump-u username-p [password] [option]-- databases library name 1 [library name 2]. > / backup path / backup file name Multi-library backup example mysqldump-u root-p-- databases autth mysql > / backup/databases-auth-mysql.sql5, full backup of all libraries mysqldump-u user name-p [password] [option]-- all-databases > / backup path / backup file name all library backup examples mysqldump-u root-p-- opt-- all-databases > / backup/all-data.sqlmysqldump backup Table 1, in the actual production environment There is a maintenance operation for a particular table At this time, mysqldump also plays an important role. 2. Use the operation of mysqldump backup table mysqldump-u username-p [password] [option] database name table name > / backup path / backup file name example mysqldump-u root-p mysql user > / backup/mysql-user.sql3, Example of using mysqldump backup table structure operation mysqldump-u username-p [password] [option]-d database name table name > / backup path / backup file name backup table structure mysqldump-u root-p mysql- d user > / backup/mysql-user.sql restore database 1, SQL backup script exported using the mysqldump command During data recovery, you can use the following methods to import ● source command database mode and run ● mysq | Command Linux mode to run 2. To restore a database using source to log in to the MySQL database ● to perform a source backup sq | path to the script (absolute path) source recovery example MySQL [(none)] > source / backup/all-data.sql3, Use the mysq | command to restore the data mysql-u user name-p [password] / opt/ bak. Txt

Full backup and recovery practice 1, enter the database, create tables, insert table data [root@master2] # mysql-uroot-p # # enter database Enter password: mysql > create database school; # # create database Query OK, 1 row affected (0.01 sec) mysql > use school # # use database Database changedmysql > create table info (# # create tables-> id int (3) not null primary key auto_increment,-> name varchar (10) not null,-> score decimal (4p1) not null); Query OK, 0 rows affected (0.02 sec) mysql > desc info # # View table structure +-+-+ | Field | Type | Null | Key | Default | Extra | + -- + | id | int (3) | NO | PRI | NULL | auto_increment | | name | varchar (10) | NO | | NULL | | score | decimal (4) | NO | | NULL | | +-+ -+ 3 rows in set (0.00 sec) mysql > insert into info (name Score) values ('stu01',88), (' stu02',77) # # insert table data Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from info # # View table content +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.01sec) mysql > select * from info limit 1 # # display only the first row in the table +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | +-+ 1 row in set (0.00 sec) 2 Physical full backup of the database [root@master2 ~] # cd / usr/local/mysql/data/ # # switch to the file db.opt in the data directory of the database [root@master2 data] # lsauto.cnf ibdata1 ib_logfile1 mysql school testib_buffer_pool ib_logfile0 ibtmp1 performance_schema sys [root@master2 data] # cd school/ [root@master2 school] # ls # # Info.frm info.ibd [root@master2 school] # cd. [root@master2 data] # tar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data/ # # compress [root@master2 data] # cd / opt/ [root@master2 opt] # lsmysql-2019-11-26.tar.xz mysql-5.7.20 rh3 in xz format Logical backup of a single database [root@master2 opt] # mysqldump-uroot-p school > / opt/school.sql # # logical backup of a single database Enter password: [root@master2 opt] # lsmysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql [root@master2 opt] # vim school.sql # # View the backup database script. CREATE TABLE `info` (`id` int (3) NOT NULL AUTO_INCREMENT, `name` varchar (10) NOT NULL `score` decimal (4pm 1) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ... = 'class1' > LOCK TABLES `info` DISABLE KEYS * /; INSERT INTO `info` VALUES. 4. Back up multiple databases [root@master2 opt] # mysqldump-uroot-p-- databases school mysql > / opt/db_school_mysql.sql## backup multiple databases Enter password: [root@master2 opt] # lsdb_school_mysql.sql mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql5 Full backup of the database [root@master2 opt] # mysqldump-uroot-p-- opt-- all-databases > / opt/all.sql # # full backup Enter password: [root@master2 opt] # lsall.sql mysql-2019-11-26.tar.xz rhdb_school_mysql.sql mysql-5.7.20 school.sql6 Backup tables in the database [root@master2 opt] # mysqldump-uroot-p school info > / opt/school_info.sql # # backup tables in the database Enter password: [root@master2 opt] # lsall.sql mysql-2019-11-26.tar.xz rh school.sqldb_school_mysql.sql mysql-5.7.20 school_info.sql7 Backup the table structure in the database [root@master2 opt] # mysqldump-uroot-p-d school info > / opt/school_info_desc.sql # # backup the table structure Enter password: [root@master2 opt] # lsall.sql mysql-5.7.20 school_info.sqldb_school_mysql.sql rh school.sqlmysql-2019-11-26.tar.xz school_info_desc.sql8 Restore database based on script [root@master2 opt] # mysql-uroot-p # # enter database Enter password: mysql > show databases # # View database +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test | +- -+ 6 rows in set (0.00 sec) mysql > use school # # use database Database changedmysql > show tables; # # to view the table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > drop table info; # # Delete table Query OK, 0 rows affected (0.01 sec) mysql > show tables # View table Empty set (0.00 sec) mysql > source / opt/school.sql # # restore database script file mysql > show tables # # View table +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) 9, restore database based on external MySQL command mysql > drop table info; # # Delete table Query OK, 0 rows affected (0.01 sec) mysql > show tables # # View table Empty set (0.00 sec) mysql > quit # # exit Bye [root@master2 opt] # mysql-uroot-p123123 school

< /opt/school.sql ##利用mysql命令进行恢复mysql: [Warning] Using a password on the command line interface can be insecure.[root@master2 opt]# mysql -uroot -p123123 ##进入数据库mysql: [Warning] Using a password on the command line interface can be insecure.mysql>

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.