mysql多实例部署

w2535636586

已于 2022-08-03 08:35:19 修改

阅读量37

点赞数

文章标签： mysql

于 2022-08-01 09:49:42 首次发布

本文链接：https://blog.csdn.net/w2535636586/article/details/126094945

版权

mysql数据库备份与恢复

数据库常用备份方案

数据库备份方案：

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

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

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

//备份整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use ray;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# ls
all-20220728165001.sql  anaconda-ks.cfg
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-201808131500.sql
Enter password: 
[root@localhost ~]# ls
all-201808131500.sql  all-20220728165001.sql  anaconda-ks.cfg

//备份ray库中的shuaige表和shabi表
mysql> use ray;
Database changed
mysql> show tables;
+---------------+
| Tables_in_ray |
+---------------+
| shabi         |
| shuaige       |
+---------------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 ray shuaige shabi > table-201808131500.sql
Enter password: 
[root@localhost ~]# ls
all-201808131500.sql  all-20220728165001.sql  anaconda-ks.cfg  table-201808131500.sql

//备份ray库
[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --databases ray > wq-201808131500.sql
Enter password: 
[root@localhost ~]# ls
all-201808131500.sql    anaconda-ks.cfg         wq-201808131500.sql
all-20220728165001.sql  table-201808131500.sql
//模拟误删ray数据库
mysql> drop database ray;
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)

mysql>

mysql数据恢复

//恢复ray数据库
[root@localhost ~]# ls
all-201808131500.sql    anaconda-ks.cfg         wq-201808131500.sql
all-20220728165001.sql  table-201808131500.sql
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 < wq-201808131500.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
//恢复ray数据库的shuaige表和shabi表
mysql> use ray
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> source table-201808131500.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_ray |
+---------------+
| shabi         |
| shuaige       |
+---------------+
2 rows in set (0.00 sec)

mysql> 
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database ray;
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 ~]# ls
all-201808131500.sql    anaconda-ks.cfg         wq-201808131500.sql
all-20220728165001.sql  table-201808131500.sql
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 < all-201808131500.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+

差异备份与恢复

mysql差异备份

开启MySQL服务器的二进制日志功能

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

server-id=1
log-bin=mysql_bin
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

对数据库进行完全备份

[root@localhost ~]# mysql -uroot -p123456
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 2
Server version: 5.7.38-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
//完全备份
[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902211406.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 1724
-rw-r--r--. 1 root root 875351 Aug  1 08:54 all-201808131500.sql
-rw-r--r--. 1 root root 875503 Aug  1 09:26 all-201902211406.sql
-rw-r--r--. 1 root root      0 Jul 28 18:28 all-20220728165001.sql
-rw-------. 1 root root   1082 Jun 28 14:14 anaconda-ks.cfg
-rw-r--r--. 1 root root   2402 Aug  1 09:01 table-201808131500.sql
-rw-r--r--. 1 root root   2538 Aug  1 09:04 wq-201808131500.sql

mysql差异备份恢复

模拟误删数据

[root@localhost ~]# mysql -uroot -p123456 -e 'drop database ray;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

刷新创建新的二进制日志

[root@localhost ~]# ll /opt/data/
total 123004
-rw-r-----. 1 mysql mysql    31463 Jul 28 18:02 192.168.253.130.err
-rw-r-----. 1 mysql mysql       56 Jul 27 19:53 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 client-key.pem
-rw-r-----. 1 mysql mysql      820 Aug  1 09:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug  1 09:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:53 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:26 ibtmp1
-rw-r-----. 1 mysql mysql    12111 Aug  1 09:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug  1 09:10 mysql
-rw-r-----. 1 mysql mysql      302 Aug  1 09:32 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 Aug  1 09:26 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Aug  1 09:14 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:53 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 sys
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 123008
-rw-r-----. 1 mysql mysql    31463 Jul 28 18:02 192.168.253.130.err
-rw-r-----. 1 mysql mysql       56 Jul 27 19:53 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 client-key.pem
-rw-r-----. 1 mysql mysql      820 Aug  1 09:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug  1 09:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:53 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:26 ibtmp1
-rw-r-----. 1 mysql mysql    12111 Aug  1 09:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug  1 09:10 mysql
-rw-r-----. 1 mysql mysql      349 Aug  1 09:34 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 Aug  1 09:34 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Aug  1 09:34 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Aug  1 09:14 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:53 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 sys
[root@localhost ~]#

恢复完全备份

[root@localhost ~]# ls
all-201808131500.sql  all-20220728165001.sql  table-201808131500.sql
all-201902211406.sql  anaconda-ks.cfg         wq-201808131500.sql
[root@localhost ~]# mysql -uroot -p123456 < all-201902211406.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| ray                |
| sys                |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -e 'show tables from ray'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#

恢复差异备份

[root@localhost ~]# ll /opt/data/
total 124028
-rw-r-----. 1 mysql mysql    31463 Jul 28 18:02 192.168.253.130.err
-rw-r-----. 1 mysql mysql       56 Jul 27 19:53 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 client-key.pem
-rw-r-----. 1 mysql mysql      820 Aug  1 09:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:37 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Aug  1 09:37 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 19:53 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug  1 09:26 ibtmp1
-rw-r-----. 1 mysql mysql    12111 Aug  1 09:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Aug  1 09:37 mysql
-rw-r-----. 1 mysql mysql      349 Aug  1 09:34 mysql_bin.000002
-rw-r-----. 1 mysql mysql   854795 Aug  1 09:37 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 Aug  1 09:34 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Aug  1 09:14 mysql.pid
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 19:53 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 19:53 public_key.pem
drwxr-x---. 2 mysql mysql       20 Aug  1 09:37 ray
-rw-r--r--. 1 mysql mysql     1112 Jul 27 19:53 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 19:53 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 19:53 sys
//检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.38-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 219 | Query          |         1 |         302 | drop database ray                     |
| mysql_bin.000002 | 302 | Rotate         |         1 |         349 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
5 rows in set (0.00 sec)

//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=219 /opt/data/mysql_bin.000002 |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.