Instead of installing the full MySQL Server, you can choose to use UniServer, especially if you're looking for a lighter and more portable solution. UniServer is a compact Windows-based server package that includes MySQL (or MariaDB), Apache, and PHP, all pre-configured for quick and easy use. It’s ideal for local development and testing environments where you don’t need the full-scale features of a production-grade MySQL Server. UniServer doesn’t require installation in the traditional sense — it can run directly from a folder or even a USB drive, making it convenient and non-intrusive. This makes it a great option for developers who want a simple setup without modifying system-wide configurations or installing background services.

The UniServer software includes a MySQL server and a MySQL client. UniServer is user-friendly software, and its installation is simple:

1. download UniServer from the website https://www.uniformserver.com
2. extract the downloaded package to your computer's root directory (C:/ or D:/)

When you start UniServer, you will see the Control Panel:

This panel allows you to start and stop the MySQL Server. You can also open the Server Console (Windows Command Prompt) or the MySQL Console (MySQL monitor).

When you click the button MySQL Console, you will execute command

mysql -h localhost -u root -pRootPass

Where RootPass is your MySQL root password.

For macOS users, especially those with Apple Silicon (M1/M2/M3) processors, the easiest way to install MySQL Server is using Homebrew, a popular package manager for macOS. This method is preferred by many Mac developers because it simplifies installation, updates, and service management through the command line.

Installation steps using Homebrew:

1. If you don't have Homebrew installed, first install it from https://brew.sh
2. Open Terminal and run: brew install mysql
3. Start the MySQL service: brew services start mysql
4. Secure your installation by running: mysql_secure_installation

Homebrew makes it easy to manage MySQL:

• Start MySQL: brew services start mysql
• Stop MySQL: brew services stop mysql
• Restart MySQL: brew services restart mysql
• Update MySQL: brew upgrade mysql

Alternatively, you can download the official MySQL Installer for macOS (ARM64 DMG Archive for Apple Silicon) from https://dev.mysql.com/downloads/mysql/. Choose the version that matches your macOS and processor architecture.

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 (usually in MySQL installation directory, e.g., C:\Program Files\MySQL\MySQL Server 8.0\my.ini)
• Mac/Linux: my.cnf (locations: /etc/my.cnf, /etc/mysql/my.cnf, or ~/.my.cnf)
• UniServer: UniServerZ\core\mysql\my.ini

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

mysql --help | grep "Default options"

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
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:
   • Windows (Command Prompt as Administrator): net stop mysql then net start mysql
   • Mac (Homebrew): brew services restart mysql
   • Linux: sudo systemctl restart mysql
   • UniServer: Use the Control Panel to stop and start MySQL

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

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 file and copy the file to UniServer folders
3. 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 'Finish_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