文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql-备份与恢复-03

2019-02-05 22:43

关注


	mysql-备份与恢复-03
[数据库教程]

目录
https://downloads.mysql.com/archives/community/
[root@vm1 src]# ll
total 628704
drwxr-xr-x. 2 root root         6 May 11  2019 debug
drwxr-xr-x. 2 root root         6 May 11  2019 kernels
-rw-------. 1 root root 643790848 Oct 25 14:56 mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

1.1 安装

创建mysql用户

[root@vm1 src]# groupadd -r mysql
[root@vm1 src]# useradd -M -s /sbin/nologin -g mysql mysql
[root@vm1 src]# id mysql
uid=1000(mysql) gid=992(mysql) groups=992(mysql)

解压安装包

[root@vm1 src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@vm1 src]# cd /usr/local/
[root@vm1 local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
‘mysql‘ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/‘
[root@vm1 local]# ll
total 0
..................................
lrwxrwxrwx. 1 root root  36 Oct 25 15:23 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Oct 25 15:21 mysql-5.7.22-linux-glibc2.12-x86_64
..................................
[root@vm1 local]# chown -R mysql.mysql /usr/local/mysql*
[root@vm1 local]# ll mysql* -d
lrwxrwxrwx. 1 mysql mysql  36 Oct 25 15:23 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Oct 25 15:21 mysql-5.7.22-linux-glibc2.12-x86_64
[root@vm1 local]# 

添加环境变量

[root@vm1 local]# echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
[root@vm1 local]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@vm1 local]# source /etc/profile.d/mysql.sh 
[root@vm1 local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@vm1 local]# 

建立数据存放目录

[root@vm1 local]# mkdir /opt/mysql_data
[root@vm1 local]# chown -R mysql.mysql /opt/mysql_data/
[root@vm1 local]# ll /opt/mysql_data/ -d
drwxr-xr-x. 2 mysql mysql 6 Oct 25 15:37 /opt/mysql_data/

初始化数据库

[root@vm1 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql_data/
2020-10-25T07:42:17.677524Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-25T07:42:18.017186Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-25T07:42:18.095020Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-25T07:42:18.110566Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9b9506e2-1695-11eb-8a78-000c29d6e1c9.
2020-10-25T07:42:18.112445Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-10-25T07:42:18.114197Z 1 [Note] A temporary password is generated for root@localhost: 2CJJ,NgX6Dg5
##临时密码2CJJ,NgX6Dg5,密码随机

生成配置文件

[root@vm1 local]# vi /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /opt/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve

添加到service服务

[root@vm1 support-files]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@vm1 support-files]# sed -ri ‘s#^(basedir=).*#1/usr/local/mysql#g‘ /etc/init.d/mysqld
[root@vm1 support-files]# sed -ri ‘s#^(datadir=).*#1/opt/mysql_data#g‘ /etc/init.d/mysqld

启动mysql服务

[root@vm1 init.d]# service mysqld start
Starting MySQL.Logging to ‘/opt/mysql_data/vm1.localdomain.err‘.
.. ERROR! The server quit without updating PID file (/opt/mysql_data/vm1.localdomain.pid).

##启动失败,重新初始化
[root@vm1 mysql_data]# /usr/local/mysql/bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/opt/mysql_data/ 
2020-10-26 00:38:57 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2020-10-26 00:39:00 [WARNING] The bootstrap log isn‘t empty:
2020-10-26 00:39:00 [WARNING] 2020-10-25T16:38:57.381349Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

##删除/opt/mysql_data/*,使用mysqld --initialize重新初始化
[root@vm1 mysql_data]# rm -rf *
[root@vm1 mysql_data]# 
[root@vm1 mysql_data]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/opt/mysql_data/ 
2020-10-25T16:40:43.194427Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-10-25T16:40:43.554603Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-10-25T16:40:43.637823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-10-25T16:40:43.653065Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d32f9d09-16e0-11eb-ab7d-000c29d6e1c9.
2020-10-25T16:40:43.655116Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed‘ cannot be opened.
2020-10-25T16:40:43.656976Z 1 [Note] A temporary password is generated for root@localhost: 48q(pej35,Bp
[root@vm1 mysql_data]# ls
auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  sys
[root@vm1 mysql_data]# service mysqld start
Starting MySQL.Logging to ‘/opt/mysql_data/vm1.localdomain.err‘.
 SUCCESS! 
[root@vm1 tmp]# ss -antl
State           Recv-Q          Send-Q                     Local Address:Port                     Peer Address:Port          
LISTEN          0               128                              0.0.0.0:22                            0.0.0.0:*             
LISTEN          0               80                                     *:3306                                *:*             
LISTEN          0               128                                 [::]:22                               [::]:*             
[root@vm1 tmp]# ps -ef|grep mysqld
root        1710       1  0 00:41 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/mysql_data --pid-file=/opt/mysql_data/vm1.localdomain.pid
mysql       1805    1710  0 00:41 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=vm1.localdomain.err --pid-file=/opt/mysql_data/vm1.localdomain.pid
root        1850    1199  0 00:49 pts/0    00:00:00 grep --color=auto mysqld
[root@vm1 tmp]# 

登陆mysql并修改root密码

[root@vm1 ~]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@vm1 ~]# yum install libncurses* -y 
......................................
Installed:
  ncurses-c++-libs-6.1-7.20180224.el8.x86_64   ncurses-compat-libs-6.1-7.20180224.el8.x86_64  

Complete!
[root@vm1 mysql_data]# mysql -uroot -p
Enter password:                          ##初始化生成的临时密码
ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/tmp/mysql.sock‘ (2)

##创建软连接
[root@vm1 tmp]# ln -s /opt/mysql_data/mysql.sock /tmp/mysql.sock
[root@vm1 tmp]# ll
total 4
-rwx------. 1 root root 1379 Oct 22 10:19 ks-script-7li_m2mk
lrwxrwxrwx. 1 root root   26 Oct 26 01:07 mysql.sock -> /opt/mysql_data/mysql.sock

##初密码登陆后修改密码
[root@vm1 tmp]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘h‘ for help. Type ‘c‘ to clear the current input statement.

mysql> ^C
mysql> set password=password(‘123456‘);
Query OK, 0 rows affected, 1 warning (0.01 sec)

2.1 备份方案

备份方案 特点
全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。
数据恢复快。
备份时间长
增量备份 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份
与前一次相比增加和者被修改的文件
没有重复的备份数据
备份时间短
恢复数据时必须按一定的顺序进行
差异备份 备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时
我们只需对第一次全量备份和最后一次差异备份进行恢复。

2.2 备分工具mysqldump

//语法:
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS:
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机,请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307

2.3 备份与恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wisan_db           |
+--------------------+
mysql> show tables;
+--------------------+
| Tables_in_wisan_db |
+--------------------+
| student_info       |
| student_score      |
+--------------------+
2 rows in set (0.00 sec)
mysql> select * from student_info;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | xiang |   18 |
|  2 | peng  |   19 |
|  3 | fan   |   17 |
|  4 | yi    |   20 |
|  5 | jing  | NULL |
|  6 | wisan |   19 |
|  7 | flora |   18 |
|  8 | wang  | NULL |
mysql> select * from student_score;
+----+-----------+-------+-------+
| id | name      | score | grade |
+----+-----------+-------+-------+
|  2 | peng      |    88 | A     |
|  3 | fan       |    90 | A     |
|  5 | jing      |   100 | A     |
|  6 | wisan     |  NULL | C     |
|  7 | flora     |    85 | B     |
|  8 | wang      |  NULL | C     |
| 10 | lisi      |    60 | B     |
| 11 | biyue     |    85 | B     |
| 13 | zhangshan |    70 | B     |
| 15 | qiuwu     |    70 | B     |
+----+-----------+-------+-------+
##全量备份
[root@vm1 ~]# mysqldump -uroot -p  --all-databases > all-vm1.sql
Enter password: 
[root@vm1 ~]# ls
all-vm1.sql  anaconda-ks.cfg

##备份数据库的部分表
[root@vm1 ~]# mysqldump -uroot -p wisan_db student_info student_score > tb_student.sql
Enter password: 
[root@vm1 ~]# ls
all-vm1.sql  anaconda-ks.cfg  tb_student.sql

##备份数据库
[root@vm1 ~]# mysqldump -uroot -p  --databases wisan_db  > wisan_db.sql
Enter password: 
[root@vm1 ~]# ll
total 496
-rw-r--r--. 1 root root 491025 Oct 26 01:40 all-vm1.sql
-rw-------. 1 root root   1097 Oct 22 10:19 anaconda-ks.cfg
-rw-r--r--. 1 root root    788 Oct 26 01:46 tb_student.sql
-rw-r--r--. 1 root root   3137 Oct 26 01:49 wisan_db.sql

mysql> drop database wisan_db;
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

[root@vm1 ~]# mysql -uroot -p < all-vm1.sql 
Enter password: 
[root@vm1 ~]# mysql -uroot -p -e ‘show databases;‘
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wisan_db           |
+--------------------+

2.4 差异备份

[root@vm1 ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /opt/mysql_data/mysql.sock
port=3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve

server-id=1
log-bin=mysql_bin       ##开启二进制功能
 
##数据同上
[root@vm1 ~]# mysqldump -uroot -p970801 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20201026.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@vm1 ~]# ll
total 496
-rw-r--r--. 1 root root   1251 Oct 26 01:57 all-20201026.sql
-rw-r--r--. 1 root root 491025 Oct 26 01:40 all-vm1.sql
-rw-------. 1 root root   1097 Oct 22 10:19 anaconda-ks.cfg
-rw-r--r--. 1 root root    788 Oct 26 01:46 tb_student.sql
-rw-r--r--. 1 root root   3137 Oct 26 01:49 wisan_db.sql

[root@vm1 opt]# mysql -uroot -p
Enter password: 
ERROR 1524 (HY000): Plugin ‘0‘ is not loaded

mysql-备份与恢复-03

原文地址:https://www.cnblogs.com/fyjpeng/p/13876726.html

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯