A pretty common topic in Support tickets is the rather infamous error: ERROR 1040: Too many connections. The issue is pretty self-explanatory: your application/users are trying to create more connections than the server allows, or in other words, the current number of connections exceeds the value of the max_connections variable.
This situation on its own is already a problem for your end-users, but when on top of that you are not able to access the server to diagnose and correct the root cause, then you have a really big problem; most times you will need to terminate the instance and restart it to recover.
Root user can’t connect either! Why!?
In a properly set up environment, a user with SUPER privilege will be able to access the instance and diagnose the error 1040 problem that is causing connection starvation, as explained in the manual:
mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the privilege to administrators and not to normal users (who should not need it), an administrator who also has the PROCESS privilege can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected.
But we see lots of people who give SUPER privileges to their application or script users, either due to application requirements (dan Lire la suite…
Source: Nico’s Blog
MySQL Master-Slave replication is natively supported by MySQL. However its configuration is not so simple. For each slave added as a replica, a few configuration steps must be done on both the master and itself.
So if you want to install a master instance on a machine or VM, and then install 5 other instances as slaves on other hosts, you will be doing quite a lot of back-and-forth configuration. I couldn’t find any way of configuring the replication automatically on the web, so I decided to create my own bash script to do it.
MySQL Master-Slave Replication
What is it and why use it ?
This form of replication is pretty simple: only a single instance, known as the master, creates or updates data. Each additional instance, called slave, simply keeps a copy of the master’s data, by replicating it each time the master has new data. Lire la suite…
Source: ryadel.com
We already had the opportunity to talk about Replication between MySQL Database in a previous article, where we described it as a great way to increase the security and reliability of data storage without spending a fortune. In this article we will see what to do when the Replication stops working: how to notice, what can I do to restore it and, most importantly, how to ensure that the data is re-synchronized.
Identify causes
The first thing to do is make sure that the Replication is actually broken. Although in most cases we can clearly see it by just looking at the replicated data, we need to check it in an objective way. In order to do this go to the Slave, issue the command SHOW SLAVE STATUS – or SHOW SLAVE STATUS\G if you prefer to read the results in human-readable format – and go read the column contents: Slave_SQL_Running and Slave_IO_Running: if there’s at least a NO, that means that the Replication is actually broken, otherwise the problem is attributable to other causes. Lire la suite…
