Downloaded 27 times
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Scripting – Examples 19 • Connect to myserver.foo.com • Execute create_account.js with any extra arguments forwarded to the script (sys.argv[]) • The script can use the session global variable to execute SQL in the target server $ mysqlsh root@myserver.foo.com –f create_account.js appuser@’192.168.%.%’](https://image.slidesharecdn.com/dev5987slideshare-181105124415/75/MySQL-Shell-The-DevOps-Tool-for-MySQL-19-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Ops) • API Command line integration – New cmdline syntax to invoke built-in Shell commands – Requires minimal extra typing, quoting and escaping – Supported objects: dba, cluster, shell, shell.options and util Syntax: mysqlsh [OPTIONS] [URI] -- <object_name> <method_name> [argument_list] 27 $ mysqlsh root@myserver:33060 –- util import-json ~/Downloads/myData/data.json --collections=‘zip codes’ --schema=‘json_test’ MySQL Administration](https://image.slidesharecdn.com/dev5987slideshare-181105124415/75/MySQL-Shell-The-DevOps-Tool-for-MySQL-27-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 38 • Let’s use as an example: service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], }](https://image.slidesharecdn.com/dev5987slideshare-181105124415/75/MySQL-Shell-The-DevOps-Tool-for-MySQL-38-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 39 What if current is 5.7 and latest is 8.0? Will it break everything? service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], } ! • Let’s use as an example:](https://image.slidesharecdn.com/dev5987slideshare-181105124415/75/MySQL-Shell-The-DevOps-Tool-for-MySQL-39-2048.jpg)
![Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 40 service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], before => Exec [‘upgrade checker’] } exec { “upgrade checker”: command => “mysqlsh -- util check-for-server-upgrade { --user=root -- host=localhost --port=3306} --password=‘myPwd’” require => Service[“mysqlsh”] } • Use MySQL Shell upgrade checker](https://image.slidesharecdn.com/dev5987slideshare-181105124415/75/MySQL-Shell-The-DevOps-Tool-for-MySQL-40-2048.jpg)
Uploaded byMiguel Araújo
MySQL Shell: The DevOps Tool for MySQL
The document presents an overview of the MySQL Shell, a DevOps tool designed for MySQL development and administration, featuring multi-language support and various APIs. It highlights the integration of MySQL Shell with MySQL InnoDB Cluster for high availability and simplifies tasks through automation and scripting capabilities. The document also outlines the benefits of DevOps practices, aiming to enhance collaboration between developers and operations teams.
MySQL Shell: The DevOps Tool for MySQL
- 1. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Miguel Araújo Senior Software Developer Alfredo Kojima Senior Software Development Manager MySQL Middleware and Clients October 24, 2018 MySQL Shell The DevOps Tool for MySQL
- 2. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. 2
- 3. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Program Agenda introduction to DevOps MySQL Shell Development with the Shell - Live demo! Shell meets continuous automation MySQL InnoDB Cluster Shell as the operations frontend of IDC - Live demo! Summary 1 2 3 4 5 3 6 7
- 4. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 4 Who are we?
- 5. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | whoami Miguel Araújo • MySQL Senior Software Developer • Lead developer for the AdminAPI at the MySQL Shell team • MySQL InnoDB Cluster evangelist • Living in Portugal • http://about.me/miguel-araujo 5
- 6. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | whoami Alfredo Kojima • MySQL Senior Software Development Manager • Technical lead for MySQL Clients team: X Plugin, Router, Shell, C Connectors • MySQL InnoDB Cluster evangelist • Living in the USA 6
- 7. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to DevOps MySQL Shell Development with the Shell - Live demo! Shell meets continuous automation MySQL InnoDB Cluster Shell as the operations frontend of IDC - Live demo! Summary 1 3 4 5 7 6 7 2
- 8. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 8 DevOps: What is it? DEVOPS Software OperationsSoftware Development
- 9. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 9 DevOps: What is it? As simple as: Engineers (developers) and operations working together to: Get things done faster Automate processes Share knowledge
- 10. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | DevOps: Goals • Continuous, faster development • Faster time to deliver • Lower the failure rate • Reduce dependency of staff that owns all the knowledge by sharing the competences to all the team 10
- 11. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to DevOps MySQL Shell Development with the Shell - Live demo! Shell meets continuous automation MySQL InnoDB Cluster Shell as the operations frontend of IDC - Live demo! Summary 1 3 4 5 11 6 7 2
- 12. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Introduction • Interactive multi-language console interface that supports development and administration for the MySQL Server • Can be used to perform data queries or updates, and administration operations: Scriptable “DevOps” APIs Unified Interface for MySQL Developers and DBAs: one tool! • Intuitive and easy to use 12
- 13. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 1.0 • Supports MySQL Server 5.7 8.0 Supports MySQL Server 5.7 and 8.0 Higly recommended version! Note: Upgrade to 8.0 right away if you’re still using 1.0 13 MySQL Shell: Versions
- 14. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Goal • One tool for MySQL development and administrative tasks • Natural interface for all MySQL related ”DevOps”! – Unified Interface for Developers and DBAs • Intuitive, flexible and powerful • Provides scripting with development and administrative APIs 14
- 15. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) • Multi-language support – JavaScript, Python and SQL – Both interactive and batch operations 15 MySQL Development
- 16. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) • Document and Relational models – Supports the classic relational model – Modern fluent API for the MySQL Document Store • CRUD and Relational – Supports the traditional Table results, as well as JSON or Tab separated output 16 MySQL Development
- 17. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) • Support for scripting in Python and JavaScript • APIs for MySQL DBs, Document Store, InnoDB cluster, Shell and more • Import any installed module (in Python) • Execute scripts from – Interactive REPL interface (interactive interpreter) – Directly from the command line • mysqlsh --py –e "print 'Hello world'" – From Python, JS, SQL script files Scripting 17
- 18. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) • session – Assigned to the DB session when the shell connects to MySQL – The MySQL session used by the Shell in SQL mode – Execute queries Scripting – Globals 18 mysql-js> session.query(“show status”);
- 19. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Scripting – Examples 19 • Connect to myserver.foo.com • Execute create_account.js with any extra arguments forwarded to the script (sys.argv[]) • The script can use the session global variable to execute SQL in the target server $ mysqlsh root@myserver.foo.com –f create_account.js appuser@’192.168.%.%’
- 20. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Scripting – Non interactive execution 20 $ mysqlsh -–no-wizards • Disables interactive features – Passwords, confirmations, prompts, etc., disabled • Implicit when stdout is not a tty
- 21. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Document Store – X DevAPI 21 • Support for MySQL Document Store API • Create collections • Import JSON dumps • Try out queries and write simple scripts interactively • X DevAPI CRUD operations • SQL
- 22. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Other development APIs 22 • mysql – mysql.getSession(), etc. – Connects to MySQL server using classic MySQL protocol – run SQL queries – mysqlx for X protocol sessions and X Dev API
- 23. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Dev) Other development APIs 23 • shell – Programmatic access to most shell features – Configuration options (option) – Password Manager API – Pager API (pager) – logging – URI parsing – Prompting for user input – Establish connections: shell.connect(‘user@host:port’)
- 24. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Ops) • Administration API: AdminAPI – Creation and Management of InnoDB Clusters – Hides the complexity of: • Configuration • Provisioning • Orchestration – Simple and straight-forward – Doesn’t require MySQL expertise – Flexible, powerful and secure – Available in both JavaScript and Python 24 MySQL Administration
- 25. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Deploy and manage MySQL sandboxes: – dba.deploySandboxInstance() – dba.stopSandboxInstance() – dba.startSandboxInstance() – dba.deleteSandboxInstance() – dba.killSandboxInstance() Important: Sandbox instances are only suitable for testing purposes! MySQL Shell: Features (Ops) AdminAPI - Sandboxes 25
- 26. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Ops) • Shell Utilities: util object – Upgrade checker • Verify whether instances are ready for upgrading to MySQL 8 – Import JSON documents • From a file or standard input to a MySQL Server • Supports importing to a collection or relational table 26 mysql-js> util.checkForServerUpgrade(“root@localhost:3306”); MySQL Administration
- 27. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (Ops) • API Command line integration – New cmdline syntax to invoke built-in Shell commands – Requires minimal extra typing, quoting and escaping – Supported objects: dba, cluster, shell, shell.options and util Syntax: mysqlsh [OPTIONS] [URI] -- <object_name> <method_name> [argument_list] 27 $ mysqlsh root@myserver:33060 –- util import-json ~/Downloads/myData/data.json --collections=‘zip codes’ --schema=‘json_test’ MySQL Administration
- 28. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Display context information • Custom/dynamic tags that react to context – export PRODUCTION_SERVERS=192.168.42.16 MySQL Shell: Features (DBA toolbox) Customizable Prompts 28
- 29. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | • Sample prompt file: – mysql-shell/share/mysqlsh/prompt/ • Customization instructions: – mysql-shell/share/mysqlsh/prompt/README MySQL Shell: Features (DBA toolbox) Customizable Prompts 29
- 30. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (DBA toolbox) Secure Credential Manager • MySQL server passwords can be queried from a secure password store • Uses existing OS native password storage APIs – Keychain (macOS) – Credential Manager (Windows) – MySQL login-path encrypted files (all) • Optionally auto-save typed passwords (default will prompt) • Or use API to store programmatically – shell.storeCredential("root@192.168.1.32", "s3cr37!"); • Write your own password store backend 30
- 31. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (DBA toolbox) History • Typed command history • Persisting disabled by default, enable with – option history.autoSave=true • Filter SQL statements that may contain sensitive info – e.g. SET PASSWORD='my password' – option history.sql.ignorePattern 31
- 32. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (DBA toolbox) Pager • Pass SQL results through an external screen paging program – pager less – show status; 32
- 33. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Features (DBA toolbox) Built-in Help • Documentation for included APIs, shell commands, SQL, etc. • help keyword – (wildcards allowed) 33
- 34. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | --cluster • Prepares the Shell for managing the InnoDB cluster the target server belongs to • Automatically assigns the cluster global variable --redirect-primary • Connects the shell to the primary of the InnoDB cluster group, in case the one we connect to is a secondary --redirect-secondary • Connects the shell to a secondary member of the InnoDB cluster group MySQL Shell: Features (Ops) AdminAPI - options 34
- 35. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 35 MySQL Shell Development with the Shell Live Demo!
- 36. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to DevOps MySQL Shell Development with the Shell - Live demo! Shell meets continuous automation MySQL InnoDB Cluster Shell as the operations frontend of IDC - Live demo! Summary 1 3 4 5 36 6 7 2
- 37. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 37 • Continuous automation frameworks provide mechanisms for setup, configuration and management of MySQL Server • DBA can automate operations by using modules. • Classic example: Ensure MySQL Server deployments use the latest available version
- 38. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 38 • Let’s use as an example: service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], }
- 39. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 39 What if current is 5.7 and latest is 8.0? Will it break everything? service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], } ! • Let’s use as an example:
- 40. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Shell meets continuous automation 40 service { ‘mysql’: ensure => latest, enable => true, require => Package[‘mysql-server-community’], before => Exec [‘upgrade checker’] } exec { “upgrade checker”: command => “mysqlsh -- util check-for-server-upgrade { --user=root -- host=localhost --port=3306} --password=‘myPwd’” require => Service[“mysqlsh”] } • Use MySQL Shell upgrade checker
- 41. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Program Agenda Introduction to DevOps MySQL Shell Development with the Shell - Live demo! Shell meets continuous automation MySQL InnoDB Cluster Shell as the operations frontend of IDC - Live demo! Summary 1 3 4 5 41 6 7 2
- 42. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 100% Virtually all organization require their most critical systems to be highly available 42
- 43. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 43 • High Availability: critical factor • Replication as a common solution • MySQL has support for: – Classic master-slave replication – DRBDS – Other OS or VM solutions… MySQL InnoDB Cluster: Background
- 44. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL InnoDB Cluster: Background 44 • High Availability: critical factor • Replication as a common solution • MySQL has support for: – Classic master-slave replication – DRBDS – Other OS or VM solutions… Group Replication ! • Update-everywhere (m-m) • Virtually synchronous replication • Automatic server fail-over – Distributed recovery – Group reconfiguration • Powered by a GCS: Implementation of Paxos
- 45. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL InnoDB Cluster: Background • Challenging task – Not easy to setup and maintain – Technical knowledge needed – How to configure the applications? – How to integrate all the components? 45 ? ?
- 46. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | MySQL InnoDB Cluster: Vision 46 – Engineering Team “A single product — MySQL — with high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use.”
- 47. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 47
- 48. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 48 MySQL Group Replication InnoDB cluster
- 49. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 49 MySQL Group Replication InnoDB cluster App Servers with MySQL Router
- 50. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 50 MySQL Group Replication InnoDB cluster App Servers with MySQL Router MySQL Shell Setup, Manage, Orchestrate
- 51. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 51 MySQL Group Replication InnoDB cluster App Servers with MySQL Router MySQL Shell Setup, Manage, Orchestrate Clients
- 52. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 52 One Product Full Stack HA Solution Easy to use! InnoDB cluster
- 53. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | 53 MySQL Shell Shell as the operations frontend of InnoDB Cluster Live Demo!
- 54. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Summary • MySQL Shell brings together developers and DBAs • Unified interface: one tool! • Development and Administration APIs and features • MySQL InnoDB Cluster is THE built-in HA solution for MySQL: • Full-stack: High Availability out-of-the-box • Easy to use: usability as a top concern 54
- 55. Copyright © 2018,Oracle and/or its affiliates. All rights reserved. | Resources • MySQL Shell Userguide: – https://dev.mysql.com/doc/refman/en/mysql-shell.html • MySQL InnoDB Cluster Userguide: – http://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-userguide.html • APIs Reference manuals – JavaScript: https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/ – Python: https://dev.mysql.com/doc/dev/mysqlsh-api-python/ • Blogging – http://mysqlserverteam.com/category/high-availability 55