MySQL replication manager has ability to

1. Start, Stop Slave
2. Set Status of Slave

Download

License: GNU General Public License

This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and “Create Stored Routite…” This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

• In your ~/.bashrc add the following…

  export EDITOR=vim VISUAl=vim
  

• Create / Edit ~/.vimrc file, and add the following…

  set noai
  set nonu
  

• Log into the MySQL CLI, and set the following command

  delimiter //

The Developer now is ready on the server side to create/edit the procs. On the client side I assume the Developer has a Notepad equivalent text editor, or even better, I suggest Kate (if you are on Linux box).

In the editor…

• Write an if exists drop procedure statement…
• With few carriage returns following…, write the whole content (SP)
• If a specific user/s need execute, write the grant execute permission after few carriage returns…

The dev is now ready to Create the proc..

1. Paste the if exists drop statement and execute
2. In the mysql prompt, select the dB and enter the following prompt

   \e

   . This is basically to go into editor mode. Press “i”, to get into the insert mode and paste the actual SP Content

3. Press Escape key, to go back in normal mode and then press “:wq” to save and exit
4. Enter “//” (the command delimiter) and press Enter, that should either create the proc, or return with the errors.
5. Execute the Execute Grant statements

Done, Simple.

PS: Copy Paste @ rescue yet again !!! 😀

MySQL support same-server replication into another database, Its quite a weired requirement, but in reality weired is common.

Consider a server 192.168.5.70, which has 2 databases db1 and db2
Now we shall set up replication for two tables on db1, ie. table1 and table2.

Here is the my.cnf

[mysqld]
server-id=1
#### Replication ####
report-host=master-is-slave-host
log-bin=192.168.5.70-binlog
relay-log=192.168.5.70-relaylog

replicate-same-server-id=1

binlog-do-db=db1

# Note.... On rewrite, the  command is changed into buffer
# so the replicate-do-db and replicate-do-table should have the
# re-written db name.
replicate-rewrite-db=db1->db2
replicate-do-table=db2.table1
replicate-do-table=db2.table2

Lets look at it carefully.

replicate-same-server-id=1
This is to tell the slave the commands that it has to execute will be having the same server-id as its own.

binlog-do-db=db1
To log only database db1

replicate-rewrite-db=db1->db2
Let the slave know that whatever command is for db1 it has to execute on db2

replicate-do-table=db2.table1
replicate-do-table=db2.table2
Note how the table in db2 is replicated, as against the general thinking of db1.table1, MySQL could be using some buffered string and then evaluating the replicate-do-table rule.

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.

Now that master is all set! Its time for the slave to obey the master. To setup the slave, We need to tell the Slave server which log file it is supposed to read, and yet again we need to give a unique server-id to the slave.

So open the /etc/my.cnf and add the following below the [mysqld] section.

server-id=2
replicate-do-db=test
report-host=slave-server-1

• The directive replicate-do-db: specifies that only test database is to be replicated.
• The directive report-host: specifies the hostname that will be seen on the master

Restart your MySQL server.

Now we are ready to tell the server about the master server.
Give following command to the slave server.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.5.100',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=4;

The master_log_pos is the parameter which tells the server to read the file from a particular position.
You can check the position by the following command on the master server.

mysql> show binlog events in 'mysql-bin.000009' limit 10;
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000009 | 4    | Format_desc | 2         | 98          | Server ver: 5.0.19-standard-log, Binlog ver: 4                     |
| mysql-bin.000009 | 98   | Intvar      | 2         | 126         | INSERT_ID=10077                                                    |
| mysql-bin.000009 | 126  | Query       | 2         | 430         | use `test`; insert into testtable (tname) values('ruturaj vartak') |
| mysql-bin.000009 | 430  | Query       | 2         | 511         | use `test`; truncate table testtable                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+

So you can MASTER_LOG_POS set anything in the Pos column.

This should set up all the required configuration of the slave. You just need to tell slave to start the replication of the slave by the following command

mysql> start slave;

To configure the master we need to start binary logging, and some minor tweaking. Before we move ahead, I assume that you have the root or Administrator access to the sytem where you are setting up replication. First let us setup the Binary logging.

To enable binary logging, we need to configure the configuration file /etc/my.cnf on Linux. If you are in Windows, I guess it should be in c:\program files\MySQL\MySQL Server 5.0\my.ini. Open the file and add the following below the [mysqld] section

log-bin=mysql-bin
server-id=1

So the configuration should look something like

[mysqld]
log-bin=mysql-bin
server-id=1
... other parameters ...

[mysql]
... parameters for mysql client ...

• The directive log-bin defines the file name of the binary log. All the binary logs that will be created in the data directory, are named something like mysql-bin.000004
• The directive server-id is to give the servers in the replication a unique numeric ID. It has to be an integer. So we’ll call our Master Server as 1.

This would set the master, all we require is to restart the Server. You can restart the server (in linux) with either of the following commands

• service mysql restart
• /etc/rc.d/init.d/mysql restart
• mysqladmin -u root -p shutdown
  and then mysqld_safe &

In windows, you’d be having an easier interface 😀