图文结合丨带你轻松玩转MySQL Shell for GreatSQL

$ cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)
$ uname -a
Linux hy 3.10.0-1160.el7.x86_64 #1 SMP Mon Oct 19 16:18:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

$ tar -xvf greatsql-shell-8.0.25-16-Linux-glibc2.17-x86_64.tar.xz

$ echo "export PATH=$PATH:/usr/local/greatsql-shell-8.0.25-16-Linux-glibc2.17-x86_64/bin" >> /root/.bash_profile

$ python3 -V
Python 3.6.8

$ mysqlsh

$ cat /root/.mysqlsh/mysqlsh.log

ModuleNotFoundError: No module named 'certifi'

$ pip3.6 install --user certifi

$ ls /usr/local/GreatSQLshell/greatsql-shell-8.0.25-16-Linux-glibc2.17-x86_64/share/mysqlsh/prompt
prompt_16.json      prompt_256.json       prompt_256pl.json    prompt_dbl_256.json       prompt_dbl_256pl.json  README.prompt
prompt_256inv.json  prompt_256pl+aw.json  prompt_classic.json  prompt_dbl_256pl+aw.json  prompt_nocolor.json

$ export MYSQLSH_PROMPT_THEME=/usr/local/GreatSQLshell/greatsql-shell-8.0.25-16-Linux-glibc2.17-x86_64/share/mysqlsh/prompt/prompt_16.json  

$ mysqlsh --help

...上面省略部分...
Usage examples:
$ mysqlsh root@localhost/schema
$ mysqlsh mysqlx://root@some.server:3307/world_x
$ mysqlsh --uri root@localhost --py -f sample.py sample param
$ mysqlsh root@targethost:33070 -s world_x -f sample.js
$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON
$ mysqlsh mysqlx://user@host/db --import ~/products.json shop

$ mysqlsh -S/data/GreatSQL/mgr01/mysql.sock root@localhost

Please provide the password for 'root@localhost': //这里输入密码
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): y //是否保存密码

GreatSQL Py > \sql
Switching to SQL mode... Commands end with ;

GreatSQL Py > \system ls /usr/local
greatsql-shell-8.0.25-16-Linux-glibc2.17-x86_64.tar.xz    GreatSQL8.0.32

GreatSQL Py > \history
    1  \system ls /usr/local
    2  /history
    3  history
    4  help()
    5  /hasattr()

greatsql> select count(*) from student1;
+----------+
| count(*) |
+----------+
|  7000000 |
+----------+
1 row in set (2.62 sec)

greatsql> system ls -l /data/GreatSQL/mgr01/test
-rw-r----- 1 mysql mysql 759169024 7月  25 12:15 student1.ibd

GreatSQL Py > \? dump_instance
The following options are supported://找到这句下面就是

Requirements
- MySQL Server 5.7 or newer is required.
- File size limit for files uploaded to the OCI bucket is 1.2 TiB.
- Columns with data types which are not safe to be stored in text form
  (i.e. BLOB) are converted to Base64, hence the size of such columns
  cannot exceed approximately 0.74 * max_allowed_packet bytes, as
  configured through that system variable at the target server.
- Schema object names must use latin1 or utf8 character set.
- Only tables which use the InnoDB storage engine are guaranteed to be
dumped with consistent data.

GreatSQL Py > util.dump_instance("/data/backups",{"compression": "none","threads":"16"})
Acquiring global read lock
Global read lock acquired
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
...中间省略
1 thds dumping - 109% (19.00M rows / ~17.37M rows), 263.77K rows/s, 54.42 MB/s            
Duration: 00:01:05s                                                           
Schemas dumped: 3                                                             
Tables dumped: 11                                                             
Data size: 3.05 GB                                                            
Rows written: 19000005                                                        
Bytes written: 3.05 GB                                                        
Average throughput: 46.80 MB/s

$ ll /data/backups/
#有好多这里就列举几个
-rw-r----- 1 root root     5773 8月   2 11:28 @.done.json
-rw-r----- 1 root root     1119 8月   2 11:27 @.json
-rw-r----- 1 root root      231 8月   2 11:27 @.post.sql
-rw-r----- 1 root root      231 8月   2 11:27 @.sql
-rw-r----- 1 root root      458 8月   2 11:27 test.json
-rw-r----- 1 root root 24536863 8月   2 11:27 test@student1@0.tsv

$ head -3 test@student1@0.tsv
1       Kathleen Ford   F       344 Jiangnan West Road, Haizhu District 139-1119-0424   163     lin4brNtHD      918
2       David Mitchell  M       355 Papworth Rd, Trumpington    5892 672144     702     qoA6axcT6u      218
3       Lin Yunxi       M       620 Hanover Street      7091 590385     194     Tl4LY3UmgY      765

GreatSQL Py > util.dump_schemas(["test"],"/data/backup_schemas",{"threads":"16"})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
...中间省略...
1 thds dumping - 109% (19.00M rows / ~17.37M rows), 530.98K rows/s, 49.34 MB/s uncompressed, 22.02 MB/s compressed    
Duration: 00:00:58s                                                                      
Schemas dumped: 1    
Tables dumped: 4
Uncompressed data size: 3.05 GB
Compressed data size: 1.57 GB
Compression ratio: 1.9
Rows written: 19000000
Bytes written: 1.57 GB
Average uncompressed throughput: 52.35 MB/s
Average compressed throughput: 26.96 MB/s 

- includeSchemas: list of strings (default: empty) - List of schemas to
  be included in the dump.

GreatSQL Py > util.dump_instance("/data/backups",{"includeSchemas":["test"],"threads":"16"})

GreatSQL localhost  Py > util.dump_tables("test",["student1"],"/data/backup_table",{"threads":"16"})
Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
...中间省略...
1 thds dumping - 110% (7.00M rows / ~6.31M rows), 539.12K rows/s, 44.17 MB/s uncompressed, 18.75 MB/s compressed   
Duration: 00:00:12s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 572.88 MB
Compressed data size: 242.92 MB
Compression ratio: 2.4
Rows written: 7000000
Bytes written: 242.92 MB
Average uncompressed throughput: 44.50 MB/s      
Average compressed throughput: 18.87 MB/s   

- includeTables: list of strings (default: empty) - List of tables or
  views to be included in the dump in the format of schema.table.

GreatSQL Py > util.dump_instance("/data/backups",{"includeTables":["test.test"],"threads":"16"})

GreatSQL Py > util.load_dump("/data/backup_table",{"threads":"16"})

GreatSQL Py > util.load_dump("/data/backup_table",{"threads":"16","resetProgress":True})

$ mysqlsh -S/data/GreatSQL/mgr01/mysql.sock root@localhost
MySQL Shell 8.0.25

GreatSQL Py > dba.configure_instance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as 172.17.139.77:3306
#提示当前的用户是管理员，不能直接用于MGR集群，需要新建一个账号
ERROR: User 'root' can only connect from 'localhost'. New account(s) with proper source address specification to allow remote connection from all instances must be created to manage the cluster.
1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel
Please select an option [1]: 2 #这里选择2，即创建一个最小权限账号

Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: GreatSQL  #用户名
Password for new account:  #密码
Confirm password:    #确认密码

applierWorkerThreads will be set to the default value of 4.

The instance '172.17.139.77:3306' is valid to be used in an InnoDB cluster.

Cluster admin user 'GreatSQL'@'%' created.
The instance '172.17.139.77:3306' is already ready to be used in an InnoDB cluster.

# 这个警告消息是告诉你正在使用的系统变量@@slave_parallel_workers已经被弃用，将在未来的版本中被移除，建议你使用新的变量名replica_parallel_workers来替换。
WARNING: '@@slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead. (Code 1287).

Successfully enabled parallel appliers.

GreatSQL Py > exit()

$ mysqlsh --uri GreatSQL@172.17.139.77:3306
MySQL Shell 8.0.25

GreatSQL 172.17.139.77:3306 ssl  Py > c = dba.create_cluster('MGR1');
A new InnoDB cluster will be created on instance '172.17.139.77:3306'.

Validating instance configuration at 172.17.139.77:3306...

This instance reports its own address as 172.17.139.77:3306

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '172.17.139.77:33061'. Use the localAddress option to override.

Creating InnoDB cluster 'MGR1' on '172.17.139.77:3306'...

Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

GreatSQL 172.17.139.77:3306 ssl  Py > c.add_instance('GreatSQL@172.17.139.77:3307');
#这里要指定MGR专用账号
...省略...
Please select a recovery method [C]lone/[A]bort (default Abort): Clone  <-- 选择用Clone方式从第一个节点全量复制数据
Validating instance configuration at 172.17.139.77:3306...
...省略...
The instance '172.17.139.77:3306' was successfully added to the cluster.

GreatSQL 172.17.139.77:3306 ssl  Py > c.describe()
{
    "clusterName": "mgr1", 
    "defaultReplicaSet": {
        "name": "default", 
        "topology": [
            {
                "address": "172.17.139.77:3306", 
                "label": "172.17.139.77:3306", 
                "role": "HA"
            }, 
            {
                "address": "172.17.139.77:3307", 
                "label": "172.17.139.77:3307", 
                "role": "HA"
            }
        ], 
        "topologyMode": "Single-Primary"
    }
}

GreatSQL 172.17.139.77:3306 ssl  Py > \help dba*
Found several entries matching dba*

GreatSQL 172.17.139.77:3306 ssl  Py > \help dba.get_cluster