I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work. I'd prefer not to drop and re-create all of the user accounts, but that's the only solution I've come up with so far. Hmm.. Refusing network connections would work as well, as the maintenance will be done via an os-local mysql CLI. I'd prefer to not have to stop and re-start the server, but that's not impossible.
What are best practices here?
Thanks in advance, Brad
Issue the command:
service mysql restart --skip-networking
This prevents all TCP/IP connections.
You can login as root@localhost and the client program will use the socket file rather than TCP/IP. Do all your DDL work. Can you shut down the web-server (if you're using one)?
As said by Brad Silva, you can skip connections, but you need to restart the mysql server anyway ( with --skip-networking ), as skip_networking is a read only variable.
When finished, restart mysql without this parameter.
EDIT: When I say it's a read-only variable, I mean you cannot simply set it in the console, like other variables you can switch at runtime. This requires a restart, unless I'm wrong.
Partial solutions:
As root, do SET GLOBAL read_only = ON;. This will prevent write operations by every one by root.
SET GLOBAL max_connections = 1;. But the trick is to grab that "1"; I don't have a clean way to do that.
Note: With either trick, you may need to kill the existing connections in order to kick the connected users out. And "connection pools" may complicate matters.
