[root@master2~]# mysql -uroot -p ##进入数据库
Enter password:
mysql>createdatabase school;##创建数据库
Query OK,1row affected (0.01 sec)
mysql>use school;##使用数据库Database changed
mysql>createtable info(##创建表-> id int(3)notnullprimarykeyauto_increment,-> name varchar(10)notnull,-> score decimal(4,1)notnull);
Query OK,0rows affected (0.02 sec)
mysql>desc info;##查看表结构+-------+--------------+------+-----+---------+----------------+| Field |Type|Null|Key|Default| Extra |+-------+--------------+------+-----+---------+----------------+| id |int(3)|NO| PRI |NULL|auto_increment|| name |varchar(10)|NO||NULL||| score |decimal(4,1)|NO||NULL||+-------+--------------+------+-----+---------+----------------+3rowsinset(0.00 sec)
mysql>insertinto info (name,score)values('stu01',88),('stu02',77);##插入表数据
Query OK,2rows affected (0.02 sec)
Records: 2 Duplicates: 0Warnings: 0
mysql>select*from info;##查看表内容+----+-------+-------+| id | name | score |+----+-------+-------+|1| stu01 |88.0||2| stu02 |77.0|+----+-------+-------+2rowsinset(0.01 sec)
mysql>select*from info limit1;##只显示表中的前1行+----+-------+-------+| id | name | score |+----+-------+-------+|1| stu01 |88.0|+----+-------+-------+1rowinset(0.00 sec)
2,对数据库进行物理的完全备份
[root@master2~]# cd /usr/local/mysql/data/ ##切换到数据库的数据目录下[root@master2data]# ls
auto.cnf ibdata1 ib_logfile1 mysql school test
ib_buffer_pool ib_logfile0 ibtmp1 performance_schema sys
[root@master2data]# cd school/[root@master2 school]# ls ##数据中的文件
db.opt info.frm info.ibd
[root@master2 school]# cd ..[root@master2data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/ ##用xz格式压缩[root@master2data]# cd /opt/[root@master2 opt]# ls
mysql-2019-11-26.tar.xz mysql-5.7.20 rh
3,对单个数据库进行逻辑上的备份
[root@master2 opt]# mysqldump -uroot -p school > /opt/school.sql ##逻辑备份单个数据库
Enter password:
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql[root@master2 opt]# vim school.sql ##查看备份数据库脚本...CREATETABLE`info`(`id`int(3)NOTNULLAUTO_INCREMENT,`name`varchar(10)NOTNULL,`score`decimal(4,1)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8;...LOCKTABLES`info`WRITE;/*!40000 ALTER TABLE `info` DISABLE KEYS */;INSERTINTO`info`VALUES(1,'stu01',88.0),(2,'stu02',77.0);...
4,对多个数据库进行备份
[root@master2 opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql##备份多个数据库
Enter password:
[root@master2 opt]# ls
db_school_mysql.sql mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql
[root@master2 opt]# mysqldump -uroot -p school info > /opt/school_info.sql ##对数据库中的表进行备份
Enter password:
[root@master2 opt]# lsall.sql mysql-2019-11-26.tar.xz rh school.sql
db_school_mysql.sql mysql-5.7.20 school_info.sql
7,对数据库中的表结构进行备份
[root@master2 opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql ##对表结构进行备份
Enter password:
[root@master2 opt]# lsall.sql mysql-5.7.20 school_info.sql
db_school_mysql.sql rh school.sql
mysql-2019-11-26.tar.xz school_info_desc.sql
8,基于脚本恢复数据库
[root@master2 opt]# mysql -uroot -p ##进入数据库
Enter password:
mysql>showdatabases;##查看数据库+--------------------+|Database|+--------------------+| information_schema || mysql || performance_schema || school || sys || test |+--------------------+6rowsinset(0.00 sec)
mysql>use school;##使用数据库Database changed
mysql>showtables;##查看表+------------------+| Tables_in_school |+------------------+| info |+------------------+1rowinset(0.00 sec)
mysql>droptable info;##删除表
Query OK,0rows affected (0.01 sec)
mysql>showtables;###查看表
Empty set(0.00 sec)
mysql> source /opt/school.sql##恢复数据库脚本文件
mysql>showtables;##查看表+------------------+| Tables_in_school |+------------------+| info |+------------------+1rowinset(0.00 sec)
9,基于外部MySQL命令恢复数据库
mysql>droptable info;##删除表
Query OK,0rows affected (0.01 sec)
mysql>showtables;##查看表
Empty set(0.00 sec)
mysql> quit ##退出
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>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>showtables;##查看表+------------------+| Tables_in_school |+------------------+| info |+------------------+1rowinset(0.00 sec)
二,MySQL增量备份及恢复
1,开启二进制日志文件
[root@master2 opt]# 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/mysql.pid
socket =/usr/local/mysql/mysql.sock
log-bin=mysql-bin ##开启二进制日志文件
server-id =1[root@master2 opt]# systemctl restart mysqld.service ##重启mysql服务[root@master2 opt]# cd /usr/local/mysql/data/ ##切换到mysql站点[root@master2data]# ls ##查看二进制日志文件
auto.cnf ib_logfile0 mysql performance_schema test
ib_buffer_pool ib_logfile1 mysql-bin.000001 school
ibdata1 ibtmp1 mysql-bin.index sys
2,进行完全备份
[root@master2data]# mysqldump -uroot -p123123 school > /opt/school.sql ##一次完全备份
mysqldump: [Warning]Using a password on the command line interface can be insecure.[root@master2data]# ls
auto.cnf ib_logfile0 mysql performance_schema test
ib_buffer_pool ib_logfile1 mysql-bin.000001 school
ibdata1 ibtmp1 mysql-bin.index sys
[root@master2data]# mysqladmin -uroot -p123123 flush-logs ##刷新二进制日志文件
mysqladmin: [Warning]Using a password on the command line interface can be insecure.[root@master2data]# ls ##生成新的二进制日志文件,接下来的操作会保存在mysql-bin.000002中
auto.cnf ib_logfile0 mysql mysql-bin.index sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema test
ibdata1 ibtmp1 mysql-bin.000002 school
3,进入数据库,模拟误操作
[root@master2data]# mysql -uroot -p123123 ##进入数据库
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>select*from info;##查看表+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0|+----+------+-------+2rowsinset(0.00 sec)
mysql>insertinto info (name,score)values('by01',66);##正确操作
Query OK,1row affected (0.00 sec)
mysql>select*from info;+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0||3| by01 |66.0|+----+------+-------+3rowsinset(0.00 sec)
mysql>deletefrom info where name='st01';##错误操作
Query OK,1row affected (0.00 sec)
mysql>insertinto info (name,score)values('by02',99);##正确操作
Query OK,1row affected (0.00 sec)
mysql>select*from info;+----+------+-------+| id | name | score |+----+------+-------+|2| st02 |77.0||3| by01 |66.0||4| by02 |99.0|+----+------+-------+3rowsinset(0.00 sec)[root@master2data]# mysqladmin -uroot -p123123 flush-logs ##刷新二进制日志文件
mysqladmin: [Warning]Using a password on the command line interface can be insecure.[root@master2data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt##用64位解码器查看二进制日志文件,并生成一个文件[root@master2data]# cd /opt/[root@master2 opt]# ls
bak.txt mysql-5.7.20 rh school.sql[root@master2 opt]# vim bak.txt ##查看二进制日志文件# at 1084#191127 20:14:01 server id 1 end_log_pos 1132 CRC32 0xdcc90eb5 Write_rows: table id 221 flags: STMT_END_F### INSERT INTO `school`.`info` ##第一次正确操作的时间和位置### SET### @1=3### @2='by01'### @3=66.0...# at 1302 ##停止位置点#191127 20:14:46 server id 1 end_log_pos 1357 CRC32 0x6648509a Table_map: `school`.`info` mapped to number 221# at 1357#191127 20:14:46 server id 1 end_log_pos 1405 CRC32 0x1eeb752b Delete_rows: table id 221 flags: STMT_END_F### DELETE FROM `school`.`info` ##第二次执行错误操作的时间和位置191127 20:14:46### WHERE### @1=1### @2='st01'### @3=88.0# at 1405 ##开始位置点#191127 20:14:46 server id 1 end_log_pos 1436 CRC32 0xf1c8d903 Xid = 54...# at 1630#191127 20:15:16 server id 1 end_log_pos 1678 CRC32 0x08d9b0f4 Write_rows: table id 221 flags: STMT_END_F### INSERT INTO `school`.`info` ##第二次正确操作的时间和位置191127 20:15:16### SET### @1=4### @2='by02'### @3=99.0
4,基于时间点进行断点恢复
[root@master2 opt]# mysql -uroot -p123123 ##进入数据库
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>droptable info;##删除数据库
Query OK,0rows affected (0.01 sec)
mysql>select*from info;##查看表
ERROR 1146(42S02): Table'school.info' doesn't exist
mysql> source /opt/school.sql ##恢复完全备份数据库脚本
...
mysql> show tables; ##查看表
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info; ##查看表数据
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
+----+------+-------+
2 rows in set (0.00 sec)
[root@master2 opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-2720:14:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123
##恢复bin.000002中前一个正确的执行语句(从第二个错误语句时间点停止)
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> use school; ##使用数据库
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 info; ##查看表数据,恢复了第一次正确操作
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | st01 | 88.0 |
| 2 | st02 | 77.0 |
| 3 | by01 | 66.0 |
+----+------+-------+
3 rows in set (0.00 sec)
[root@master2 opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-2720:15:16' /usr/local/mysql/data/mysql-bin.000002| mysql -uroot -p123123
##跳过错误节点,恢复最后一个正确的操作(从最后一个正确的操作时间点开始)
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>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>select*from info;##查看表数据,恢复了第二次正确操作,跳过了错误的操作+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0||3| by01 |66.0||4| by02 |99.0|+----+------+-------+4rowsinset(0.00 sec)
5,基于位置点进行断点恢复
mysql>deletefrom info where name='by01';##为实验方便直接删除
Query OK,1row affected (0.01 sec)
mysql>deletefrom info where name='by02';##删除
Query OK,1row affected (0.00 sec)
mysql>select*from info;##完全备份的初始状态+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0|+----+------+-------+2rowsinset(0.00 sec)
mysql> quit
Bye
[root@master2 opt]# mysqlbinlog --no-defaults --stop-position='1302' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 ##跳过错误操作的位置点从上一个位置点开始[root@master2 opt]# mysql -uroot -p123123 ##进入数据库
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>select*from info;##查看表数据,恢复了第一次正确的操作+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0||3| by01 |66.0|+----+------+-------+3rowsinset(0.00 sec)
mysql> quit
Bye
[root@master2 opt]# mysqlbinlog --no-defaults --start-position='1405' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 ##从错误的位置后一个位置点开始,跳过错误操作的位置点[root@master2 opt]# mysql -uroot -p123123 ##进入数据库
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>use school;##使用数据库
Reading table information for completion oftableandcolumn names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>select*from info;##查看表数据,跳过错误操作,恢复第二次正确操作数据+----+------+-------+| id | name | score |+----+------+-------+|1| st01 |88.0||2| st02 |77.0||3| by01 |66.0||4| by02 |99.0|+----+------+-------+4rowsinset(0.00 sec)
6,对于增量备份全部恢复
[root@master2 opt]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123##全部增量恢复