mysql8.0.35数据库备份与恢复和多实例部署

懵逼的大鹅

已于 2024-06-11 14:18:42 修改

阅读量256

点赞数 5

文章标签：数据库 adb

于 2024-04-03 10:51:41 首次发布

本文链接：https://blog.csdn.net/2301_79638098/article/details/137335227

版权

文章目录

1.数据库常用备份方案
2 .mysql备份工具mysqldump
3.mysql数据库备份实例
多实例安装

1.数据库常用备份方案

数据库备份方案：

全量备份
增量备份
差异备份

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

2 .mysql备份工具mysqldump

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

3.mysql数据库备份实例

3. 1.全量备份与恢复

1.查看有哪些库，并使用全量备份备份test库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| product          |
| product_category |
+------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -uroot -p1  --databases test > /test/test-$(date '+%Y%m%d')-01 //全量备份到一个叫test-当前时间-01的文件中
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /test
test-20231208-01
2.模拟误删测试全量备份是否成功
mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p1 < /test/test-20231208-01 //恢复全量备份内容
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| product          |
| product_category |
+------------------+
2 rows in set (0.01 sec)

3.2. 差异备份与恢复

1.对数据库test进行全量备份
[root@localhost ~]# mysqldump -uroot -p1 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > /test/all-$(date '+%Y%m%d')-01.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
WARNING: --delete-master-logs is deprecated and will be removed in a future version. Use --delete-source-logs instead.
[root@localhost ~]# ls /test/
all-20231208-01.sql

2.增加新内容
mysql> select * from product_category;
+---------------+-----------+
| category_name | parent_id |
+---------------+-----------+
| 无线鼠标      |         1 |
| 无线键盘      |         2 |
| 蓝牙耳机      |         3 |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> insert into product_category values('有线耳机',4)
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from product_category;
+---------------+-----------+
| category_name | parent_id |
+---------------+-----------+
| 无线鼠标      |         1 |
| 无线键盘      |         2 |
| 蓝牙耳机      |         3 |
| 有线耳机      |         4 |
+---------------+-----------+
4 rows in set (0.00 sec)

3.删除test数据库
mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)

4.刷新创建新的二进制日志
[root@localhost data]# mysqladmin -uroot -p1 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls
mysql_bin.000003
mysql_bin.000004 

5.恢复完全备份
[root@localhost ~]# mysql -uroot -p1 < /test/all-20231208-01.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| product          |
| product_category |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from product_category;
+---------------+-----------+
| category_name | parent_id |
+---------------+-----------+
| 无线鼠标      |         1 |
| 无线键盘      |         2 |
| 蓝牙耳机      |         3 |
+---------------+-----------+
3 rows in set (0.00 sec)

3.3.恢复差异备份

//检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                               |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------+
| mysql_bin.000003 |    4 | Format_desc    |         1 |         126 | Server ver: 8.0.35, Binlog ver: 4                                                                  |
| mysql_bin.000003 |  126 | Previous_gtids |         1 |         157 |                                                                                                    |
.......

| mysql_bin.000003 | 1413 | Xid            |         1 |        1444 | COMMIT /* xid=1380 */                                                                              |
| mysql_bin.000003 | 1444 | Anonymous_Gtid |         1 |        1521 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                               |
| mysql_bin.000003 | 1521 | Query          |         1 |        1625 | drop database test /* xid=1382 */                                                                  |
| mysql_bin.000003 | 1625 | Rotate         |         1 |        1672 | mysql_bin.000004;pos=4
//使用mysqlbinlog恢复差异备份
[root@localhost ~]#  mysqlbinlog --stop-position=1521 /opt/data/mysql_bin.000003 | mysql -uroot -p1
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from product_category;
+---------------+-----------+
| category_name | parent_id |
+---------------+-----------+
| 无线鼠标      |         1 |
| 无线键盘      |         2 |
| 蓝牙耳机      |         3 |
| 有线耳机      |         4 |
+---------------+-----------+
4 rows in set (0.00 sec)

多实例安装

//下载二进制格式的mysql软件包
[root@localhost ~]# ls
anaconda-ks.cfg    mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz

//创建系统用户
[root@localhost ~]# useradd -r -s /sbin/nologin -M mysql
[root@localhost ~]# id mysql
uid=990(mysql) gid=990(mysql) groups=990(mysql)

//解压安装包至/usr/local
[root@localhost ~]# tar xf mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin  games    lib    libexec  mysql-8.0.35-linux-glibc2.28-x86_64  share
etc  include  lib64  mysql    sbin
[root@localhost local]# mv mysql-8.0.35-linux-glibc2.28-x86_64 mysql
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

//修改目录的属主和属组
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 16  2022 bin
drwxr-xr-x. 2 root  root    6 May 16  2022 etc
drwxr-xr-x. 2 root  root    6 May 16  2022 games
drwxr-xr-x. 2 root  root    6 May 16  2022 include
drwxr-xr-x. 2 root  root    6 May 16  2022 lib
drwxr-xr-x. 3 root  root   17 Dec  5 17:33 lib64
drwxr-xr-x. 2 root  root    6 May 16  2022 libexec
drwxr-xr-x. 9 mysql mysql 129 Dec  5 18:03 mysql
drwxr-xr-x. 2 root  root    6 May 16  2022 sbin
drwxr-xr-x. 5 root  root   49 Dec  5 17:33 share
drwxr-xr-x. 2 root  root    6 May 16  2022 src

//配置环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql

//创建各实例数据的存放目录
[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# ls /opt/data/
3306            3307            3308

//初始化各实例
1.初始化3306
[root@localhost ~]# mysql --initialize --user=mysql --datadir=/opt/data/3306
mysql: [ERROR] unknown option '--initialize'.
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3306
2023-12-09T13:46:43.410969Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 1719
2023-12-09T13:46:43.495571Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-09T13:46:44.246612Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-09T13:46:51.063440Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: U8z_XMw;Vv!U
2.初始化3307
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3307
2023-12-09T13:48:46.663935Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 1768
2023-12-09T13:48:46.683859Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-09T13:48:47.502910Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-09T13:48:55.388502Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: XFB=+4wuzFVY
3.初始化3308
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/3308
2023-12-09T13:49:25.198157Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 1815
2023-12-09T13:49:25.310279Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-12-09T13:49:26.057123Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-12-09T13:49:30.551533Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: si.Xl>;cQ0lY

//安装perl
[root@localhost ~]# yum -y install perl
.....
Complete!

//配置配置文件
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

//启动各个实例
[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -antl
State   Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN  0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN  0        70                     *:33060                *:*              
LISTEN  0        151                    *:3306                 *:*              
LISTEN  0        151                    *:3307                 *:*              
LISTEN  0        151                    *:3308                 *:*              
LISTEN  0        128                 [::]:22                [::]:*

//修改密码
1.修改3306密码
[root@localhost ~]# mysql -uroot -p'U8z_XMw;Vv!U' -S /tmp/mysql3306.sock  -P3306
mysql> alter user root@localhost identified with mysql_native_password by '1' ;
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -uroot -p'1' -S /tmp/mysql3306.sock  -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>

2.修改3307密码
[root@localhost ~]# mysql -uroot -p'XFB=+4wuzFVY' -S /tmp/mysql3307.sock  -P3307
mysql> alter user root@localhost identified with mysql_native_password by '1'；
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p'1' -S /tmp/mysql3307.sock  -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> quit
Bye


3.修改3308密码
[root@localhost ~]# mysql -uroot -p'si.Xl>;cQ0lY' -S /tmp/mysql3308.sock  -P3308
mysql> alter user root@localhost identified with mysql_native_password by '1';
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysql -uroot -p'1' -S /tmp/mysql3308.sock  -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> 

//设置开机自启
1.3306
root@localhost ~]# cd /usr/lib/systemd/system
[root@localhost system]# cp sshd.service mysql3306.service
[root@localhost system]# vim mysql3306.service 
[root@localhost system]# cat mysql3306.service 
[Unit]
Description=mysql 3306 server daemon
After=network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/nysql start 3306
Execstop=kill -9 $(ps -ef |grep -v 'grep' | grep 3306 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

2.3307
[root@localhost system]# cp mysql3306.service mysql3307.service 
[root@localhost system]# vim mysql3307.service 
[root@localhost system]# cat mysql3307.service 
[Unit]
Description=mysql 3307 server daemon
After=network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/nysql start 3307
Execstop=kill -9 $(ps -ef |grep -v 'grep' | grep 3307 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

3.3308
[root@localhost system]# cp mysql3306.service mysql3308.service 
[root@localhost system]# vim mysql3308.service 
[root@localhost system]# cat mysql3308.service 
[Unit]
Description=mysql 3308 server daemon
After=network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/nysql start 3308
Execstop=kill -9 $(ps -ef |grep -v 'grep' | grep 3308 | awk '{print $2}')
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

[root@localhost system]# systemctl daemon-reload
[root@localhost system]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN   0        70                     *:33060                *:*              
LISTEN   0        151                    *:3306                 *:*              
LISTEN   0        151                    *:3307                 *:*              
LISTEN   0        151                    *:3308                 *:*              
LISTEN   0        128                 [::]:22                [::]:* 

//查看端口号是否能再次运行，先停掉端口号
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3306|awk '{print $2}')
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3307|awk '{print $2}')
[root@localhost system]# kill -9 $(ps -ef | grep -v 'grep'|grep 3308|awk '{print $2}')
[root@localhost system]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port     Peer Address:Port  Process   
LISTEN   0        128              0.0.0.0:22            0.0.0.0:*               
LISTEN   0        128                 [::]:22               [::]:* 

//设置软连接
[root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/
[root@localhost ~]# which my_print_defaults 
/usr/local/mysql/bin/my_print_defaults

//设置开机自启
[root@localhost ~]# systemctl enable mysql3306
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3306.service → /usr/lib/systemd/system/mysql3306.service.
[root@localhost ~]# systemctl enable mysql3307
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3307.service → /usr/lib/systemd/system/mysql3307.service.
[root@localhost ~]# systemctl enable mysql3308
Created symlink /etc/systemd/system/multi-user.target.wants/mysql3308.service → /usr/lib/systemd/system/mysql3308.service.

//重启虚拟机测试
[root@localhost ~]# reboot
[root@localhost system]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port  Process  
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*              
LISTEN   0        70                     *:33060                *:*              
LISTEN   0        151                    *:3306                 *:*              
LISTEN   0        151                    *:3307                 *:*              
LISTEN   0        151                    *:3308                 *:*              
LISTEN   0        128                 [::]:22                [::]:*