阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL数据库备份之逻辑备份

210次阅读
没有评论

共计 3898 个字符,预计需要花费 10 分钟才能阅读完成。

一、MySQL 数据库备份之逻辑备份

1. 命令简介:

# mysqldump -h 服务器 - u 用户名 - p 密码 数据库名 > 备份文件.sql
1)关于数据库名:
-A, –all-databases       所有库
school         数据库名
school stu_info t1 school  数据库的表 stu_info、t1
-B, –databases bbs test mysql 多个数据库
2)关于其它参数说明:
–single-transaction        #InnoDB 一致性 服务可用性
-x, –lock-all-tables          #MyISAM 一致性 服务可用性
-E, –events                    #备份事件调度器代码
–opt                              #同时启动各种高级选项
-R, –routines                #备份存储过程和存储函数
-F, –flush-logs              #备份之前刷新日志
–triggers                      #备份触发器
–master-data=1|2        #该选项将会记录 binlog 的日志位置与文件名并追加到文件中

2、操作过程:

1)创建库表:

mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+——————–+
| Database          |
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| school            |
| sys                |
+——————–+
5 rows in set (0.00 sec)

mysql> use school;
Database changed

mysql> select * from school.t1;
Empty set (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+——+
| id  |
+——+
|    1 |
|    2 |
+——+
2 rows in set (0.00 sec)

mysql>

2)逻辑备份:

[root@localhost ~]# mysqldump -uroot -p’Yanglt123.’ –all-databases \
> –single-transaction \
> –routines \
> –triggers \
> –master-data=1 \
> –flush-logs > /tmp/`date +%F`-mysql-all.sql`

mysqldump: [Warning] Using a password on the command line interface can be insecure.    #此提示是密码明文显示的愿意
[root@localhost tmp]#

 注意事项:

–master-data=1    #该选项将会记录 binlog 的日志位置与文件名并追加到文件中
参数为 1 和 2 的时候,都是把 position 日志截断,如果为 2 的话第 22 行为注释状态,为 1 的时候没有注释,建议选择 1:

 [root@localhost tmp]# vim 2018-09-19-mysql-all.sql 可以
 19 — Position to start replication or point-in-time recovery from
 20 —
 21
 22 CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=154;
 23
 24 —
:set nu                                                       

二、数据库恢复

1. 停止数据库【systemtl stop mysqld】
2. 清理环境【rm -rf /var/lib/mysql/*;】
3. 启动数据库【初始密码 /var/log/mysqld.log】
4. 重置密码【新密码】
5. mysql 恢复数据【新密码】
6. 刷新授权【备份时密码】

注:如果不是一个新的数据库环境,我们需要从第一步开始,如果已经是一个新的数据环境,我们可以直接从第 5 步执行。

先创建一个表,等一下验证恢复情况:
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(1),(2)
    -> ;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show tables;
+——————+
| Tables_in_school |
+——————+
| t1              |
| t2              |
+——————+
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;

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3898字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中