Introduction to MySQL

0:11Hi, guys. My name is Trevor Sullivan. And welcome to this CBT Nugget skill where we are going to be learning about the MySQL relational database engine. Now, MySQL is a relational database engine, which essentially means that you've got your data stored in all sorts of different tables. And there's relationships between those tables and things like that. And right in front of me here, I've got the reference manual for MySQL. And this is a massive set of documentation. I mean, there's just tons of information here about how to actually use MySQL.

0:44But before we actually get into all the nitty-gritty technical details about MySQL, let's first actually try to understand some of the business challenges that MySQL aims to solve. Now, let's say that you've come up with a really cool business idea. Maybe you're going to spin up a new line of product. You're going to launch a new business called Trevor's Apparel. And you are going to sell top-quality gym wear that is so comfortable that your customers are just absolutely going to love it.

1:15You've got all these customers who are potentially interested in purchasing gym wear. But you want to make sure that your customers, your potential customers out there, your prospects that you're going after to try to market to them, that they're aware of your brand and aware of the products that you are offering in the retail marketplace for gym wear. So one of the things that you could do is-- you might have heard about this whole internet thing. Well, in the internet, you can spin up your own website. And you can create a platform where you can have products and have search capabilities and have marketing capabilities and shopping carts and ordering systems and fulfillment systems and all these really cool functions that work behind the scenes to actually get products out to your customers.

2:03So when you start this Trevor's Apparel business, you decide to hire a team of developers. And they are going to build a web application. Probably not too dissimilar from many online retailers like amazon.com or any of the Shopify retailers that are out there selling different products, they are all building platforms that are web applications that these customers out here, these prospects that you are trying to market your products to, can access your service, your website, and search through your product catalog using a web application.

2:38Because pretty much everybody out there has a web browser these days-- they might be using a Chromebook. They might be using an Android phone. They might be on a Windows 10 desktop computer even like I am right here. As long as they've got a web browser, they'll be able to access your Trevor's Apparel website across the internet directly. Now, your web application is going to need somewhere to store its data, right? So you've got all this metadata that describes your products and your customers and things like their names, their first and last name, and what they're shipping addresses and what their credit card details are and what products they've put in their shopping cart and what orders they have placed on your platform.

3:24And on the fulfillment side, as your product is actually getting shipped out to your customers, you need to make sure that those are orders that are being placed by your customers, are going to be marked as fulfilled and shipped and tracked to make sure that those products are actually getting received by your customers. So in order to store all of that metadata about your business, about Trevor's Apparel, and all the products and all the orders and all of that customer data, you're going to need a data storage solution.

3:56And that is exactly where MySQL comes into play. Now, there are a lot of other competing products as well that are similar to MySQL. You might have heard of things like Microsoft SQL server or PostgreSQL or a bunch of other proprietary engines out there, like Amazon Aurora for example. But essentially, what a relational database does is it stores information about your business-- your products, your employees, your orders, and all of that type of information that makes your business actually function on the back end.

4:31Now, you're not going to expose that database directly to your customers because your customers would have to have very intrinsic knowledge about databases in order to be able to query that data, not to mention the fact that you need a security layer to ensure that customers are not able to get access to proprietary information about your business and that customers are not able to see data about each other as well. And so that's where your web application comes into play. Your customers can log into the web application, create an account.

5:03They can place orders. And they can look at their order history and track their orders and all that kind of stuff. And that is exactly what your web servers are going to do. However, your web servers, behind the scenes, are going to connect to your MySQL database that has all of that product and customer and employee information. And it's going to connect across a private network interface. And so typically what you'll see in a typical network architecture is that there is some kind of firewall that restricts access to a database server because it has such sensitive information stored in it, about employees, about customers and things like that.

5:40You don't want customers' credit card information or personal addresses or things like that being compromised. And so, in order to secure that, your web application can handle the authentication and authorization of the actual data inside of the database. So when your customers come out to your website and try to search for maybe gym shoes or gym socks or the most comfortable shirt to wear to go to the gym and things like that, they'll be able to search for that data in your database server.

6:12And you'll be able to store all of that data in the database server. You'll be able to update descriptions about products, add new products in there. And as long as those products are injected into that database, your customers will be able to search for them on your web application. So that's really kind of where MySQL fits into the picture. Now, there are a lot of different ways to run MySQL. You can run it using containers. You can use managed services from cloud providers out there. You can install it on a bare metal Linux instance as well if you'd like to.

6:45And we're going to get into some of those different ways that you can actually spin up MySQL and some of the different tools that you can use to interact with MySQL as well as you start developing a business application that's leveraging a database backend. In any case, let's go ahead and jump into our next video where we'll be exploring more about MySQL. I hope this has been informative for you. And I'd like to thank you for viewing.

0:00[MUSIC PLAYING] Hi, guys, and welcome back to this skill, where we're learning about the MySQL Database engine. Now, I wanted to discuss with you a few different competing database engines and a few different types of database services that you'll come across in the software industry. Now, MySQL, as we previously discussed, is a relational database engine.

0:31So our data is going to be stored in tables. And those tables will have relationships with each other. So, for example, if you think about maybe a table with an order ID, and maybe you have a table with customers that has a customer ID, you can actually create relationships between a specific customer and a specific order. And say, all right, this customer placed this particular order. And you can then draw those relationships by building those joins between different tables. Now, there are some other relational database engines out there as well.

1:03In fact, MySQL is owned and developed by Oracle. MySQL has gone through a number of different acquisitions. And I won't get too deep into all that history. But MySQL was acquired by Sun Microsystems some years ago. And then Sun Microsystems was acquired by Oracle. So MySQL, even though it is a free database product, there is actually also an enterprise version of it as well that is offered by Oracle. And Oracle is providing the support for the MySQL Project.

1:34Now, the developers, the original developer of MySQL, actually split off and created a GPL-licensed version of MySQL. So they kind of forked the GPL license compatible code, and they created a different tool known as MariaDB. So MariaDB and MySQL originally came from the same code base, however, they have kind of diverged now. And so some of the features that you find in MySQL may not be available in MariaDB and vice versa.

2:05So just be aware that MariaDB is certainly another option that is being actively developed in the open source industry. And it may be a great tool if your company is particularly sensitive around software licensing concerns. Now, these are just a couple of different relational database engines. There's also other types of databases as well. One of my favorite things that I've come across over the years has actually been what's known as a graph database. And what's really cool about graph databases is that instead of thinking about your data as these tables, like order table, and a customer's table, and an employee's table, and things like that, and all relationships that you have between tables, with a graph database, there's just a totally different concept, where instead of having this tabular data, you actually just have these nodes, right?

2:51So you can kind of see on their marketing landing page here, they've got these nodes. And they've got these relationships between nodes. So a node could represent an object, like an order. And the order could reference other nodes that could refer over to products that make up your products database. And with employees, you could have maybe a node that represents an employee at your company. And then have a relationship to another employee who serves as that person's manager.

3:21And so you can draw all these different correlations between data. Graph databases are really cool. It's a really cool way to think about data because it's just very logical in terms of how humans think about data representation. Now, I do have a couple of other skills over here at CBT Nuggets that actually cover the Neo4j Graph Database Engine. So feel free to check those out if you are interested. Now, there's also another type of database engine as well, known as a document storage engine. And this is typically-- you can think of it kind of like a JSON document storage engine, where you have just an object that has a whole bunch of properties on it.

3:57Maybe a person with a first name, and a last name, and a birth date, and a social security number, and all that kind of information. And you can just store all that data as just kind of a blob, or just a separate document that's separate from all other documents within the system. Now, these types of storage engines, like MongoDB, which is a document storage engine, gives you a little bit more flexibility over your data because you're not tied to a schema like you are in a relational database model. In a relational database, you have these predefined table schemas.

4:29And all of the data that you insert into those tables must conform with the schema that has been created ahead of time. And we'll get more into that a little bit later. But do be aware that there are these document storage engines, like CouchDB from the Apache Foundation, MongoDB. There's also one out there called Couchbase as well. There's a whole bunch of different document storage engines that are available. In fact, AWS, Amazon Web Services, they actually offer a service called a DynamoDB, which is a proprietary managed service for a document storage.

5:03Also Microsoft Azure, they have a service out here called Cosmos DB, which is kind of a document storage engine as well. So that is certainly an option for storing your data if you have less strict needs for your data schema. Now, another way to run MySQL as well is to actually leverage a managed cloud provider. So you could actually go out to a service like Microsoft Azure or Amazon Web Services, and these massive cloud providers have built managed services around MySQL.

5:35So while you are certainly more than welcome to go out to the MySQL page and drill through the documentation, and learn how to install MySQL yourself, and go through the upgrade process as new versions of it come out, you can actually kind of offload some of that management overhead over onto a cloud provider. Now, you are typically going to pay more for these managed services than if you were to operate the service yourself. But depending on your business's particular need, it might just be easier to leverage one of these managed services in order to run MySQL.

6:10So over here in Amazon RDS, you can see we can go out to Databases. We can hit Create database. And just like that, we have a wizard here that allows us to create a database engine that is fully managed by the cloud provider. So you don't have to worry about SSHing into a virtual machine and running all the software installation commands and creating configuration files and pointing your database server at those configuration files, and things like that. All of that kind of management is handled for you under the hood by these managed services.

6:42So that's a really nice way to run a variety of different relational database engines, as well as document storage engines as well, like DynamoDB, Cosmos DB, and even services like MongoDB. So if you were to search for a Mongo up here, Amazon actually has a managed service that's MongoDB compatible. And they call that Amazon DocumentDB. So there are so many different ways these days to run different types of database engines, whether you need document storage engines or graph database engines. In fact, speaking of graph database engines, AWS has a managed service here called Amazon Neptune as well that uses the Apache TinkerPop APIs in order to interact with that.

7:22And so Neptune is kind of another option for graph databases as well, in addition to Neo4j over here. So depending on your particular needs, you may want to explore different database engines, depending on what your specific business application is. But these are just some of the different data storage models that you have when it comes to storing your data in a database engine. We've got graph databases. We've got document storage engines. And we've got, of course, relational databases, like MySQL, MariaDB, Microsoft SQL Server, Oracle Database Server, and many others as well.

7:59That's kind of an overview of what your different data storage mechanisms look like on the database server side of things that are really high level. In any case, let's go ahead and better understand what a relational database does for us. I hope this has been informative for you. And I'd like to thank you for viewing.

0:00[JINGLE PLAYING] Hi, guys, and welcome back. In this video, we're going to take a look at what a database storage engine does for us in the relational database area. So essentially, we've got this sample diagram here thanks to the folks over at dbdiagram.io. Feel free to head over there and grab the sample database right here.

0:30There's also a bunch of other sample databases that are available online. In fact, there is a really popular MySQL employees database that is free, as well, that you can take a look at. However, this service right over here just has a really nice diagramming mechanism that allows you to see all the different tables in your relational database, and the relationships between those tables as well. So that's what we're going to get into in this particular video. Now, typically, when you are developing a business application, you are going to plan out your database and figure out what kind of data you're going to be storing in there and what the different data storage requirements are going to look like.

1:09You're oftentimes going to start with some kind of diagramming exercise, very similar to what we have in front of us here, where you're going to list out the different data storage points that you are going to have in your system. So if you are building a service like Trevor's Apparel, for example, you probably have a product suite. So you'd have things like a products table right here. And you'd have a name for the product. And that would have a particular data type associated with it. You'd have a merchant ID, perhaps.

1:40So if you're ordering products from other wholesalers and then reselling those products under your brand, then you could link those products over to a particular merchant, even if that merchant is yourself, even if you are the manufacturer of that original product. Also, a merchant might belong to a particular country. And so that merchant-- if I just move this table over here, you can see the relationship between the merchant and the country code. So we have this little blue line that's being drawn up here over to the country code.

2:12And so basically, for every country that we are either placing orders from for wholesalers to get product in or even where we have customers located, as well, then we can tie those customers and merchants back to a particular country. So right over here, you can see we also have a users table. And the users table also has a country_code field here that refers back over to the country where the user belongs to. So we have this common data set here called "countries."

2:42And instead of duplicating all of that data for every single order that's placed or for every single order to a merchant that is placed by us, by our company, we don't have to duplicate that data in one massive, flat data structure. We can actually normalize that data and put the data into different tables where it makes sense. So right up here, this table for countries is actually really, really simple because all we have is something that's known as a primary key, which is, essentially, just a unique identifier for a particular row of data in a particular table.

3:16So right here, "countries" has a primary key field of "code." So no two countries can share the same code. So if USA is ID number 1, then Canada cannot also be number 1, or France can also not be number 1. Only one of those countries can actually have the given country code. So if USA was number 1, then maybe Canada is number 2, and maybe France is number 3, and UK is number 4, and things like that. So that's what we call a primary key-- is basically just a unique identifier that is exclusive to a particular row of data within a relational database table.

3:56Now, the relationships that you see here between the primary key on countries and the other tables, such as merchants here, or between users and countries here, this is what we would call a foreign key. So, essentially, we are linking a field in a particular table over to the primary key of a different table. So essentially, what we're saying here is that the country code for users or the country code for merchants must match up to one of the codes of one of the countries that is defined inside of the countries table.

4:34So that's what we would call a foreign key relationship. Now, the other thing that you'll see here in this little diagram is the number 1 next to the country. And then down here, you have an asterisk. So essentially, what it's saying is that many different users in the users table could actually share the same country code. And so that's what the little asterisk there is indicating-- is that we could have many, many different users, maybe hundreds or thousands of different users, maybe over in, let's say, Poland, for example. And we could have thousands of users in Poland that all share the same country code.

5:08So we can have a one-to-many relationship between the single country and many different users that belong to that particular country. And the same applies for merchants as well. Maybe we have 10 or 20 or 30 different merchants that we are working with in a foreign country. And so the asterisk down here indicates that we could have a many-to-one relationship-- so many merchants to one particular country. So that's the relationships between different tables within a relational database engine.

5:39So you can see that there's a bunch of different relationships here. So up here under orders, for example, we have things like the order ID and the product ID. So essentially, what we're doing is we're linking products from our product table right down here. And we're linking those over to specific orders by linking over to an order_items table. And then separately, the order_items is linked over to a specific order within our system that's placed by a user. So this is what a simple database diagram would look like for a relational database engine.

6:14You could apply these similar principles to pretty much any of the relational database systems that we talked about in the previous video. But just understand that this is what the table structure, the schema of your database, might often look like. And depending on your business needs, the schema of these particular tables, or even additional tables beyond what's shown here, would morph over time as your developers are actively evolving those database structures. In any case, let's go ahead and take a look at some of the different ways that we can actually run a MySQL database engine.

6:49I hope this has been informative for you, and I'd like to thank you for viewing.

0:00[MUSIC PLAYING] Hi, guys, and welcome back. In this video, we are actually going to explore some of the different ways that you can actually run a MySQL database engine. Now, as we looked at previously, there's lots of different types of database engines out there. We looked at things like graph databases, and document storage engines, and relational database engines that are kind of competing with each other.

0:31However, we're going to take a look at some of the different ways that you can actually spin up MySQL for development and testing and learning purposes, as well as production capacity as well. Now, if you drill over to the documentation for MySQL over at dev.mysql.com, this is kind of the authoritative reference manual for all things pertaining to MySQL. It's very easy to get quickly overwhelmed in all this documentation that's available, and schemas, and data types, and all that kind of fun stuff, but we're just going to focus right now on just how to kind of install and get MySQL up and running.

1:08Now, MySQL is a database engine. As we looked at previously, it's kind of designed so that many different servers can communicate with that same database. Now, you might have more than just your web servers that are communicating with your database. You might have backend processes that are periodically running data from your MySQL database-- maybe old product information, deprecated products, and it's just kind of moving that over into more of like an archival type of storage. So it's not exclusively web servers that are necessarily going to be connecting to your database.

1:41Those are just kind of the most prominent servers, because they are customer-facing. They're facing your users, and your users need to be able to access information in that database via your web application. So just keep in mind that the MySQL database engine is designed to be network-connected and accessed from many, many different servers. So let's switch back over to the documentation right here, and take a look a little bit more depth. So MySQL is actually a cross-platform, and it's also an open source database engine as well.

2:11There are a couple of different editions of it available. So there is like a free community version of MySQL, and they also have an enterprise version of MySQL as well. So just be aware of that, that we are going to be talking mainly about the MySQL Community Edition here, but there is plenty-- more than enough to go through with that. MySQL Enterprise has some more advanced features, like thread pooling for example, and some more advanced authentication mechanisms, more advanced security tools, and things like that. So if your business does have those particular needs, feel free to explore MySQL Enterprise if you'd like to.

2:45Now, because MySQL is a cross platform utility, you can install MySQL on the Microsoft Windows platform. You can head out, and you can just download the MySQL package and install [INAUDIBLE] system. You can also use a package manager to install it as well, though one of the package managers that I always like to recommend is called Scoop. And so if you have Scoop installed on your system, which you can do with this little PowerShell script one-liner right down here, you can actually use Scoop to install some of the MySQL utilities onto your Windows 10 system.

3:17And that's a really nice kind of scriptable way to automate setting up a dev environment. If you want to set up a dev environment up in the cloud that's just easily repeatable so you can start it up and shut it down, and rebuild it from scratch when you need to, using the Scoop package manager avoids all the pointing and clicking that is necessary to go out and download a software installer to your system, and then double click the installer, and click on all the wizard dialogues, and things like that. But I definitely recommend using more automation-type tools, like package managers, on the Windows platform in order to get that installed on your system.

3:52Now, there are a couple of other ways that you can install it as well. So you can even install it over here on macOS either using the native packages or using a package manager for macOS as well. Same thing with Linux. Because MySQL is primarily something that you're going to see installed on a Linux system, there are different packages available for it through Yum repositories and APT repositories for Debian based distributions. Or if you're using SUSE Linux Enterprise Edition, there's packages for that, as well as RPM packages.

4:22So there are a whole bunch of different packages, depending on which particular Linux distribution you are actually going to be using. Now, I personally generally prefer using Ubuntu Linux, just because it's very user friendly. It's very well supported, it's constantly being updated, but there are many other Linux distributions out there that you can run MySQL on as well. Now, one of my personal favorite ways of running the MySQL along with many other software utilities and different programming run times is actually to leverage containerization.

4:52And you might have heard of Docker before, and Docker and containers has been kind of an evolving thing over the last six or seven years, where you can very easily spin up almost any software running up inside of a Linux container. And you can run things like Python, Ruby, JavaScript on Node.js, or .NET Core applications, or even applications like MySQL, or even things like MongoDB, for example. You can run all these applications inside of these isolated containers, and it makes it really easy deploy and scale applications either locally using an orchestrator like Kubernetes, or even up in the cloud using either Kubernetes, or some proprietary service, like Amazon Elastic Container Service, or Microsoft's Azure Container Instances, or Google Cloud Run and other services like that.

5:41Now, the reason I like using Docker is because for development purposes. If you're just learning about something, or you're developing an application just for your personal use, or you're just kind of tinkering around, using a container to spin up any kind of service makes it really easy to spin it up. And then as soon as you're done using it, you can just tear it down. You don't really have to worry about cleaning up a whole bunch of stuff because you haven't installed any software packages, and you don't have all these issues with conflicting dependencies. So if you wanted to run two different versions of MySQL side-by-side, like MySQL 5.7 and MySQL version 8.0, you can just spin those up in two separate containers running on the exact same host, and you're not going to run into those dependency conflicts.

6:25That results in all sorts of issues when you are trying to resolve them, and you end up probably having to just rebuild your system from scratch anyway. So using Docker containers can really help save a lot of the headache that developers run into in addition to allowing DevOps engineers to scale applications that are running up in the cloud. So if you head over to hub.docker.com, this is the kind of main package registry, or I should say container image registry for all sorts of container images out there.

6:56And if you just do a search for MySQL, there is an officially supported MySQL container image. In fact, there's many, many different container images out there. So just under the MySQL repository, if you head over here to tags, this is going to show you all the different versions of this particular container image that are available. So there is typically just the latest tag, which if you don't specify an image tag in Docker, it just assumes the latest tag. However, if you want to run a specific version of MySQL, like the latest 8.0 version, or maybe you want to run MySQL 5.7.35 or 5.6.51, or any specific version of MySQL, you can actually just grab the appropriate container image tag from the MySQL repository here over on the Docker hub.

7:46You can just use the Docker pull command right here to download that onto your Docker host, and then you can simply spawn a new container using the documentation that's available here. So they actually show you right here how to actually spin up a brand new MySQL instance. So essentially, you're just going to do a Docker run command. You could optionally give the container a name, although naming containers is generally frowned upon, just because-- I don't know-- treat them like cattle, not pets. And then one of the really important things here is going to be that you need to set a root password.

8:18And so the root password is going to be the password that you authenticate through the MySQL database engine as you use different tools in order to connect through that database engine. And we'll talk about some more of those tools a little bit later on, but you do need to make sure that you set a password. Otherwise, you will not be able to log into that container, into the MySQL service that is running inside of that container. And so just be aware of that. Now the other option here is the MySQL image and the specific tag that we want to run.

8:50So of course, we want the SQL repository here in the Docker hub, but then you can also specify that version specifier or the tag on that container image to determine exactly which version of MySQL you're going to run. Now typically, I'm going to be using the latest version of MySQL, version 8.0, but just be aware that, if you need to run automated tests, or if you want to run performance tests against different versions to determine if there is a performance difference between different versions, or you want to test your database upgrade processes across different versions, and things like that, that is one of the things that makes containers really, really great.

9:27Now, the other thing I want to make sure that you do as well is to expose a port to your host as well. So MySQL's standard port is going to be TCP 8080. And I don't see an example right here, but if you use the --publish-- let me actually just do a quick Control-F here. Now I don't see it. But if you just do a run --publish, that's basically how you can forward a port from your local host over to the container port.

9:57So if I do a docker run --env MySQL_ROOT_PASSWORD=-- let's say-- cbtnuggets123. And then maybe I do a --publish 33306 to 3306. I'm just going to choose kind of a random high port here on my host to just make sure it doesn't conflict with any of the lower ports, and then I'll just specify MySQL maybe latest, or I could do MySQL version 8.0, or things like that. Basically, this is all we really need to do in order to run a brand new SQL container.

10:31So if I just hit Enter here, you'll see that it's going to go out and download that container image from the Docker hub, and then it will go ahead and spin up a container on my system. Now, if you don't have Docker installed on your system, there are lots of ways that you can actually run Docker as well. So you can just head over to your favorite search engine and search for Docker Desktop. And Docker Desktop is a really cool tool that just allows you to spin up a dockerized Linux environment on your Windows or macOS system. And if you're on Linux, you can just install the Docker engine natively on there and use the Docker CLI in order to spin up a MySQL container just like we did over here in our terminal.

11:12So as you'll see, I've already allocated port 3306 on my host. So that's actually conflicting right now, but I could essentially just change this command and choose a different port, like maybe 3308 and forward that to 3306. And then we'll see all of this log information that gets spun up-- and this is all being spit out by the MySQL engine, or the entry point into the MySQL engine container. And it's basically going to initialize a brand new database server. And it will be available for us to connect to using different tools that allow us to manage a MySQL instance.

11:48But let's go ahead and look at those next. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00[AUDIO LOGO] Hi, guys, and welcome back. So far, we have spent a bunch of time talking about different types of database engines and different ways that you can run MySQL and what a relational database structure even looks like. But you might be wondering up to this point, it's like, well, how do I actually interact with this database engine? You said it runs on the network over here in your little network diagram right here.

0:33But how do I actually start developing an application? How do I talk to this database server so that I can build out my table schema and do upgrades and make exports of my database for backups and things like that? Well, we're going to talk about that in this video. So let's go ahead and jump over here into the MySQL documentation. And I want to start out by talking about some of the official tools that are available for MySQL. So previously, we were looking at the installation documentation here for different platforms. We're going to go ahead and collapse that for now.

1:06But I wanted to just draw your attention over here on dev.mysql.com under the documentation under MySQL Programs. Now there are lots of different types of MySQL programs that are available, depending on what you are attempting to accomplish. So if you are looking for modifying your installation of MySQL, there's programs for that. There is, of course, the MySQL daemon itself and the startup script and managing multiple SQL servers and all that kind of stuff as well. And then there's also these client programs.

1:37And this is really where you're going to get the bulk of your interaction with the MySQL database engine that's currently listening on a network port. And so we've got basically this really main tool right here, which is just MySQL. So we could essentially just spin up this MySQL command line utility and connect to a remote host or even our local host if we're using a Docker like we did in the previous video to create our host. Or if you're using a cloud service, you could connect to a remote database that's listening on a cloud service.

2:08Or even if it's on your local network, but it's just running on a remote device from the developer workstation or laptop that you're currently sitting at, then you could use that to do that as well. So MySQL CLI is the official CLI for connecting to that database server and issuing queries and exploring databases that are present on that particular host and things like that. There's also another tool here called mysqladmin as well that helps you to administer your MySQL instances. There is mysqlcheck that allows you to do table maintenance.

2:39There's another really important one here called mysqldump. So this is a utility that you can use to take backups of your databases, which is really important for business continuity in the event that your database server was to have some kind of disaster. Or maybe the cloud vendor that you are using for your managed MySQL instance, maybe they have some kind of major outage, which certainly has happened in the past as we've seen with many cloud provider outages out there. And essentially, what mysqldump allows you to do is just take backups of your database.

3:11You can restore those backups in an automated fashion and test them out to make sure that they have high integrity and make sure that if you do need to restore those databases that they'll actually work the way that your business units are expecting them to. And there's a couple of other ones here as well, like importing data using mysqlimport as well, mysqlshow to get database table column information, and then mysqlslap, which is essentially a load testing utility. So you can use this if you'd like to load test a MySQL database and to better understand how your application, your business application performs when the database is under a very heavy load.

3:50And that gets into a whole other area of software development, which is load testing. There's lots of different types of testing that you need to do-- user experience testing. Sorry, user acceptance testing rather. And then things like security testing, automated security tests, penetration testing. And load testing is just another one of those types of testing that you want to perform against your business application before putting it out into production so that you can understand how your application is going to respond to your end users if the database is under load, and if you need to make any tweaks to your application to maybe give your users helpful information to say, hey, our system is experiencing heavy load right now.

4:30Please come back later or something like that. Or if you just need the opportunity to scale out your application servers and make sure that you're able to sustain that load or implement some kind of caching layer, cache data to reduce the load on your database server, those are all options that you have as well. Now, there are some other utilities out there that we can use to interact with database engines as well. So one of those is an open source utility called Adminer. And essentially what this is is it's a web application that runs in a single PHP file.

5:01That's kind of its claim to fame is that it's a very simple installation. There's not a very complex project that you have to deploy. But this is essentially a web-based application that you can spin up either in a container, or you can manually install it if you're good at installing PHP and setting up your PHP runtime environment and things like that. There is a Docker container for it as well. There's also user-contributed packages here for like Debian packages and Arch Linux and things like that as well. But essentially, you can just spin up an Adminer instance and then log into the web interface and then use the Adminer instance to connect to your remote MySQL instance.

5:39And then you can start issuing queries against that MySQL instance to do things like manage your databases or view your tables or build out your table structure or insert data into tables or drop tables if you need to or pretty much any kind of SQL management tasks that you need to perform. Now, there's another very similar one to Adminer are called phpMyAdmin. And essentially phpMyAdmin is very similar to Adminer, just in the sense that it's a web-based interface. You can spin it up as a container image as well.

6:11So that just makes it really easy to spin up phpMyAdmin. There is a little bit more configuration that goes into it up front, just making sure that it's got all the right environment variables set so that it knows how to connect to your MySQL instance with the username, password, remote host, port, all that kind of stuff. So phpMyAdmin, I believe, is probably the most popular utility out there. But you do have a couple of options out there. Now, there is another one called MySQL Workbench here as well. And this is kind of an official tool for MySQL.

6:44One of the challenges that I actually have with MySQL Workbench is that it is a native application. And unfortunately, it does not scale very well on high DPI displays. It doesn't really look all that great either. So it's actually a little bit nicer to work with a utility that's web based, that scales a little bit more nicely. It runs in your web browser. You can just navigate to it without having to worry about installing any software packages and things like that. However, I would point out that you can actually install MySQL Workbench using the Scoop package manager as well if you're on the Windows 10 platform.

7:17If you just do a scoop install mysqlworkbench, I think-- there might be a dash in there somewhere. But essentially, you can just use Scoop package manager to get that installed if you do want to give it a try as well. Now, there are a couple of other utilities out there as well. One of those is called Sqlectron. And I actually found this one because I was looking for a quasi native client that's cross platform but also scales kind of nicely like a web application does.

7:47And thankfully, I came across this one called Sqlectron. And what's nice about Sqlectron is that it's built on the Electron JS APIs. And essentially, it is a web-based application. But it kind of looks and feels like a native application on your system. There's actually a whole bunch of utilities out there that are built on Electron that you might not even realize. In fact, I'm pretty sure Slack is built on electron, if I recall correctly. That's a pretty popular one. And there's a lot of other Electron-based applications out there as well.

8:18I know another one's called GitKraken. They used to be built on Electron as well. But luckily, there's actually a MySQL utility here that allows you to install a native application. It's actually just a zip file that you can extract. It's pre-built, prepackaged for you. You just extract a zip file on your system. And it's open source. So you can actually head over to their GitHub repository. You can file issues against the project if you run into any problems or have feature requests. It is a fairly popular project as well with 3.7 thousand stars on GitHub.

8:51I mean, it's not crazy popular, but it's a reasonably popular project to get that many stars on GitHub. And you can see it's been pretty recently updated, as recently as a couple of days ago. The latest official release was back on April this year in 2021. So just a couple of months old. So it is a pretty recent and up-to-date project. And you can just go ahead and download one of the software packages for it on your system here. So there's BSD packages. There's zip packages for Mac or for Windows, for Linux as well.

9:24There's a setup installer executable as well, Debian packages for Linux, RPM packages, pacman packages, and things like that. So a lot of different options to download Sqlectron. And that's kind of become my tool of choice so far as far as administering a MySQL environment goes. Now there are some other utilities as well. So we talked about MySQL CLI. We talked about Adminer, phpMyAdmin, MySQL Workbench, and Sqlectron. However, in the real world where you start working with very complex database structures in a relational database model, there's also another utility out there called Prisma Studio.

10:04Now, this is a little bit different in the sense that it requires you to actually use their schema definition language in order to create and load a schema into your database engine. But once you do, they have-- I believe this is Electron-based as well. But it's a cross-platform utility that allows you to connect to your database and view your table schemas and the data that's inside of your MySQL database and all that kind of stuff. So definitely check out Prisma Studio as well. And they actually have a really nice open source utility that allows you to define your schema in MySQL database engine and then deploy that and perform migrations as that schema changes over time as well.

10:46So I'd definitely recommend checking out Prisma. And we may look at even building some training on that in the future as well. So those are just some of the different utilities that we can use in order to manage a MySQL database instance. We've got MySQL CLI. We've got Adminer here. We've got phpMyAdmin and MySQL Workbench and Sqlectron as well. Most of these are cross-platform utilities as well, or they just run as web applications. So pretty easy to spin up. In any case, I hope this has been informative for you, and I'd like to thank you for viewing.

0:00[JINGLE PLAYING] Hi, guys, and welcome back. I wanted to just give you a quick overview of one of the utilities that we looked at in the previous video and show you just how easy it is to connect to your MySQL database instance that we spun up using a Docker container previously. Now, this is Sqlectron right here. And if you just refresh it here, you get this nice, little introduction screen that's kind of nice.

0:33They must have put some effort into creating that as part of the open source project. So that's kind of nice. But one of the things I really like about Sqlectron here is that it gives you this really nice connection management experience. So right now, I have a single connection configured right here. And it has this card-like interface that's very straightforward and easy to understand. So I have a big, old Connect button here, which, as the name implies, is going to connect to my database server that's configured there. I have a little profile name right here. I have the hostname and the port, as well, that I'll be connecting to.

1:04And I can just click this little Edit button right here to edit the properties of this particular connection. So I can change the profile name right here. So I could just call this CBT Nuggets Docker, for example, and just click on Save right down here. Now I've renamed my profile here. One of the other nice things, too, is that you can just hit Control-Plus and Control-Minus on your keyboard to resize the text here as well. So it's a nice, scalable application because it is built on the electron.js web APIs that make it feel like a native application, but not really be one.

1:38And then if we go back into Edit here, there's actually a bunch of different database types that are supported as well. So, of course, MySQL is supported. We've also got MariaDB, we've got PostgreSQL. We've got Amazon Redshift support here, as well, which is actually built on Postgres. It's just a column-based data storage instead of row-based data storage. We've got Microsoft SQL Server, SQLite, which is essentially just a local file, and then we've also got Apache Cassandra support here as well. So there's a whole bunch of different options available for the database types that you're connecting to.

2:10You can set up your credentials, your server address, maybe an IP address or a DNS name that you're connecting to, your user ID and password, your initial database if you would like to specify one. You have the option to enable SSH tunneling as well. So if you need to establish an SSH tunnel to a remote service in order to encrypt and secure your network traffic across the internet, that is an option here as well. You can enable that and plug in your SSH hosts that you're going to be connecting to and your private key and all that stuff for your SSH connection.

2:41So this is a really nice utility that just allows you to very easily connect to your remote databases. And then, once we've saved our profile here, we can just click on Connect. And you can see that it connects to my database here. This is actually a Docker container that I had previously spun up already and loaded a sample employees table into. You can actually see right here on the far left we've got our standard MySQL table, as well as our information schema and performance schema, and our system database here, as well, and, of course, my employees database, which is the one that I imported into this environment from the employees sample database that's available on GitHub.

3:18And then once you drill into the database, you can see all of the individual tables that make up that database, as well as any SQL views, as well as any stored procedures or functions right down here. And under the tables, if you drill into those, like employees, for example, you can see that we actually get the table schema here as well. We can see all of the columns that belong to that particular table. We can see the data type for each of those particular columns as well. We can see indices that are configured on that table, as well as any triggers, if applicable.

3:49And so this is just a really nice interface to just be able to explore your databases, your tables, your table schemas, and things like that without having to necessarily write a whole bunch of Transact-SQL code that you would typically use in order to interact with a relational database engine. And then separately over here under this editor section, we have this tab-based interface. So we can add more tabs over here on the right. And each of these tabs can hold a query that we can then issue against our server and our database that we would like to manage.

4:21So if we wanted to insert some data, we could just say "INSERT INTO," blah, blah, blah. And we could write that query. And then just click on Execute over here to execute that query. So for example, if I was to say "use employees," for example, and then maybe "select star from employees limit 10"-- let's just try to execute that. And you can see we get "Query executed successfully" here. And we have 10 rows of data. Let me just bump the size down a little bit here so it scales a little bit more nicely.

4:51And right down here, you can see the table of the query output that we just issued to the database server that's running over inside of a Docker container on our localhost, thanks to Docker Desktop. So you can see we've got our primary key column right here, which is our employee number. That's just a unique identifier for each row in the table. And then each employee has a birth date, a first name, a last name, a gender, and their hire date as well. This guy was actually hired a few days after I was born. Imagine that.

5:22So anyways, that's just a quick overview of Sqlectron here. It's just a really nice interface to interact with your database at a simplistic level. You can save and load queries and things like that. You can manage your connections. You can shut that connection off and switch contexts over to a different database server and things like that. So-- definitely recommend checking out Sqlectron as you start to explore MySQL. I hope this has been informative for you, and I'd like to thank you for viewing.