Practical MySQL Server Administration - Part 4: Startup, Shutdown, and Connection Management
Part 4 focuses on startup, shutdown, and connection handling in this 7-part MySQL administration series.
Much of MySQL operational work comes down to three questions: how do I stop the server safely, how do I start it again cleanly, and why does a specific user gain or lose access? Each question sounds basic, but all three sit at the center of real production administration.
Start and Stop the MySQL Server
On modern Linux systems, use systemctl as the primary interface for controlling the service.
Shut down the server:
systemctl stop mysqld
systemctl status mysqld
You can also shut it down from a client session:
mysqladmin -u root -p shutdown
Example terminal output:
[root@mysql-a ~]# systemctl stop mysqld
[root@mysql-a ~]# systemctl status mysqld
[root@mysql-a ~]# mysqladmin -u root -p shutdown
Enter password:
Start the server again:
systemctl start mysqld
systemctl status mysqld
I prefer checking status immediately after every stop or start operation, even in lab environments. It catches service failures early.
Understand Authentication Inputs
MySQL 8.0 uses caching_sha2_password as the default authentication plugin. In practical terms, successful authentication depends on more than just the password.
MySQL evaluates:
- the username
- the password
- the client host
The third input creates a lot of confusion.
Connect Locally and Remotely
Local connection syntax:
mysql -u<username> -p<password>
Remote connection syntax:
mysql -u<username> -p<password> -h <server_name>
Example terminal output:
-bash-4.2$ mysql -uroot -pWElcome_1234#
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql>
-bash-4.2$ mysql -uroot -pWElcome_1234# -h mysql-b
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql-b |
+------------+
1 row in set (0.00 sec)
The remote query against
@@hostnamequickly proves you reached the expected server.
Host-Based Users Matter More Than Many People Expect
The same username can behave differently depending on the host definition attached to the account.
Example account creation:
CREATE USER 'mysql-1'@'192.168.2%' IDENTIFIED BY 'Welcome@123';
CREATE USER 'mysql-3'@'192.168.2%' IDENTIFIED BY 'Welcome@123';
CREATE USER 'mysql-4'@'192.168.2%' IDENTIFIED BY 'Welcome@123';
CREATE USER 'mysql-2'@'localhost' IDENTIFIED BY 'Welcome@123';
Verify the user-host pairs:
mysql> select user,host from mysql.user;
+------------------------+------------+
| user | host |
+------------------------+------------+
| mysql-1 | 192.168.2% |
| mysql-3 | 192.168.2% |
| mysql-4 | 192.168.2% |
| mysql-2 | localhost |
| root | localhost |
+------------------------+------------+
17 rows in set (0.00 sec)
A localhost-only account does not automatically work for remote connections, even if the username and password match.
Validate Local Versus Remote Access
This example makes the behavior obvious.
Local connection for mysql-2 succeeds:
-bash-4.2$ mysql -u mysql-2 -pWelcome@123
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> exit
Remote connection for the same account fails:
-bash-4.2$ mysql -u mysql-2 -pWelcome@123 -h 192.168.2.15
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mysql-2'@'mysql-a' (using password: YES)
Meanwhile, a host-based remote account behaves the opposite way.
-bash-4.2$ mysql -umysql-1 -pWelcome@123 -h 192.168.2.15 -D db2
mysql> exit
-bash-4.2$ mysql -umysql-1 -pWelcome@123 -h localhost -D db2
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mysql-1'@'localhost' (using password: YES)
This example cleanly explains host-qualified MySQL accounts to new administrators.
Grant Privileges with Reuse in Mind
The source material reinforces a good practice worth repeating: use roles where possible instead of granting large privilege sets repeatedly user by user.
For example:
GRANT CREATE, SELECT, INSERT, DELETE, UPDATE, DROP ON *.* TO 'mysql-1'@'192.168.2%';
In larger environments, roles make grants easier to audit, update, and revoke cleanly.
Final Thoughts
Startup and shutdown form the visible part of administration. Connection management causes most day-to-day surprises. If you keep service control, host-qualified accounts, and privilege strategy straight, you eliminate a large class of avoidable support issues.
In Part 5, I shift into one of the most important architectural concepts in MySQL administration: storage engines.