Things a beginner developer should know about MySQL database.

I tried to read through different blogs but they don’t really give more light to young developers in the path of MySQL database. They always revolve around basics. Like we saw in the previous article.

Selecting MySQL as a database to use for your application or project, would be the best decision a beginner developer makes. Since it is a raw SQL or many can call it a native SQL. It allows developers to use it in the way they wish to organize their data. Let’s get to see how far you can go with MySQL database.

Locking and unlocking the tables.

Anyways, did you know that it’s possible to prevent other sessions from modifying data in a specified table? The MySQL Locks is nothing but a flag that can be assigned to a table to alter its properties. MySQL allows a table lock that can be assigned by a client-server to prevent other sessions from being able to access the same table during a specific time frame. It’s done by writing simple lines of code:

LOCK TABLE <table_name> [READ|WRITE] ;

Here the user chooses whether the specified table can be modified or not, by using a READ mode or WRITE mode.

When any data is tried to be added, updated, or even deleted from the specified tables it is rejected.

To unlock the tables is also so simple. Use the code below and you will be notified:-

UNLOCK TABLES;

MySQL storage checking

It is possible to check and find out the storage that your database is attaining in the MySQL server at the given time. Let’s see how you can check the storage of the MySQL database you are running using the code below;

mysql > SELECT TABLE_SCHEMA AS ‘Database’,
     -> ROUND(SUM(DATA_LENGTH  +  INDEX_LENGTH)/1024/1024, 2) AS ‘Size(MB)’
     -> FROM Information_schema.TABLES
     -> GROUP BY TABLE_SCHEMA
     -> ORDER BY SUM (DATA_LENGTH  +  INDEX_LENGTH ) DESC;

Checking for the database variables.

We should not confuse the above code that helps to check the default storage engine of the database with the code that helps to check the storage variable of the MySQL engine. To see the storage of the MySQL engine we shall use the code below where we involve the variable attribute since the storage is variable. Though we shouldn’t confuse the two @variable and variable. A @variable indicates a user-defined variable and a variable , without @ , is a system variable, which you cannot define yourself.

show variables like ‘%storage_engine%’;

output for above code.

MySQL engine.

For starters, an SQL engine is the software that helps to recognize and interpret SQL commands to access the relational database and interrogate the data. It is also called the SQL query engine or SQL database engine. We can also be able to search and see the engines in the MySQL database we are running at that particular time. By using the code below;

show engines;

which will definitely give an output like this;

MySQL engines

Let me hope we are still in the same boat. If it’s so, let us dive deeper into more MySQL commands.

MySQL system version.

Yeah, just like you can always check for the version of the software you are using, you can also check the version of the MySQL system you are running at the moment. With simple codes below;

select version();

or 

show variables like 'version';

when you use the first code

To explain more about the version output above, since I am running a remote MySQL database provided by Crane Cloud, it is a distribution of MariaDB. A quick dive into MariaDB, MariaDB is a fork of MySQL and is 100% compatible with prior versions of MySQL. But the fun fact is that MariaDB is open-source and cross-platform. So in that way, I am running the 10.5.9 version of MariaDB which was released on 2021–02–22 and it is stable. Now I think you can tell the version of MySQL system you are running.

MySQL port.

For starters what is a port. A port is a virtual point where network connections start and end. So a MySQL port is used to connect with MySQL clients and utilities such as MySQL-dump and MySQL-pump. When running a MySQL system, it uses a port set and configured by the admins of the MySQL server. Though ideally, MySQL uses 3306 as the default port. But you can go ahead and check the port your MySQL is running on with the code below:-

show variables like 'port';

But you can also be able to see more port variables in that way, just by adding percentage signs before and after the port keyword. And you will notice that, there is an extra port variables.

show variables like '%port%';

Host-name for MySQL

Did you know that you can also know the host-name of your MySQL database that you are running as the same as your application. With the help of this code;

show variables like '%host_name%';

host-name for MySQL database

MySQL Server id

A database server is any server that runs a network database application and maintains database files, such as Microsoft SQL Server or Oracle. SQL Server is a high-performance. It is used for data storage and analysis, and it provides users with the ability to access vast amounts of data quickly over the network. MySQL server_id is the @@variable used to define the server_id in the MySQL system. Fun fact is the you can check for the server_id of your MySQL system with the code written below they all give the same output:-

show variables like 'server_id';

or

show variables like '%server_id%';

I think we should stop here for today, thanks for reading through this article but the fact is we can’t exhaust everything in here. But hopefully you have learnt something new.