MySQL增删改查及备份恢复
数据库创建及增删改查
创建数据库:
mysql> create database abc;
连接数据库:
mysql> use abc;
创建表:
mysql> create table biao1 (id int,name char(10));
插入数据:
mysql> insert into biao1 (id,name) values (1,‘z1’);
mysql> insert into biao1 (id,name) values (2,‘z2’);
查看数据:
mysql> select * from biao1;
修改数据:
mysql> update biao1 set name=‘zzz’ where id=2;
删除数据:
mysql> delete from biao1 where id=2;
删除表:
mysql> drop table biao1;
删除数据库:
mysql> drop database abc;
显示mysql信息:
mysql> status;
退出mysql:
mysql> exit;
查看数据库名:
mysql> show databases;
查看表名:
mysql> show tables;
数据库冷备份与恢复:
数据库冷备份:
#mkdir /backup
/etc/init.d/mysqld stop
#tar -zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
ll /backup
模拟数据库损坏
mkdir /bak
mv /usr/local/mysql/data/ /bak/
/etc/init.d/mysqld start→报错
mysql -u root -p→无法登陆
恢复冷备份数据:
cd /bak/
tar zxf /backup/mysql_all-2020-11-24.tar.gz -C /bak/
mv /bak/usr/local/mysql/data/ /usr/local/mysql
/etc/init.d/mysqld start(成功)
mysqldump备份与恢复:
mysqldump -u 用户名 -p 密码 选项 数据库名 表名 > /备份路径/备份文件名
常见选项:–all-databases、–opt
备份:
备份数据表
mysqldump -u root -p123.com abc biao1 > /bak/abc.biao1.sql 将abc库中的表1备份到/bak下,起名为abc.biao1.sql
备份数据库
mysqldump -u root -p123.com --database abc > /bak/abc.sql 将abc库备份到/bak下
备份所有库
mysqldump -u root -p123.com --all-databases > /bak/all.sql 将所有库备份到/bak下
查看备份文件内容:
grep -v “^–” /bak/abc.sql | grep -v “^/” | grep -v “^$”
模拟故障:
drop database abc 删除库
恢复:
mysql -u 用户名 -p 密码 选项 数据库名 < /备份路径/备份文件名
mysql -u root -p123.com < /bak/abc.sql 将abc库备份从/bak中恢复
mysql -u root -p123.com -e “show databases; ” 查看库
增量备份
开启二进制日志
vim /etc/my.cnf
log-bin=/usr/local/mysql/mysql-bin 日志文件储存位置
重启数据库
/etc/init.d/mysqld restart
查看二进制日志文件
mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000001
创建库abc和表biao1
mysql> create database abc
mysql> use abc
mysql> create table biao1(id char(10) not null,name char(20) not null)
mysql> insert into biao1 values(‘1’,‘z1’);
mysql> insert into biao1 values(‘2’,‘z2’);
mysql> select * from biao1;
±—±-----+
| 1 | z1 |
| 2 | z2 |
±—±-----+
先进行一次完整备份:
#mkdir /bak
mysqldump -u root -p123.com abc biao1 > /bak/abc.biao1.sql
生成新的二进制文件(刷新):
mysqladmin -u root -p123.com flush-logs
在添加用户做增量备份:
mysql> use abc;
mysql> insert into biao1 values(‘3’,‘z3’);
mysql> insert into biao1 values(‘4’,‘z4’);
mysql> select * from biao1;
±—±-----+
| id | name |
±—±-----+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
±—±-----+
刷新,同步:
mysqladmin -u root -p123.com flush-logs
#ll /usr/local/mysql
#cp /usr/local/mysql/mysql-bin.000002 /bak
模拟误删除:
mysql -u root -p123.com -e ‘drop table abc.biao1;’
mysql -u root -p123.com -e ‘select * from abc.biao1;’
恢复(完整→增量):
mysql -u root -p123.com abc < /bak/abc.biao1.sql
mysql -u root -p123.com -e ‘select * from abc.biao1;’
±—±-----+
| 1 | z1 |
| 2 | z2 |
±—±-----+
恢复缺少部分:
mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p123.com
mysql -u root -p123.com -e ‘select * from abc.biao1;’
±—±-----+
| id | name |
±—±-----+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
±—±-----+
基于位置恢复
模拟误删除(只能删除表内容,不能删除表或库)
mysql -u root -p123.com -e ‘delete from abc.biao1 where id=2;’ 删除id为2的内容
查看日志:
mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
at 4
#201125 15:56:22 server id 1 end_log_pos 120 CRC32 0xbd3267d4 Start: binlog v 4, server v 5.6.36-log created 201125 15:56:22 at startup
ROLLBACK/!/;
BINLOG ’
Jg6+Xw8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAmDr5fEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAdRn
Mr0=
'/!/;
at 120
#201125 15:57:37 server id 1 end_log_pos 211 CRC32 0xa2b4f2e1 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606291057/!/;
SET @@session.pseudo_thread_id=1/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
create database abc
/!/;
at 211
#201125 15:57:56 server id 1 end_log_pos 346 CRC32 0xc5886149 Query thread_id=1 exec_time=0 error_code=0
use abc
/!/;
SET TIMESTAMP=1606291076/!/;
create table biao1(id char(10) not null,name char(20) not null)
/!/;
at 346
#201125 15:58:09 server id 1 end_log_pos 423 CRC32 0x2c86d1f6 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606291089/!/;
BEGIN
/!/;
at 423
#201125 15:58:09 server id 1 end_log_pos 529 CRC32 0x5dc5a878 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606291089/!/;
insert into biao1 values(‘1’,‘z1’)
/!/;
at 529
#201125 15:58:09 server id 1 end_log_pos 560 CRC32 0x3e449cc5 Xid = 11
COMMIT/!/;
at 560
#201125 15:58:17 server id 1 end_log_pos 637 CRC32 0x44b947fc Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606291097/!/;
BEGIN
/!/;
at 637
#201125 15:58:17 server id 1 end_log_pos 743 CRC32 0x847621a8 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1606291097/!/;
insert into biao1 values(‘2’,‘z2’)
/!/;
at 743
#201125 15:58:17 server id 1 end_log_pos 774 CRC32 0x788195d1 Xid = 12
COMMIT/!/;
at 774
#201125 15:59:04 server id 1 end_log_pos 821 CRC32 0x4a61157e Rotate to mysql-bin.000002 pos: 4
DELIMITER ;
End of log file
ROLLBACK /* added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
mysqlbinlog --no-defaults --start-position=‘637’ /usr/local/mysql/mysql-bin.000001 | mysql -u root -p123.com从637位置开始恢复(帅博版日志文件里蓝色底面的是位置)
#mysqlbinlog --no-defaults --stop-position=‘774’ /usr/local/mysql/mysql-bin.000002 | mysql -u root -p123.com恢复到774位置停止(位置的选择:恢复的位置区域里要有遗失的内容,日置文件可见id=2在637区域里)
#mysql -u root -p123.com -e ‘select * from abc.biao1;’
±—±-----+
| id | name |
±—±-----+
| 1 | z1 |
| 3 | z3 |
| 4 | z4
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
±—±-----+
基于时间点
模拟误删除(只能删除表内容,不能删除表或库)
mysql -u root -p123.com -e ‘delete from abc.biao1 where id=2;’ 删除id为2的内容
#mysqlbinlog --no-defaults --start-datetime=‘2020-11-25 15:58:17’ /usr/local/mysql/mysql-bin.000001 | mysql -uroot -p123.com 从2020-11-25 15时58分17秒开始恢复(帅博版日志文件里绿底底面的是时间)
#mysqlbinlog --no-defaults --stop-datetime=‘2020-11-25 15:59:04’ /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p123.com恢复到2020-11-25 15时59分04秒停止(时间的选择:恢复的时间区域里要有遗失的内容,日置文件可见id=2在2020-11-25 15时58分17秒区域里)
#mysql -u root -p123.com -e ‘select * from abc.biao1;’
±—±-----+
| id | name |
±—±-----+
| 1 | z1 |
| 3 | z3 |
| 4 | z4
| 2 | z2 |
| 3 | z3 |
| 4 | z4 |
±—±-----+
本文地址:https://blog.csdn.net/m0_50124375/article/details/110670373
上一篇: 游戏脚本 玩玩24点
下一篇: STL容器
推荐阅读
-
[MySQL] innobackupex在线备份及恢复(全量和增量)_MySQL
-
mysql完全备份,增量备份及恢复脚本_MySQL
-
mysql数据库备份及恢复命令 mysqldump,source的用法
-
mysql数据库备份及恢复命令 mysqldump,source的用法
-
mysql增量备份及断点恢复脚本实例
-
mysql增量备份及断点恢复脚本实例
-
mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍
-
分享一个使用mysql dump对MySQL数据库进行备份及恢复教程
-
Python实现连接MySql数据库及增删改查操作详解
-
Navicat for MySQL定时备份数据库及数据恢复详解