设为首页 加入收藏

TOP

MySQL数据库备份之逻辑备份(二)
2018-10-10 10:10:22 】 浏览:183
Tags:MySQL 数据库 备份 逻辑
sp;             |
+------------------+
2 rows in set (0.00 sec)


mysql> Bye
[root@localhost ~]#


1)停止数据库
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]#
2)清理环境
此处暂时不删除bin-log日志
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql/*


3)启动数据库
[root@localhost ~]# systemctl start mysqld


4)重置密码     
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log|tail -n 1
2018-09-19T09:48:39.418109Z 1 [Note] A temporary password is generated for root@localhost: aBm<-wrj4NSV
[root@localhost ~]# mysqladmin -uroot -p'aBm<-wrj4NSV' password "Yanglt123."
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]#


5)恢复数据
[root@localhost ~]# mysql -uroot -p'Yanglt123.' < /tmp/2018-09-19-mysql-all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
可以看到它恢复到了备份点,刚才创建的表t2是在备份点之后生成的,可以看到表中没有t2:
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school            |
| sys 

;               |
+--------------------+
5 rows in set (0.01 sec)


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> show tables;
+------------------+
| Tables_in_school |
+------------------+
| t1              |
+------------------+
1 row in set (0.00 sec)


mysql>


6) 刷新授权
改完密码后与备份点的密码可能不一致,所有我们要执行此步骤,来实现与备份点密码一致。
[root@localhost ~]# mysql -p'Yanglt123.' -e 'flush privileges'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#


7)建议在逻辑备份恢复时,暂停BINLOG
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.02 sec)
mysql> source /tmp/2018-09-19-mysql-all.sql;



编程开发网
首页 上一页 1 2 下一页 尾页 2/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇Linux下MySQL主从配置详细教程 下一篇MySQL 5.7.x无法开启二进制日志的..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(217) }