Tweet
Tweet

Slide 1

Slide 1 text

Frédéric Descamps Community Manager Oracle MySQL MySQL Day Rome - September 2022 MySQL Shell and Visual Studio Code Extension Best MySQL Companions

Slide 2

Slide 2 text

Who am I ? about.me/lefred Copyright @ 2022 Oracle and/or its affiliates. 2

Slide 3

Slide 3 text

@lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in Dylan Dog fan h ps://lefred.be Frédéric Descamps Copyright @ 2022 Oracle and/or its affiliates. 3

Slide 4

Slide 4 text

What Is MySQL Shell ? Copyright @ 2022 Oracle and/or its affiliates. 4

Slide 5

Slide 5 text

MySQL Shell The MySQL Shell is an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server You can use the MySQL Shell to perform data queries and updates as well as various administration operations. Copyright @ 2022 Oracle and/or its affiliates. 5

Slide 6

Slide 6 text

MySQL Shell (2) The MySQL Shell provides: Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 7

Slide 7 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 8

Slide 8 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 9

Slide 9 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models CRUD Document and Relational APIs via scripting Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 10

Slide 10 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models CRUD Document and Relational APIs via scripting Traditional Table, JSON, Tab Separated output results formats Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 11

Slide 11 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models CRUD Document and Relational APIs via scripting Traditional Table, JSON, Tab Separated output results formats MySQL Standard and X Protocols Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 12

Slide 12 text

MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models CRUD Document and Relational APIs via scripting Traditional Table, JSON, Tab Separated output results formats MySQL Standard and X Protocols and more... Copyright @ 2022 Oracle and/or its affiliates. 6

Slide 13

Slide 13 text

MySQL Shell Overview PY JS SQL > MySQL 8.0 Upgrade Checker Auto Completion & Command History Output Formats (table, json, tabs) Prompt Themes Batch Execution 5.7 8.0 JS Document Store X dev API SQL CLI importJSON InnoDB Cluster JS JS parallel importTABLE data dump & load InnoDB ReplicaSet Copyright @ 2022 Oracle and/or its affiliates. 7

Slide 14

Slide 14 text

Some Examples of the different modes Copyright @ 2022 Oracle and/or its affiliates. 8

Slide 15

Slide 15 text

MySQL Shell in Python Mode Copyright @ 2022 Oracle and/or its affiliates. 9

Slide 16

Slide 16 text

MySQL Shell in Python Mode Copyright @ 2022 Oracle and/or its affiliates. 10

Slide 17

Slide 17 text

MySQL Shell in Python Mode Copyright @ 2022 Oracle and/or its affiliates. 11

Slide 18

Slide 18 text

MySQL Shell in Javascript Mode Copyright @ 2022 Oracle and/or its affiliates. 12

Slide 19

Slide 19 text

MySQL Shell in SQL Mode Copyright @ 2022 Oracle and/or its affiliates. 13

Slide 20

Slide 20 text

MySQL Shell Modes Copyright @ 2022 Oracle and/or its affiliates. 14

Slide 21

Slide 21 text

Configuring your Shell Copyright @ 2022 Oracle and/or its affiliates. 15

Slide 22

Slide 22 text

A nice prompt The default prompt is basic. When installing the MySQL Shell package, 10 examples of prompt are also installed (in /usr/share/mysqlsh/prompt on Linux). After having started MySQL Shell for the rst time, a new folder (~/.mysqlsh) is created. You can copy a prompt le inside that folder and rename it prompt.json: $ $ cp cp /usr/share/mysqlsh/prompt/prompt_256pl+aw.json ~/.mysqlsh/prompt.json /usr/share/mysqlsh/prompt/prompt_256pl+aw.json ~/.mysqlsh/prompt.json Copyright @ 2022 Oracle and/or its affiliates. 16

Slide 23

Slide 23 text

A nice prompt (2) Having a terminal font supporting specials characters (Powerline and Awesome) is recommended. Example: h ps://github.com/ztomer/fantasque_awesome_powerline Copyright @ 2022 Oracle and/or its affiliates. 17

Slide 24

Slide 24 text

Other con guration tips I usually enable the history of commands: \ \option option --persist history.autoSave 1 --persist history.autoSave 1 \ \option option --persist history.maxSize 5000 --persist history.maxSize 5000 All the options can be listed using \option -l Copyright @ 2022 Oracle and/or its affiliates. 18

Slide 25

Slide 25 text

Other con guration tips I usually enable the history of commands: \ \option option --persist history.autoSave 1 --persist history.autoSave 1 \ \option option --persist history.maxSize 5000 --persist history.maxSize 5000 All the options can be listed using \option -l I also recommend to replace the old mysql client by these aliases: alias alias mysql mysql= ="mysqlsh --sql mysql://localhost" "mysqlsh --sql mysql://localhost" alias alias mysqlx mysqlx= ="mysqlsh --js mysqlx://localhost" "mysqlsh --js mysqlx://localhost" Copyright @ 2022 Oracle and/or its affiliates. 18

Slide 26

Slide 26 text

MySQL Shell Utility Copyright @ 2022 Oracle and/or its affiliates. 19

Slide 27

Slide 27 text

checkForServerUpgrade() dumpInstance() dumpSchemas() dumpTables() exportTable() importJson() importTable() loadDump() MySQL Shell Utility MySQL Shell includes a toolset called "utility". The utility module contains 8 very useful methods: Copyright @ 2022 Oracle and/or its affiliates. 20

Slide 28

Slide 28 text

MySQL Shell Utility : checkForServerUpgrade() MySQL Shell helps the DBA to validate the upgrade to MySQL 8.0. With util.checkForServerUpgrade(), MySQL Shell can perform a series of tests on a speci ed MySQL sever to check is the upgrade process will succeed or if requires rst some changes. Will check your MySQL 5.7 or 8.0 installation readiness for upgrade to latest version Check for legacy issues Run the tool before upgrading! Always use latest version of MySQL Shell Copyright @ 2022 Oracle and/or its affiliates. 21

Slide 29

Slide 29 text

usage of old temporal type usage of new reserved keywords usage of utf8mb3 charset con icting name in mysql schema partitions with engine not supporting native partitioning foreign key constraint names longer than 64 characters usage of obsolete MAXDB SQL mode ag usage of orther obsolete SQL mode ags ENUM/SET column de nitions containing elements longer than 255 characters usage of partitioned tables in shared tablespaces MySQL Shell Upgrade Checker Utility Upgrade Checker performs the following checks: Copyright @ 2022 Oracle and/or its affiliates. 22

Slide 30

Slide 30 text

circular directory references in tablespace data le paths usage of removed functions usage of removed GROUP BY ASC/DESC syntax removed system variables for error logging to the system log con guration removed system variables system variables with new default values schema inconsistencies resulting from le removal or corruption table names containing 'FTS' issues reported by check table x for upgrade command MySQL Shell Upgrade Checker Utility (2) Copyright @ 2022 Oracle and/or its affiliates. 23

Slide 31

Slide 31 text

MySQL Upgrade Checker: how to use it? From the system shell: $ mysqlsh -- util checkForServerUpgrade user@localhost:3306 --target-version $ mysqlsh -- util checkForServerUpgrade user@localhost:3306 --target-version= =8.0 8.0.30 .30 \ \ --output-format --output-format= =JSON --con g-path JSON --con g-path= =/etc/mysql/my.cnf /etc/mysql/my.cnf Interactively from MySQL Shell: mysqlsh mysqlsh> > util util. .checkForServerUpgrade checkForServerUpgrade( ('[email protected]:3306' '[email protected]:3306', , { {"password" "password": :"password" "password", , "targetVersion" "targetVersion": :"8.0.30" "8.0.30", , "con gPath" "con gPath": :"/etc/my.cnf" "/etc/my.cnf"} }) ) mysqlsh mysqlsh> > util util. .checkForServerUpgrade checkForServerUpgrade( () ) Copyright @ 2022 Oracle and/or its affiliates. 24

Slide 32

Slide 32 text

MySQL Upgrade Checker - output example 16) System variables with new default values Warning: Following system variables that are not de ned in your con guration le will have new default values. Please review if you rely on their current values and if so de ne them before performing upgrade. More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ back_log - default value will change character_set_server - default value will change from latin1 to utf8mb4 collation_server - default value will change from latin1_swedish_ci to utf8mb4_0900_ai_ci event_scheduler - default value will change from OFF to ON explicit_defaults_for_timestamp - default value will change from OFF to ON innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to 2 (interleaved) innodb_ ush_method - default value will change from NULL to fsync (Unix), unbuffered (Windows) innodb_ ush_neighbors - default value will change from 1 (enable) to 0 (disable) innodb_max_dirty_pages_pct - default value will change from 75 (%) 90 (%) innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10 (%) innodb_undo_log_truncate - default value will change from OFF to ON ... Copyright @ 2022 Oracle and/or its affiliates. 25

Slide 33

Slide 33 text

MySQL Upgrade Checker - output example (2) 17) Zero Date, Datetime, and Timestamp values Warning: By default zero date/datetime/timestamp values are no longer allowed in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in SQL_MODE by default. These modes should be used with strict mode as they will be merged with strict mode in a future release. If you do not include these modes in your SQL_MODE setting, you are able to insert date/datetime/timestamp values that contain zeros. It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future. More information: https://lefred.be/content/mysql-8-0-and-wrong-dates/ sbtest_tpcc.orders2.o_entry_d - column has zero default value: 0000-00-00 00:00:00 Copyright @ 2022 Oracle and/or its affiliates. 26

Slide 34

Slide 34 text

MySQL Shell Utility : dump%() and loadDump() Another aspect of the DBAs daily tasks is to deal with logical dumps, for backup, for migrations, to reset data in dev environments ... Copyright @ 2022 Oracle and/or its affiliates. 27

Slide 35

Slide 35 text

MySQL Shell Utility : dump%() and loadDump() Another aspect of the DBAs daily tasks is to deal with logical dumps, for backup, for migrations, to reset data in dev environments ... MySQL Shell provides and amazing tool that relegates all its competitors far behind ! Copyright @ 2022 Oracle and/or its affiliates. 27

Slide 36

Slide 36 text

MySQL Shell Utility : dump%() and loadDump() Another aspect of the DBAs daily tasks is to deal with logical dumps, for backup, for migrations, to reset data in dev environments ... MySQL Shell provides and amazing tool that relegates all its competitors far behind ! Bye bye mysqldump, mysqlpump, mydumper, ... Copyright @ 2022 Oracle and/or its affiliates. 27

Slide 37

Slide 37 text

MySQL Shell Utility : dump%() and loadDump() Another aspect of the DBAs daily tasks is to deal with logical dumps, for backup, for migrations, to reset data in dev environments ... MySQL Shell provides and amazing tool that relegates all its competitors far behind ! Bye bye mysqldump, mysqlpump, mydumper, ... Welcome MySQL Shell Load & Dump !! Copyright @ 2022 Oracle and/or its affiliates. 27

Slide 38

Slide 38 text

Logical Dump 3 types of dumps are available and are accessible via those methods: dumpInstance() dumpSchemas() dumpTables() Copyright @ 2022 Oracle and/or its affiliates. 28

Slide 39

Slide 39 text

dump to remote storage (like Object Store) progress state tracking check for compatibility with MDS resume and reset capability add rate limitation use compression on the y changes: create invisible PKs force InnoDB ignore missing PKs skip invalid accounts strip de ners strip restricted grants strip tablespaces Logical Dump (2) Dumps are taken in parallel but has many other bene ts like : Copyright @ 2022 Oracle and/or its affiliates. 29

Slide 40

Slide 40 text

MySQL Shell Dump Copyright @ 2022 Oracle and/or its affiliates. 30

Slide 41

Slide 41 text

run analyze tables after load (can be limited to those having histograms) the character set can be overridden invisible PKs creation defer the creation of secondary indexes (can also be limited to FT) just load the scheme's de nition or the data resume and reset capability show progress skip binary logs update or override GTIDs Logical Load The generated dumps can also be loaded in parallel and many parameters are supported to provide the best experience. Let's see some of them: Copyright @ 2022 Oracle and/or its affiliates. 31

Slide 42

Slide 42 text

Logical Load (2) Take a look  at this output: Copyright @ 2022 Oracle and/or its affiliates. 32

Slide 43

Slide 43 text

Logical Load (2) Take a look  at this output: Copyright @ 2022 Oracle and/or its affiliates. 32

Slide 44

Slide 44 text

Logical Load (2) Take a look  at this output: MySQL Shell is also the easiest and fastest way to migrate to MySQL Database Service on OCI CLOUD Copyright @ 2022 Oracle and/or its affiliates. 32

Slide 45

Slide 45 text

MySQL Shell Load Copyright @ 2022 Oracle and/or its affiliates. 33

Slide 46

Slide 46 text

MySQL Shell Utility: importJson() If you plan to use MySQL as a Document Store, you can import JSON documents very easily. Copyright @ 2022 Oracle and/or its affiliates. 34

Slide 47

Slide 47 text

MySQL Shell Utility: importJson() If you plan to use MySQL as a Document Store, you can import JSON documents very easily. Task : migrate a collection of more than 25k documents from MongoDB to MySQL Copyright @ 2022 Oracle and/or its affiliates. 34

Slide 48

Slide 48 text

MySQL Shell Utility: importJson() If you plan to use MySQL as a Document Store, you can import JSON documents very easily. Task : migrate a collection of more than 25k documents from MongoDB to MySQL Copyright @ 2022 Oracle and/or its affiliates. 34

Slide 49

Slide 49 text

MySQL Shell Utility: importJson() If you plan to use MySQL as a Document Store, you can import JSON documents very easily. Task : migrate a collection of more than 25k documents from MongoDB to MySQL Copyright @ 2022 Oracle and/or its affiliates. 34

Slide 50

Slide 50 text

MySQL Shell : Document Store Now the documents can be used in MySQL Document Store with CRUD operations: Copyright @ 2022 Oracle and/or its affiliates. 35

Slide 51

Slide 51 text

MySQL Shell Utility: debug The debugging and diagnostics utility util.debug.collectDiagnostics() enables you to collect diagnostic data on your MySQL server. The diagnostic report is generated as a zip le. This information could be shared with MySQL Support Team for example. Copyright @ 2022 Oracle and/or its affiliates. 36

Slide 52

Slide 52 text

MySQL Shell Utility: debug (2) Copyright @ 2022 Oracle and/or its affiliates. 37

Slide 53

Slide 53 text

MySQL Shell Utility: exportTable() & importTable() This method exports a MySQL relational table into a data le. Then it can use the le to import a table's content too. Multliple dialects are supported: default linesTerminatedBy: [LF], eldsTerminatedBy: [TAB], lesEnclosedBy: [empty], eldsOptionallyEnclosed: false csv linesTerminatedBy: [CR][LF], eldsTerminatedBy: [,], lesEnclosedBy: ["], eldsOptionallyEnclosed: true csv-unix linesTerminatedBy: [LF], eldsTerminatedBy: [,], lesEnclosedBy: ["], eldsOptionallyEnclosed: false tsv linesTerminatedBy: [LF], eldsTerminatedBy: [TAB], lesEnclosedBy: ["], eldsOptionallyEnclosed: true OCI Object Storage or AWS S3 can also be used. Copyright @ 2022 Oracle and/or its affiliates. 38

Slide 54

Slide 54 text

MySQL Shell Utility: exportTable() & importTable() Copyright @ 2022 Oracle and/or its affiliates. 39

Slide 55

Slide 55 text

Admin API Copyright @ 2022 Oracle and/or its affiliates. 40

Slide 56

Slide 56 text

MySQL Shell Admin API The AdminAPI is an API that enables con guring and managing InnoDB Clusters, InnoDB ReplicaSets and InnoDB ClusterSets among other things. The AdminAPI is available via the dba global object of MySQL Shell Copyright @ 2022 Oracle and/or its affiliates. 41

Slide 57

Slide 57 text

MySQL Shell Admin API The AdminAPI is an API that enables con guring and managing InnoDB Clusters, InnoDB ReplicaSets and InnoDB ClusterSets among other things. The AdminAPI is available via the dba global object of MySQL Shell This provides a very easy and secure way to deploy MySQL Architectures for the DBA. It includes con guration, checks and even data provisioning ! Copyright @ 2022 Oracle and/or its affiliates. 41

Slide 58

Slide 58 text

MySQL Shell Admin API (2) As like most of the MySQL Shell capabilities all the Admin API calls can be done interactively or directly from the command line or a script. This allows automation with a con guration management tool for example. Copyright @ 2022 Oracle and/or its affiliates. 42

Slide 59

Slide 59 text

The new MySQL Operator for K8s also uses MySQL Shell in the backend. MySQL Shell Admin API (2) As like most of the MySQL Shell capabilities all the Admin API calls can be done interactively or directly from the command line or a script. This allows automation with a con guration management tool for example. Copyright @ 2022 Oracle and/or its affiliates. 42

Slide 60

Slide 60 text

MySQL Shell Admin API - examples Copyright @ 2022 Oracle and/or its affiliates. 43

Slide 61

Slide 61 text

MySQL InnoDB Cluster MySQL InnoDB ReplicaSet js js> > \ \c admin@mysql1 c admin@mysql1 js js> > cluster cluster = = dba.createCluster dba.createCluster( ('cluster' 'cluster') ) js js> > \ \c admin@mysql1 c admin@mysql1 js js> > rs rs = = dba.createReplicaSet dba.createReplicaSet( ('replicaset' 'replicaset') ) MySQL Shell Admin API - examples Copyright @ 2022 Oracle and/or its affiliates. 43

Slide 62

Slide 62 text

MySQL InnoDB Cluster MySQL InnoDB ReplicaSet js js> > \ \c admin@mysql1 c admin@mysql1 js js> > cluster cluster = = dba.createCluster dba.createCluster( ('cluster' 'cluster') ) js js> > \ \c admin@mysql1 c admin@mysql1 js js> > rs rs = = dba.createReplicaSet dba.createReplicaSet( ('replicaset' 'replicaset') ) Con gure server to add later js js> > dba.con gureInstance dba.con gureInstance( ('admin@mysql2' 'admin@mysql2') ) js js> > dba.con gureReplicaSetInstance dba.con gureReplicaSetInstance( ('admin@mysql2' 'admin@mysql2') ) MySQL Shell Admin API - examples Copyright @ 2022 Oracle and/or its affiliates. 43

Slide 63

Slide 63 text

MySQL InnoDB Cluster MySQL InnoDB ReplicaSet js js> > \ \c admin@mysql1 c admin@mysql1 js js> > cluster cluster = = dba.createCluster dba.createCluster( ('cluster' 'cluster') ) js js> > \ \c admin@mysql1 c admin@mysql1 js js> > rs rs = = dba.createReplicaSet dba.createReplicaSet( ('replicaset' 'replicaset') ) Con gure server to add later js js> > dba.con gureInstance dba.con gureInstance( ('admin@mysql2' 'admin@mysql2') ) js js> > dba.con gureReplicaSetInstance dba.con gureReplicaSetInstance( ('admin@mysql2' 'admin@mysql2') ) Add server to the Cluster js js> > cluster.addInstance cluster.addInstance( ('admin@mysql2' 'admin@mysql2') ) js js> > rs.addInstance rs.addInstance( ('admin@mysql2' 'admin@mysql2') ) MySQL Shell Admin API - examples Copyright @ 2022 Oracle and/or its affiliates. 43

Slide 64

Slide 64 text

Bootstrap MySQL Router $ $ sudo sudo mysqlrouter mysqlrouter --user --user= =mysqlrouter mysqlrouter --bootstrap --bootstrap $ $ sudo sudo systemctl start mysqlrouter systemctl start mysqlrouter $ $ sudo sudo mysqlrouter mysqlrouter --user --user= =mysqlrouter mysqlrouter --bootstrap --bootstrap $ $ sudo sudo systemctl start mysqlrouter systemctl start mysqlrouter MySQL Shell Admin API - examples Copyright @ 2022 Oracle and/or its affiliates. 44

Slide 65

Slide 65 text

MySQL Shell Admin API (3) The Admin API also provides all the required methods to manage your MySQL Architecture Copyright @ 2022 Oracle and/or its affiliates. 45

Slide 66

Slide 66 text

MySQL Shell Admin API (3) The Admin API also provides all the required methods to manage your MySQL Architecture Copyright @ 2022 Oracle and/or its affiliates. 45

Slide 67

Slide 67 text

Extending MySQL Shell Copyright @ 2022 Oracle and/or its affiliates. 46

Slide 68

Slide 68 text

Extending MySQL Shell As a DBA or a developer, you have two di erents ways to extend the capabilities of MySQL Shell: using the Reporting Framework creating your own modules Copyright @ 2022 Oracle and/or its affiliates. 47

Slide 69

Slide 69 text

Extending MySQL Shell As a DBA or a developer, you have two di erents ways to extend the capabilities of MySQL Shell: using the Reporting Framework creating your own modules The UDR (User De ned Reports) can be called via the \show and the \watch commands: Copyright @ 2022 Oracle and/or its affiliates. 47

Slide 70

Slide 70 text

Extending MySQL Shell - UDR Copyright @ 2022 Oracle and/or its affiliates. 48

Slide 71

Slide 71 text

Extending MySQL Shell - UDR (2) You can nd examples of extra User De ned Reports on h ps://github.com/lefred/mysql-shell-udr Copyright @ 2022 Oracle and/or its affiliates. 49

Slide 72

Slide 72 text

Extending MySQL Shell - Plugins For repetitive or complex tasks, it's also possible to extend MySQL Shell with plugins. Copyright @ 2022 Oracle and/or its affiliates. 50

Slide 73

Slide 73 text

Extending MySQL Shell - Plugins For repetitive or complex tasks, it's also possible to extend MySQL Shell with plugins. Some words on MySQL Shell Plugins: can be wri en in Python or JavaScript are loaded when starting MySQL Shell can use any local modules too Copyright @ 2022 Oracle and/or its affiliates. 50

Slide 74

Slide 74 text

Extending MySQL Shell - Plugins For repetitive or complex tasks, it's also possible to extend MySQL Shell with plugins. Some words on MySQL Shell Plugins: can be wri en in Python or JavaScript are loaded when starting MySQL Shell can use any local modules too You can nd multiple plugins to test and extend on: h ps://github.com/lefred/mysqlshell-plugins Copyright @ 2022 Oracle and/or its affiliates. 50

Slide 75

Slide 75 text

Users & Privileges - plugins examples Dumping all users and grants: Copyright @ 2022 Oracle and/or its affiliates. 51

Slide 76

Slide 76 text

Users & Privileges - plugins examples Copy users from a server to another: Copyright @ 2022 Oracle and/or its affiliates. 52

Slide 77

Slide 77 text

Users & Privileges - plugins examples Create a user wizard Copyright @ 2022 Oracle and/or its affiliates. 53

Slide 78

Slide 78 text

Users & Privileges - plugins examples Create a user wizard Copyright @ 2022 Oracle and/or its affiliates. 53

Slide 79

Slide 79 text

Users & Privileges - plugins examples Authentication methods and passwords to expire: Copyright @ 2022 Oracle and/or its affiliates. 54

Slide 80

Slide 80 text

Poor's man Collect and Trending Copyright @ 2022 Oracle and/or its affiliates. 55

Slide 81

Slide 81 text

Poor's man Collect and Trending When you have a performance problem with MySQL, the best way to diagnose the issue is to use trending information. I've extended MySQL Shell with speci c plugins that use third party libraries to collect and plot useful information. Copyright @ 2022 Oracle and/or its affiliates. 56

Slide 82

Slide 82 text

Poor's man Collect and Trending (2) Copyright @ 2022 Oracle and/or its affiliates. 57

Slide 83

Slide 83 text

Poor's man Collect and Trending (3) Copyright @ 2022 Oracle and/or its affiliates. 58

Slide 84

Slide 84 text

Poor's man Collect and Trending (4) More Information: h ps://lefred.be/content/poormans-mysql-monitoring-trending/ h ps://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-checkpoint-age/ h ps://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-mysql-history-list-length/ h ps://lefred.be/content/a-graph-a-day-keeps-the-doctor-away-full-table-scans/ h ps://lefred.be/content/dynamic-innodb-redo-log/ Copyright @ 2022 Oracle and/or its affiliates. 59

Slide 85

Slide 85 text

MySQL Shell for Visual Studio Code Copyright @ 2022 Oracle and/or its affiliates. 60

Slide 86

Slide 86 text

MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 61

Slide 87

Slide 87 text

Visual Studio Code is the most popular IDE with developers MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 61

Slide 88

Slide 88 text

MySQL Shell for VS Code We want to o er a single interface to the developers and and the operators/DBAs. Copyright @ 2022 Oracle and/or its affiliates. 62

Slide 89

Slide 89 text

MySQL Shell for VS Code We want to o er a single interface to the developers and and the operators/DBAs. A place where MySQL Workbench and MySQL Shell can merge. Copyright @ 2022 Oracle and/or its affiliates. 62

Slide 90

Slide 90 text

MySQL Shell for VS Code - Installation The installation is very simple using Visual Studio Code's Marketplace: Copyright @ 2022 Oracle and/or its affiliates. 63

Slide 91

Slide 91 text

MySQL Shell for VS Code - Installation Or just use CTRL-P in Visual Studio code and paste the following command: ext install Oracle.mysql-shell-for-vs-code Copyright @ 2022 Oracle and/or its affiliates. 64

Slide 92

Slide 92 text

MySQL Shell for VS Code - Requirements The list of requirements is pre y short: Visual Studio Code >= 1.63.0 Windows 64bit or 32bit OR Linux 64 bit OR macOSX 10.11+ Copyright @ 2022 Oracle and/or its affiliates. 65

Slide 93

Slide 93 text

MySQL Shell for VS Code - Installation: certi cates The rst time the extension is installed, the setup wizard is started to generate certi cates. Those certi cates are installed on your local user account in order to securely access MySQL Shell. Copyright @ 2022 Oracle and/or its affiliates. 66

Slide 94

Slide 94 text

MySQL Shell for VS Code - Installation: certi cates You need to install them or the extension won't work properly: Copyright @ 2022 Oracle and/or its affiliates. 67

Slide 95

Slide 95 text

MySQL Shell for VS Code - Linux This is a demo on Linux: Copyright @ 2022 Oracle and/or its affiliates. 68

Slide 96

Slide 96 text

MySQL Shell for VS Code - under the hood The certi cates, log le and some data are stored in the ~/.mysqlsh-gui directory: Copyright @ 2022 Oracle and/or its affiliates. 69

Slide 97

Slide 97 text

MySQL Shell for VS Code - features Copyright @ 2022 Oracle and/or its affiliates. 70

Slide 98

Slide 98 text

Activity Bar's Icon & View Container. The view container appears as an icon in the activity bar after you install the MySQL Shell for VS Code extension. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 71

Slide 99

Slide 99 text

Database Editor Connections View This is where you can create and manage connections to MySQL servers and MySQL HeatWave MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 72

Slide 100

Slide 100 text

OCI and MySQL HeatWave View Con gure the Oracle Cloud Instrastructure pro le and access your resources. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 73

Slide 101

Slide 101 text

MySQL Shell Sessions View This is the place where you can create and manage MySQL Shell sessions. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 74

Slide 102

Slide 102 text

MySQL Shell Tasks View Here you can see all ongoing tasks for MySQL Shell. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 75

Slide 103

Slide 103 text

SQL Notebook SQL Notebook is an embedded, console-type editor, which combines elements of a classic terminal and a visual editor. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 76

Slide 104

Slide 104 text

MySQL Shell Console MySQL Shell GUI Console provides the full power of the MySQL Shell inside a notebook- interface-styled editor MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 77

Slide 105

Slide 105 text

VScode ouput, debug, terminal... This is the standard panel of VS code with an embedded terminal, the debug console and the output. MySQL Shell and MySQL Shell Tasks have their own output that can be selected. This is very useful when debuging the extension. MySQL Shell for VS Code Copyright @ 2022 Oracle and/or its affiliates. 78

Slide 106

Slide 106 text

This editor supports 3 languages: SQL JavaScript TypeScript 2 protocols are supported: classic MySQL protocol (3306) X Protocol (mysqlx) (33060) MySQL Shell for VS Code - SQL Notebook The SQL Notebook editor is the default editor each time you establish a connection to MySQL (or SQLite). Copyright @ 2022 Oracle and/or its affiliates. 79

Slide 107

Slide 107 text

MySQL Shell for VS Code - SQL Notebook The SQL Notebook editor combines the best of the GUI editors and the command line clients. You can see it like a endless document where each commands (called prompt) is followed by a result area (output) that is linked to it. If you have multiple statements, the output area will contains multiple tabs: Copyright @ 2022 Oracle and/or its affiliates. 80

Slide 108

Slide 108 text

MySQL Shell for VS Code - SQL Notebook The commands can be modi ed ad in nitum: you can croll up and down in the document and change all statements any time. The editor provides command completion: Copyright @ 2022 Oracle and/or its affiliates. 81

Slide 109

Slide 109 text

MySQL Shell for VS Code - SQL Notebook We have also added a graphs capabilities (work in progress) in TypeScript. Currently PieGraph() is available: Copyright @ 2022 Oracle and/or its affiliates. 82

Slide 110

Slide 110 text

MySQL Shell for VS Code - SQL Notebook (cool) MySQL Shell is integrated to VS code also when you edit other les: Copyright @ 2022 Oracle and/or its affiliates. 83

Slide 111

Slide 111 text

You can select any SQL statement in your code and execute it the SQL Notebook MySQL Shell for VS Code - SQL Notebook (cool) Copyright @ 2022 Oracle and/or its affiliates. 84

Slide 112

Slide 112 text

MySQL Shell for VS Code - SQL Notebook (cool) It gets executed in a new block (prompt + output): Copyright @ 2022 Oracle and/or its affiliates. 85

Slide 113

Slide 113 text

MySQL Shell for VS Code - SQL Notebook (cool) You can modify your query and test it. When ready send it back modi ed to the code: Copyright @ 2022 Oracle and/or its affiliates. 86

Slide 114

Slide 114 text

MySQL Shell for VS Code - SQL Notebook (cool) Done ! This is a very cool feature for developers ! Copyright @ 2022 Oracle and/or its affiliates. 87

Slide 115

Slide 115 text

It supports 3 languages: SQL JavaScript Python 2 protocols are supported: classic MySQL protocol (3306) X Protocol (mysqlx) (33060) MySQL Shell for VS Code - MySQL Shell Console The MySQL Shell Consoles view allows you to have dedicated area for working with multiple MySQL Shell sessions. This is exacly like the usual temrinal MySQL Shell but in VS code: Copyright @ 2022 Oracle and/or its affiliates. 88

Slide 116

Slide 116 text

MySQL Shell for VS Code - MySQL Shell Console The AdminAPI is available when using MySQL Shell Console session. Same for the utilities: Copyright @ 2022 Oracle and/or its affiliates. 89

Slide 117

Slide 117 text

You also have the possibility to connect to your MySQL HeatWave instances using the Bastion Service that the extension will deploy and con gure for you ! MySQL Shell for VS Code - OCI If you are using OCI, MySQL Shell for VS Code is your best companion. It uses by default all con guration pro les de ned in ~/.oci/con g le. You have the possibility to see all your MySQL HeatWave instances but also other resources like compute instances. Copyright @ 2022 Oracle and/or its affiliates. 90

Slide 118

Slide 118 text

MySQL Shell for VS Code - OCI If no bastion host is present, a new one will be created: Copyright @ 2022 Oracle and/or its affiliates. 91

Slide 119

Slide 119 text

MySQL Shell for VS Code - OCI Copyright @ 2022 Oracle and/or its affiliates. 92

Slide 120

Slide 120 text

MySQL Shell for VS Code - OCI MySQL Shell Dump & Load utility is integrated and optimized for OCI MySQL HeatWave service: Copyright @ 2022 Oracle and/or its affiliates. 93

Slide 121

Slide 121 text

MySQL Shell for VS Code - OCI Copyright @ 2022 Oracle and/or its affiliates. 94

Slide 122

Slide 122 text

MySQL Shell for VS Code - OCI Copyright @ 2022 Oracle and/or its affiliates. 95

Slide 123

Slide 123 text

MySQL Shell for VS Code - OCI Copyright @ 2022 Oracle and/or its affiliates. 96

Slide 124

Slide 124 text

MySQL Shell for VS Code - se ings You have the possibility to modify some se ings for the extension (numbers of records in paging, capitalize the SQL commands, ...) Copyright @ 2022 Oracle and/or its affiliates. 97

Slide 125

Slide 125 text

Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2022 Oracle and/or its affiliates. 98

Slide 126

Slide 126 text

Questions ? Copyright @ 2022 Oracle and/or its affiliates. 99