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';

1. Session Creation:
   

   After a user logs in using mysql-client, the MySQL server validates the credentials (username, password, IP, etc.). If successful, the server establishes a session with a unique session ID.

2. Query Execution:
   

   Each SQL query (SELECT, INSERT, UPDATE, DELETE, etc.) sent by the client is linked to the established session. The server identifies the session based on its connection ID and executes the query accordingly.

3. Privilege Checking:
   

   MySQL checks user privileges only once at the start of the session. The server does not revalidate privileges for every query. Instead, it uses the privileges fetched during login.

4. Query Processing and Response:
   

   The server processes the query and returns results to the client. If the user’s privileges are modified while the session is active, the changes take effect only after the user logs in again, unless explicitly refreshed with commands like FLUSH PRIVILEGES.

MySQL server settings are stored in a configuration file:

• Windows: my.ini in directoryC:\ProgramData\MySQL\MySQL Server 8.0)
• Mac/Linux: my.cnf (locations: /etc/my.cnf or /etc/mysql/my.cnf or ~/.my.cnf)

You can find which configuration file MySQL is using with this command:

sc qc MySQL80

Here are the most common settings developers need to modify:

Setting	Purpose	Example Value
character_set_server	Default character encoding for the server. Use utf8mb4 to support all Unicode characters including emojis.	character_set_server = utf8mb4
collation_server	Default collation (sorting rules) for the server.	collation_server = utf8mb4_unicode_ci
max_allowed_packet	Maximum size of a single packet/query. Increase if you work with large files (images, BLOBs) or long SQL queries.	max_allowed_packet = 64M
default_authentication_plugin	Default authentication method for new users (see Authentication plugin section).	default_authentication_plugin = mysql_native_password
bind-address	IP address MySQL listens on. Default 127.0.0.1 (IPv4 localhost only) or ::1 (IPv6 localhost). Use 0.0.0.0 to allow connections from any IPv4 address, or :: for any IPv6 address. Warning: Only use 0.0.0.0 or :: in development environments, not in production without proper firewall rules.	bind-address = 0.0.0.0 bind-address = 0.0.0.0,::1
port	TCP port the MySQL server listens on.	port = 3306
max_connections	Maximum number of simultaneous client connections.	max_connections = 151

Steps:

1. Open the configuration file (my.ini or my.cnf) in a text editor with administrator/root privileges
2. Find the [mysqld] section (server settings)
3. Add or modify settings under [mysqld]:

   [mysqld]
   character_set_server = utf8mb4
   collation_server = utf8mb4_unicode_ci
   max_allowed_packet = 64M
   

4. Save the file
5. Restart MySQL server for changes to take effect

You can check current server settings without editing the configuration file:

-- Show all variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Show current character sets in use
SHOW VARIABLES LIKE '%character%';

• Backup first: Always make a backup copy of the configuration file before editing
• Syntax matters: Incorrect syntax can prevent MySQL from starting. If MySQL won't start after changes, restore the backup.
• Comments: Use # or ; at the start of a line for comments
• Testing: Test configuration changes in a development environment before applying to production
• Not all settings are in the file: If a setting isn't in the file, you can add it under the [mysqld] section

The following animation demonstrates how to use basic MySQL commands in the MySQL console:

Key takeaways from the demonstration:

• SHOW DATABASES: Lists all available databases on the server
• CREATE DATABASE: Creates a new database
• USE database: Switches to a specific database to work with it
• SHOW TABLES: Lists all tables in the current database
• DESCRIBE table: Shows the structure and columns of a table
• SHOW ENGINES: Displays available storage engines and their properties
• DROP TABLE/DATABASE: Permanently deletes a table or database

MySQL server has a special database called mysql, which is a system database. It contains metadata about the server configuration, including:

• user - user accounts and global privileges
• db - database-level privileges
• tables_priv - table-level privileges
• time_zone_name - time zone information

Important: You should NOT modify tables in the mysql database directly. Instead, use SQL commands like CREATE USER, GRANT, REVOKE, etc. Direct modifications can corrupt the system and cause authentication problems.

MySQL supports different authentication plugins, for example:

• mysql_native_password - Older plugin, widely compatible with legacy applications
• caching_sha2_password - Default in MySQL 8.0+, more secure but requires newer client libraries

Why it matters: Some older applications or client libraries may not support caching_sha2_password and will fail to connect. In those cases, you need to either update the client or use mysql_native_password.

If you don't set it in the CREATE USER command, MySQL will use the default of your server. You can check what is the default in your server with the command:

SHOW VARIABLES LIKE '%default_authentication%';

You can set 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 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 EXECUTE privilege to procedure debit_transfer in database Project to user testman, if he connects from localhost.

  GRANT EXECUTE ON Project.debit_transfer 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';

Below command will show which privileges user testman has, if he connects from localhost.

  SHOW GRANTS FOR 'testman'@'localhost';

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';

You can delete the the user with below command

DROP USER 'testman'@'localhost';

Without SSL, all data travelling between your application and the MySQL server is sent in plain text. This means:

• Passwords are visible to anyone on the network path
• Query results (including sensitive rows) can be read or modified in transit
• Cloud and hosted databases (AWS RDS, Azure Database, Google Cloud SQL) require or strongly recommend SSL

SSL encrypts the entire connection so that even if traffic is intercepted, it cannot be read.

You can verify whether your MySQL server has SSL enabled and which cipher is in use:

-- Check if SSL is active for the current connection
SHOW STATUS LIKE 'Ssl_cipher';

-- List all SSL-related status variables
SHOW STATUS LIKE 'Ssl%';

-- Check server-level SSL configuration
SHOW VARIABLES LIKE '%ssl%';

If Ssl_cipher returns a non-empty value (e.g., TLS_AES_256_GCM_SHA384), the connection is encrypted. An empty value means the connection is unencrypted.

You can enforce SSL at the user level so that the account cannot connect without an encrypted connection:

-- Require SSL when creating a new user
CREATE USER 'appuser'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

-- Add SSL requirement to an existing user
ALTER USER 'appuser'@'%' REQUIRE SSL;

-- Verify the requirement
SELECT user, host, ssl_type FROM mysql.user WHERE user = 'appuser';

After REQUIRE SSL is set, any attempt to connect without SSL will be refused with an access denied error.

When connecting from the command line you can control the SSL mode with the --ssl-mode option:

-- Require an encrypted connection (recommended for remote servers)
mysql --ssl-mode=REQUIRED -h db.example.com -u appuser -p

-- Verify the server certificate as well (most secure)
mysql --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem -h db.example.com -u appuser -p

-- Disable SSL (only for local development on trusted networks)
mysql --ssl-mode=DISABLED -h localhost -u root -p

ssl-mode value	Behaviour
PREFERRED	Use SSL if available, fall back to plain text (default)
REQUIRED	Always use SSL; fail if the server does not support it
VERIFY_CA	Require SSL and verify the server certificate against a CA
VERIFY_IDENTITY	Require SSL, verify CA, and check that the hostname matches the certificate
DISABLED	Never use SSL

When connecting from a Node.js / Express application with the mysql2 package, pass the SSL options in the connection configuration:

const mysql = require('mysql2');
const fs    = require('fs');

const pool = mysql.createPool({
    host    : 'db.example.com',
    user    : 'appuser',
    password: 'password',
    database: 'mydb',
    waitForConnections: true,
    connectionLimit   : 10,
    ssl: {
        ca: fs.readFileSync('/path/to/ca.pem'),
        rejectUnauthorized: true   // verify server certificate
    }
});

// Confirm the connection is encrypted
pool.query("SHOW STATUS LIKE 'Ssl_cipher'", (err, results) => {
    console.log(results[0].Value); // e.g. TLS_AES_256_GCM_SHA384
});

Set rejectUnauthorized: false only if your cloud provider uses a self-signed certificate and you cannot supply the CA file — it disables certificate verification and should not be used in production. Cloud providers (AWS RDS, Azure, Google Cloud SQL) publish their CA certificate files in their documentation.

When creating or editing a connection in MySQL Workbench, open the SSL tab. You can choose between three modes:

• No — SSL disabled
• If available — use SSL if the server supports it (default)
• Require — refuse the connection if SSL is not available

For connections to cloud databases, paste the path to the CA file provided by your cloud vendor into the SSL CA File field and set the mode to Require.

Setting the correct time zone ensures consistency in how time-related data is stored and retrieved. If the server's time zone differs from the application's or user's time zone, it can lead to confusing data mismatches, incorrect reporting, and even critical errors in systems like logging, scheduling, or financial transactions.

The TIMESTAMP data type in MySQL is time zone-aware. It stores values in UTC internally and automatically converts them to the session's time zone when queried. This makes it suitable for multi-user systems where users are in different time zones.

In contrast, DATETIME stores the value exactly as given, without any time zone conversion, which can be useful when the time is fixed and not relative to any time zone (e.g., a historical event).

When you query a TIMESTAMP field, the time displayed depends on the MySQL session time zone setting, not the client computer's time zone. The same stored time will appear different to users in different sessions if they have different time zone settings.

Example:

-- Session 1: In Finland (Europe/Helsinki, UTC+2)
SET time_zone = 'Europe/Helsinki';
INSERT INTO logs (event_time) VALUES (NOW());  -- Inserts: 2024-12-31 15:00:00
-- MySQL stores internally: 2024-12-31 13:00:00 UTC

-- Session 2: In New York (America/New_York, UTC-5)
SET time_zone = 'America/New_York';
SELECT event_time FROM logs;  -- Displays: 2024-12-31 08:00:00

-- Session 3: In Finland
SET time_zone = 'Europe/Helsinki';
SELECT event_time FROM logs;  -- Displays: 2024-12-31 15:00:00
   

Important: The client computer's time zone does not automatically affect the displayed time. Your client application must explicitly set the session time zone when connecting to MySQL.

Example in Node.js (mysql2):

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  timezone: 'Europe/Helsinki'  // Sets session time zone
});
   

Example in PHP (PDO):

$db = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'password');
$db->exec("SET time_zone = 'Europe/Helsinki'");
   

If you don't set the session time zone explicitly, MySQL uses the server's default time zone, which may not match your users' locations.

The TIMESTAMP data type is ideal when you need to track events relative to time zones or log actions with precision in a global context. Because TIMESTAMP values are stored in UTC and automatically converted to the session’s time zone, they are particularly useful in multi-user systems where users operate in different geographic regions.

It's commonly used for tracking record creation and update times (e.g., created_at, updated_at) in web applications, logging systems, or auditing changes where the exact moment an event occurred is more important than how it appears in a fixed time zone.

Note that in older versions of MySQL (up to 8.0), TIMESTAMP has a limited date range, supporting only dates between 1970 and 2038 due to its 32-bit Unix time representation. If you need to store dates outside that range such as future appointments, contracts, or historical records DATETIME is a safer and more flexible choice.

You can first check if the time zone is already active in your server, by executing command

SELECT name FROM mysql.time_zone_name;

If the result is not empty, it is active. If it is not active, you can fix it like this:

1. Download file (timezone_2025b_posix_sql.zip) from https://dev.mysql.com/downloads/timezones.html
2. Extract the zip file to a folder of your choice, e.g. C:\temp\timezone
3. Open Command Prompt or PowerShell as Administrator
4. Navigate to the folder: cd C:\temp\timezone
5. Execute command mysql -u root -p mysql < timezone_posix.sql

Then you can use the Time Zone in SELECT-queries like this:

SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Helsinki') AS 'Finnish_time';
SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Stockholm') AS 'Swedish_time';

In Linux, you can activate Time Zones, by giving below command

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql