国产成人精品18,久久99国产精品成人欧美,天天五月天丁香婷婷深爱综合

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

一、MySQL Shell 安裝

1、下載

可以在MySQL官網(wǎng)進(jìn)行下載，地址https://dev.mysql.com/downloads/shell/。

需要根據(jù)操作系統(tǒng)類(lèi)型、版本及glibc版本選擇對(duì)應(yīng)的文件下載，例如：

[root@VM-4-14-centos ~]# uname  -a 
Linux VM-4-14-centos 3.10.0-1160.99.1.el7.x86_64 #1 SMP Wed Sep 13 14:19:20 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
[root@VM-4-14-centos ~]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

因此可以選擇如下版本下載。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

2、部署

上傳文件至目標(biāo)目錄后解壓文件。

解壓后建議配置軟鏈接。

tar -zxvf mysql-shell-8.0.35-linux-glibc2.17-x86-64bit.tar.gz

可以看到對(duì)應(yīng)的工具了。

ln -s mysql-shell-8.0.35-linux-glibc2.17-x86-64bit mysql-shell

建議再配置一下環(huán)境變量。

將“/usr/local/mysql-shell/bin"追加至/etc/profile中。

在其他位置直接運(yùn)行mysqlsh命令，即可得到如下結(jié)果：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

此時(shí)，完成了mysql shell部署。

二、進(jìn)行數(shù)據(jù)庫(kù)備份

1、登錄數(shù)據(jù)庫(kù)

使用mysqlsh登錄數(shù)據(jù)庫(kù)，并列出庫(kù)名，例如：

[root@VM-4-14-centos ~]# mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock
Please provide the password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock': *********
Save password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): 
MySQL Shell 8.0.35
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 10
Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  localhost  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.0008 sec)
 MySQL  localhost  SQL >

其中登錄語(yǔ)法為：

mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock

輸入密碼后即可登錄成功，輸入密碼后會(huì)確認(rèn)是否保存密碼，建議選擇No(默認(rèn)值）。

登錄成功后，可以選擇\sql ，即SQL命令模式。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

2、備份整個(gè)實(shí)例

創(chuàng)建備份目錄。

mkdir -p /data/backup

登錄數(shù)據(jù)庫(kù)。

mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock

登錄后是在js模式下，備份數(shù)據(jù)是在JS模式下進(jìn)行，因此不用切換。

備份整個(gè)實(shí)例

MySQL  localhost  JS > util.dumpInstance("/data/backup")
Acquiring global read lock
Global read lock acquired
Initializing - done 
2 out of 6 schemas will be dumped and within them 12 tables, 0 views.
2 out of 5 users will be dumped.
Gathering information - done 
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
129% (870 rows / ~670 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s                                                            
Total duration: 00:00:00s                                                           
Schemas dumped: 2                                                                   
Tables dumped: 12                                                                   
Uncompressed data size: 14.06 MB                                                    
Compressed data size: 4.88 MB                                                       
Compression ratio: 2.9                                                              
Rows written: 870                                                                   
Bytes written: 4.88 MB                                                              
Average uncompressed throughput: 14.06 MB/s                                         
Average compressed throughput: 4.88 MB/s                                            
MySQL  localhost  JS >

如無(wú)異常，即完成了實(shí)例備份。可見(jiàn)，備份效率比較高（4線程處理）。

備份后，備份目錄結(jié)果里可以查看結(jié)果如下：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

其中的主要文件解釋?zhuān)?/span>

@.done.json： 該文件記錄了備份結(jié)束時(shí)間，每個(gè)庫(kù)下每個(gè)表的大小等信息，例如：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

@.json：該文件記錄了客戶(hù)端版本，備份類(lèi)型（實(shí)例、庫(kù)或表等），元數(shù)據(jù)信息以及binlog信息（點(diǎn)位及GTID）。例如：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

@.sql, @.post.sql：這兩個(gè)文件記錄注釋信息. 導(dǎo)入數(shù)據(jù)時(shí), 我們可以通過(guò)這兩個(gè)文件自定義的SQL. 在數(shù)據(jù)導(dǎo)入前和數(shù)據(jù)導(dǎo)入后執(zhí)行，本次為全量備份，因此只有版本等注釋信息。

庫(kù)名.json： 記錄的是對(duì)應(yīng)庫(kù)名、表等信息。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

庫(kù)名.sql: 具體的建庫(kù)SQL腳本。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

庫(kù)名@表名.json:記錄了對(duì)于的表的元數(shù)據(jù)信息，包括庫(kù)名，表名，字段名，主鍵等信息。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

庫(kù)名@表名.sql: 具體的建表SQL腳本。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

庫(kù)名@表名@@*.tsv.zst： 具體數(shù)據(jù)文件。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

庫(kù)名@表名@@*.tsv.zst.idx： 具體索引文件。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

@.users.sql : 數(shù)據(jù)庫(kù)用戶(hù)信息，包含創(chuàng)建用戶(hù)以及授權(quán)的SQL腳本。

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

3、備份指定庫(kù)

創(chuàng)建備份目錄：重新創(chuàng)建一個(gè)專(zhuān)用于備份指定庫(kù)的目錄。

mkdir -p data/backup/backup_schemas

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

使用shellsh登錄數(shù)據(jù)庫(kù)，并查看當(dāng)前有哪些庫(kù)。

# mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock
Please provide the password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock': *********
Save password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): 
MySQL Shell 8.0.35
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 28
Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  localhost  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.0010 sec)

備份指定的庫(kù)（schema）,如果多個(gè)庫(kù)，則用逗號(hào)分隔。

MySQL  localhost  SQL > \js
Switching to JavaScript mode...
 MySQL  localhost  JS > util.dumpSchemas(['testdb'],'/data/backup/backup_schemas')
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 schemas will be dumped and within them 11 tables, 0 views.
Gathering information - done 
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done         
Writing table metadata - done         
Starting data dump
130% (862 rows / ~662 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s                                                            
Total duration: 00:00:00s                                                           
Schemas dumped: 1                                                                   
Tables dumped: 11                                                                   
Uncompressed data size: 14.06 MB                                                    
Compressed data size: 4.88 MB                                                       
Compression ratio: 2.9                                                              
Rows written: 862                                                                   
Bytes written: 4.88 MB                                                              
Average uncompressed throughput: 14.06 MB/s                                         
Average compressed throughput: 4.88 MB/s                                            
 MySQL  localhost  JS >

以上則備份完成。

到對(duì)于目錄下查看備份結(jié)果如下：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！

4、備份指定表

再次先創(chuàng)建目錄。

[root@VM-4-14-centos ~]# mkdir -p /data/backup/backup_tables
[root@VM-4-14-centos ~]# cd /data/backup/backup_tables
[root@VM-4-14-centos backup_tables]#

登錄數(shù)據(jù)庫(kù)，并查看庫(kù)及表名。

[root@VM-4-14-centos backup_tables]# mysqlsh -u root  -p -S /data/mysql/mysql3306/tmp/mysql.sock
Please provide the password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock': *********
Save password for 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): 
MySQL Shell 8.0.35
Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
Creating a session to 'root@/data%2Fmysql%2Fmysql3306%2Ftmp%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 35
Server version: 8.0.33-25 Percona Server (GPL), Release 25, Revision 60c9e2c5
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  localhost  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.0009 sec)
 MySQL  localhost  SQL > use testdb1
Default schema set to `testdb1`.
Fetching global names, object names from `testdb1` for auto-completion... Press ^C to stop.
 MySQL  localhost  testdb1  SQL > show tables;
+-------------------+
| Tables_in_testdb1 |
+-------------------+
| test1             |
+-------------------+
1 row in set (0.0014 sec)
 MySQL  localhost  testdb1  SQL >

進(jìn)行指定表的備份，如果多個(gè)表，則表名用逗號(hào)分隔。

MySQL  localhost  testdb1  JS >  util.dumpTables('testdb1',['test1'],'/data/backup/backup_tables')
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 tables and 0 views will be dumped.
Gathering information - done 
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done       
Writing table metadata - done       
Starting data dump
100% (8 rows / ~8 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s                                                        
Total duration: 00:00:00s                                                       
Schemas dumped: 1                                                               
Tables dumped: 1                                                                
Uncompressed data size: 157 bytes                                               
Compressed data size: 90 bytes                                                  
Compression ratio: 1.7                                                          
Rows written: 8                                                                 
Bytes written: 90 bytes                                                         
Average uncompressed throughput: 157.00 B/s                                     
Average compressed throughput: 90.00 B/s                                        
 MySQL  localhost  testdb1  JS >

查看備份結(jié)果：

轉(zhuǎn)戰(zhàn)MySQL Shell！數(shù)據(jù)庫(kù)備份新姿勢(shì)，輕松搞定備份操作！