0

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

  • Do '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)? – Vérace Jan 25 '16 at 16:39
  • @Vérace; For my requirements, I think this is the best answer. Could you put it in as an answer, so I can mark it as accepted? – Brad Silva Jan 28 '16 at 0:37
0

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)?

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.