Full MySQL--, incremental backup and recovery (practical article! )

First, full backup and recovery 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. Backup 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>