mysql如何备份和恢复
为什么需要数据备份
数据库的日志
数据库日志类型和作用
mysql数据库的日志默认保存位置在/usr/local/mysql/data
redo 重做日志
errorlog 错误日志
slow query log 慢查询日志
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
#skip-grant-tables
#default-storage-engine=INNODB
s1ow_query_log=ON //慢查询日志默认关闭,这里开启
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log //指定日志路径和文件名
long_query_time=5 //设置超过5s就被记录到日志中,默认10s
undo 回滚日志
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
#skip-grant-tables
#default-storage-engine=INNODB
log-error=/usr/local/mysql/data/mysql_error.log //开启错误日志,指定错误日志保存路径和文件名
bin log 二进制日志
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
#skip-grant-tables
#default-storage-engine=INNODB
log_bin=mysql-bin //启用二进制日志
relay log 中继日志
general log 普通日志
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
#skip-grant-tables
#default-storage-engine=INNODB
general_log=ON //因为默认关闭的,所以要开启
general_log_file=/usr/local/mysql/data/mysql_general.log //指定保存路径和文件名
如何查询日志是否开启成功
mysql> show variables like 'general%'; //查询普通日志是否开启和存放路径,variables表示变量,like表示模糊查询,%表示通配符
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'log_bin%'; //查看二进制日志是否开启
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like '%slow%'; //查看慢查询日功能是否开启和路径位置
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/mysql_slow_query.log |
+---------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like 'long_query_time'; //查看慢查询的时间设置
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
mysql> set global slow_query_log=ON; //在数据库里面开启慢查询日志
Query OK, 0 rows affected (0.09 sec)
[root@mysql data]# cd /usr/local/mysql/data/ //查看二进制日志
[root@mysql data]# ls
abc blbl ibdata1 ibtmp1 mysql-bin.index mysql_slow_query.log sys
auto.cnf dldl ib_logfile0 mysql mysql_error.log performance_schema
bbs ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql_general.log school
数据库的备份
物理备份
三种物理备份
逻辑备份
三种逻辑备份
物理冷备份与恢复
[root@mysql mysql]# tar zcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/ //先打包数据库中所有的数据
[root@mysql mysql]# ls /opt/
apache-tomcat-9.0.16.tar.gz fenge,sh mysql-boost-5.7.20.tar.gz php-7.1.10.tar.bz2
centos7 jdk-8u201-linux-x64.rpm nginx-1.15.9 rh
dir_SC_UTF8 mysql-5.7.20 nginx-1.15.9.tar.gz webdata
Discuz_X3.4_SC_UTF8.zip mysql_all_2021-07-13.tar.gz php-7.1.10 说明.htm //有一个以时间戳命名的文件就是我们的压缩文件
[root@mysql mysql]# mv /usr/local/mysql/data/ /opt/ //模拟数据损坏
[root@mysql mysql]# tar zxf /opt/mysql_all_2021-07-13.tar.gz -C /usr/local/mysql/ //指定路径解压我们备份的数据
[root@mysql mysql]# ls
bin COPYING COPYING-test docs include lib man mysql-test README README-test share support-files usr //我们解压的data在usr目录中
[root@mysql mysql]# mv /usr/local/mysql/usr/local/mysql/data/ ./
[root@mysql mysql]# ls
bin COPYING COPYING-test data docs include lib man mysql-test README README-test share support-files usr //将数据移到原本的位置就可以实现备份了
mysqldump的备份与恢复(温备份)
完全备份一个或多个完整的库
[root@mysql mysql]# mysqldump -uroot -pabc123 --databases blbl > /opt/blbl.sql //备份blbl库到opt下
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# ls /opt/
apache-tomcat-9.0.16.tar.gz dir_SC_UTF8 mysql-5.7.20 nginx-1.15.9.tar.gz webdata
blbl.sql Discuz_X3.4_SC_UTF8.zip mysql_all_2021-07-13.tar.gz php-7.1.10 说明.htm
centos7 fenge,sh mysql-boost-5.7.20.tar.gz php-7.1.10.tar.bz2
data jdk-8u201-linux-x64.rpm nginx-1.15.9 rh
[root@mysql mysql]# mysqldump -uroot -pabc123 --databases blbl dldl > /opt/blbl-dldl.sql //同时备份blbl和dldl两个库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# ls /opt/
apache-tomcat-9.0.16.tar.gz data jdk-8u201-linux-x64.rpm nginx-1.15.9 rh
blbl-dldl.sql dir_SC_UTF8 mysql-5.7.20 nginx-1.15.9.tar.gz webdata
完全备份所有库
[root@mysql mysql]# mysqldump -uroot -pabc123 --all-databases > /opt/all.sql //指定所有库并备份到opt下
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# ls /opt/
all.sql centos7 fenge,sh mysql-boost-5.7.20.tar.gz php-7.1.10.tar.bz2
apache-tomcat-9.0.16.tar.gz data jdk-8u201-linux-x64.rpm nginx-1.15.9 rh
完全备份指定库中的部分表
[root@mysql mysql]# mysqldump -uroot -pabc123 blbl qq ss > /opt/qq-ss.sql //指定库命备份其中的部分表到opt下
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# ls /opt/
all.sql data mysql-5.7.20 php-7.1.10 说明.htm
apache-tomcat-9.0.16.tar.gz dir_SC_UTF8 mysql_all_2021-07-13.tar.gz php-7.1.10.tar.bz2
blbl-dldl.sql Discuz_X3.4_SC_UTF8.zip mysql-boost-5.7.20.tar.gz qq-ss.sql
mysqldump -uroot -pabc123 [-d] blbl qq ss > /opt/qq-ss.sql //-d表示只保存数据库的表结构,不使用说明表数据也一起备份
当备份加--databases是针对库操作,不加就是针对库中的表操作
恢复数据库
方法一:
mysql> drop database blbl; //删除blbl库
Query OK, 5 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| bbs |
| dldl |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
8 rows in set (0.00 sec)
mysql> source /opt/blbl.sql //使用source命令恢复数据库
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)
. . .
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| bbs |
| blbl |
| dldl |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
9 rows in set (0.00 sec)
方法二:
[root@mysql mysql]# mysqldump -uroot -pabc123 blbl qwe > /opt/blbl.qwe.sql //先备份blbl库中的qwe表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# ls /opt/
all.sql blbl.qwe.sql Discuz_X3.4_SC_UTF8.zip mysql_all_2021-07-13.tar.gz php-7.1.10
[root@mysql mysql]# mysql -uroot -pabc123 -e 'drop table blbl.qwe;' //删除blbl中的qwe表,模拟表丢失
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# mysql -uroot -pabc123 -e 'show tables from blbl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_blbl |
+----------------+
| qq |
| ss |
| tyu |
| yy |
+----------------+
[root@mysql mysql]# mysql -uroot -pabc123 blbl < /opt/blbl.qwe.sql //将备份的表导入库中
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql mysql]# mysql -uroot -pabc123 -e 'show tables from blbl;' //查看库中的表,发现qwe被恢复了
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_blbl |
+----------------+
| qq |
| qwe |
| ss |
| tyu |
| yy |
+----------------+
数据库的增量备份与恢复
如何查看二进制文件内容
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 //--no-defaults 默认字符集,--base64-output=decode-rows使用64位编码机制去解码(decode)并按行读取(rows)-v显示详细内容
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File '/opt/mysql-bin.000002' not found (Errcode: 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
进行完全备份
[root@mysql data]# mysqldump -uroot -pabc123 blbl yy > /opt/blbl_yy_$(date +%F).sql //完全备份blbl中的yy表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysqladmin -u root -pabc123 flush-logs //刷新二进制文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# ls //多了一个00003
abc blbl ibdata1 ibtmp1 mysql-bin.000002 mysql_error.log performance_schema
auto.cnf dldl ib_logfile0 mysql mysql-bin.000003 mysql_general.log school
mysql> create database school2; //进入数据库创建一个库
Query OK, 1 row affected (0.00 sec)
mysql> use school2;
Database changed
mysql> create table test1 (id int(4),name varchar(4)); //创建一个表,然后插入数据
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 values(1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(2,'two');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)
[root@mysql data]# mysqladmin -u root -p flush-logs
Enter password: //再次刷新一下二进制日志
[root@mysql data]# ls //多了一个00004
abc blbl ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.index mysql_slow_query.log school2
auto.cnf dldl ib_logfile0 mysql mysql-bin.000003 mysql_error.log performance_schema sys
bbs ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql_general.log school
增量恢复
[root@mysql data]# mysqldump -uroot -pabc123 blbl yy > /opt/blbl_yy.sql //备份blbl库中的yy表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysql -uroot -pabc123 -e 'drop table blbl.yy;' //模拟表丢失
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysql -uroot -p blbl < /opt/blbl_yy.sql //恢复yy表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| bbs |
| blbl |
| dldl |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+--------------------+
10 rows in set (0.00 sec)
mysql> drop database school2; //进入数据库删除school2
Query OK, 1 row affected (0.02 sec)
[root@mysql data]# mysqlbinlog --no-defaults mysql-bin.000003 | mysql -uroot -pabc123 //基于00003二进制文件恢复school2库
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| bbs |
| blbl |
| dldl |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+--------------------+
10 rows in set (0.00 sec)
断点恢复
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 //查看二进制日志内容
# at 707
#210713 22:22:14 server id 1 end_log_pos 751 CRC32 0x74457936 Write_rows: table id 250 flags: STMT_END_F
### INSERT INTO `school2`.`test1`
### SET
### @1=1
### @2='one'
# at 751
#210713 22:22:14 server id 1 end_log_pos 782 CRC32 0x1fe43fdf Xid = 1128
COMMIT/*!*/;
# at 782
#210713 22:22:19 server id 1 end_log_pos 847 CRC32 0x7fa69efb Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 976
#210713 22:22:19 server id 1 end_log_pos 1020 CRC32 0x9e270224 Write_rows: table id 250 flags: STMT_END_F
### INSERT INTO `school2`.`test1`
### SET
### @1=2
### @2='two'
# at 1020
#210713 22:22:19 server id 1 end_log_pos 1051 CRC32 0x8163cd96 Xid = 1129
COMMIT/*!*/;
mysql> drop database school2; //模拟库丢失
Query OK, 0 rows affected (0.01 sec)
[root@mysql data]# mysqlbinlog --no-defaults --stop-position='976' /opt/mysql-bin.000003 | mysql -uroot -pabc123 //在976停止,之后的数据不恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.
root@mysql data]# mysqlbinlog --no-defaults --start-position='1020' /opt/mysql-bin.000003 | mysql -uroot -pabc123 //基于1020开始,跳过中间的数据
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1; //发现中间跳过的没有被恢复
+------+------+
| id | name |
+------+------+
| 1 | one |
+------+------+
1 row in set (0.00 sec)
- 基于时间点恢复
mysql> drop database school2;
Query OK, 1 row affected (0.00 sec)
[root@mysql data]# mysqlbinlog --no-defaults --stop-datetime='2021-07-13 22:22:14' /opt/mysql-bin.000003 | mysql -uroot -pabc123 //基于时间点停止
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysqlbinlog --no-defaults --start-datetime='2021-07-13 22:22:19' /opt/mysql-bin.000003 | mysql -uroot -pabc123 //基于这个时间开始
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use school2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 2 | two |
+------+------+
1 row in set (0.00 sec)