Introduction to MySQL

0:11Hi, guys.

0:12My name is Trevor Sullivan.

0:13And welcome to this CBT Nugget skill

0:15where we are going to be learning about the MySQL

0:19relational database engine.

0:21Now, MySQL is a relational database engine,

0:24which essentially means that you've

0:25got your data stored in all sorts of different tables.

0:29And there's relationships between those tables and things

0:32like that.

0:33And right in front of me here, I've

0:34got the reference manual for MySQL.

0:37And this is a massive set of documentation.

0:40I mean, there's just tons of information

0:42here about how to actually use MySQL.

0:44But before we actually get into all

0:46the nitty-gritty technical details about MySQL,

0:50let's first actually try to understand

0:52some of the business challenges that MySQL aims to solve.

0:57Now, let's say that you've come up with a really cool business

1:00idea.

1:01Maybe you're going to spin up a new line of product.

1:03You're going to launch a new business called

1:05Trevor's Apparel.

1:07And you are going to sell top-quality gym wear that

1:10is so comfortable that your customers are just

1:12absolutely going to love it.

1:15You've got all these customers who

1:16are potentially interested in purchasing gym wear.

1:20But you want to make sure that your customers,

1:22your potential customers out there,

1:24your prospects that you're going after to try to market to them,

1:28that they're aware of your brand and aware of the products

1:31that you are offering in the retail marketplace for gym

1:35wear.

1:36So one of the things that you could do

1:38is-- you might have heard about this whole internet thing.

1:41Well, in the internet, you can spin up your own website.

1:44And you can create a platform where

1:46you can have products and have search capabilities

1:50and have marketing capabilities and shopping carts

1:52and ordering systems and fulfillment systems

1:55and all these really cool functions that

1:57work behind the scenes to actually get products out

2:01to your customers.

2:03So when you start this Trevor's Apparel business,

2:06you decide to hire a team of developers.

2:09And they are going to build a web application.

2:12Probably not too dissimilar from many online retailers

2:16like amazon.com or any of the Shopify

2:19retailers that are out there selling different products,

2:22they are all building platforms that

2:24are web applications that these customers out here,

2:27these prospects that you are trying

2:29to market your products to, can access your service,

2:32your website, and search through your product catalog

2:36using a web application.

2:38Because pretty much everybody out there

2:40has a web browser these days--

2:42they might be using a Chromebook.

2:43They might be using an Android phone.

2:45They might be on a Windows 10 desktop computer

2:48even like I am right here.

2:51As long as they've got a web browser,

2:53they'll be able to access your Trevor's Apparel website

2:58across the internet directly.

3:00Now, your web application is going to need somewhere

3:03to store its data, right?

3:05So you've got all this metadata that

3:07describes your products and your customers and things

3:10like their names, their first and last name,

3:13and what they're shipping addresses

3:15and what their credit card details are

3:18and what products they've put in their shopping cart

3:20and what orders they have placed on your platform.

3:24And on the fulfillment side, as your product is actually

3:28getting shipped out to your customers,

3:30you need to make sure that those are orders that

3:32are being placed by your customers,

3:34are going to be marked as fulfilled

3:37and shipped and tracked to make sure

3:39that those products are actually getting

3:42received by your customers.

3:45So in order to store all of that metadata about your business,

3:49about Trevor's Apparel, and all the products and all the orders

3:52and all of that customer data, you're

3:54going to need a data storage solution.

3:56And that is exactly where MySQL comes into play.

4:00Now, there are a lot of other competing products

4:03as well that are similar to MySQL.

4:05You might have heard of things like Microsoft SQL

4:07server or PostgreSQL or a bunch of other proprietary engines

4:12out there, like Amazon Aurora for example.

4:16But essentially, what a relational database does

4:18is it stores information about your business--

4:21your products, your employees, your orders,

4:25and all of that type of information

4:27that makes your business actually

4:29function on the back end.

4:31Now, you're not going to expose that database directly

4:34to your customers because your customers would

4:36have to have very intrinsic knowledge about databases

4:41in order to be able to query that data,

4:43not to mention the fact that you need a security layer to ensure

4:46that customers are not able to get access

4:49to proprietary information about your business

4:52and that customers are not able to see data about each other

4:56as well.

4:57And so that's where your web application comes into play.

4:59Your customers can log into the web application,

5:02create an account.

5:03They can place orders.

5:06And they can look at their order history

5:08and track their orders and all that kind of stuff.

5:10And that is exactly what your web servers are going to do.

5:14However, your web servers, behind the scenes,

5:16are going to connect to your MySQL database that

5:19has all of that product and customer and employee

5:21information.

5:23And it's going to connect across a private network interface.

5:26And so typically what you'll see in a typical network

5:29architecture is that there is some kind of firewall that

5:32restricts access to a database server

5:35because it has such sensitive information stored in it,

5:38about employees, about customers and things like that.

5:40You don't want customers' credit card

5:42information or personal addresses or things

5:45like that being compromised.

5:47And so, in order to secure that, your web application

5:51can handle the authentication and authorization

5:55of the actual data inside of the database.

5:58So when your customers come out to your website

6:00and try to search for maybe gym shoes or gym socks or the most

6:05comfortable shirt to wear to go to the gym and things

6:07like that, they'll be able to search

6:09for that data in your database server.

6:12And you'll be able to store all of that data in the database

6:15server.

6:16You'll be able to update descriptions about products,

6:18add new products in there.

6:20And as long as those products are injected

6:21into that database, your customers

6:24will be able to search for them on your web application.

6:27So that's really kind of where MySQL fits into the picture.

6:31Now, there are a lot of different ways to run MySQL.

6:34You can run it using containers.

6:37You can use managed services from cloud providers out there.

6:41You can install it on a bare metal Linux instance

6:44as well if you'd like to.

6:45And we're going to get into some of those different ways

6:47that you can actually spin up MySQL and some

6:49of the different tools that you can

6:51use to interact with MySQL as well as you start developing

6:56a business application that's leveraging a database backend.

7:00In any case, let's go ahead and jump into our next video

7:03where we'll be exploring more about MySQL.

7:06I hope this has been informative for you.

7:08And I'd like to thank you for viewing.

0:00[MUSIC PLAYING]

0:11Hi, guys, and welcome back to this skill,

0:13where we're learning about the MySQL Database engine.

0:17Now, I wanted to discuss with you

0:19a few different competing database

0:20engines and a few different types of database services

0:24that you'll come across in the software industry.

0:27Now, MySQL, as we previously discussed,

0:29is a relational database engine.

0:31So our data is going to be stored in tables.

0:34And those tables will have relationships with each other.

0:37So, for example, if you think about maybe a table

0:39with an order ID, and maybe you have a table with customers

0:43that has a customer ID, you can actually

0:45create relationships between a specific customer

0:48and a specific order.

0:50And say, all right, this customer

0:51placed this particular order.

0:53And you can then draw those relationships

0:57by building those joins between different tables.

1:00Now, there are some other relational database engines

1:02out there as well.

1:03In fact, MySQL is owned and developed by Oracle.

1:07MySQL has gone through a number of different acquisitions.

1:10And I won't get too deep into all that history.

1:13But MySQL was acquired by Sun Microsystems some years ago.

1:17And then Sun Microsystems was acquired by Oracle.

1:20So MySQL, even though it is a free database product,

1:24there is actually also an enterprise version of it

1:27as well that is offered by Oracle.

1:29And Oracle is providing the support for the MySQL Project.

1:34Now, the developers, the original developer of MySQL,

1:37actually split off and created a GPL-licensed version of MySQL.

1:43So they kind of forked the GPL license compatible code,

1:47and they created a different tool known as MariaDB.

1:51So MariaDB and MySQL originally came from the same code base,

1:56however, they have kind of diverged now.

1:58And so some of the features that you find in MySQL

2:01may not be available in MariaDB and vice versa.

2:05So just be aware that MariaDB is certainly

2:08another option that is being actively developed

2:10in the open source industry.

2:12And it may be a great tool if your company

2:14is particularly sensitive around software licensing concerns.

2:19Now, these are just a couple of different relational database

2:22engines.

2:22There's also other types of databases as well.

2:25One of my favorite things that I've come across over the years

2:28has actually been what's known as a graph database.

2:30And what's really cool about graph databases

2:32is that instead of thinking about your data

2:34as these tables, like order table,

2:37and a customer's table, and an employee's table,

2:40and things like that, and all relationships

2:42that you have between tables, with a graph database,

2:45there's just a totally different concept, where

2:47instead of having this tabular data,

2:49you actually just have these nodes, right?

2:51So you can kind of see on their marketing landing page here,

2:54they've got these nodes.

2:55And they've got these relationships between nodes.

2:58So a node could represent an object, like an order.

3:02And the order could reference other nodes

3:05that could refer over to products that

3:08make up your products database.

3:10And with employees, you could have

3:12maybe a node that represents an employee at your company.

3:16And then have a relationship to another employee who serves

3:19as that person's manager.

3:21And so you can draw all these different correlations

3:24between data.

3:25Graph databases are really cool.

3:26It's a really cool way to think about data because it's just

3:29very logical in terms of how humans think

3:32about data representation.

3:34Now, I do have a couple of other skills over here at CBT Nuggets

3:37that actually cover the Neo4j Graph Database Engine.

3:41So feel free to check those out if you are interested.

3:44Now, there's also another type of database engine

3:46as well, known as a document storage engine.

3:49And this is typically-- you can think of it kind of like a JSON

3:52document storage engine, where you

3:54have just an object that has a whole bunch of properties

3:56on it.

3:57Maybe a person with a first name, and a last name,

3:59and a birth date, and a social security number,

4:02and all that kind of information.

4:04And you can just store all that data

4:05as just kind of a blob, or just a separate document

4:09that's separate from all other documents within the system.

4:12Now, these types of storage engines,

4:14like MongoDB, which is a document storage engine,

4:17gives you a little bit more flexibility over your data

4:20because you're not tied to a schema

4:22like you are in a relational database model.

4:26In a relational database, you have these predefined table

4:28schemas.

4:29And all of the data that you insert into those tables

4:33must conform with the schema that

4:35has been created ahead of time.

4:37And we'll get more into that a little bit later.

4:39But do be aware that there are these document storage

4:42engines, like CouchDB from the Apache Foundation, MongoDB.

4:46There's also one out there called Couchbase as well.

4:49There's a whole bunch of different document storage

4:51engines that are available.

4:53In fact, AWS, Amazon Web Services,

4:55they actually offer a service called

4:57a DynamoDB, which is a proprietary managed

5:00service for a document storage.

5:03Also Microsoft Azure, they have a service out

5:05here called Cosmos DB, which is kind of a document storage

5:08engine as well.

5:09So that is certainly an option for storing your data

5:12if you have less strict needs for your data schema.

5:17Now, another way to run MySQL as well

5:20is to actually leverage a managed cloud provider.

5:23So you could actually go out to a service like Microsoft Azure

5:27or Amazon Web Services, and these massive cloud providers

5:32have built managed services around MySQL.

5:35So while you are certainly more than welcome to go out

5:38to the MySQL page and drill through the documentation,

5:42and learn how to install MySQL yourself,

5:45and go through the upgrade process as new versions of it

5:48come out, you can actually kind of

5:50offload some of that management overhead

5:53over onto a cloud provider.

5:55Now, you are typically going to pay more for these managed

5:58services than if you were to operate the service yourself.

6:01But depending on your business's particular need,

6:04it might just be easier to leverage one of these managed

6:06services in order to run MySQL.

6:10So over here in Amazon RDS, you can

6:12see we can go out to Databases.

6:14We can hit Create database.

6:15And just like that, we have a wizard here

6:18that allows us to create a database engine that is fully

6:22managed by the cloud provider.

6:24So you don't have to worry about SSHing into a virtual machine

6:27and running all the software installation commands

6:30and creating configuration files and pointing your database

6:34server at those configuration files, and things like that.

6:36All of that kind of management is handled for you

6:40under the hood by these managed services.

6:42So that's a really nice way to run

6:44a variety of different relational database engines,

6:47as well as document storage engines

6:49as well, like DynamoDB, Cosmos DB, and even services

6:53like MongoDB.

6:54So if you were to search for a Mongo up here,

6:57Amazon actually has a managed service

6:58that's MongoDB compatible.

7:01And they call that Amazon DocumentDB.

7:03So there are so many different ways these days

7:06to run different types of database engines,

7:08whether you need document storage engines or graph

7:10database engines.

7:11In fact, speaking of graph database engines,

7:14AWS has a managed service here called Amazon Neptune

7:16as well that uses the Apache TinkerPop APIs in order

7:20to interact with that.

7:22And so Neptune is kind of another option

7:24for graph databases as well, in addition to Neo4j over here.

7:29So depending on your particular needs,

7:30you may want to explore different database engines,

7:34depending on what your specific business application is.

7:37But these are just some of the different data storage models

7:40that you have when it comes to storing your data in a database

7:44engine.

7:45We've got graph databases.

7:46We've got document storage engines.

7:49And we've got, of course, relational databases,

7:51like MySQL, MariaDB, Microsoft SQL Server, Oracle Database

7:57Server, and many others as well.

7:59That's kind of an overview of what your different data

8:01storage mechanisms look like on the database server

8:04side of things that are really high level.

8:05In any case, let's go ahead and better

8:07understand what a relational database does for us.

8:10I hope this has been informative for you.

8:12And I'd like to thank you for viewing.

0:00[JINGLE PLAYING]

0:11Hi, guys, and welcome back.

0:13In this video, we're going to take a look at what a database

0:16storage engine does for us in the relational database area.

0:20So essentially, we've got this sample diagram here

0:23thanks to the folks over at dbdiagram.io.

0:26Feel free to head over there and grab the sample database right

0:29here.

0:30There's also a bunch of other sample databases

0:32that are available online.

0:33In fact, there is a really popular MySQL employees

0:37database that is free, as well, that you can take a look at.

0:40However, this service right over here

0:42just has a really nice diagramming mechanism

0:44that allows you to see all the different tables

0:47in your relational database, and the relationships

0:50between those tables as well.

0:51So that's what we're going to get

0:53into in this particular video.

0:55Now, typically, when you are developing a business

0:57application, you are going to plan out your database

1:01and figure out what kind of data you're

1:04going to be storing in there and what the different data storage

1:07requirements are going to look like.

1:09You're oftentimes going to start with some kind of diagramming

1:12exercise, very similar to what we have in front of us here,

1:15where you're going to list out the different data storage

1:17points that you are going to have in your system.

1:20So if you are building a service like Trevor's Apparel,

1:23for example, you probably have a product suite.

1:27So you'd have things like a products table right here.

1:31And you'd have a name for the product.

1:34And that would have a particular data type associated with it.

1:37You'd have a merchant ID, perhaps.

1:40So if you're ordering products from other wholesalers

1:42and then reselling those products under your brand,

1:45then you could link those products over

1:48to a particular merchant, even if that merchant is yourself,

1:51even if you are the manufacturer of that original product.

1:55Also, a merchant might belong to a particular country.

1:59And so that merchant-- if I just move this table over here,

2:04you can see the relationship between the merchant

2:06and the country code.

2:07So we have this little blue line that's being drawn up here

2:10over to the country code.

2:12And so basically, for every country

2:14that we are either placing orders from for wholesalers

2:18to get product in or even where we have customers located,

2:21as well, then we can tie those customers and merchants back

2:26to a particular country.

2:28So right over here, you can see we also have a users table.

2:31And the users table also has a country_code field here

2:35that refers back over to the country where

2:38the user belongs to.

2:39So we have this common data set here called "countries."

2:42And instead of duplicating all of that data

2:45for every single order that's placed

2:46or for every single order to a merchant that

2:50is placed by us, by our company, we

2:52don't have to duplicate that data in one massive, flat data

2:56structure.

2:56We can actually normalize that data

2:59and put the data into different tables where it makes sense.

3:03So right up here, this table for countries

3:05is actually really, really simple

3:06because all we have is something that's

3:08known as a primary key, which is, essentially,

3:11just a unique identifier for a particular row of data

3:15in a particular table.

3:16So right here, "countries" has a primary key field of "code."

3:21So no two countries can share the same code.

3:24So if USA is ID number 1, then Canada cannot also be number 1,

3:30or France can also not be number 1.

3:33Only one of those countries can actually

3:35have the given country code.

3:37So if USA was number 1, then maybe Canada is number 2,

3:41and maybe France is number 3, and UK is

3:43number 4, and things like that.

3:45So that's what we call a primary key--

3:47is basically just a unique identifier

3:50that is exclusive to a particular row of data

3:53within a relational database table.

3:56Now, the relationships that you see here

3:59between the primary key on countries and the other tables,

4:03such as merchants here, or between users and countries

4:07here, this is what we would call a foreign key.

4:09So, essentially, we are linking a field in a particular table

4:14over to the primary key of a different table.

4:18So essentially, what we're saying here

4:20is that the country code for users or the country

4:24code for merchants must match up to one of the codes of one

4:30of the countries that is defined inside of the countries table.

4:34So that's what we would call a foreign key relationship.

4:37Now, the other thing that you'll see here in this little diagram

4:40is the number 1 next to the country.

4:42And then down here, you have an asterisk.

4:45So essentially, what it's saying is

4:46that many different users in the users table

4:49could actually share the same country code.

4:52And so that's what the little asterisk there is indicating--

4:55is that we could have many, many different users,

4:57maybe hundreds or thousands of different users, maybe over in,

5:01let's say, Poland, for example.

5:03And we could have thousands of users in Poland

5:05that all share the same country code.

5:08So we can have a one-to-many relationship

5:11between the single country and many different users

5:15that belong to that particular country.

5:17And the same applies for merchants as well.

5:19Maybe we have 10 or 20 or 30 different merchants

5:22that we are working with in a foreign country.

5:24And so the asterisk down here indicates

5:27that we could have a many-to-one relationship-- so

5:30many merchants to one particular country.

5:33So that's the relationships between different tables

5:36within a relational database engine.

5:39So you can see that there's a bunch

5:41of different relationships here.

5:43So up here under orders, for example,

5:46we have things like the order ID and the product ID.

5:49So essentially, what we're doing is

5:50we're linking products from our product table right down here.

5:54And we're linking those over to specific orders

5:58by linking over to an order_items table.

6:01And then separately, the order_items

6:03is linked over to a specific order

6:05within our system that's placed by a user.

6:09So this is what a simple database

6:11diagram would look like for a relational database engine.

6:14You could apply these similar principles

6:16to pretty much any of the relational database systems

6:19that we talked about in the previous video.

6:22But just understand that this is what

6:23the table structure, the schema of your database,

6:26might often look like.

6:28And depending on your business needs,

6:30the schema of these particular tables,

6:32or even additional tables beyond what's shown here,

6:35would morph over time as your developers are actively

6:40evolving those database structures.

6:43In any case, let's go ahead and take a look

6:45at some of the different ways that we can actually

6:47run a MySQL database engine.

6:49I hope this has been informative for you,

6:51and I'd like to thank you for viewing.

0:00[MUSIC PLAYING]

0:11Hi, guys, and welcome back.

0:12In this video, we are actually going

0:14to explore some of the different ways

0:16that you can actually run a MySQL database engine.

0:20Now, as we looked at previously, there's

0:21lots of different types of database engines out there.

0:24We looked at things like graph databases, and document storage

0:27engines, and relational database engines that are kind

0:29of competing with each other.

0:31However, we're going to take a look at some

0:32of the different ways that you can actually spin up

0:35MySQL for development and testing

0:37and learning purposes, as well as production capacity as well.

0:41Now, if you drill over to the documentation for MySQL

0:44over at dev.mysql.com, this is kind

0:48of the authoritative reference manual for all things

0:50pertaining to MySQL.

0:52It's very easy to get quickly overwhelmed

0:54in all this documentation that's available,

0:57and schemas, and data types, and all that kind of fun stuff,

1:02but we're just going to focus right now

1:03on just how to kind of install and get MySQL up and running.

1:08Now, MySQL is a database engine.

1:10As we looked at previously, it's kind of

1:12designed so that many different servers can communicate

1:15with that same database.

1:17Now, you might have more than just

1:19your web servers that are communicating

1:21with your database.

1:21You might have backend processes that are periodically

1:25running data from your MySQL database-- maybe old product

1:29information, deprecated products,

1:31and it's just kind of moving that over

1:33into more of like an archival type of storage.

1:35So it's not exclusively web servers

1:38that are necessarily going to be connecting to your database.

1:41Those are just kind of the most prominent servers,

1:43because they are customer-facing.

1:45They're facing your users, and your users

1:47need to be able to access information

1:49in that database via your web application.

1:52So just keep in mind that the MySQL database engine

1:55is designed to be network-connected and accessed

1:58from many, many different servers.

2:00So let's switch back over to the documentation

2:03right here, and take a look a little bit more depth.

2:05So MySQL is actually a cross-platform,

2:08and it's also an open source database engine as well.

2:11There are a couple of different editions of it available.

2:14So there is like a free community version of MySQL,

2:17and they also have an enterprise version of MySQL as well.

2:21So just be aware of that, that we

2:23are going to be talking mainly about the MySQL Community

2:25Edition here, but there is plenty--

2:28more than enough to go through with that.

2:30MySQL Enterprise has some more advanced features,

2:32like thread pooling for example, and some more advanced

2:35authentication mechanisms, more advanced security tools,

2:38and things like that.

2:39So if your business does have those particular needs,

2:41feel free to explore MySQL Enterprise if you'd like to.

2:45Now, because MySQL is a cross platform utility,

2:48you can install MySQL on the Microsoft Windows platform.

2:53You can head out, and you can just download the MySQL package

2:55and install [INAUDIBLE] system.

2:58You can also use a package manager

3:00to install it as well, though one

3:02of the package managers that I always like to recommend

3:04is called Scoop.

3:06And so if you have Scoop installed on your system, which

3:08you can do with this little PowerShell script one-liner

3:11right down here, you can actually

3:12use Scoop to install some of the MySQL utilities

3:15onto your Windows 10 system.

3:17And that's a really nice kind of scriptable way

3:20to automate setting up a dev environment.

3:22If you want to set up a dev environment up

3:24in the cloud that's just easily repeatable so you can start it

3:27up and shut it down, and rebuild it from scratch when you need

3:29to, using the Scoop package manager

3:32avoids all the pointing and clicking

3:33that is necessary to go out and download a software

3:36installer to your system, and then double click

3:38the installer, and click on all the wizard dialogues,

3:41and things like that.

3:42But I definitely recommend using more automation-type tools,

3:45like package managers, on the Windows platform

3:47in order to get that installed on your system.

3:52Now, there are a couple of other ways

3:53that you can install it as well.

3:54So you can even install it over here

3:56on macOS either using the native packages

3:59or using a package manager for macOS as well.

4:03Same thing with Linux.

4:04Because MySQL is primarily something

4:06that you're going to see installed on a Linux system,

4:09there are different packages available for it

4:11through Yum repositories and APT repositories

4:14for Debian based distributions.

4:16Or if you're using SUSE Linux Enterprise Edition,

4:18there's packages for that, as well as RPM packages.

4:22So there are a whole bunch of different packages,

4:24depending on which particular Linux distribution you

4:27are actually going to be using.

4:29Now, I personally generally prefer using Ubuntu Linux,

4:31just because it's very user friendly.

4:33It's very well supported, it's constantly being updated,

4:36but there are many other Linux distributions out there

4:39that you can run MySQL on as well.

4:42Now, one of my personal favorite ways of running the MySQL along

4:47with many other software utilities and different

4:49programming run times is actually

4:51to leverage containerization.

4:52And you might have heard of Docker before,

4:54and Docker and containers has been

4:57kind of an evolving thing over the last six or seven

4:59years, where you can very easily spin up

5:01almost any software running up inside of a Linux container.

5:05And you can run things like Python, Ruby,

5:08JavaScript on Node.js, or .NET Core applications,

5:12or even applications like MySQL, or even things like MongoDB,

5:17for example.

5:17You can run all these applications inside

5:19of these isolated containers, and it makes it really easy

5:23deploy and scale applications either locally using

5:27an orchestrator like Kubernetes, or even up in the cloud

5:30using either Kubernetes, or some proprietary service,

5:33like Amazon Elastic Container Service,

5:35or Microsoft's Azure Container Instances,

5:38or Google Cloud Run and other services like that.

5:41Now, the reason I like using Docker

5:43is because for development purposes.

5:45If you're just learning about something,

5:46or you're developing an application just

5:48for your personal use, or you're just kind of tinkering around,

5:51using a container to spin up any kind of service

5:55makes it really easy to spin it up.

5:58And then as soon as you're done using it,

5:59you can just tear it down.

6:01You don't really have to worry about cleaning up

6:03a whole bunch of stuff because you haven't installed

6:05any software packages, and you don't have all these issues

6:08with conflicting dependencies.

6:10So if you wanted to run two different versions of MySQL

6:12side-by-side, like MySQL 5.7 and MySQL version 8.0,

6:17you can just spin those up in two separate containers running

6:20on the exact same host, and you're not

6:23going to run into those dependency conflicts.

6:25That results in all sorts of issues

6:29when you are trying to resolve them,

6:31and you end up probably having to just rebuild your system

6:33from scratch anyway.

6:34So using Docker containers can really

6:36help save a lot of the headache that developers

6:39run into in addition to allowing DevOps engineers to scale

6:43applications that are running up in the cloud.

6:46So if you head over to hub.docker.com,

6:49this is the kind of main package registry,

6:51or I should say container image registry for all sorts

6:55of container images out there.

6:56And if you just do a search for MySQL,

6:58there is an officially supported MySQL container image.

7:02In fact, there's many, many different container images

7:04out there.

7:04So just under the MySQL repository,

7:07if you head over here to tags, this

7:09is going to show you all the different versions

7:12of this particular container image that are available.

7:16So there is typically just the latest tag,

7:18which if you don't specify an image tag in Docker,

7:21it just assumes the latest tag.

7:23However, if you want to run a specific version of MySQL,

7:27like the latest 8.0 version, or maybe

7:30you want to run MySQL 5.7.35 or 5.6.51,

7:35or any specific version of MySQL,

7:38you can actually just grab the appropriate container image tag

7:42from the MySQL repository here over on the Docker hub.

7:46You can just use the Docker pull command right here

7:48to download that onto your Docker host,

7:50and then you can simply spawn a new container

7:53using the documentation that's available here.

7:56So they actually show you right here how to actually spin up

8:00a brand new MySQL instance.

8:02So essentially, you're just going

8:03to do a Docker run command.

8:05You could optionally give the container

8:07a name, although naming containers is generally

8:09frowned upon, just because--

8:11I don't know-- treat them like cattle, not pets.

8:13And then one of the really important things

8:15here is going to be that you need to set a root password.

8:18And so the root password is going

8:20to be the password that you authenticate through the MySQL

8:23database engine as you use different tools in order

8:27to connect through that database engine.

8:29And we'll talk about some more of those tools a little bit

8:31later on, but you do need to make sure

8:33that you set a password.

8:34Otherwise, you will not be able to log into that container,

8:38into the MySQL service that is running inside

8:41of that container.

8:42And so just be aware of that.

8:44Now the other option here is the MySQL image

8:48and the specific tag that we want to run.

8:50So of course, we want the SQL repository here in the Docker

8:54hub, but then you can also specify that version specifier

8:57or the tag on that container image

8:59to determine exactly which version of MySQL

9:02you're going to run.

9:04Now typically, I'm going to be using the latest

9:06version of MySQL, version 8.0, but just

9:09be aware that, if you need to run automated tests,

9:12or if you want to run performance

9:13tests against different versions to determine

9:16if there is a performance difference

9:17between different versions, or you want to test your database

9:20upgrade processes across different versions,

9:22and things like that, that is one

9:24of the things that makes containers really,

9:26really great.

9:27Now, the other thing I want to make sure that you do as well

9:29is to expose a port to your host as well.

9:33So MySQL's standard port is going to be TCP 8080.

9:37And I don't see an example right here,

9:40but if you use the --publish--

9:42let me actually just do a quick Control-F here.

9:44Now I don't see it.

9:45But if you just do a run --publish,

9:50that's basically how you can forward a port from your local

9:53host over to the container port.

9:57So if I do a docker run --env MySQL_ROOT_PASSWORD=--

10:02let's say-- cbtnuggets123.

10:06And then maybe I do a --publish 33306 to 3306.

10:11I'm just going to choose kind of a random high port

10:13here on my host to just make sure it doesn't conflict

10:16with any of the lower ports, and then

10:18I'll just specify MySQL maybe latest,

10:22or I could do MySQL version 8.0, or things like that.

10:25Basically, this is all we really need

10:27to do in order to run a brand new SQL container.

10:31So if I just hit Enter here, you'll

10:33see that it's going to go out and download that container

10:36image from the Docker hub, and then it

10:39will go ahead and spin up a container on my system.

10:42Now, if you don't have Docker installed on your system,

10:45there are lots of ways that you can actually

10:47run Docker as well.

10:48So you can just head over to your favorite search engine

10:51and search for Docker Desktop.

10:53And Docker Desktop is a really cool tool

10:55that just allows you to spin up a dockerized Linux

10:58environment on your Windows or macOS system.

11:00And if you're on Linux, you can just

11:02install the Docker engine natively on there

11:04and use the Docker CLI in order to spin up

11:07a 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.

11:19So that's actually conflicting right now,

11:20but I could essentially just change this command

11:22and choose a different port, like maybe 3308 and forward

11:27that to 3306.

11:28And then we'll see all of this log information

11:30that gets spun up-- and this is all

11:32being spit out by the MySQL engine,

11:35or the entry point into the MySQL engine container.

11:38And it's basically going to initialize a brand new database

11:40server.

11:41And it will be available for us to connect

11:44to using different tools that allow

11:46us to manage a MySQL instance.

11:48But let's go ahead and look at those next.

11:50I hope this has been informative for you,

11:52and I'd like to thank you for viewing.

0:00[AUDIO LOGO]

0:11Hi, guys, and welcome back.

0:13So far, we have spent a bunch of time talking

0:15about different types of database

0:16engines and different ways that you can run MySQL

0:20and what a relational database structure even looks like.

0:23But you might be wondering up to this point,

0:25it's like, well, how do I actually interact

0:27with this database engine?

0:28You said it runs on the network over here

0:30in your little network diagram right here.

0:33But how do I actually start developing an application?

0:36How do I talk to this database server

0:37so that I can build out my table schema

0:40and do upgrades and make exports of my database for backups

0:45and things like that?

0:46Well, we're going to talk about that in this video.

0:49So let's go ahead and jump over here

0:50into the MySQL documentation.

0:53And I want to start out by talking about some

0:54of the official tools that are available for MySQL.

0:59So previously, we were looking at the installation

1:01documentation here for different platforms.

1:03We're going to go ahead and collapse that for now.

1:06But I wanted to just draw your attention over here

1:08on dev.mysql.com under the documentation under MySQL

1:12Programs.

1:13Now there are lots of different types of MySQL programs

1:17that are available, depending on what you

1:19are attempting to accomplish.

1:22So if you are looking for modifying

1:24your installation of MySQL, there's programs for that.

1:27There is, of course, the MySQL daemon itself

1:30and the startup script and managing multiple SQL servers

1:34and all that kind of stuff as well.

1:36And then there's also these client programs.

1:37And this is really where you're going

1:39to get the bulk of your interaction

1:41with the MySQL database engine that's currently

1:43listening on a network port.

1:45And so we've got basically this really main tool right here,

1:48which is just MySQL.

1:50So we could essentially just spin up this MySQL command line

1:53utility and connect to a remote host or even our local host

1:58if we're using a Docker like we did in the previous video

2:01to create our host.

2:02Or if you're using a cloud service,

2:04you could connect to a remote database that's

2:06listening on a cloud service.

2:08Or even if it's on your local network,

2:10but it's just running on a remote device

2:12from the developer workstation or laptop

2:14that you're currently sitting at,

2:16then you could use that to do that as well.

2:18So MySQL CLI is the official CLI for connecting to that database

2:22server and issuing queries and exploring databases

2:26that are present on that particular host and things

2:28like that.

2:30There's also another tool here called mysqladmin

2:32as well that helps you to administer your MySQL

2:34instances.

2:36There is mysqlcheck that allows you to do table maintenance.

2:39There's another really important one here called mysqldump.

2:42So this is a utility that you can

2:44use to take backups of your databases, which

2:48is really important for business continuity in the event

2:51that your database server was to have some kind of disaster.

2:54Or maybe the cloud vendor that you are using for your managed

2:58MySQL instance, maybe they have some kind of major outage,

3:01which certainly has happened in the past

3:03as we've seen with many cloud provider outages out there.

3:06And essentially, what mysqldump allows

3:08you to do is just take backups of your database.

3:11You can restore those backups in an automated fashion

3:13and test them out to make sure that they have high integrity

3:18and make sure that if you do need

3:20to restore those databases that they'll actually

3:22work the way that your business units are expecting them to.

3:25And there's a couple of other ones here as well,

3:27like importing data using mysqlimport as well,

3:31mysqlshow to get database table column information,

3:34and then mysqlslap, which is essentially a load testing

3:37utility.

3:38So you can use this if you'd like

3:39to load test a MySQL database and to better understand

3:43how your application, your business application

3:46performs when the database is under a very heavy load.

3:50And that gets into a whole other area of software development,

3:53which is load testing.

3:54There's lots of different types of testing

3:56that you need to do--

3:57user experience testing.

4:00Sorry, user acceptance testing rather.

4:02And then things like security testing,

4:04automated security tests, penetration testing.

4:06And load testing is just another one of those types of testing

4:09that you want to perform against your business application

4:12before putting it out into production

4:14so that you can understand how your application is going

4:17to respond to your end users if the database is under load,

4:21and if you need to make any tweaks to your application

4:23to maybe give your users helpful information to say,

4:27hey, our system is experiencing heavy load right now.

4:30Please come back later or something like that.

4:32Or if you just need the opportunity to scale out

4:34your application servers and make sure

4:37that you're able to sustain that load

4:38or implement some kind of caching layer,

4:41cache data to reduce the load on your database server,

4:44those are all options that you have as well.

4:46Now, there are some other utilities out there

4:48that we can use to interact with database engines as well.

4:53So one of those is an open source utility called Adminer.

4:57And essentially what this is is it's

4:58a web application that runs in a single PHP file.

5:01That's kind of its claim to fame is that it's

5:03a very simple installation.

5:05There's not a very complex project

5:07that you have to deploy.

5:09But this is essentially a web-based application

5:11that you can spin up either in a container,

5:14or you can manually install it if you're

5:15good at installing PHP and setting up your PHP runtime

5:18environment and things like that.

5:20There is a Docker container for it as well.

5:22There's also user-contributed packages

5:24here for like Debian packages and Arch Linux

5:27and things like that as well.

5:29But essentially, you can just spin up an Adminer instance

5:32and then log into the web interface

5:34and then use the Adminer instance to connect

5:36to your remote MySQL instance.

5:39And then you can start issuing queries against that MySQL

5:42instance to do things like manage your databases

5:45or view your tables or build out your table structure

5:48or insert data into tables or drop tables

5:51if you need to or pretty much any kind of SQL management

5:54tasks that you need to perform.

5:56Now, there's another very similar one to Adminer

5:59are called phpMyAdmin.

6:02And essentially phpMyAdmin is very similar

6:04to Adminer, just in the sense that it's

6:06a web-based interface.

6:08You can spin it up as a container image as well.

6:11So that just makes it really easy to spin up phpMyAdmin.

6:14There is a little bit more configuration

6:16that goes into it up front, just making sure

6:18that it's got all the right environment variables set

6:20so that it knows how to connect to your MySQL instance

6:24with the username, password, remote host, port,

6:27all that kind of stuff.

6:29So phpMyAdmin, I believe, is probably

6:31the most popular utility out there.

6:34But you do have a couple of options out there.

6:37Now, there is another one called MySQL Workbench here as well.

6:41And this is kind of an official tool for MySQL.

6:44One of the challenges that I actually

6:46have with MySQL Workbench is that it

6:47is a native application.

6:49And unfortunately, it does not scale very well

6:52on high DPI displays.

6:54It doesn't really look all that great either.

6:56So it's actually a little bit nicer

6:58to work with a utility that's web based,

7:01that scales a little bit more nicely.

7:02It runs in your web browser.

7:04You can just navigate to it without having

7:06to worry about installing any software packages

7:08and things like that.

7:09However, I would point out that you can actually

7:11install MySQL Workbench using the Scoop package

7:14manager as well if you're on the Windows 10 platform.

7:17If you just do a scoop install mysqlworkbench, I think--

7:22there might be a dash in there somewhere.

7:24But essentially, you can just use Scoop package manager

7:27to get that installed if you do want to give it a try as well.

7:31Now, there are a couple of other utilities out there as well.

7:34One of those is called Sqlectron.

7:37And I actually found this one because I

7:39was looking for a quasi native client that's cross platform

7:44but also scales kind of nicely like a web application does.

7:47And thankfully, I came across this one called Sqlectron.

7:51And what's nice about Sqlectron is that it's

7:52built on the Electron JS APIs.

7:55And essentially, it is a web-based application.

7:59But it kind of looks and feels like a native application

8:01on your system.

8:02There's actually a whole bunch of utilities out there

8:05that are built on Electron that you might not even realize.

8:08In fact, I'm pretty sure Slack is built on electron,

8:11if I recall correctly.

8:13That's a pretty popular one.

8:14And there's a lot of other Electron-based applications

8:17out there as well.

8:18I know another one's called GitKraken.

8:20They used to be built on Electron as well.

8:23But luckily, there's actually a MySQL utility here

8:26that allows you to install a native application.

8:30It's actually just a zip file that you can extract.

8:32It's pre-built, prepackaged for you.

8:34You just extract a zip file on your system.

8:36And it's open source.

8:37So you can actually head over to their GitHub repository.

8:40You can file issues against the project

8:42if you run into any problems or have feature requests.

8:46It is a fairly popular project as well with

8:483.7 thousand stars on GitHub.

8:51I mean, it's not crazy popular, but it's

8:54a reasonably popular project to get that many stars on GitHub.

8:57And you can see it's been pretty recently updated,

8:59as recently as a couple of days ago.

9:01The latest official release was back on April this year

9:04in 2021.

9:06So just a couple of months old.

9:08So it is a pretty recent and up-to-date project.

9:10And you can just go ahead and download

9:12one of the software packages for it on your system here.

9:15So there's BSD packages.

9:17There's zip packages for Mac or for Windows, for Linux as well.

9:24There's a setup installer executable as well,

9:26Debian packages for Linux, RPM packages, pacman packages,

9:31and things like that.

9:32So a lot of different options to download Sqlectron.

9:36And that's kind of become my tool of choice

9:38so far as far as administering a MySQL environment goes.

9:43Now there are some other utilities as well.

9:45So we talked about MySQL CLI.

9:47We talked about Adminer, phpMyAdmin, MySQL Workbench,

9:51and Sqlectron.

9:52However, in the real world where you

9:55start working with very complex database

9:57structures in a relational database model,

10:00there's also another utility out there called Prisma Studio.

10:04Now, this is a little bit different in the sense

10:06that it requires you to actually use their schema definition

10:10language in order to create and load a schema

10:14into your database engine.

10:16But once you do, they have--

10:18I believe this is Electron-based as well.

10:20But it's a cross-platform utility

10:23that allows you to connect to your database

10:25and view your table schemas and the data

10:27that's inside of your MySQL database

10:29and all that kind of stuff.

10:31So definitely check out Prisma Studio as well.

10:33And they actually have a really nice open source utility

10:36that allows you to define your schema in MySQL database engine

10:41and then deploy that and perform migrations as that schema

10:44changes over time as well.

10:46So I'd definitely recommend checking out Prisma.

10:49And we may look at even building some training on that

10:52in the future as well.

10:53So those are just some of the different utilities

10:55that we can use in order to manage a MySQL database

10:58instance.

10:59We've got MySQL CLI.

11:00We've got Adminer here.

11:02We've got phpMyAdmin and MySQL Workbench and Sqlectron

11:06as well.

11:07Most of these are cross-platform utilities as well,

11:09or they just run as web applications.

11:11So pretty easy to spin up.

11:13In any case, I hope this has been informative for you,

11:15and I'd like to thank you for viewing.

0:00[JINGLE PLAYING]

0:11Hi, guys, and welcome back.

0:13I wanted to just give you a quick overview of one

0:15of the utilities that we looked at in the previous video

0:18and show you just how easy it is to connect to your MySQL

0:21database instance that we spun up using a Docker

0:24container previously.

0:26Now, this is Sqlectron right here.

0:28And if you just refresh it here, you

0:30get this nice, little introduction

0:31screen that's kind of nice.

0:33They must have put some effort into creating that as part

0:35of the open source project.

0:36So that's kind of nice.

0:37But one of the things I really like about Sqlectron

0:39here is that it gives you this really nice connection

0:42management experience.

0:44So right now, I have a single connection

0:46configured right here.

0:47And it has this card-like interface

0:49that's very straightforward and easy to understand.

0:52So I have a big, old Connect button here,

0:54which, as the name implies, is going to connect to my database

0:57server that's configured there.

0:58I have a little profile name right here.

1:00I have the hostname and the port,

1:02as well, that I'll be connecting to.

1:04And I can just click this little Edit button

1:06right here to edit the properties

1:08of this particular connection.

1:10So I can change the profile name right here.

1:12So I could just call this CBT Nuggets Docker, for example,

1:17and just click on Save right down here.

1:19Now I've renamed my profile here.

1:20One of the other nice things, too,

1:22is that you can just hit Control-Plus and Control-Minus

1:24on your keyboard to resize the text here as well.

1:28So it's a nice, scalable application

1:30because it is built on the electron.js web APIs that

1:34make it feel like a native application,

1:36but not really be one.

1:38And then if we go back into Edit here,

1:40there's actually a bunch of different database types

1:42that are supported as well.

1:43So, of course, MySQL is supported.

1:45We've also got MariaDB, we've got PostgreSQL.

1:48We've got Amazon Redshift support here, as well, which

1:51is actually built on Postgres.

1:53It's just a column-based data storage instead

1:55of row-based data storage.

1:57We've got Microsoft SQL Server, SQLite,

2:00which is essentially just a local file,

2:02and then we've also got Apache Cassandra support here as well.

2:05So there's a whole bunch of different options

2:07available for the database types that you're connecting to.

2:10You can set up your credentials, your server address, maybe

2:13an IP address or a DNS name that you're connecting to,

2:16your user ID and password, your initial database if you

2:19would like to specify one.

2:21You have the option to enable SSH tunneling as well.

2:24So if you need to establish an SSH tunnel to a remote service

2:27in order to encrypt and secure your network

2:30traffic across the internet, that is an option here as well.

2:34You can enable that and plug in your SSH hosts

2:36that you're going to be connecting

2:38to and your private key and all that stuff

2:40for your SSH connection.

2:41So this is a really nice utility that

2:43just allows you to very easily connect

2:45to your remote databases.

2:47And then, once we've saved our profile here,

2:49we can just click on Connect.

2:51And you can see that it connects to my database here.

2:54This is actually a Docker container

2:55that I had previously spun up already and loaded a sample

2:58employees table into.

3:00You can actually see right here on the far

3:01left we've got our standard MySQL table, as well

3:05as our information schema and performance schema,

3:07and our system database here, as well, and, of course,

3:10my employees database, which is the one that I imported

3:13into this environment from the employees sample database

3:16that's available on GitHub.

3:18And then once you drill into the database,

3:20you can see all of the individual tables that make up

3:22that database, as well as any SQL views,

3:25as well as any stored procedures or functions right down here.

3:29And under the tables, if you drill

3:30into those, like employees, for example,

3:33you can see that we actually get the table schema here as well.

3:37We can see all of the columns that belong

3:39to that particular table.

3:40We can see the data type for each

3:42of those particular columns as well.

3:44We can see indices that are configured

3:46on that table, as well as any triggers, if applicable.

3:49And so this is just a really nice interface

3:51to just be able to explore your databases, your tables,

3:54your table schemas, and things like that

3:57without having to necessarily write

3:59a whole bunch of Transact-SQL code

4:01that you would typically use in order

4:03to interact with a relational database engine.

4:06And then separately over here under this editor section,

4:08we have this tab-based interface.

4:10So we can add more tabs over here on the right.

4:13And each of these tabs can hold a query

4:16that we can then issue against our server and our database

4:20that we would like to manage.

4:21So if we wanted to insert some data,

4:23we could just say "INSERT INTO," blah, blah, blah.

4:26And we could write that query.

4:28And then just click on Execute over here

4:29to execute that query.

4:31So for example, if I was to say "use employees," for example,

4:35and then maybe "select star from employees limit 10"--

4:40let's just try to execute that.

4:42And you can see we get "Query executed successfully" here.

4:45And we have 10 rows of data.

4:47Let me just bump the size down a little bit

4:49here so it scales a little bit more nicely.

4:51And right down here, you can see the table of the query output

4:56that we just issued to the database server that's

4:59running over inside of a Docker container on our localhost,

5:02thanks to Docker Desktop.

5:04So you can see we've got our primary key column right here,

5:06which is our employee number.

5:08That's just a unique identifier for each row in the table.

5:11And then each employee has a birth date, a first name,

5:14a last name, a gender, and their hire date as well.

5:18This guy was actually hired a few days after I was born.

5:21Imagine that.

5:22So anyways, that's just a quick overview of Sqlectron here.

5:26It's just a really nice interface

5:27to interact with your database at a simplistic level.

5:30You can save and load queries and things like that.

5:33You can manage your connections.

5:34You can shut that connection off and switch contexts

5:37over to a different database server and things like that.

5:40So-- definitely recommend checking out Sqlectron

5:43as you start to explore MySQL.

5:46I hope this has been informative for you,

5:47and I'd like to thank you for viewing.