@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
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
MySQL Shell (2) The MySQL Shell provides: Both Interactive and Batch operations Document and Relational Models Copyright @ 2022 Oracle and/or its affiliates. 6
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
lefred
okamototakuyasr2
revcomm_inc
shomaekawa
takanorip
oldbigbuddha
coconala_engineer
baseballyama
ufried
moritamasami
oracle4engineer
hfukunak
leveragestech
tenderlove
danielanewman
jakevdp
reverentgeek
tammyeverts
colly
sonatard
sachag
deanohume
jasonvnalue
cromwellryan
chrislema