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
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
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
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
characters (Powerline and Awesome) is recommended. Example: h ps://github.com/ztomer/fantasque_awesome_powerline Copyright @ 2022 Oracle and/or its affiliates. 17
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
Utility MySQL Shell includes a toolset called "utility". The utility module contains 8 very useful methods: Copyright @ 2022 Oracle and/or its affiliates. 20
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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 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
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
also added a graphs capabilities (work in progress) in TypeScript. Currently PieGraph() is available: Copyright @ 2022 Oracle and/or its affiliates. 82
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
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
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
shoota
oracle4engineer
__allllllllez__
pauli
bkuhlmann
nagix
wmsbill
trycycle
yuhta28
yhana
dayjournal
ham0215
lynnandtonic
iamctodd
eileencodes
philhawksworth
morganepeng
sugarenia
marcduiker
lemiorhan
tenderlove
paigeccino
keithpitt
sferik
