mysql备份

1. mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf -->
–default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf 1 mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求

2. mysql数据库备份

2.1 数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

备份方案 特点

全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长
增量备份 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据,备份时间短,恢复数据时必须按一定的顺序进行
差异备份 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

2.2 mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

//备份整个数据库(全备)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwl               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use cwl;
Database changed
mysql> show tables;
+----------------+
| Tables_in_cwl |
+----------------+
| wangxiaolan            |
| student        |
+----------------+
2 rows in set (0.00 sec)


[root@wangxiaolan ~]# ls
anaconda-ks.cfg  create  httpd-2.4.37  httpd-2.4.37.tar.bz2  mysql.pam
 [root@wangxiaolan ~]# mysqldump -uroot -p  --all-databases > all-201902211529
Enter password: 
[root@wangxiaolan ~]# ls
all-201902211529  anaconda-ks.cfg  create  httpd-2.4.37  httpd-2.4.37.tar.bz2  mysql.pam
[root@wangxiaolan ~]# vim all-201902211529 

//备份cwl库的wangxiaolan表和student
[root@wangxiaolan ~]# mysqldump -uroot -p cwl wangxiaolan student > table-201902211540
Enter password: 
[root@wangxiaolan ~]# ls
all-201902211529  create        httpd-2.4.37.tar.bz2  table-201902211540

 //备份cwl库      
 [root@wangxiaolan ~]# mysqldump -uroot -p --databases cwl > cwl-201902211543.sql
Enter password: 
[root@wangxiaolan ~]# ls
all-201902211529  create        httpd-2.4.37.tar.bz2   mysql.pam
anaconda-ks.cfg   httpd-2.4.37  cwl-201902211543.sql  table-201902211540

//模拟误删cwl数据库
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwl               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database cwl;
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)

2.3mysql数据恢复

//恢复cwl数据库
[root@wangxiaolan ~]# ls
all-201902211529  create        httpd-2.4.37.tar.bz2   mysql.pam
anaconda-ks.cfg   httpd-2.4.37  cwl-201902211543.sql  table-201902211540
[root@wangxiaolan ~]# mysql -uroot -p < all-201902211529 
Enter password: 
[root@wangxiaolan ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwl               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 


//恢复cwl数据库的wangxiaolan表和student表
mysql> drop table wangxiaolan;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_cwl |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)
mysql> source cwl-201902211543.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_cwl |
+----------------+
| wangxiaolan            |
| student        |
+----------------+
2 rows in set (0.00 sec)

mysql> 


//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwl               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database cwl;
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> 


// 恢复整个数据库
[root@wangxiaolan ~]# mysql -uroot -p < all-201902211529 
Enter password: 
[root@wangxiaolan ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cwl               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

mysql差异备份

//开启mysql服务的二进制日志功能
[root@wangxiaolan-59 ~]# vim /etc/my.cnf
[root@wangxiaolan-59 ~]# tail  /etc/my.cnf
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 1			//服务器的标志符
log-bin = mysql_bin		//开启二进制日志功能

[root@wangxiaolan-59 ~]# service mysqld restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 
//对数据库进行完全备份

mysql> selete * from lizhao.student；
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
+----+-----------+------+

[root@wangxiaolan-59 ~]# mysqldump -uroot -pchenwanli! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221900.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

//添加新内容
mysql> insert into student values(3,‘messi',22),(4,'leo',23); 
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | messi     |   22 |
|  4 | leo       |   23 |
+----+-----------+------+
//修改messi年龄

mysql> update student set age = 30 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> selete * from lizhao.student；
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | messi     |   22 |
|  4 | leo       |   30 |
+----+-----------+------+
//模拟删除lizhao数据库

mysql> drop database lizhao;
Query OK, 2 rows affected (0.19 sec)

Query OK, 3 rows affected (0.31 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
5 rows in set (0.00 sec)

[root@wangxiaolan-59 ~]# mysqladmin -uroot -pchenwanli! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@wangxiaolan-59 ~]# ll /opt/data
总用量 188580
-rw-r-----. 1 mysql mysql       56 2月  20 15:32 auto.cnf
-rw-r-----  1 mysql mysql     4326 2月  23 02:57 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 2月  23 03:12 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  23 03:12 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  20 15:31 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 2月  23 03:02 ibtmp1
drwxr-x---. 2 mysql mysql     4096 2月  21 21:48 mysql
-rw-r-----  1 mysql mysql      364 2月  23 03:14 mysql_bin.000003
-rw-r-----  1 mysql mysql      154 2月  23 03:14 mysql_bin.000004 （新生成的日志文件）
-rw-r-----  1 mysql mysql       38 2月  23 03:14 mysql_bin.index
-rw-r-----  1 mysql mysql        6 2月  23 02:57 mysql.pid
drwxr-x---. 2 mysql mysql     8192 2月  20 15:32 performance_schema
drwxr-x---. 2 mysql mysql     8192 2月  20 15:32 sys
-rw-r-----  1 mysql mysql    49454 2月  23 02:57 wangxiaolan-59.err
-rw-r-----. 1 mysql mysql    38337 2月  22 00:38 wangxiaolan.err
drwxr-x---  2 mysql mysql    12288 2月  23 00:40 zabbix

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lizhao             |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.12 sec)

mysql> selete * from lizhao.student；
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | messi     |   22 |
|  4 | leo       |   23 |
+----+-----------+------+
//只恢复到了没有修改数据前的数据表，查询正在使用的二进制文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//查看日志，找到误删记录在哪

mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.23-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000003 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 219 | Query          |         1 |         317 | drop database lizhao                  |
| mysql_bin.000003 | 317 | Rotate         |         1 |         364 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.01 sec)
//恢复到没有误删之前的数据库

[root@wangxiaolan-59 ~]# mysqlbinlog --stop-position=219 /opt/data/mysql_bin.000002 |mysql -uroot -plizhao123!
mysql: [Warning] Using a password on the command line interface can be insecure.


[root@wangxiaolan-59 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

mysql> use lizhao;
mysql> selete * from lizhao.student；
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | messi     |   22 |
|  4 | leo       |   30 |
+----+-----------+------+