ai换脸刘亦菲 > yinpinse >

ai 裸舞 xtrabackup整库全备规复单库

发布日期：2025-01-10 10:54 点击次数：143

ai 裸舞 xtrabackup整库全备规复单库

1. 创建测试环境

树立测试库ai 裸舞

mysql> create database testa;
Query OK， 1 row affected (0.00 sec)

mysql> create database testb;
Query OK， 1 row affected (0.00 sec)

mysql> create database testc;
Query OK， 1 row affected (0.00 sec)

mysql> create database testd;
Query OK， 1 row affected (0.01 sec)

为每个库创建测试表

天堂在线

mysql> use testa;
Database changed
mysql> CREATE TABLE `test` (                                 
 
           `id` int(11) NOT NULL AUTO_INCREMENT，               
 
           `str` varchar(255) NOT NULL，                                    
 
           `state` tinyint(1) unsigned zerofill DEFAULT NULL，  
 
           `state2` tinyint(2) unsigned zerofill DEFAULT NULL， 
 
           `state3` tinyint(3) unsigned zerofill DEFAULT NULL， 
 
           `state4` tinyint(4) unsigned zerofill DEFAULT NULL， 
 
           PRIMARY KEY (`id`)                                  
 
         ) ;
Query OK， 0 rows affected (0.14 sec)

mysql> insert test(id，str) values (1，'zzl');
Query OK， 1 row affected (0.01 sec)

mysql> commit;
Query OK， 0 rows affected (0.00 sec)

--其余三个库作念交流操作，插入数据条目无领域
**testb:**
mysql> insert test(id，str，state) values (2，'zzl'，2);
Query OK， 1 row affected (0.00 sec)

mysql> commit;
Query OK， 0 rows affected (0.00 sec)

**testc：**

mysql> insert test(id，str，state，state2) values (3，'zzl'，3，3);
Query OK， 1 row affected (0.00 sec)

mysql> commit;
Query OK， 0 rows affected (0.00 sec)

**testd：**

mysql> insert test(id，str，state，state2，state3) values (4，'zzl'，4，4，4);
Query OK， 1 row affected (0.00 sec)

mysql> commit;
Query OK， 0 rows affected (0.01 sec)
图片

测试环境如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testa              |
| testb              |
| testc              |
| testd              |
| work               |
+--------------------+
10 rows in set (0.00 sec)

---表信息
mysql> select * from test; --testa
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  1 | zzl |  NULL |   NULL |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testb
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  2 | zzl |     2 |   NULL |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testc
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  3 | zzl |     3 |     03 |   NULL |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from test; --testd
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  4 | zzl |     4 |     04 |    004 |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

2. 测试进程

PERCONA XTRABACKUP 装置次序官网搬运：

！$ yum -y install libev　　#先装置libev包，短缺会导致失败报错。!

第一种次序 $ sudo root yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm #装置Percona yum存储库 $ yum install percona-xtrabackup-24　　#装置percona-xtrabackup 备份器具 --介怀MySQL8不再被xtrabackup24撑握

第二种次序 $ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/ 　　#下载刊行包 $ yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm　　#装置刊行包！可能会报短缺依赖包，短缺什么yum install 什么！

第三种次序 $ yum install cmake gcc gcc-c ++ libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel vim $ git clone https://github.com/percona/percona-xtrabackup.git　 $ cd percona-xtrabackup $ cmake -DBUILD_CONFIG = xtrabackup_release -DWITH_MAN_PAGES = OFF && make -j4 $ make install

1）细目装置软件包

[root@mysql8 ~]# rpm -qa | grep xtrabackup
percona-xtrabackup-80-8.0.14-1.el7.x86_64

2）创建备份目次

[root@mysql8 ~]# mkdir /data/backup -p
[root@mysql8 ~]# mkdir /data/backupInc -p
[root@mysql8 ~]# chown -R mysql.mysql /data/

3）创建备份用户

mysql> create user 'xtra'@'%' identified by '1234';
Query OK， 0 rows affected (0.01 sec)

mysql> grant backup_admin，process，reload，lock tables，replication client on *.* to 'xtra'@'%';
Query OK， 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON performance_schema.log_status TO 'xtra'@'%';
Query OK， 0 rows affected (0.13 sec)

mysql> flush privileges;
Query OK， 0 rows affected (0.01 sec)

4）实施全备

[root@mysql8 backup]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --backup --parallel=3 --target-dir=/data/backup/
...
201118 12:00:32 [00]        ...done
201118 12:00:32 Backup created in directory '/data/backup/'
MySQL binlog position: filename 'mysql-bin.000040'， position '195'， GTID of the last change '32415cb9-d5ff-11ea-a561-000c29135205:1-104'
201118 12:00:32 [00] Writing /data/backup/backup-my.cnf
201118 12:00:32 [00]        ...done
201118 12:00:32 [00] Writing /data/backup/xtrabackup_info
201118 12:00:32 [00]        ...done
xtrabackup: Transaction log of lsn (212703909) to (212707801) was copied.
201118 12:00:34 completed OK!

5）稽查现时备份文献

[root@mysql8 backup]# ll
total 1577028
-rw-r----- 1 root root        521 Nov 18 12:00 backup-my.cnf
-rw-r----- 1 root root      24064 Nov 18 12:00 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Nov 18 12:00 ibdata1
-rw-r----- 1 root root  419430400 Nov 18 12:00 ibdata2
drwxr-x--- 2 root root        143 Nov 18 12:00 mysql
-rw-r----- 1 root root        195 Nov 18 12:00 mysql-bin.000040
-rw-r----- 1 root root         45 Nov 18 12:00 mysql-bin.index
-rw-r----- 1 root root   25165824 Nov 18 12:00 mysql.ibd
drwxr-x--- 2 root root       4096 Nov 18 12:00 performance_schema
drwxr-x--- 2 root root         28 Nov 18 12:00 sys
drwxr-x--- 2 root root        148 Nov 18 12:00 test
drwxr-x--- 2 root root         22 Nov 18 12:00 testa
drwxr-x--- 2 root root         22 Nov 18 12:00 testb
drwxr-x--- 2 root root         22 Nov 18 12:00 testc
drwxr-x--- 2 root root         22 Nov 18 12:00 testd
-rw-r----- 1 root root   54525952 Nov 18 12:00 undo_001
-rw-r----- 1 root root   41943040 Nov 18 12:00 undo_002
drwxr-x--- 2 root root         39 Nov 18 12:00 work
-rw-r----- 1 root root         64 Nov 18 12:00 xtrabackup_binlog_info
-rw-r----- 1 root root         97 Nov 18 12:00 xtrabackup_checkpoints
-rw-r----- 1 root root        645 Nov 18 12:00 xtrabackup_info
-rw-r----- 1 root root       6144 Nov 18 12:00 xtrabackup_logfile
-rw-r----- 1 root root         39 Nov 18 12:00 xtrabackup_tablespaces

6）进行删库误操作

mysql> drop database testa;
Query OK， 1 row affected (0.03 sec)

mysql> drop database testb;
Query OK， 1 row affected (0.02 sec)

mysql> drop database testc;
Query OK， 1 row affected (0.01 sec)

7）将备份文献中不需要规复的库移出目次

[root@mysql8 backup]# mkdir /data/xtratest
[root@mysql8 backup]# mv testa /data/xtratest/
[root@mysql8 backup]# mv testb /data/xtratest/

8）期骗日记准备

[root@mysql8 backup]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --apply-log-only --target-dir=/data/backup
Log background threads are being closed...
Shutdown completed; log sequence number 212707801
Number of pools: 1
201118 14:11:46 completed OK

[root@mysql8 data]#  xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --target-dir=/data/backup
Shutdown completed; log sequence number 212707852
201118 14:13:24 completed OK!

9）清空数据文献，日记文献，undo文献，将干系文献拷贝回指定目次

报错信息如下：
未计帐数据文献：
 [root@mysql8 data]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
Original data directory /data/mysql/mysql_3306/data/ is not empty!

未计帐日记文献： 
[root@mysql8 undolog]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
201118 14:22:35 [01] Copying undo_001 to /data/mysql/mysql_3306/undolog/undo_001
201118 14:22:35 [01]        ...done
201118 14:22:35 [01] Copying undo_002 to /data/mysql/mysql_3306/undolog/undo_002
201118 14:22:35 [01]        ...done
201118 14:22:35 [01] Copying ib_logfile0 to /data/mysql/mysql_3306/data/ib_logfile0
201118 14:22:41 [01]        ...done
201118 14:22:41 [01] Copying ib_logfile1 to /data/mysql/mysql_3306/data/ib_logfile1
201118 14:22:49 [01]        ...done
201118 14:22:51 [01] Copying ib_logfile2 to /data/mysql/mysql_3306/data/ib_logfile2
201118 14:23:24 [01]        ...done
201118 14:23:29 [01] Copying ibdata1 to /data/mysql/mysql_3306/data/ibdata1
201118 14:23:41 [01]        ...done
201118 14:23:41 [01] Copying ibdata2 to /data/mysql/mysql_3306/data/ibdata2
201118 14:23:50 [01]        ...done
xtrabackup: Can't create/write to file '/data/mysql/mysql_3306/logs/mysql-bin.000040' (OS errno 17 - File exists)'
[01] error: cannot open the destination stream for mysql-bin.000040
[01] Error: copy_file() failed. 

未计帐undo：
[root@mysql8 data]# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --host=192.168.103.67 --user=xtra --password=1234 --port=3306 --datadir=/data/mysql/mysql_3306/data/ --copy-back --target-dir=/data/backup/
xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql_3306/data --open_files_limit=65535 --server-id=3306 --log_bin=/data/mysql/mysql_3306/logs/mysql-bin --innodb_buffer_pool_size=2867M --innodb_data_file_path=ibdata1:1G;ibdata2:200M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=2G --innodb_log_files_in_group=3 --innodb_undo_directory=/data/mysql/mysql_3306/undolog --innodb_undo_tablespaces=95 --innodb_io_capacity=4000 --innodb_write_io_threads=8 --innodb_read_io_threads=8 --innodb_open_files=65535 --innodb_max_dirty_pages_pct=50 --innodb_flush_method=O_DIRECT --innodb_checksum_algorithm=crc32 --innodb_file_per_table=1 --datadir=/data/mysql/mysql_3306/data/ 
xtrabackup: recognized client arguments: --port=3306 --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock --host=192.168.103.67 --user=xtra --password=* --port=3306 --copy-back=1 --target-dir=/data/backup/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
xtrabackup: Can't create/write to file '/data/mysql/mysql_3306/undolog/undo_001''(OS errno 17 - File exists)
[01] error: cannot open the destination stream for undo_001
[01] Error: copy_file() failed.

10）规复后操作

—规复完成ai 裸舞后，介怀文献权限

[root@mysql8 data]# pwd
/data/mysql/mysql_3306/data
[root@mysql8 data]# chown -R mysql.mysql /data/

再行开动数据库

3. 校验数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| testa              |
| testb              |
| testc              |
| testd              |
| work               |
+--------------------+
10 rows in set (0.00 sec)

mysql> use testa;
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_testa |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)


mysql> select * from test;
ERROR 1812 (HY000): Tablespace is missing for table `testa`.`test`.
表结构信息存放在ibdata内部，同样会被规复关联词没罕有据

mysql> show tables;
+-----------------+
| Tables_in_testd |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-----+-------+--------+--------+--------+
| id | str | state | state2 | state3 | state4 |
+----+-----+-------+--------+--------+--------+
|  4 | zzl |     4 |     04 |    004 |   NULL |
+----+-----+-------+--------+--------+--------+
1 row in set (0.00 sec)

规复完成

本站仅提供存储管事，悉数执行均由用户发布，如发现存害或侵权执行，请点击举报。

友情链接：

ai换脸 刘亦菲

ai换脸 刘亦菲 > yinpinse >

ai 裸舞 xtrabackup整库全备规复单库

ai换脸刘亦菲

ai换脸刘亦菲 > yinpinse >