 So, a very special guest today is Hananto Wikatsono. He is a principal solution engineer at Oracle MySQL Global Business Unit, Asia Pacific. He has around 20 years of experience as an IT solution architect and database administrator focusing on design optimization, support, and solution using Oracle hardware and software. He published blogs on procedures of deploying MySQL on container-driven infrastructures and more on MySQL Shell custom plugins. Now, let's welcome Mr. Hananto Wikatsono. Mr. Hananto, over to you. Hi Mr. Hananto, I think you are muted. Okay, sorry. Alright, okay, good afternoon everyone and thanks for your time and interest in attending my session. So, this session will explain about MySQL Shell and its functionalities, facilities, and more. Interestingly, I would like to explain about Shell capabilities to do many, many things dealing with MySQL database. Despite of having a lot of functions in MySQL Shell that can help both JGA and developers dealing with MySQL database, these tools provide extendable framework that enables people to create their own custom plugin using either Python or JavaScript. And they can inject their own new set of commands into the Shells and use them. So, for me, I'm excited to share all of these Shell capabilities as well as how to do coding the custom plugin for anyone, for everyone who interested in using this Shell and coding the plugin for extending the Shell functions to meet your requirement. So, this is my modifications of giving this talk. So, this is the state hardware statement from Oracle. This is about me. So, yeah, basically, I'm a database architect and experience both Oracle database and MySQL. And I consider myself as a DevOps and database cloud native believer. And I love coding scriptings. I easily get fascinated with Math and Physics. And these are my tech blocks and GitHub where I share my workshop materials coding for my Kubernetes operator and coding for some custom plugin for MySQL Shell and much more. Okay, what is MySQL Shell? Okay, basically, MySQL Shell is MySQL database client tools, but it expands for various reasons. As a database tool, MySQL Shell is used for DMLs and queries against MySQL database. It's also a code editor for development with MySQL. It has Xdef API that enables developers to use MySQL Shell to create programs on JavaScript, Python, or SQL dealing with both relational data and unstructured data formatted in JSON document. You can use MySQL Shell, sorry, MySQL database as hybrid database, not just for relational database, but also for no-isql database. So you can use JavaScript or Python for CRUD statements against the no-isql portions of MySQL database. More than that, MySQL Shell has admin API that helps DBA, MySQL DBA, dealing with the inodb cluster provisioning, managing life cycles, and so on, as well as the load dump that helps the DBA also to do a logical backup and recovery. This MySQL load dump is basically an advanced logical backup and recovery to replace the traditional MySQL dump. So we will talk a little bit detail later on. All right, these are some of the available functionalities in MySQL Shell. So as a client tools and a code editor, MySQL Shell provides an interactive client execution mode. When you can type, command, and enter a statement, it will be interactively processed and printed on screen. So it supports unicode text input and color terminal for better look and feel, and it supports multi-line code enabling MySQL Shell to catch multiple lines and execute that as a single statement. So MySQL Shell supports batch code execution as well. They're taken from different source and process those lines in a non-interactive way. So script processing will be on single language, of course, and non-formatted output, unless you put interactive parameter when you run the MySQL Shell from the command line. You can use this to integrate with X&O tool as well. So the XTF API and protocol X in MySQL Shell enables the MySQL Shell to deal with the relational database and document store. So you can use CRUD for document store and SQL for relational table. So you can also format the output in traditional type of format also using MySQL Shell. You can also format it using JSON. You can also format it using tag-separated output. So later on, if you need to have a CSV, then you can just easily run the batch command to convert the tag-separated output into the CSV. It's very simple. So you can activate JSON wrapping for all output when you start MySQL Shell from the command line to help you to integrate MySQL Shell with X&O tool. So I highlight specifically for extension and utilities in red font in my presentation slide because these two are the main central topic of this talk. So this diagram depicts almost all functionalities of MySQL Shell. Some of them we already discussed in previous slide. Okay, we have JavaScript, Python, SQL. They are the main language. Okay, once you log into the Shell terminal, you can log into the database and deal with MySQL database using JavaScript, Python, and SQL. So we already explained also about the aspects execution mode and interactive execution mode and also the admin API that helped MySQL DBA deal with InnoDB cluster, XDAF API that helped developers to deal with MySQL document store, JSON document format, and so on. So we will focus more about the customizing MySQL Shell look and feel at all. I will discuss about that, how to customize things, how to change on them, and how to use command history to easily call command that has been executed in Shell without retyping it when you want to reuse it, which will improve your productivity when using the Shell. So MySQL Shell provide auto-completion as well because the command is so many, you don't have to memorize all those commands because it has auto-completions. Once you type a little bit, just press tap button, then the command will be auto-completed. If more than one command returns over there, then it will print out all possible commands and you just need to type again a little bit complete and then press the button tap, then deal with it. You can execute the command and so on. So the MySQL Shell also has a parallel import table for rapid data import to relational table for large data set. It will analyze the input data and distribute it into a couple of chunks and every chunk will have its own session and it has parallel connections to the MySQL database and it will complete faster than using a single-treated upload. It also has an import JSON, functionality to import the JSON document. If the import tables importing the relational tables, import JSON is really related to the importing JSON document. Just sharing a couple of tips to further speed up the import task. If your database is not in production yet and you can actually disable the VDLock or maybe disable the VDLock if your production is not part of the replication of code replication. You can disable the VDLock, you can disable the VDLock so that it will reduce the IO right during the import. So last but not least, MySQL Shell also has a functionalities to do a pre-check for MySQL 5.7, let's say. Before you want to upgrade, if you want to upgrade this MySQL Shell, sorry, MySQL database 5.7 to MySQL 8.0. So it will produce a report about things that you need to change first in your MySQL 5.7 before executing the upgrade and so on. Starting with MySQL Shell, MySQL Shell comes with support on Microsoft Windows, Linux, and Mac OS for 64-bit platform. Please use latest available MySQL Shell versions even though you have lower MySQL server version. So download MySQL Shell latest versions to play even though your MySQL server version is way below. Use this even for your MySQL 5.7 with limited functionalities even, you can use it for MySQL 5.6 but please bear in mind that not all capabilities for MySQL Shell will be compatible with that. As you see on the screen, I have the installation, how to install Windows, Mac OS, and Linux. In Linux, we have RPM, we have tar, and we have the MPT as well. So once you install that on your machines, then you can start MySQL Shell to connect to the database. First time, the MySQL Shell will form format in the default format and that's it. And then Shell mode is set to JavaScript. So to connect to the database, simply use backslash C or backslash connect, then follow by the connection string formatted as user, add server name or IP address and column database port number. Then you will need to enter the password and once password is validated, the shell will ask if the password shall be stored locally on MySQL Shell home directory or not. It will store locally in encrypted format if you accept yes. So the next login using the same terminal, it will be out of the login and you don't have to provide the password anymore. So automatically you can also mention the password in your connection strings, as you see on the screen. You can also use connection strings when starting the MySQL Shell. User, add password, sorry, user, column, password, add servers and column default. So if you are not happy with the default form format from MySQL Shell, then you can pick one of the form format templates from directly from inside the MySQL Shell installation directly. The form format template is a JSON format. So you can just need to copy this JSON file to MySQL Shell home directory as a from.json, as you see on the screen. Then when we launch MySQL Shell again, the form format will be changed. The MySQL Shell is really, really configurable. It relies on shell configuration that can be modified easily and persisted. So in order to show current configurations on the shell, we can execute command shell.option. So my slide shows the default configuration of MySQL Shell. Default mode for the problems is none. So that once you log in, then it will go to the JavaScript. And we also have the history of autosave that determine if MySQL Shell will memorize the commands that have been executed. If autosave is equal to true, yeah. So the history max size will determine how many numbers of past commands will be stored in the MySQL Shell command history. So we can change the MySQL Shell current configurations by issuing command shell.option.save persist, blah, blah, blah. So I give you on JavaScript as well as on Python. So we have JavaScript and Python. A little bit different, but do not worry because we have auto completion. Auto completion will help you. So you can also change the modes between Python, JavaScript and SQL as you see on the screen. It's very simple. So we don't have to change the mode from JavaScript to SQL. Let's say if you want to execute SQL command on JavaScript mode, because what we can do is just using backslice sql and followed by SQL statement in order to execute SQL statement on the JavaScript mode. So it's very handy. You don't have to change the modes. So this is the utilities on MySQL Shell. So you can use MySQL Shell to perform logical backup and recovery against MySQL that are this. MySQL Shell download utility on instance level, schema level, and table level. So it supports export on all schemars or selected schemars, all table or selected table in accordance to your need. Okay. What tables or schema you want to download. It supports online, non-locking, and consistent download load, but it is guaranteed that only, you know, the storage engine tables will be consistent. Yeah. So by default, the download will be multi-tracked with the default track is set to four. And then the utilities will open their own sessions for each track and then copy the options such as connections, compressions, and SS options from the global session and so on. So MySQL Shell download is also able to provide information about backup activity progress. Yeah. The progress information will include estimated total numbers of rows to be done, the numbers of rows done so far, the percentage is complete, and the throughput in rows and bytes per second. So once the done and load activity is filled, let's say, for example, I upload to MySQL developer service on the cloud and then somehow it is filled in the middle, then MySQL Shell actually records my backup progress, and I just need to rerun the backup with the same command and done and load will be running started from where it was filled. So it supports the backup on OCI storage bucket as well. It is compatible with 5.6.7 and technically can run on 5.6 as well, but with limited functionalities, okay, not all functionalities will be compatible with MySQL 5.6. So it also can run on drag and mode. So with drag and mode, it will check whether the command will be successful or not, what item to be done, is there any compatibility issue. Compatibility issue need to be fixed before executing the done load before real, yeah, for real with certain options. So interestingly, okay, the diagram that I presented on the slide on the right side basically shows the performance comparisons with other logical data tools, and it shows MySQL Shell done load superiority, yeah, in terms of performance. The benchmark runs using MySQL 8.4.21 with redo log disabled just to speed up the things and so that it won't, you know, based on the redo log speed and so on. So it is using bare metal OCI compute with 44 core Intel Xeon 512 GB RAM and 240 megabyte per second IO throughput using that zero. So the testing shows a huge throughput using MySQL Shell done load compared to other logical data tools in MySQL. So these are the commands in JavaScript and their corresponding command in Python and to dump the instance and dump schemas and dump tables. And I've given examples on the slide how to dump instance and put the dump files into the directory. Okay, it will run on portraits because I don't mention the parameters in that command. So once completed, we will have a backup files in encrypted format. So it will have a backup in a compressed format. Yeah, that's for data. And then for, you know, metadata, okay, it will create a SQL file. Yeah, SQL files to create the object again during the data loading and some of the files, let's say, Edward Jason. Edward Jason is very interesting. Okay, because shell dump instance can be consistent backup, the Edward Jason will show that consistency equal to true. And then it's also mentioned about GTID executed. It is very, very important. So it will record the last GTID executed, okay, once the backup is in. So it is very useful during the restoration, it will dump, as we will restore as GTID touch, and we can create a synchronous reputation based on GTID. If you want to restore for creating the read replica, very, very important. So this is the example to perform some schemas in JavaScript. Okay, we can put array of schemas that we want to backup in the backup location for dump table, we can put arrays of tables in the backup location as well. And if you want to migrate the data to MySQL database service, we need to use parameter OCI and VS equal to true. We need to use a strip diviner parameter because MySQL database service requires specific privilege, spatial privilege to create a database object. And then we need to use a strip restricted grants, okay, because we need to remove a specific privilege that are restricted by the MySQL database service from grant statements. So user and their roles cannot be given this privilege. This parameter is really, really important. If you want to load your data through MySQL database service on cloud on OCI. So a question, can we use MySQL shell dump to backup MySQL version 5.6 and load to MySQL 8.0 to perform out-of-place upgrade using logical backup restore? The answer is yes. You can do that by MySQL shell dump with caveat, of course. Use users parameter equal to false to exclude users, their roles and their grants creation in the dump. So once loaded to 8.0, then we need to create roles, grants and user again manually in MySQL 8.0. Without setting this user equal to false, the backup will be failed. This is for MySQL 5.6. So this is the syntax for load-down to restore backup to database both in Java script on the left and corresponding Python syntax on the right. The local in-file parameter is mandatory to set to on, to make the server permits local data loading by client that requests the local data loading in the database. Otherwise, the loading will be failed. For more options, you can see that. And then special for skipping load. Again, we can actually skip the locations by set the parameter skipping load equal to true. With this parameter, then all threads of the data loading will use SQL being not equal to zero. It will make the restoration faster and less this consumption. So this is the example of using MySQL shell load-down as part of the upgrade to MySQL 8.0. Ignore versions because we are using the lower MySQL versions and will be restored in MySQL 8.0. And then we need to use update GTID set so that the GTID executed on the lower versions will be applied to GTID parts of the MySQL 8.0. So once done, then we can create a GTID-based repetition. Very simple. So in terms of migration, data migration, you can create a baseline data migration by using MySQL shell-down and load it to the MySQL 8.0. While you load it, there is a transaction coming to the lower MySQL, the current production. So in order to make these transactions to be able to synchronize to the target MySQL 8.0, we can use a GTID specification and waiting for the copy. Very important and very useful. So this is basically clients that communicate with MySQL servers using X protocol and use XDAF API to develop applications while the MySQL shell provides this ability by implementing XDAF API and communicating to MySQL server through X protocols to do a CRUB by using JavaScript, Python, or simple S2L. And then MySQL server runs X plugin to communicate to X protocol to MySQL shell that implementing the XDAF API. It's very simple. So these are samples of MySQL operations in MySQL document store in MySQL 8.0. And using CRUD, we can do create, we can do read, update, and delete, and so on. And MySQL document store is an asset compliance that supports transactions, comments, and all that. So you can import the JSON document to an existing tables or collections to a new one created for the import, let's say. So this slide shows two examples to import product.json to MySQL DB schema and collection name is product. The syntax between JavaScript and Python are similar, but we don't have to worry to memorize the things because we have MySQL shell auto correct. So for the third example, okay, if the table, the target table, and collection does not exist in the database, then it will automatically created by the utility by using the JSON file name. So MySQL shell also has an admin API that gives a set of commands for DBA to easily deploy and manage the NDB cluster. So what is MySQL NDB cluster? It is a shared nothing architecture for MySQL high capability and scaling feature as integrated end-to-end solutions that easy to use, easy to manage using MySQL shell. So it provides consistency with no data loss, high availability with automatic failover, with replica scalability, and it can provide up to 99.99% system high capability SLA on MySQL. So MySQL shell supports JavaScript and Python in addition to native SQL mode for the admin API. So admin API is executed by a DBA global variable and it's associated method in MySQL shell. So it will be easy for the DBA to convict, go, deploy, and abuse the NDB cluster and NDB asset. You can use it for productions, you can use it for sandbox if you want to test the deployment before committing to a full productions deployment. So upgrade checker is used to verify whether MySQL server instance are ready for upgrade. It will automatically check the specified target release and giving advice for further check that we need to do manually. The upgrade checker utility can check the configuration files also for MySQL if we mentioned the configuration file, where is the configuration file, and so on. So it will check whether the existing configurations will be compatible with the higher release or not. For example, on the screen, we log in using MySQL shell to source the database via X protocol or classic protocol with a user that has reload process and select the release. So we run the upgrade and check the output and we review the finding and perform the necessary adjustment before continuing the upgrade. So in this case, MySQL shell is really, really helpful, really is a great tool to do a lot of things with MySQL database. We like as a DBA or developer. So next, this is our programming in MySQL shell. MySQL shell has expandable framework that we can use to create our own customized report or we can recall it as user defined report. So we can call this report once using backslash show or we can constantly refresh the output using backslash watch. And of course, we can mention the interval it are. So in order to view how many of customized object or user defined report that we have in MySQL shell, simply log into MySQL shell and issue commands backslash show. Then it will show the release of customized report or user defined report. And to execute one of the report, simple just backslash show and then followed by the user defined report name. So this is the user defined report sample that I created in my GitHub. You can check. I mean, sorry, it's not this. This is my friend's GitHub. So first of all, we need to create the directory. First of all, we need to create directory. What we call it as plug-in group. The plug-in group will be sitting behind or will be sitting under the plug-in directory in MySQL shell home directory. MySQL shell home directory is dot MySQL directory that exists in our home. Once we execute the MySQL shell, then it will automatically create the home. MySQL shell home directory under our user home directory in our OS. So as you see on the screen, we have register report. Shell register report is used for registering our user defined report so that it can be recognized by the MySQL shell. So this user shell register report we mentioned about the procedure that will be being called by this report. So we need to still write the Python or JavaScript procedure as you see on the screen. We can use depth lock info. Lock info is the name of the report and then session is the parameter. And then this is the query and the query will produce the JSON format. I don't want JSON format. Then I need to put this output into the variable array variable. Then I need to loop in this array variable and reformat in the output and so on. So the output is something like this. So this is how to run a report. We have backslash show, we have backslash watch, and we can put the interval. The interval is number of seconds in between. It's 0.5 seconds. It will show. So we also have null CLS that specify the screen will not clear before refresh and so on so that we can always refer to the previous result and so on. So the last one, which is report.lock info is how to call using shell method using this shell function. And shell.get session is basically to get our current session because we have this code, we have this parameter for session. That means we need to parsing the session parameter. So shell.get session is the procedure to get a current session when we log in or connect to this. So this is the build in report of mysql shell. We have tracks, query, and track. Tracks will report all the mysql tracks available, whether it is foreground or background. And then track, we can specify which track ID, which TID needs to monitor and so on. And then query, it's just simple. We can put the interval also because we use xwise watch. And as I mentioned about plug-in group, plug-in group is like, you know, sub-directly under dot mysql sh slice plug-ins directory. Yeah. So as you see there, I have a couple of the plug-in plug-in group. I would need extension, firewall, quick replications, Kubernetes, and so on. So under this directory, I will have the what I call it as init.py. What is my init.py? Okay, that's all. I will explain. Oh, yes, this is the init.py. If we, you know, code our plug-in using Python, then we need to have init.py inside this plug-in. Or init.js if you are using JavaScript. So only init file will be loaded by the Mysql. When it is loaded, I mean, Mysql shell. When it is loaded, when we start Mysql shell from the command line from the old one, then, you know, the init whatever procedure, whatever, you know, like the init will be loaded. So this is how to run our plug-in, which is a validated password. So actually with this, all of this plug-in, I pick up the simple one that I created, which is a validated password. So in order to know how many numbers of methods inside this plug-in, then I just issue command value underscore pass and dot, and basically press button tap, then I will have install plug-in and set policy. The Mysql shell framework will add another one, which is help. So I can create help actually using the init file. The init file, either on JS or on our UI, we can put the three text over there that if we run help, then that three text will be appeared. All right. So this is the samples of Mysql shell plug-in inclusion. Let's say I want to execute the help, something like this, then this is the three text, three defined text. And inside the validated password directory, I have two files, basically init.py, which is the mandatory, the main file that being executed by Mysql shell, being loaded by Mysql shell, once we execute from the OS command, and password validation.py. Password validation.py I ignore. Mysql shell will not read that. However, if I segregated the code outside the init.py because I want to have the clean, I want to have the clean init.py. So usually what I did, I create init.py and then I import another file, which is password validation.py, or any file that I want to run inside the init.py. So I import that. Yeah, this is the samples of init.py. So the class is validated password. And as I mentioned, okay, because my code is residing on validated password.py, then I need to import that into the init.py. So that when Mysql shell is loading, Mysql shell is loading all the plug-in, then it will load whatever code that available in password.validation.py as well. Yeah, this is the plug-in samples, how to run the code. As you see on the screen, I have two declarations, the important declarations. The first declaration plug-in Mysql function is telling the Mysql shell that I want these functions to be available on the shell. Without this, then these functions will not be able to be called from the Mysql shell. Yeah, this is to say to the Mysql shell that hey, I need to load this coding. Yeah, because maybe inside one code source code, maybe not all code we want to be available. Yeah, we want them to be available online on the shell. So we can just pick and choose which commands or which procedures need to be able to be seen from the Mysql shell. Yeah, so this is the install plug-in, is the procedure definitions, and this is the help. Yeah, as you see on the screen, I can put help over here, and this is the core coding. Yeah, the core code. The same like this. Okay, I have a function, validate password.setPolicy is how the Mysql shell will expose these procedures on the shell. And setPolicy is, depth setPolicy is basically like Python's procedure declaration and so on. And this is the main coding. So this is how it runs. Okay, usually in Mysql, we can install plug-in simple by install plug-in, blah, blah, blah, and so on. But with this, it's even simpler that you should command validate password to install plug-in, then it will install validate password plug-in. And the other one, validate password.setPolicy is to set the policy for the password, like password length, password, you know, calculator, number of calculator, uppercase, lowercase, numbers of the special characters within the password and within the same, within one password and so on. So without this, okay, we can use, we can do easily as well, no worries, but with this, validate password plug-in, at least it will speed up the DBA work. We don't have to type in multiple commands and remember various, you know, system variables in Mysql to configure the password complexity and so on. This is the very simple plug-ins, but there are a couple of plug-ins that we created in a GitHub that are more complicated than this. Feel free to do the GitHub. Okay, this is the GitHub that we shared the code, okay, which is a AAA underscore, there's sg at master sg, so you can see a plug-in that I uploaded over there. And this one is working from my colleague at E-FRED, AAA underscore sg at slice-master-shell-flagging. And this is all the blocks that written by E-FRED, very useful that, you know, is very interesting to read that post-block.