浙江网站建设推广公司十大排行,网页设计及讲解多少钱,外贸网站网站推广,短链接生成器手机版mysql数据备份#xff1a;
数据备份方式
物理备份#xff1a; 冷备#xff1a;.冷备份指在数据库关闭后,进行备份,适用于所有模式的数据库热备#xff1a;一般用于保证服务正常不间断运行#xff0c;用两台机器作为服务机器#xff0c;一台用于实际数据库操作应用,另外…mysql数据备份
数据备份方式
物理备份 冷备.冷备份指在数据库关闭后,进行备份,适用于所有模式的数据库热备一般用于保证服务正常不间断运行用两台机器作为服务机器一台用于实际数据库操作应用,另外一台实时的从前者中获取数据以保持数据一致.如果当前的机器熄火,备份的机器立马取代当前的机器继续提供服务冷备常用命令cp tar scp … 逻辑备份 mysqldump //备份命令mysql /恢复数据命令 1. 物理备份及恢复
1.1. 备份操作
实例1
mkdir -p /data/mysql
cp -r /var/lib/mysql/ /data/mysql/mysql.bak
实例2
tar -zcvf /data/mysql/mysql.tar.gz /var/lib/mysql
实例3
#在备份数据库创建备份目录
mkdir /data
#备份数据
scp -r /var/lib/mysql 192.168.2.20:/data/mysql.bak2. Mysqldump备份
2.1. 只备份表不备份数据本身
备份mysql数据库中的所有表但是不会自动生成创建mysql数据库的语句
[rootwww ~]# mysqldump -uroot -p1234 mysql /root/mysql.sql2.2. 备份数据库与表
备份mysql数据库中的所有表并且会生成创建mysql数据库的SQL语句也就是导入时不需要先创建数据库
[rootwww ~]# mysqldump -uroot -p1234 --databases mysql /root/mysql.sql2.3. 备份多个数据库
备份数据库MySQL、ys到/root/ys_mysql.sql
[rootwww ~]# mysqldump -uroot -p1234 --databases mysql ys /root/ys_mysql.sql2.4. 备份所有数据库
[rootwww ~]# mysqldump -uroot -p1234 --all-databases /root/all.sql
或者
[rootwww ~]# mysqldump -uroot -p1234 -A /root/all.sql
2.5. 备份mysql数据库并且记录pos点
[rootwww ~]# mysqldump -uroot -p1234 --master-data mysql /root/mysql.sql2.6. 备份数据库并刷新日志
[rootwww ~]# mysqldump -uroot -p1234 --master-data --flush-logs mysql /root/mysql.sql
2.3. 数据库的恢复
[rootwww ~]# mysqldump -uroot -p1234 --databases ys /root/ys.sql
首先把ys数据库删除
mysql drop database ys;
Query OK, 31 rows affected, 2 warnings (0.06 sec)mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
| mysql |
| sys |
--------------------
4 rows in set (0.00 sec)
mysql exit
Bye
## 恢复数据库ys
[rootwww ~]# mysql -uroot -p1234 /root/ys.sql
[rootwww ~]# mysql -uroot -p1234
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
| sys |
| mysql |
| ys |
--------------------
4 rows in set (0.00 sec)或者是mysql source /root/ys.sql; ---source 命令导入数据库需要先登录到数库终端
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| performance_schema |
| sys |
| mysql |
| ys |
--------------------
4 rows in set (0.00 sec) 3. 增量备份与恢复
增量备份与恢复的特点 增量备份的优点是没有重复数据备份量不大时间短。但缺点也很明显需要建立在上次完全备份及完全备份之后所有的增量才能恢复。 3.1. binlog日志概述
什么是binlog日志也称作二进制日志mysql服务日志文件的一种记录除查询外的所有SQL语句可用于数据备份和恢复配置mysql主从同步的必要条件
3.2. 启动日志
配置项用途server_id数字指定id值1-255log_bin[目录名/文件名]启用binlog日志max_binlog_size数值m指定binlog日志文件容量(默认1G)
[rootlocalhost ~]# vim /etc/my.cnf
修改内容如下
[mysqld]
.....
log_bin
server_id100
[rootlocalhost ~]# systemctl restart mysqld
###查看binlog日志是否开启
登录数据库
mysql show master status;
-----------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------------
| localhost-bin.000001 | 154 | | | |
-----------------------------------------------------------------------------------
1 row in set (0.00 sec)
binlog相关文件默认存放在/var/lib/mysql/
主机名-bin.index 索引文件主机名-bin.000001 第一个二进制文件主机名-bin.000002 第二个二进制文件 …
正常情况下binlog二进制文件默认1G容量超过重新创建新的binlog二进制文件想生成新的binlog日志文件方法如下
1.重启mysql服务
systemctl restart mysqld
或者
mysql -root -p密码 -e flush logs
或者
mysqldump --flush-logs3.3.增量备份
定时执行flush logs方法重新创建新的日志生成二进制文件序列并及时把这些文件保存到一个安全的地方即完成了一个时间段的增量备份
##创建数据库
create database school default charsetutf8mb4;
##创建表格
use school;
create table stuinfo(- id int primary key auto_increment,- name char(20) not null,- sex enum(boy,girl) not null,- age int unsigned not null,- likes set(run,go fishing,Listen to the music,Play basketball) defaultrun,play basketball);
##往stuinfo表格里插入数据
insert into stuinfo values(1,bob,boy,25,run,go fishing);
##查看表格数据
select * from stuinfo;
------------------------------------
| id | name | sex | age | likes |
------------------------------------
| 1 | bob | boy | 25 | run,go fishing |
------------------------------------
##退出数据库并使用mysqldump完全备份school数据库并重新创建一个binlog文件。
exit ---退出数据库
mkdir /data ---创建存放数据的目录
mysqldump -uroot -p1234 --flush-logs -B school /data/$(date %Y-%m-%d)-school.sql
ls /data/
2023-02-14-school.sql
##进入数据库继续往表格里插入数据
use school;
insert into stuinfo values(2,tom,boy,28,run,play basketball);
select * from stuinfo;
-----------------------------------------
| id | name | sex | age | likes |
-----------------------------------------
| 1 | bob | boy | 25 | run,go fishing |
| 2 | tom | boy | 28 | run,Play basketball |
-----------------------------------------
##退出数据库生成增量备份文件
exit; ---退出数据库
mysql -uroot -p1234 -e show master status;flush logs ----查看当前的binlog文件名称然后创建一个新的binlog文件
-----------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------------
| localhost-bin.000002 | 436 | | | |
-----------------------------------------------------------------------------------
##把localhost-bin.000002文件拷贝到/data下并修改当前日期作为增量备份文件
cp -r /var/lib/mysql/localhost-bin.000002 /data/
mv /data/localhost-bin.000002 /data/$(date %Y-%m-%d)-localhost-bin.000002
ls /data/
2023-02-14-localhost-bin.000002 2023-02-14-school.sql3.4. 数据恢复
##删除school的所有表
drop table stuinfo;
##查看还有其他表格
show tables;
------------------
| Tables_in_school |
------------------
------------------
##退出数据库,恢复数据
quit;
mysql -uroot -p1234 /data/2023-02-14-school.sql ---先恢复完全备份的文件
mysqlbinlog /data/2023-02-14-localhost-bin.000002 | mysql -uroot -p1234 --恢复增量备份的部分。3.5.恢复指定范围的数据
语法
mysqlbinlog 选项 binlog日志名称 | mysql -uroot -p密码3.5.1. 查看binlog日志文件
[rootlocalhost ~]# mysqlbinlog /data/2023-02-14-localhost-bin.000002可以看的出来这种日志格式不易看懂
3.5.2. 修改日志记录格式
##查看当前日志记录格式
mysql show variables like binlog_format;
----------------------
| Variable_name | Value |
----------------------
| binlog_format | ROW |
----------------------
1 row in set (0.00 sec)
三种记录格式
1.statement 报表模式
2.row 行模式
3.mixed 混合模式##修改日志记录格式
[rootlocalhost ~]# vim /etc/my.cnf
[mysqld]
....
binlog_formatmixed
[rootlocalhost ~]# systemctl restart mysqld
###查看是否修改成功
mysql show variables like binlog_format;
----------------------
| Variable_name | Value |
----------------------
| binlog_format | MIXED |
----------------------
1 row in set (0.01 sec)
3.5.3. 恢复指定范围数据
##往stuinfo表格中插入数据:
insert into stuinfo values(3,grace,girl,20,run,play basketball);
insert into stuinfo values(4,andy,boy,23,play basketball);
select * from stuinfo;
-------------------------------------------
| id | name | sex | age | likes |
-------------------------------------------
| 1 | bob | boy | 25 | run,go fishing |
| 2 | tom | boy | 28 | run,Play basketball |
| 3 | grace | girl | 20 | run,Play basketball |
| 4 | andy | boy | 23 | Play basketball |
-------------------------------------------
##删除grace这个数据
delete from stuinfo where namegrace;
select * from stuinfo;
-----------------------------------------
| id | name | sex | age | likes |
-----------------------------------------
| 1 | bob | boy | 25 | run,go fishing |
| 2 | tom | boy | 28 | run,Play basketball |
| 4 | andy | boy | 23 | Play basketball |
-----------------------------------------
##查看当前的binlog日志文件名称
show master status;
-----------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-----------------------------------------------------------------------------------
| localhost-bin.000006 | 1095 | | | |
-----------------------------------------------------------------------------------
### 恢复grace的数据信息
[rootlocalhost ~]# mysqlbinlog /var/lib/mysql/localhost-bin.000006 ---查看binlog日志内容
# at 302 ---起始pos值
#230214 17:27:39 server id 100 end_log_pos 449 CRC32 0x2dbf2580 Query thread_id3 exec_time0 error_code0
use school/*!*/;
SET TIMESTAMP1676366859/*!*/;
insert into stuinfo values(3,grace,girl,20,run,play basketball)
/*!*/;
# at 449
#230214 17:27:39 server id 100 end_log_pos 480 CRC32 0x3116b5d3 Xid 21
COMMIT/*!*/; ---回车操作
# at 480 ---结束pos值
#230214 17:28:12 server id 100 end_log_pos 545 CRC32 0x0b918b41 Anonymous_GTID last_committed1 sequence_number2 rbr_onlyno
SET SESSION.GTID_NEXT ANONYMOUS/*!*/;
# at 545
[rootlocalhost ~]# mysqlbinlog --start-position302 --stop-position480 /var/lib/mysql/localhost-bin.000006 | mysql -uroot -p1234
或者
[rootlocalhost ~]# mysqlbinlog --start-datetime2023-02-14 17:27:39 --stop-datetime2023-02-14 17:28:12 /var/lib/mysql/localhost-bin.000006 | mysql -uroot -p1234
###查看是否恢复
select * from stuinfo;
-------------------------------------------
| id | name | sex | age | likes |
-------------------------------------------
| 1 | bob | boy | 25 | run,go fishing |
| 2 | tom | boy | 28 | run,Play basketball |
| 3 | grace | girl | 20 | run,Play basketball |
| 4 | andy | boy | 23 | Play basketball |
-------------------------------------------
