There could be many reasons why a connection to MySQL server can fail, like

• Networking Problem
• Server itself could be down
• Authentication Problems
• Maximum Connection Errors allowed.

Of all the errors, this thread will discuss Maximum Connection Errors.
This particular parameter max_connect_errors defines the no. of connection errors a particular host can make before it is banned. Yes Banned! This is a feature that MySQL provides to limit erroneous clients.

By default 10 maximum connection errors are allowed per host. You can check using foll. command.

mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
+--------------------+-------+
1 row in set (0.02 sec)

You can set the above variable in the same manner as setting max_connections, ie in my.cnf file.

[mysqld]
max_connect_errors=100
...

Once particular host exceeds the no. of errors, a particular command is required to be given to the server to reset the host connect errors and indirectly allow the hosts to connect to the server. This command flush hosts will help you do that.

In our scenario, where there are 300 connections per/second, and each connection-query is taking some processing time before connection is closed, There could be a time where the Server is busy, and starts to queue processes, in such a case the connection would be still held until its query is processed. So when a new connection is required, the server may reject it considering the connections that are currently active. ie. We’d set max_connections to 300, so every new connection request could be denied adding to the connection errors, which could mean the host could be blocked. Considering a Web Server + MySQL Server scenario, the web server’s host could be banned until a flush hosts is fired at the MySQL Server.

So in such a case, it is necessary to increase the no. of simultaneous connections that can be made to the server by changing the max_connections parameter. Still if the load on MySQL server is high, and you are getting Connection errors, its time to load balance the Server.

MySQL will allow n number of connections at a given point of time, To find out that n no of connections run the following command.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

So this server will allow max of 100 connections at any given point of time.

Before we change the following variable, its necessary to understand how MySQL uses connections and tables. Consider that for every connection on your server, you are querying 3 tables. ie. MySQL will open 3 file descriptors for each connection (1 for each table)

MySQL Doc
…the table is opened independently by each concurrent thread. …

Considering your site has a about 300 connections per/second, 200 of those connections will be dropped since your setting is 100. So changing the setting to 300, MySQL will open (300 x 3 = 900) file descriptors. A file descriptor is nothing but a connection to that file (programmers,sysads will know what I mean). Now its necessary to check what does your OS suuport, normally OS will support about X no. of file descriptors at a given point of time. So increasing your connection settings will not help solve the issue. You need to consult your OS’s manual before changing the settings on a production server.

Fine, Lets assume that every param is under the limit, we can tweak around, As in MySQL almost every parameter can be controlled by configuration file /etc/my.cnf in Linux or [MySQL INSTALL DIR]\my.ini in Windows.

Open the file and add the following under mysqld section max_connections=300. So it should be something like this.

[mysqld]
max_connections=300
...

Restart MySQL Server after changing the file. You can alternatively run a Command while the server is running, but the setting won’t be maintained if the server restarts. The following command can be executed

mysql> set global max_connections=300;

References

1. Show Variables
2. Server System Variables
3. Server Parameters
4. Too many connections
5. Table Cache

Once we’ve issued the command start slave;, The Replication should ideally start, But as well all know nothing works initially. So to check everything is working, issue the following command.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.5.99
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000009
        Read_Master_Log_Pos: 37822065
             Relay_Log_File: ruturaj-vartak-relay-bin.000028
              Relay_Log_Pos: 37822202
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 37822065
            Relay_Log_Space: 37822202
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Check the parameters

• Slave_IO_Running (Should be yes)
• Slave_SQL_Running (Should be yes)

If either of them are No, Open the /hostname.err file. This is the mysql error file, You can check it for a specific configuration error. Most often either the Master log is not present (incorrect filename) or the master log position is incorrect. You can re-specify the parameters by using change master to .. command.

But be sure you issue stop slave first and then change master to … command and finally start slave

Once both the Slave_IO and Slave_SQL parameters are yes, you can start writing, modifying anything in the test database of the server.

You can also check the hosts connected by issuing this command on the master

mysql> show slave hosts;
+-----------+----------------+------+-------------------+-----------+
| Server_id | Host           | Port | Rpl_recovery_rank | Master_id |
+-----------+----------------+------+-------------------+-----------+
| 1         | slave-server-1 | 3306 | 0                 | 2         |
+-----------+----------------+------+-------------------+-----------+

You can now check the MySQL’s data directory if the binary log is created. You should find a file something like mysql-bin.000001. You will also find a file mysql-bin.index.

In the mysql-bin.index file you will find the list of all the binary logs. i.e. If you restart the server a multiple times you will find your mysql-bin.index to have something like

./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003

Now try this query in the mysql prompt show master logs; It should show something like this, depending the no. of binary logs present.

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 | 9066115   |
| mysql-bin.000005 | 117       |
| mysql-bin.000006 | 98        |
+------------------+-----------+

Now it is essential to add a user account which the replication will use, Since we are granting a permission, you need to log in with a root access to mysql, and then run the following command.

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'192.168%' IDENTIFIED BY 'slavepass';

Flush the privileges using mysql> flush privileges

This should ideally make your master ready for Replication.