There are various ways to shutdown MySQL. The traditional cross platform method is to use the shutdown command in the
mysqladmin client. One drawback is that it requires shell access; another is that it cannot start MySQL again automatically. There are platform specific options that can perform a restart such as using
systemctl on Linux or install MySQL as a service on Microsoft Windows. What I will look at here though is the built in support for stopping and restarting MySQL using SQL statements.
MySQL 5.7 added the
SHUTDOWN statement which allows you to shut down MySQL using the MySQL command-line client or MySQL Shell. The command is straight forward to use:
You will need the SHUTDOWN privilege to use the statement – this is the same as it required to use
mysqladmin to shutdown MySQL. There is one gotcha to be aware of with the
SHUTDOWN statement: it only works with the old (traditional) MySQL protocol. If you attempt to use it when connected to MySQL using the new X Protocol, you get the error: ERROR: 3130: Command not supported by pluggable protocols as shown in the next example:
RESTART statement, on the other hand, works through both protocols and also requires the
For the restart to work, it is necessary that MySQL has been started in presence of a “monitoring service”. This is the default on Microsoft Windows (to disable the monitoring service start MySQL with
--no-monitor). On Linux the monitoring service can for example be systemd or
As an example of where the
RESTART statement comes in handy is for MySQL Shell’s AdminAPI for administrating a MySQL InnoDB Cluster cluster. MySQL Shell can when connected to MySQL Server 8.0 use the new
SET PERSIST syntax to make the required configuration changes and then use the
RESTART statement to restart the instance to make non-dynamic configuration changes take effect.
RESTART statements may not be the most important changes in MySQL 5.7 and 8.0, but they can be handy to know of in some cases.