I made a mistake and set the max_connections column for root to '1', so now every time I try to connect to change it I get:
ERROR 1226 (42000): User 'root' has exceeded the 'max_connections_per_hour' resource (current value: 1)
It was my personal server, so I don't have any other user.
I've tried:
But nothing works. Does anyone know how to change it?
You have to remove that attribute via MySQL
This only works for MySQL 5.7
ALTER USER root@localhost MAX_CONNECTIONS_PER_HOUR 0;
If you have other root users (like root@'127.0.0.1' and root@'%'), you will have to run a separate ALTER USER for each root user.
UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;
Log out and login to MySQL and you should notice the difference
If you are logged out, you have to come back in an hour and try again.
If you can't wait one hour and you can restart mysql, you could enable --skip-grant-tables
service mysql restart --skip-grant-tables --skip-networking
Next, you can login and perform APPROACH #2
Then, restart mysql normally
service mysql restart
With Windows, it's a little more involved.
Assuming you installed MySQL in Windows using the MSI Installer, there is no my.ini.
Here is where it gets crazy, but here we go ...
Login to the Windows Command Line as Administrator and run
C:\> net stop mysql
Find the folder where mysqld.exe is located on the Windows.
Usually, it would be something like C:\ProgramData\MySQL\MySQL Server 5.6
Start mysqld and login (no password needed)
cd "C:\ProgramData\MySQL\MySQL Server 5.6"
start mysqld --skip-grant-tables --skip-networking
mysql
Apply APPROACH #2
UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;
exit
Shutdown mysqld manually
mysqladmin shutdown
Start mysqld as a service
net start mysql
