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)
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:
In order to connect from Client to Server the parameters are:
mysql -u mysqlUser -h mysqlserver.company.com -D databasename -p
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';
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 the other is to use SSL and third one 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.
MySQLWorkbench version 8.0.27 does not have the option use SSL if available and that might be a problem when you are connectiong to your local-server, which does not have SSL-support. You can change the configuration like this
>2|Require,3|Require and Verify CA,4|Require and Verify Identitywith these:
>0|No,1|If available,2|Require,3|Require and Verify CA,4|Require and Verify Identity
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
Action | Command |
---|---|
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; |
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. |
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:
Type | Properties |
---|---|
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. |
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.sqlExample Below command will backup Project database to file project.sql inlcuding also the stored procedures
mysqldump -u root Project -p --routiness > project.sqlAnd the next command will backup only the table Book
mysqldump -u root Project Book -p >book.sqlAnd then you can use those codes to return your database and tables.
mysql -u root -D Project -p < mybackup.sql
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
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 like this
CREATE USER 'testman'@'localhost' 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';
MySQL supports different kind of authentication plugins examle
default_authentication_plugin=mysql_native_passwordYou can check what is the default in your server with the command
show variables LIKE '%default_authentication%';
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 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';
You can delete the the user with below command
DROP USER 'testman'@'localhost';
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:
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.
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.
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 mysqlAfter the installation, you can start your MySQL with the command
brew services start mysqlor with the command
mysql.server startI 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:
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.)
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.