MySQL Backup and recovery

Database Common backup scheme

1. Full-scale backup
Refers to a full copy of all data or applications at a given point in time
Advantages: Fast Data recovery
Cons: Long backup times

2. Incremental backup
means that after a full backup or a previous incremental backup, each subsequent backup needs to back up only the files that were added and/or deleted compared to the previous one. This means that the object of the first incremental backup is the addition and modification of the file that is produced after the full preparation; The object of the second incremental backup is the addition and modification of the file resulting from the first incremental backup.
Advantages: No duplication of backup data, short backup time;
Cons: The data must be recovered in a certain order
Differential backup
Backs up all files that have changed since the last full backup, and for which additional or modified files are backed up. When recovering, we only need to recover the first full-amount backup and the last differential backup.

Install MySQL in binary format

‘提前将MySQL软件包下载到本地： https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz‘[[email protected] ~]# cd /usr/src/[[email protected] src]# lsdebug  kernels

Passed Xftp to/usr/src
1:

2:

‘创建用户和组‘[[email protected] src]# groupadd -r mysql[[email protected] src]# useradd -M -s /sbin/nologin -g mysql mysql

' Unzip software to/usr/local ' [[email protected] src]# tar XF mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz-c/usr/local/[[   Email protected] src]# ls/usr/local/bin libexecetc mysql-5.7.22-linux-glibc2.12-x86_64games sbininclude Sharelib srclib64 ' will unzip the software into a soft link ' [[[email protected] src]# cd/usr/local/[[email protected] local]# ln-sv m Ysql-5.7.22-linux-glibc2.12-x86_64/mysql ' MySQL ', ' mysql-5.7.22-linux-glibc2.12-x86_64/' [[email  Protected] local]# lltotal 0drwxr-xr-x. 2 root root 6 Nov 5 bindrwxr-xr-x. 2 root root 6 Nov 5 etcdrwxr-xr-x. 2 root root 6 Nov 5 gamesdrwxr-xr-x. 2 root root 6 Nov 5 includedrwxr-xr-x. 2 root root 6 Nov 5 libdrwxr-xr-x. 2 root root 6 Nov 5 lib64drwxr-xr-x. 2 root root 6 Nov 5 libexeclrwxrwxrwx. 1 root root 17:27 mysql---mysql-5.7.22-linux-glibc2.12-x86_64/drwxr-xr-x. 9 root root 129 17:22 mysql-5.7.22-linux-glibc2.12-x86_64drwxr-xr-x. 2 root root  6 Nov 5 sbindrwxr-xr-x. 5 root root 14:26 sharedrwxr-xr-x. 2 root root 6 Nov 5 src

‘修改目录/usr/local/mysql的属主属组‘[[email protected] local]# chown -R mysql.mysql /usr/local/mysql[[email protected] local]# ll /usr/local/mysql -dlrwxrwxrwx. 1 mysql mysql 36 Aug 19 17:27 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

‘添加环境变量‘[[email protected] local]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh[[email protected] local]# cd[[email protected] ~]# . /etc/profile.d/mysql.sh [[email protected] ~]# echo $PATH/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

‘建立数据存放目录‘[[email protected] ~]# mkdir /opt/data[[email protected] ~]# chown -R mysql.mysql /opt/data/[[email protected] ~]# ll /opt/total 0drwxr-xr-x. 2 mysql mysql 6 Aug 19 17:34 data

  ' Initialize database ' [[[email protected] ~]#/usr/local/mysql/bin/mysqld--initialize--user=mysql--datadir=/opt /data/2018-08-19t09:35:42.456244z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-08-19t09 : 35:45.297165z 0 [Warning] innodb:new log files created, lsn=457902018-08-19t09:35:45.860507z 0 [Warning] Innodb:creatin G FOREIGN KEY constraint system tables.2018-08-19t09:35:46.031406z 0 [Warning] No existing UUID have been found, so we assu Me that's the first time that this server has been started. Generating a new uuid:3fc6ce6b-a393-11e8-ac64-000c29989243.2018-08-19t09:35:46.033662z 0 [Warning] Gtid table is not rea Dy to be used. Table ' mysql.gtid_executed ' cannot be opened.2018-08-19t09:35:46.043424z 1 [Note] A temporary password are generated for ' [ Email protected]: GP0CT) 0Rkygg This is the password, random password '  

  ' build configuration file ' [[[email protected] ~]# cat >/etc/my.cnf <<EOF> [mysqld]> basedir =/usr/local/ mysql> DataDir =/opt/data> Socket =/tmp/mysql.sock> Port = 3306> Pid-file =/opt/data/mysql.pid> user = m Ysql> skip-name-resolve> eof[[email protected] ~]# cat/etc/my.cnf ' mysql configuration file for/etc/my.cnf ' [mysqld] Basedir =/usr/local/mysql ' Specify the installation path for mysql ' datadir =/opt/data ' Specify the data storage path for mysql ' socket =/tmp/mysql.sock ' designation Socket file Location ' port = 3306 ' Setting the listener port ' Pid-file =/opt/data/mysql.pid ' Specifies the process ID file storage path ' user = MySQL ' specifies what the user's identity for MySQL service ' skip-n Ame-resolve ' disable MySQL for DNS resolution of external connections, and Use this option to eliminate the time for DNS resolution for MySQL. If this option is turned on, all remote host connection authorizations must use the IP address method otherwise MySQL will not be able to handle connection requests '  
 properly

  ' Configure service startup script ' [[[email protected] ~]# cp-a/usr/local/mysql/support-files/mysql.server/etc/init.d/ Mysqld[[email protected] ~]# Sed-ri ' s#^ (basedir=). *#\1/usr/local/mysql#g '/etc/init.d/mysqld [[email  Protected] ~]# Sed-ri ' s#^ (datadir=). *#\1/opt/data#g '/etc/init.d/mysqld  

 ' Start MySQL ' [[[email protected] ~]#/etc/init.d/mysqld startstarting mysql.logging to '/opt/data/yxr.err '. success! [[email protected] ~]# ps-ef|grep mysqlroot 15787 1 0 17:57 pts/2 00:00:00/bin/sh/usr/local/mysql/bi N/mysqld_safe--datadir=/opt/data--pid-file=/opt/data/mysql.pidmysql 15965 15787 4 17:57 pts/2 00:00:00/usr/loc Al/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/opt/data--plugin-dir=/usr/local/mysql/lib/plugin--user =mysql--log-error=yxr.err--pid-file=/opt/data/mysql.pid--socket=/tmp/mysql.sock--port=3306root 15997 1669 0 17 : Pts/2 00:00:00 grep--color=auto mysql[[email protected] ~]# ss-antlstate recv-q send-q Local Address:P ORT Peer address:port LISTEN 0 *:22 *:* LIS TEN 0 127.0.0.1:25 *:* LISTEN 0 128::: 22 :::* 

‘修改密码，先使用临时密码登录‘[[email protected] ~]# /usr/local/mysql/bin/mysql -uroot-pERROR 1045 (28000): Access denied for user ‘root-p‘@‘localhost‘ (using password: NO)[[email protected] ~]# /usr/local/mysql/bin/mysql -uroot Enter password: Welcome to the MySQL monitor.  

Create a database with your name, and create a table student that contains three fields (Id,name,age)

' Enter MySQL ' [[[email protected] ~]#/usr/local/mysql/bin/mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 7Server version:5.7.22 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle and /or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement. ' See what databases the current instance has ' mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS |+--------------------+4 rows in Set (0.00 sec) ' Create a database Yaoxiaorong ' mysql> the creation of databases Yaoxiaorong; Query OK, 1 row Affected (0.00 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema || SYS | | Yaoxiaorong |+--------------------+5 rows in Set (0.00 sec) ' Enter Yaoxiaorong database ' mysql> use Yaoxiaorong;database cha Nged ' creates table student ' mysql> ' in database Yaoxiaorong ' CREATE TABLE student (ID int not null,name varchar (+) not null,age tinyint); Query OK, 0 rows affected (0.03 sec) ' created table in database Yaoxiaorong Rudy ' mysql> CREATE TABLE Ruby (id int not null,name varcharchar ( ) not null,age tinyint); Query OK, 0 rows affected (0.03 sec) ' View what tables are in the current database ' mysql> show tables;+-----------------------+| Tables_in_yaoxiaorong |+-----------------------+| Ruby | | Student |+-----------------------+2 rows in Set (0.00 sec) ' View table structure ' mysql> desc yaoxiaorong.student;+------- +--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| ID | Int (11) |     NO | |       NULL | || name | varchar (100) |     NO | |       NULL | || Age | Tinyint (4) | YES | |       NULL | |+-------+--------------+------+-----+---------+-------+3 rows in Set (0.05 sec)

Inserting data into a newly created student table (with INSERT statement)

mysql> INSERT INTO student(id,name,age) VALUE (1,‘tom‘,20);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO student (id,name,age) VALUE(2,‘jerry‘,23),(3,‘wangqing‘,25),(4,‘sean‘,28),(5,‘zhangshan‘,26),(6,‘zhangshan‘,20),(7,‘lisi‘,NULL),(8,‘chenshou‘,10),(9,‘wangwu‘,3),(10,‘qiuyi‘,15),(11,‘qiuxiaotian‘,20);Query OK, 10 rows affected (0.00 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> select * from student;+----+-------------+------+| id | name        | age  |+----+-------------+------+|  1 | tom         |   20 ||  2 | jerry       |   23 ||  3 | wangqing    |   25 ||  4 | sean        |   28 ||  5 | zhangshan   |   26 ||  6 | zhangshan   |   20 ||  7 | lisi        | NULL ||  8 | chenshou    |   10 ||  9 | wangwu      |    3 || 10 | qiuyi       |   15 || 11 | qiuxiaotian |   20 |+----+-------------+------+11 rows in set (0.01 sec)

‘修改lisi的年龄为50‘mysql> update student set age = 50 where name = ‘lisi‘;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from student where name = ‘lisi‘;+----+------+------+| id | name | age  |+----+------+------+|  7 | lisi |   50 |+----+------+------+1 row in set (0.00 sec)

Sort in descending order of the age field

mysql> select * from student order by age desc; +----+-------------+------+| id | name        | age  |+----+-------------+------+|  7 | lisi        |   50 ||  4 | sean        |   28 ||  5 | zhangshan   |   26 ||  3 | wangqing    |   25 ||  2 | jerry       |   23 ||  1 | tom         |   20 ||  6 | zhangshan   |   20 || 11 | qiuxiaotian |   20 || 10 | qiuyi       |   15 ||  8 | chenshou    |   10 ||  9 | wangwu      |    3 |+----+-------------+------+11 rows in set (0.01 sec)

Backing up the entire database (fully prepared)

[[email protected] ~]# mysqldump -uroot -p --all-databases > all-201808191905.sqlEnter password: [[email protected] ~]# lsall-201808191905.sql  anaconda-ks.cfg

Backing up student tables and Ruby tables for the Yaoxiaorong library

[[email protected] ~]# mysqldump -uroot -p yaoxiaorong student Ruby > table-201808191909.sqlEnter password: [[email protected] ~]# lsall-201808191905.sql  table-201808191909.sqlanaconda-ks.cfg

Backing Up the Yaoxiaorong library

[[email protected] ~]# mysqldump -uroot -p --databases yaoxiaorong > yxr-201808191914.sqlEnter password: [[email protected] ~]# lsall-201808191905.sql  table-201808191909.sqlanaconda-ks.cfg       yxr-201808191914.sql

Simulated mis-deletion of yaoxiaorong database

[[email protected] ~]#/usr/local/mysql/bin/mysql-uroot-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 14Server version:5.7.22 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS | | Yaoxiaorong |+--------------------+5 rows in Set (0.01 sec) mysql> drop database Yaoxiaorong; Query OK, 2 rows affected (0.04 sec) mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | |              Sys  |+--------------------+4 rows in Set (0.00 sec) 

Recover Yaoxiaorong Library

[[email protected] ~]# mysql -uroot -p < all-201808191905Enter password: [[email protected] ~]# mysql -uroot -p -e‘show databases;‘Enter password: +--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || yaoxiaorong        |+--------------------+

' Recover Yaoxiaorong database student table and ruby table ' [[[email protected] ~]# mysql-uroot-p;  Enter Password:welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 18Server version:5.7.22 mysql Community Server (GPL) Copyright (c) #, 2018, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS | | Yaoxiaorong |+--------------------+5 rows in Set (0.01 sec) mysql> use Yaoxiaorong; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> Source Table-201808191909.sqlquery OK, 0 rows affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) query OK, 0 ro WS Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0  . xx sec) query OK, 0 rows affected, 1 warning (0.01 sec) query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.02  SEC) query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.05 sec) query OK, 0 Rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, rows AFFEC Ted (0.01 sec) records:11 duplicates:0 warnings:0query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.01 s  EC) Query OK, 0 rows affected (0.01 sec) query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) query OK, 0  Rows affected (0.02 sec) query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected, 1 Warning (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows AF Fected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) Query OK, 0 rows affected (0.00 S EC) mysql> Show tables;+-----------------------+| Tables_in_yaoxiaorong |+-----------------------+| Ruby | | Student |+-----------------------+2 rows in Set (0.00 sec)

simulate deleting an entire database

Mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS | | Yaoxiaorong |+--------------------+5 rows in Set (0.01 sec) mysql> drop database Yaoxiaorong; Query OK, 2 rows affected (0.01 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | SYS |+--------------------+4 rows in Set (0.00 sec) mysql> Exitbye ' Recover entire database ' [[email protected] ~]# ls All-201808191905.sql table-201808191909.sqlanaconda-ks.cfg yxr-201808191914.sql[[email protected] ~]# mysql-u Root-p <all-201808191905.sql Enter Password: [[email protected] ~]# mysql-uroot-p-e ' show databases; ' Enter Password: +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | |             Sys   || Yaoxiaorong |+--------------------+

MySQL backup and restore