1.备份数据库:
[root@localhost ~]# systemctl stop mysqld
###关闭数据库
[root@localhost ~]# mkdir /backup
###在根下新建个backup目录
[root@localhost ~]# tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
###备份压缩
◆模拟故障
[root@localhost backup]# mkdir /bak
###在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/
###将目录data数据移动到bak 里面去
[root@localhost backup]# ll /usr/local/mysql/
###查看mysql目录发现data被移走,模拟故障成功
total 64
drwxr-xr-x 2 mysql mysql 4096 Sep 10 16:01 bin
-rw-r--r-- 1 mysql mysql 17987 Sep 13 2017 COPYING
-rw-r--r-- 1 mysql mysql 17987 Sep 13 2017 COPYING-test
drwxr-xr-x 2 mysql mysql 55 Sep 10 16:01 docs
drwxr-xr-x 3 mysql mysql 4096 Sep 10 16:01 include
drwxr-xr-x 4 mysql mysql 191 Sep 10 16:01 lib
drwxr-xr-x 4 mysql mysql 30 Sep 10 16:01 man
drwxr-xr-x 10 mysql mysql 4096 Sep 10 16:01 mysql-test
-rw-r--r-- 1 mysql mysql 2478 Sep 13 2017 README
-rw-r--r-- 1 mysql mysql 2478 Sep 13 2017 README-test
drwxr-xr-x 28 mysql mysql 4096 Sep 10 16:01 share
drwxr-xr-x 2 mysql mysql 90 Sep 10 16:01 support-files
drwxr-xr-x 3 mysql mysql 17 Sep 10 16:01 usr
◆数据库恢复
[root@localhost mysql]# mkdir /restore
###在根目录新建restore 目录
[root@localhost mysql]#tar xzvf /backup/mysql_all-2020-09-13.tar.gz -C /restore/
####将备份数据库解压到 /restore目录下 注意:根据自己的时间节点解压
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
###将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld
###启动mysql数据库
[root@localhost mysql]# systemctl status mysqld
###查看mysql数据库启动状态 ,日志文件显示正常。
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-09-13 16:30:44 CST; 24s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 29346 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 29327 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 29348 (mysqld)
CGroup: /system.slice/mysqld.service
└─29348 /usr/local/mysql/bin/mysqld --daemonize --...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: Version:...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain systemd[1]: Started MyS...
Hint: Some lines were ellipsized, use -l to show in full.
2.mysqldump备份与恢复
创建环境
[root@localhost ~]# mysql -uroot -p
###进入mysql数据库
mysql> create databases auth;
###创建auth 数据库
mysql> use auth;
####进入auth 数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
mysql> insert into users values('lisi', password('123456'));
退出数据库
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql
###备份数据路mysql中的user表 ,文件保存在当前操作目录下面
Enter password: ###输入密码
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql
####备份auth数据库 文件保存在当前操作目录下面
Enter password: ####输入密码
查看备份文件
[root@localhost /]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_name` char(16) NOT NULL,
`user_passwd` char(48) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;
恢复表
[root@localhost /]# mysql -u root -p ###进入数据库
Enter password: ###输入密码
mysql> show databases; ###查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| bdqn |
| myadm |
| mysql |
| performance_schema |
| sys |
mysql> create database text ; ###新建数据库text
mysql>\q ###退出
[root@localhost opt]# mysql -u root -p text < mysql-user.sql
Enter password:
[root@localhost /]# mysql -u root -p -e 'show tables from text' ###验证输出结果
Enter password:
+----------------+
| Tables_in_text |
+----------------+
| user | ###导进去的表
+----------------+
数据库恢复
[root@localhost /]# mysql -u root -p -e 'drop database auth'
###模拟故障删除auth数据库
Enter password:
[root@localhost /]# mysql -u root -p -e 'show databases'
######查看databases所有数据库有没有auth,发现没有了
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| bdqn |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
[root@localhost /]# mysql -u root -p < ./auth.sql
###导入数据库auth
Enter password: ###输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'
###查看databases所有数据库有没有auth,发现有了
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth | ###导进去的auth数据库
| bdqn |
| myadm |
| mysql |
| performance_schema |
| sys |
| text |
+--------------------+
MySQL数据库备份与恢复
最新推荐文章于 2024-10-08 10:45:48 发布