Replication : Be careful while executing DCL with replicate-ignore-db=mysql

June 13th, 2013 | Ramesh Sivaraman

Replication : Be careful while executing DCL with replicate-ignore-db=mysql.

Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql. But if we are executing DCL statements from different database schema (other than mysql schema) in master, the event will replicate to slave.

slave configuration

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-test-01
                  Master_User: repl
                  Master_Port: 3306
		--
		--
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
		--
		--
        Seconds_Behind_Master: 0
		--
		--
1 row in set (0.00 sec)

mysql>

eg :

1, Create one user on master from diff database schema.

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> grant usage on *.* to ram_test@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user='ram_test' and host='%';
+----------+------+
| user     | host |
+----------+------+
| ram_test | %    |
+----------+------+
1 row in set (0.00 sec)

mysql>

On slave also user creation DCL event will execute even after  giving replicate-ignore-db=mysql parameter. If you look at the binlog event of user creation you can see ‘use `test`’ statement attached with that event. Since mysql is taking default database schema for binary logging this event will execute in slave without any issue.

mysql> show binlog events in 'binary-log.000817' from  7275 limit 1;
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
| Log_name          | Pos  | Event_type | Server_id | End_log_pos | Info                                           |
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
| binary-log.000817 | 7275 | Query      |  1000	    |        7386 | use `test`; grant usage on *.* to ram_test@'%' |
+-------------------+------+------------+-----------+-------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

On slave


mysql>  select user,host from mysql.user where user='ram_test' and host='%';
+----------+------+
| user     | host |
+----------+------+
| ram_test | %    |
+----------+------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| mysql-test-02		  |
+-------------------------+
1 row in set (0.00 sec)

mysql>

2, There is a chance of replication error with this configuration.
In this example I am dropping one user on master from diff database schema.

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> drop user ram_test1@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+-------------------------+
| @@hostname              |
+-------------------------+
| mysql-test-01		  |
+-------------------------+
1 row in set (0.00 sec)

mysql>

Here you can see replication failed with ‘Operation DROP USER failed’. This is because user ‘ram_test1′@’%’ was not present in slave instance.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-test-01
                  Master_User: repl
                  Master_Port: 3306
			--
			--
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
			--
			--
                   Last_Errno: 1396
                   Last_Error: Error 'Operation DROP USER failed for 'ram_test1'@'%'' on query. Default database: 'test'. Query: 'drop user ram_test1@'%''
			--
			--

mysql>

Make sure you are in mysql schema while executing DCL with replicate-ignore-db=mysql configuration :-)

MySQL uptime report script

March 5th, 2012 | Ramesh Sivaraman

Perl script for mysql up-time report. Monitor your mysql servers up-time status from single server.

Prerequisite.
1, Apache server
2, Perl modules: DBI, CGI

Create one common user to connect mysql servers.

mysql > GRANT usage ON *.* TO 'mysqlmon'@'192.168.0.1' IDENTIFIED BY ‘mon123';

Script

#!/usr/bin/perl
use CGI qw(:standard);
use DBI;
my $user= "mysqlmon";
my $pass= 'mon123';
my @instanses = ("192.168.0.1","192.168.0.2","192.168.0.3");
my @host = ("MYSERVER1"," MYSERVER2"," MYSERVER3");
my $query = new CGI;
print $query->header ( );
print start_html(-title=>"MySQL Uptime Report");
print "<H1>MySQL Uptime Report</H1>";
print "<HR>";
print "<br>";
print "<table border=1>";
print  "<tr><th>Hostname</th><th>IP Address</th><th>Daemon</th><th>Uptime</th><th>Recorded Time</th></tr>";
my $n = 0;
while($instanses[$n]){
        $dbc = DBI->connect("DBI:mysql:host=$instanses[$n]",$user,$pass,{
         PrintError => 0   ### Don't report errors via warn(  )
        } );
        # if condition to handle any server connection problem
        if($DBI::errstr){
                print  "<tr><td>" . $host[$n] ."</td><td>" . $instanses[$n] ."</td><td>mysqld</td><td>".$DBI::errstr ."</td><td> ***** MySQL is down ***** </td></tr>";
                $n++;
                next;
        }
        my $sts = $dbc->prepare("show status like 'Uptime'");
        $sts->execute();
        # Uptime value
        while(($vname,$value) = $sts->fetchrow_array){
                $time = int($value/(24*60*60))." days ". ($value/(60*60))%24 . " hours " . ($value/60)%60 . " mins " . $value%60 . " secs";
                print  "<tr><td>" . $host[$n] ."</td><td>" . $instanses[$n] ."</td><td>mysqld</td><td>". $time ."</td><td>" . localtime(time) ."</td></tr>";
        }#end of while
        $n++
}
print "</table>";
print "<br>";
print "<HR>";
print end_html;

You need to save this script in cgi-bin directory. If you are using Red Hat / Fedora Linux default location for cgi-bin directory is /var/www/cgi-bin. Setup execute permission on the script

$ chmod +x mysqluptime.pl

Open your web browser and test the script.

for example type url  http://192.168.0.1/cgi-bin/mysqluptime.pl

output

MySQL Tips

March 2nd, 2012 | Ramesh Sivaraman

1, Getting locked query from processlist

SELECT TIME,USER,HOST,DB,COMMAND,SUBSTR(INFO,1,50) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE
STATE='LOCKED' ORDER BY 1 DESC;

2, Getting database schema size from information_schema

SELECT TABLE_SCHEMA, SUM((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) AS SIZE_MB FROM
INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA ORDER BY SIZE_MB DESC

3, Query for bulk drop table statement from database schema

SELECT CONCAT('DROP TABLE ',table_name,';') INTO OUTFILE '/tmp/drop_tables.sql' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';

4, Convert table from InnoDB to MyISAM

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' engine=MyISAM;') FROM information_schema.tables WHERE engine = 'InnoDB';

5, Storage Engine Selection Tips

i, Logging Purpose (Each click on internet, telephone calls etc) – MyISAM, Archive, PBXT etc. Insertion rate on table 1000 rows per second
ii, Read-only or read-mostly tables : MyISAM is suitable for this (eg : construct a catalog or listing of some sort (jobs, auctions, real estate, etc.)) . But wee need to take care of table crash issue.
iii, Order Processing : InnoDB (Transaction and Foreign key)
iv, Stock quotes : running a high-traffic web service that has a real-time quote feed and thousands of users, a query should never have to wait. Many clients could be trying to read and write to the table simultaneously, so rowlevel
locking or a design that minimizes updates is the way to go.

6, Query for collecting user privileges

select concat('show grants for ',user,'@','"',host,'"',';') from mysql.user;

7, mysql root password recovery in Linux flavors

Start MySQL in safe mode
    mysqld_safe --skip-grant-tables
Enter the console as root
    mysql -u root
Set the new password
    UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
Update the privileges
    FLUSH PRIVILEGES;