MySQL
MySQL

MySQL is a typical Client/Server system, So it is composed of two logical parts a server which provides services and a client, which requests them. The database is on the server and the user will have to use a client to connect to the server in order to use the database.

When you install MySQL to your computer you will get the server(=mysqld) and a command line client called mysql monitor(=mysql)

Connecting to MySQL

In order to connect to MySQLServer, you will need some client. The client can be the text based client (mysql monitor) or some graphical client example: MySQLWorkbench, HeidiSQL, SQLYog, DBeaver.

In order to connect to MySQL, you have to give these parameters:

  • -h hostname (dns-name or ip-addres of the server)
  • -u username
  • -D database (optional)
  • -p (if you connect with the password)

Example

In order to connect from Client to Server the parameters are:

mysql -u mysqlUser -h mysqlserver.company.com -D databasename -p

PORT

The default TCP-Port for MySQL is 3306. If your server is using some other port then you have to add the port number to your connection like this 

mysql -u username -h hostname -P 8889 -D database -p

You can check which port your server is using with the command 

show variables LIKE 'port';

Basic commands

This a very short manual to MySQL, you can found more details example from https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

ActionCommand
Create a new database mysql> create database databasename;
List all databases mysql> show databases;
Change the default database mysql> use databasename;
List the tables in the database mysql> show tables;
Check the structure of the table mysql> describe tablename;
Delete a database mysql> drop database databasename;
Delete a table mysql> drop table tablename;
Change password mysql> SET PASSWORD=PASSWORD('new_password');
in MYSQL 8
mysql> SET PASSWORD='new_password';
Check the engines of your tables mysql> show table status;
Check the engines in server mysql> show engines;

MySQL Datatypes

There might be differences between different MySQL-server versions.

Ty p e S i z e D e s c r i p t i o n
CHAR[Length] Length bytes A fixed-length field from 0 to 255 characters long.
VARCHAR(Length) String length + 1 bytes A fixed-length field from 0 to 255 characters long.
TINYTEXT String length + 1 bytes A string with a maximum length of 255 characters.
TEXT String length + 2 bytes A string with a maximum length of 65,535 characters.
MEDIUMTEXT String length + 3 bytes A string with a maximum length of 16,777,215 characters.
LONGTEXT String length + 4 bytes A string with a maximum length of 4,294,967,295 characters.
TINYINT[Length] 1 byte Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT[Length] 2 bytes Range of -32,768 to 32,767 or 0 to 65535 unsigned.
MEDIUMINT[Length] 3 bytes Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
INT[Length] 4 bytes Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
BIGINT[Length] 8 bytes Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.
FLOAT 4 bytes A small number with a floating decimal point.
DOUBLE[Length, Decimals] 8 bytes A large number with a floating decimal point.
DECIMAL[Length, Decimals] Length + 1 or Length + 2 bytes A DOUBLE stored as a string, allowing for a fixed decimal point.
DATE 3 bytes In the format of YYYY-MM-DD.
DATETIME 8 bytes In the format of YYYY-MM-DD HH:MM:SS.
TIMESTAMP 8 bytes In the format of YYYYMMDDHHMMSS.
(In older MySQL-servers there is Year 2038 problem)
TIME 3 bytes In the format of HH:MM:SS
ENUM 1 or 2 bytes Short for enumeration, which means that each column can haveone of several possible values.
SET 1, 2, 3, 4, or 8 bytes Like ENUM except that each column can have more than one ofseveral possible values.

Engines and Table Types

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases.

When you create a table, you have to design which table type you are going to use. The various storage engines provided with MySQL are designed with different use cases in mind.

You can use below command to check which engines your server supports: 

		SHOW ENGINES\G

And below command will show what are the table types in your database: 

		SHOW TABLE STATUS\G

Here are some table types and their properties:

TypeProperties
InnoDB InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints
MyISAM These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
Memory Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

MySQLDump

You can backup databases and tables with MySqlDump. It is very simple application, which will create the sql-code that you can use to create the databases and tables with the data.

Example Below command will backup Project database to file project.sql 

  mysqldump -u root Project -p > project.sql
Example Below command will backup Project database to file project.sql inlcuding also the stored procedures 
mysqldump -u root Project -p --routiness > project.sql
And the next command will backup only the table Book 
mysqldump -u root Project  Book -p >book.sql
And then you can use those codes to return your database and tables.
Example You can execute the code from "mybackup.sql" to database Project with this command 
  mysql -u root -D Project -p < mybackup.sql

User Management

User management is easiest to handle with some graphical client as MySQLWorkbench(instructions), dBForgeStudio or HeidiSQL. But still it useful to know the basic things behind this system.

MySQL stores all user information to table user inside database mysql.
You can check which users you have in your server, like this: 

  SELECT User FROM mysql.user

So the table is user and there is a column named User.

And if you want to check also which are the allowed hosts for the users, you use can this: 

  SELECT User,Host FROM mysql.user

You can create new user, which can connect from localhost like this 

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test_pass';

You can create new user, which can connect from everywhere like this 

CREATE USER 'testuser'@'%' IDENTIFIED BY 'test_pass';

You can create new user, which can connect from domain mydomain.com like this 

CREATE USER 'testuser'@'%.mydomain.com' IDENTIFIED BY 'test_pass';

If you want to set the authentication to mysql_native_password, you can create the user like this 

CREATE USER 'netuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'netpass';

Authentication plugin

MySQL supports different kind of authentication plugins example

  • mysql_native_password
  • caching_sha2_password
If, you don't set it in the CREATE USER command, MySQL will use the default of your server. You can set the mysql_native_password as the default by adding below line to my.cnf(Mac, Linux) or to my.ini(Windows) 
default_authentication_plugin=mysql_native_password
You can check what is the default in your server with the command 
show variables LIKE '%default_authentication%';

Grant

You can use Grant commands to manage users privileges.
Below command will add SELECT privileges to all tables in database Project to user testman, if he connects from localhost. 

  GRANT SELECT ON Project.* TO 'testman'@'localhost';
Below command will add SELECT and INSERT privileges to all tables in database Project to user testman, if he connects from localhost. 
  GRANT SELECT,INSERT ON Project.* TO 'testman'@'localhost';
Below command will add all privileges to all tables in database Project to user testman, if he connects from localhost. 
  GRANT ALL ON Project.* TO 'testman'@'localhost';

Revoke
You can remove privileges from users with the revoke command.

Below command will remove SELECT privileges to all tables in database Project to user testman, if he connects from localhost. 
  REVOKE SELECT ON Project.* FROM 'testman'@'localhost';
Below command will remove SELECT and INSERT privileges to all tables in database Project to user testman, if he connects from localhost. 
  REVOKE SELECT,INSERT ON Project.* FROM 'testman'@'localhost';
Below command will remove all privileges to all tables in database Project to user testman, if he connects from localhost. 
  REVOKE ALL ON Project.* FROM 'testman'@'localhost';
DROP USER

You can delete the the user with below command 

DROP USER 'testman'@'localhost';

Install MySQL
Windows

You can install just MySQL or some versions of XAMP: WAMP(Windows, Apache, MySQL, PHP), MAMP(MacOS, Apache, MysQL,PHP), LAMP(Linux, Apache, MySQL, PHP)) which includes MySQL.

UniServer (UniForm Server) is one version of WAMP. It is handy, because it doesn't need it to be installed. After unzipping the download file, you can start to use it. It is also easy to copy to another computer.

UniServer works only in Windows, if you are using Linux or Mac, you have to install some LAMP- or MAMP-version. When you start UniServer, you will see the Control Panel:
uniserver control panel
From this panel you can start and stop Apache and MySQL. You can also start MySQL Console and Server Console. Server Console is just a Windows command line and MySQL Console is MySQL-monitor.

Mac

The easiest way to install MySQL to MAC is probably to use Homebrew.

If you don't have Homebrew in your MAC, you can get it from https://flaviocopes.com/homebrew/

All you have to do is execute command 

brew install mysql
After the installation, you can start your MySQL with the command 
brew services start mysql
or with the command 
mysql.server start
I recommend the later, because if you use the first command, your MySQL will start automatically whenever you reboot your MAC.

The root password is not set, so you should set it after installation like this:

  • Connect to your MySQL with the command mysql -u root
  • Execute command SET PASSWORD='somepassword'

MySQL Configuration

my.ini or my.cnf is normally the configuration file for MySQL. You can edit that file from the Control-panel of Uniserver. Example if you want to write Stored Procedures the default size of thread_stack is often too small. Se open the MySQL-menu and choose Edit config file and change this thread_stack = 128K to bigger value, example 256 or 512.

bind-address

By default, MySQL does not allow network logins. You can change this setting by editing the bind-address setting in the configuration file.

If the setting is like this bind-address = 127.0.0.1, the server will only respond to requests from localhost. If you change it to bind-address=0.0.0.0, the server will listen to all IP addresses associated with it, meaning it will respond to requests from any source.

MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

You can find a tutorial for using MySQLWorkbench from https://www.guru99.com/introduction-to-mysql-workbench.html

And the manual for MySQLWorkbench from https://dev.mysql.com/doc/workbench/en/

If you have problems with the installation check this https://dev.mysql.com/doc/workbench/en/wb-installing.html
(Example in Windows you might need to install C++ Redistributable.)

Workbench Intallation videos

Here is a link to video where I show how to install MySQLWorkbench to Windows https://www.youtube.com/watch?v=qQHCnMlFJh4. And note that when I started the installation, I get the error and I have to install the "C++ 2019 Redistributable ..." first.

Here is a link to video where I show how to install MySQLWorkbench to MAC https://www.youtube.com/watch?v=5V6fGEgi6sw. And note that I didn't have the latest version of macOS, and that's why I couldn't intall the latest version of MySQLWorkbench.

Note: There is no sound on those videos.

Secure connection

It is not safe to connect to the Server over public internet using TCP/IP. And quite often the company does not even allow to connect to the server over the internet. Then one option is to use VPN and another is to use TCP/IP over SSH. The last option is described in the image. So inside the companys network there is a SSH-server which has also a MySQL-client. Then it is possible to connect to that SSH-server and then from that server connect to the MySQL-Server.

You can also connect with MySQL Workbench using TCP/IP over SSH, if you configure the connection like in below image.

SSL if available

Some MySQLWorkbench versions does not have the option useSSL if available and that might be a problem when you are connecting to your local-server, which does not have SSL-support. You can change the configuration like this

  1. Open to some editor file MySQL\MySQL Workbench...\modules\data\mysql_rdbms_info
  2. Search and replace these texts 
       >2|Require,3|Require and Verify CA,4|Require and Verify Identity
    with these: 
       >0|No,1|If available,2|Require,3|Require and Verify CA,4|Require and Verify Identity
Now you will have the option like in below image



Toggle Menu

Copyright © Pekka Alaluukas / 2024
email