MySQL Shell Does All The Things!

Inside MySQL: Sakila Speaks

For this episode, Fred and Scott are joined by Miguel Araujo, Senior Principal Software Engineer for MySQL Shell. Miguel outlines MySQL Shell's history and discusses its more popular and powerful features. The conversation winds down with us discussing our favorite features.

---------------------------------------------------------

Episode Transcript:

00;00;09;13 - 00;00;30;16 Welcome to Inside MySQL: Sakila Speaks, a podcast dedicated to all things MySQL. We bring you the latest news from the MySQL team, MySQL product updates and insightful interviews with members of the MySQL community. Sit back and enjoy as your hosts bring you the latest updates on your favorite open-source database. Let's get started. 00;00;30;20 - 00;01;01;11 Hello and welcome to Sakila Speaks, the podcast dedicated to MySQL. I'm leFred and I'm Scott Stroz. I know today's guest for a long time already has had the chance to work with them on several solutions. Please welcome Miguel Araujo, Senior Principal software Engineer on the MySQL Shell Team. Miguel is the technical lead developer of the Admin API, core component of the HA and the replication solutions like the MySQL InnoDB Cluster MySQL InnoDB replica set and MYSQL InnoDB cluster set. 00;01;01;28 - 00;01;25;07 Welcome, Miguel. Hey guys. Thanks for inviting me to this podcast. So, Miguel, we want you to talk about MySQL Shell. Can you give us a high-altitude overview of what MySQL Shell is? Basically, what it is, who should be using it and why? So first and foremost, Shell is the command client for my SQL Server, right? 00;01;25;21 - 00;01;50;28 You can do with Shell what you could do with a classic MySQL client. So, you connect to a server to my SQL server and it can run queries on it using SQL. But Shell is much, much more than that. It's a... we like to call it a modern advanced command client for MySQL server, and maybe I should get a little quick history lesson about it. 00;01;50;28 - 00;02;27;00 So, Shell came out with... there was a purpose to build this new command client and this kick comes back to 2016. If I recall correctly, when the document store was being developed and implemented. And with MySQL Doc Strore we have created this new API to interact with it, the X-dev API, and this X-Dev API follow the common standards and this was implemented in JavaScript in Python, and for that we needed a new shell, a new command line client to interact with the server and speak. 00;02;27;10 - 00;03;05;16 This is new API and interact with the Doc Store. So, for that we create this shell with the with implementation of the X-Dev API exposed in JavaScript and Python. So Shell was born with a with this multi-language support so SQL classic and then JavaScript in Python and in the beginning with with the X-Dev API exposed to those two languages. But we also built it as a modern interface, so it has a customizable and reactive prompt. 00;03;05;29 - 00;03;32;21 It has auto-completion, syntax highlight. It has a built-in help system. So, it's built as the for example, we have in Linux bash or the Shell or on Shell or whatever or in Windows PowerShell and Shell can be seen as something like that. But for MySQL, so you started your shell, you can run commands in it, you can connect to instances and operate on them. 00;03;32;21 - 00;04;06;21 So, it has this interact interface and also a scripting interface because since you have support for those languages, for JavaScript Python and SQL you can write your own scripts and then you can execute them in in Shell. And a another general feature ... most known and, and useful are the APIs built in. So I was just mentioning the X-Dev API that was the first one. 00;04;07;07 - 00;04;37;05 And then we of we have introduced the Shell API and the utilities and then the Admin API and the so starting with the Shell API, for example, we have operating system utilities. You can create reports, you can create plugins. The utilities include things such as the upgrade checker that is a utility built in shell that you can use to verify whether your server is ready to be upgraded to the new version. 00;04;37;20 - 00;05;09;05 And if not, what needs to be done. You have things as dump and load to dump instances and load them. This is very fast. It's it's very loved by the community and the Admin API that is used to deploy MySQL architectures like Fred said in the beginning – InnoDB cluster states replica set and so on. And also Shell is extensible, can write plugins for it and that's something great. 00;05;09;05 - 00;05;35;10 And that's one of the things that makes it a modern client. Yeah, to be honest, I am a very fan of Shell. It replaced the old MySQL client for me, the classic one I always use Shell everywhere. And so recently during the MySQL Belgium days, who you are there, you were speaking also about Shell. Many people like Booking.com and Canonical guys. 00;05;35;18 - 00;05;58;14 They praised MySQL shell. They use it, they love it, and especially the admin API. So let me ask you a personal question. As the Admin API is your baby, are you proud of that? Oh yeah, I am. And it's it's you know, it's one of the most rewarding things that you can get as a as a software engineer is to to get this kind of feedback from the user. 00;05;58;14 - 00;06;34;08 So, I know now that I didn't waste seven years coding this thing for nobody to use. It's I don't know that there's a lot of people happy with it and lots of users and, and people like Booking.com or Canonical using it. It's, it's, it's really motivating. It's, it's really rewarding. So, I'm quite, quite happy about it. And and not only that they, they are users there but they use it extensively and they have huge deployments and the kind of feedback you get from those those users, it's it's it's great. 00;06;35;03 - 00;07;02;09 It validates many of our decisions and invalidates others and provides feedback to make things better for the future. So, it's it's really it's really great, really happy. So as a developer myself, I can appreciate the the feeling of hearing that other people like what you've created. So, congratulations on that. Yeah, thanks. Now, you and Fred have have both talked about the admin API. 00;07;02;24 - 00;07;44;10 Can you give our listeners some details about the API and what can be done with it? And is there any functionality in the API that maybe hasn't been implemented yet? Okay. So again, a bit of historical context. So, MySQL has had support for replication for a very, very long time. Right? There was the asynchronous classic replication and before we put before Shell and the other API DBA and, and sysadmins and so on, they, they had to deploy those replication topologies manually. 00;07;44;20 - 00;08;18;09 And that involved a lot of steps, manual steps. It involved a lot of scripting, automation, monitoring and so on. And that's led to many customized, customized solutions, lots of deviations and lots of work for this. These people maintaining those architectures. And then in 2016, 2015 of them remember exactly the group replication was being developed and it came out as a preview. 00;08;19;17 - 00;08;50;17 And with that we thought. So, we have Shell, we can write APIs in Shell, we can manage instances of MySQL, so, let's do something to help users to deploy MySQL architectures. So back then, architecture based on group replication and asynchronous replication. And let's make an API to make that super easy to deploy, super easy to maintain and ...and to monitor. 00;08;51;01 - 00;09;19;28 And this is how the Admin API was born. Initially we focused on group replication because this brought a lot of great things, such as out of a lower consistency network, partition handling and so on. So that was the first focus and we, we've, we've developed the API with that in mind because group replication wasn't and still can be tricky to deploy and maintain. 00;09;20;15 - 00;09;48;07 And with the Admin API with just two or three commands, you can have your cluster up and running. It's very easy and it's very, very straightforward and it's pleasant. We tried to focus on usability a lot to make the experience pleasant, but also it's you're also it's powerful in the sense that you also can do things and configure things to the data. 00;09;48;07 - 00;10;34;13 So, so the Admin API was born from that need. And we, we introduced the support for InnoDB Cluster, but then we also added support for sandbox management. So, you can deploy sandboxes to test the solutions before going into production. You have things to help you configure the instances to be ready for the MySQL architectures. So, with a single command you can connect to an instance and the command will tell you some instances where they are not in the sense of if if the right configurations are in place and if they are not, you have another command that will just apply them for you. 00;10;34;17 - 00;11;22;26 So crazy. You also can create accounts to manage the topologies and then you have the whole set of commands to deploy InnoDB cluster, InnoDB replica set, cluster set. And the last addition is read replicas. We have integrated the whole thing and we have also integrated provisioning. So, for example, when you add an instance to a cluster, we will take a look at the GTID sets and the replications, the status of the data sets and so on, and we will guide the users to to the right provisioning method either based on replication or clone, and Shell will automate everything for you. 00;11;22;27 - 00;11;53;29 So, users will have to do any tricks and configurations. It's all integrated and with with the together with all of this, the router management that is another piece of the solutions. And one of the things we we were also careful when implementing the Admin API was to follow best practices as we would deploy the topologies with the recommended settings as defaults. 00;11;54;07 - 00;12;21;04 Then users can change of course, but we tried to follow the best practices. So, this is what the API is about. It's an API built in shell, in shell, everything is written in C++ just so the details and then those things are exposed as APIs in JavaScript and Python. So, you can access the the language of your choice, you can script it. 00;12;21;13 - 00;12;47;14 You can also call Shell with this command, the command line API. So, you can call Shell as the shell binary and in a non-interactive way, so called the shell binary. And after that you can pass the parameters to Shell and those parameters can be calls to the Admin API. This is good to integrating in other scripts or other tool things. 00;12;49;12 - 00;13;43;07 I think you asked before what's missing from the admin. That's that's the hard question that because there are so many things that can be done I can for example in with the general feeling that I get or one of the things that people ask recently is to have the an API as a standalone Python library, for example, the canonical folks were requesting that and there's there's a lot of people that's write scripts in Python to use the Admin API and those scripts have to call shell after a shell somehow there are multiple ways of doing it and then they they use the other API and some people think that it would be better if they could 00;13;43;07 - 00;14;14;21 import in their Python scripts the API is a standalone library, so this is one thing possibly missing that we might work on and provide to the users. Somewhat related would be a different approach Approach to that would be to have Shell running as a demon and with an HTTP interface and the API will in that case be exposed via REST. 00;14;15;08 - 00;14;55;22 So, people could just to the rest API calls to run the API. So, this will make the API language agnostic. So no JavaScript or python. This could be something nice. In terms of usability. So, the Admin API most of the output is JSON. This is a standard practice in this kind of API. So for example, when you call cluster starters, you get the status of your cluster in a JSON object with all the topology and the status based for every instance. 00;14;57;15 - 00;15;36;08 Maybe some people like to have a different output. For example, table or YAML, or text based and maybe add some colors on it. This could be interesting. What else? In terms of architectures? MySQL architectures there are some things missing. For example, support for multi-tier replication in replica set or or even with replicas. So, you could build trace of replication to offload servers to be something interesting. 00;15;36;08 - 00;16;16;24 There is something missing that I would like to work on eventually. That is a better framework for options management. So, option management, I mean settings of the cluster members, something better to apply a setting or no members or are a kind of this I would like to call it an options framework. So, make it easy to change options so users could do those things using the Admin API instead of logging to each instance and do the changes manually. 00;16;16;24 - 00;16;48;15 There are some things to work to integrate better. MySQL router in the sense of manage management of other instances and also monitoring. It would be nice to have something like this called self-healing. For example, you have a cluster and one of the members is missing, but it's reachable. The way to to solve that is to call a rejoin instance. 00;16;49;05 - 00;17;09;11 We have a cluster self-healing command that will call the command, and the command will take a look at the cluster status. And for example, if this is an instance missing but is reachable, will rejoin it or if it needs that the risk and needs to be called will call it. So, kind of a command to encapsulate that would be could be nice. 00;17;11;02 - 00;17;41;19 We also have some things missing regarding the enterprise features. So, support for firewall or enterprise Firewall and enterprise audit as well. Yeah. From the here that's a can remember now and also what I can share but is missing from the Admin API. So there's there's work to be done. It never gets boring. Yeah it seems so so you have plenty of new challenges. 00;17;41;19 - 00;18;09;25 So even if we reach something very mature right now, we can see that there is still plenty stuff to do. But, you know, I will tell you a secret. I have deployed maybe once or twice a group replication manually wrote all of the rest of the time. I always use the the admin API with MySQL shell and the and even asynchronous application. 00;18;09;25 - 00;18;38;23 Now I would say I don't even remember how to do it manually. Replica set is so easy to do, so there is no secret than that. So yeah, you said that MySQL Shell support so as well, which is great of course Python and JavaScript. But I've also heard from the users, you know, and I know we provide all the binaries, but there are still a lot of people in the community that likes to compile themself. 00;18;39;05 - 00;19;06;18 Their binaries, right? And they compile also the shell and the, but these guys, they tend to skip building MySQL Shell with the V8 engine, so the, the engine for JavaScript. So, I would like to ask you if there is an issue to not have a JavaScript support in the shell, because when we check our documentation or blogs most of the time everything it's written using JavaScript, right? 00;19;06;26 - 00;19;46;18 So, would it be a problem to not have a JavaScript compiling in my shell? Well, no, not at all. It's just a c-make a flag, you can disable it and you can get shell without V8 and without JavaScript support and there's no issue at all anyone can do it. And I so there are historical reasons why about your the second part of your question, the way we we have documentation and examples all written JavaScript and it's historical reasons, JavaScript was was the default of shell. 00;19;46;18 - 00;20;11;26 It's changing in the next release to SQL by the way. So, JavaScript was the default for historical reasons because of the dev API, and that's why the other API examples and so on were all written in JavaScript just because it was the default. I think now we understand that very few people use it in JavaScript and most of our users use it in Python. 00;20;12;13 - 00;20;48;01 So, I think that we should definitely start blogging and exemplifying and possibly changing the documentation to, to use to write the examples using the python. So yeah, we should definitely do that. Yeah. Thank you. Yeah. So there is no limitations to everything that you expose in JavaScript. You expose it also in Python. Right? Exactly. So like I was saying before, Shell is written in C++ and then we have language bindings and we expose the APIs in both Python and JavaScript. 00;20;48;08 - 00;21;09;15 If Shell is built without JavaScript support, then those are only exposed in Python. So there's absolutely no no limitations. And I have a favorite feature of MYSQL shell, which I'll share after you tell us what is the feature that you like the most or use most often that you don't think other people know about or don't think they use enough? 00;21;09;18 - 00;21;28;12 Has to be one. I think I have many. There's one that I think a lot of people are not at least not aware of it, and that's the built in help system. I see people relying on the API documentation and so on went online when they could have it in shell itself and it's so easy to access it. 00;21;28;12 - 00;21;57;12 Just do slash help or slash backslash help or backslash question mark. And then after it you put text and you can have access to, to the to the built in help system. So, for example, the backslash Admin API and it get the description of what the admin is, what objects are available on the API or what the classes and for example is the backslash cluster and it will print to the available cluster commands. 00;21;57;26 - 00;22;24;15 Then it can access every single command, help information. It's really, really helpful and you do it all in shell. You don't need to go to a browser or anywhere else to consult that information. And there's there's more to that. You can even, for example, connect to an instance. You establish a session to an instance, and you can access the information about the the commands you can do. 00;22;25;05 - 00;22;52;16 The backslash help, for example, show and it'll give you the information about show or transactions or all the the the supported and MySQL commands. And this is really good sometimes I'm writing a query and I need to understand or to get some extra information about some specific command. And I just do it in Shell and this is just awesome. 00;22;52;16 - 00;23;14;28 But there's, there's more. Okay. And this one Fred loves that is the plugin supports that. Fred even has a repository in GitHub with lots of plugins. So, what I also contributed to some of those and I think this there's still some people that are not aware of it. You can extend Shell, you can write your own plugins in Python. 00;23;15;13 - 00;23;46;04 It's it has become really, really easy to do it and it's awesome. I wrote my own plugins to do management of my testing environments. For example, and I love it and I think people should be, should be, should use it more. So that's one of my favorite features. I have more. I don't know if you want to share your Scott Well you stole mine...it was the help system. 00;23;46;06 - 00;24;13;09 Oh right. Oh cool. Yeah I use that. I use that all the time because I just can't remember the, the exact syntax or most of the time it's, it's trying to make sure that I have the options right for whatever, you know, configuration block that might be available for a particular command. But I use that all the time and it's like it I was like really surprised how comprehensive some of the documentation is through there. 00;24;13;15 - 00;24;34;22 Fred, what's your favorite? I won't say it's my favorite, but one. It's not. It's not commonly used and it could help a lot of people. It's the reporting framework. So like you can write your plug ins, but you can also write your reports and there are some built in reports and there I see very, very few people using those reports. 00;24;35;07 - 00;25;02;03 And the sometimes it's quite cool that you can have a small shell window running and that to make your own. For example, I created a report to see the the speed of the group replication are running in a cluster and you can see a life, Oh, it works. Well, I think that this is cool under very underused or not even used by people and I think they did. 00;25;02;03 - 00;25;27;24 They should because it's very, very interesting. Yeah, I agree. We have some built in reports but it's like you said, it's really easy to create new ones and then you can use, for example, slash watch and summary parts and you have a shell window. They're running every 2 seconds the reports and you get information. For example, memory is or threads and these kind of things. 00;25;28;02 - 00;25;57;27 It's really useful and should people know about it? There's something else that I use a lot and is the is you can execute system commands in shell and I think no one knows about it then you just do backslash system and you can do system command you can do a sudo apt-get install or you can do an ls or anything in shell. 00;25;58;04 - 00;26;26;25 So it's you don't need to switch the terminals to the commands. It's, it's really cool and really cool and similar and it's not the same but there's something else that is is the edit commands with edit Shell will launch your default editor for example. VI and I use this a lot when I'm testing admin API stuff, so I do slash edit vi is launched and I write my commands to create a cluster. 00;26;26;27 - 00;27;06;11 For example, I deploy some sandboxes I call create cluster, I call out these things, etc. etc. All in this API editor and then I do save and all that information goes to show and just it hit enter and everything is executed. It's it's really useful. I like it a lot. What else. There's something that people ask a lot and is so Shell has it saves the history of your comments per session and sometimes people say, Hey, I lost my session history because I closed Shell, but you can save it. 00;27;06;26 - 00;27;26;29 It's just a slash history save and it will save the history in the file. And when you launch Shell again, you will have the lowest days through there. I also use this a lot, so it's very useful. So yeah, those are my favorite ones. That's all for this episode. Thank you to our guest, Miguel Arroyo, and thank you for listening. 00;27;26;29 - 00;27;49;01 Please join Scott and I again next time for another Inside MySQL: Sakila Speaks. That’s a wrap on this episode of Inside MySQL: Sakila Speaks. Thanks for hanging out with us. If you enjoyed listening, please click subscribe to get all the latest episodes. We would also love your reviews and ratings on your podcast app. Be sure to join us for the next episode of Inside MySQL: Sakila Speaks.