Slave skip counter terminates the entire transaction

If you have been an admin for MySQL with replication, at some point you are going to need to skip statements on a replica that passed on the master but are failing on the slave. There are too many reason this can happen and depending on the situation it might be safe to skip or might lead to data inconsistencies on the replica.

When you skip the error, it skips the entire transaction. Even if the first part of the transaction would have been successful because there are multiple statements in the transaction and one of them fails, then all of them are skipped on the slave.

This has been accurately described by Jervin Real in this blog post:

https://www.percona.com/blog/2013/07/23/another-reason-why-sql_slave_skip_counter-is-bad-in-mysql/

Something to note is that skipping errors on Aurora is different than on normal MySQL server:

On a normal MySQL slave you would run this;

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

show slave status;

If you have thousands of errors and want to skip them all then you can put a large number in the SQL_SLAVE_SKIP_COUNTER. If you have that many error then you probably have larger issues and might want to consider rebuilding the slave to prevent data inconsistency problems.

However on Aurora you can only skip one error at a time:

CALL mysql.rds_skip_repl_error;

show slave status;

 

You don't have to stop and start replication but do need to use the stored procedure provided by the Aurora folks.

I have a client that uses Aurora as  a disaster recovery "site". The client has thousands of databases that are being replicated from their local data center into Aurora. If their local data center were to go down, they have all their data in Aurora. However, periodically some statement breaks replication and I have to skip replication if it is safe to do so.

One of the semi frustrating things I have to deal with is the widespread use of the BLACKHOLE storage engine in Aurora by my client. When replicating from the local data center to Aurora, the client does not always want to replicate every single database into Aurora. There might be hundreds of databases on a single server and the client only want to replicate one of them into Aurora. What I do is export and import all the tables for all the database into Aurora. Then I change all the tables for the databases I don't want to replicate to BLACKHOLE. However, eventually people start creating new tables, modifying existing tables on the master server which is located in the local data center and those DDL changes are replicated to the database for which I have set all the tables to BLACKHOLE. Eventually this causes replication to break. Because I don't care about the data in the BLACKHOLE tables I can skip all the errors but sometimes I have to skip hundreds of errors.

How to break replication to Aurora instance slaves

I have a client which is migrating database to the AWS cloud. We are setting up Aurora slaves from the physical data to replicate all data. Today I was updating my permissions for my own user and ran a query like this on all the physical machines:

GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY PASSWORD '*PASSWORDHASH' WITH GRANT OPTION;

That query ran fine on all the physical machine in the data center but it broke replication on every single Aurora instance that was replicating. This is because there are many permissions which are not allowed in Aurora because only the "SUPER" user can have them and that user is controlled by Amazon. When granting permissions or changing password hash, we have to be careful to only run grant statements which will not break replication on Aurora.

Here is an interesting list of other things you can learn from doing migrations to amazon rds:

https://www.percona.com/blog/2014/07/28/what-i-learned-while-migrating-a-customer-mysql-installation-to-amazon-rds/

if else / case statements in MySQL queries

Being able to change what is displayed in the query or to display the results of a different column based on criteria in a WHERE clause is super helpful. Because this is so useful and something I use so frequently I writing a blog post on it.

Here is am example query I wrote. I want to display the Aurora "EndPoint" alias when I provide an IP Address. I've created two tables, one which has RDS/Aurora Cluster details, and one with RDS/Aurora Instance details. I've pulled this information from the AWS API and stored it locally to easily query it in a relational database. If the IP Address turns out to be the writer end point then I want the c.EndPoint column value to be displayed. If the IP Address is for reader end point then I want the c.ReaderEndpoint to be displayed. If there is only one instance in the cluster then this query will always return c.EndPoint (writer end point).


    SELECT
    IF(i.IsClusterWriter = 1,  c.EndPoint,  c.ReaderEndpoint ) AS alias
    FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx';

With a case statement, I could also write it like this:

    SELECT
    (case when (i.IsClusterWriter = 1)
 THEN
      c.EndPoint
 ELSE
      c.ReaderEndpoint
 END)
 as alias
     FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx'

Other examples:

https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query

Clearing the buffer cache before starting MySQL - Flush out the file system cache

Here was as good question I found on stackexchange:

How do you empty the buffers and cache on a Linux system?

http://unix.stackexchange.com/questions/87908/how-do-you-empty-the-buffers-and-cache-on-a-linux-system

If I don't flush out the file system cache when restarting MySQL on prod severs, I frequently get timeouts when restarting. The servers I typically work with have about 250GB of RAM, 40 CPU. Something like this will happen where I have to kill the start command or it just doesn't start.

# service mysql status
MySQL (Percona Server) running (182654)                    [  OK  ]
# service mysql restart
Shutting down MySQL (Percona Server).......................[  OK  ]......................................
Starting MySQL (Percona Server).......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................^C

However, if I run this:

sync && echo 3 > /proc/sys/vm/drop_caches

MySQL starts up quick.

Starting MySQL (Percona Server)............................[  OK  ]....................

Error installing sys schema because performance schema structure is wrong

When attempting to install the sys schema which I downloaded from here, I sometimes I get this error:

mysql -u root -p -h MyServerName.com < ./sys_56.sql

ERROR 1033 (HY000) at line 47 in file: './views/p_s/processlist.sql': Incorrect information in file: './performance_schema/threads.frm'

This is related to a bug after upgrading MySQL. The structure of the performance schema is wrong because it wasn't fixed at the time of upgrade.

The way I typically fix this is by dropping performance schema and re-installing it.

First ssh into the system.

Logon to MySQL:
DROP DATABASE performance_schema;
Exit MySQL and run:

mysql_upgrade -u root -p

mysql_upgrade will re-create the performance schema with the correct structure. 

Now install sys schema. 

You will need to restart MySQL service to actually get the performance schema and sys schema to start working. 

Migrating users to RDS or Aurora

I've mentioned this before what a pain it can be migrating databases that have DEFINERS in the triggers/stored procs/events to AWS. I'm doing migrations to Aurora now and the user permissions are also very annoying.

There are some privileges which are not allowed at all on Aurora and RDS:
Super,  Shutdown, File

The above three privileges will give this error:
Access denied for user '<user name>'@'%' (using password: YES)

If you are scripting out user migrations from MySQL to RDS and have users with "ALL PRIVILEGES ON *.*" then you need to remove that from the grant statements and replace it with allowed permissions.

These are allowed when granting global privileges on *.* with RDS:

Select, Insert, Update, Delete, Create, Drop, Reload, Process, References,  Index, Alter, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, Execute, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, Event, Trigger, WITH GRANT OPTION;

For example:

GRANT Select, Insert, Update, Delete, Create, Drop, Reload, Process, References,  Index, Alter, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, Execute, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, Event, Trigger  ON *.* TO 'test_user'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;

Note: Using "WITH GRANT OPTION" should only be give to administrators.

These are allowed when granting to <database>.* on RDS:

Select, Insert, Update, Delete, Create, Drop, References,  Index, Alter, CREATE TEMPORARY TABLES, LOCK TABLES, Execute,  CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, Event, Trigger, WITH GRANT OPTION

For example:

GRANT Select, Insert, Update, Delete, Create, Drop, References,  Index, Alter, CREATE TEMPORARY TABLES, LOCK TABLES, Execute,  CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, Event, Trigger  ON test.* TO 'test_user'@'%' IDENTIFIED BY '12345678' WITH GRANT OPTION;

Note: Using "WITH GRANT OPTION" should only be give to administrators.

All of the following  GLOBAL PRIVILEGES will fail regardless of using RDS or normal MySQL if you try to grant them to a specific database (they can only be granted to *.*):

Process, SHOW DATABASES, CREATE USER, REPLICATION SLAVE, REPLICATION CLIENT, Reload

For example:

GRANT Process, SHOW DATABASES, CREATE USER, REPLICATION SLAVE, REPLICATION CLIENT, Reload ON test.* TO 'test_user'@'%' IDENTIFIED BY '12345678';

The error you will get is:

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

There are also more permissions which you can grant to a user for AWS that I have not referenced here such as:

GRANT SELECT INTO S3 ON *.* TO 'test_user'@'%';

I am not covering those AWS specific permissions because those won't appear in your non RDS MySQL Servers because they are specific to RDS. But you would get an error if you try running that on a normal MySQL server.

Querying processlist and grouping by IPAddress

Wanted to group all the sleeping connections on a MySQL instance by IP Address. Here is the simple query:

SELECT user, LEFT( host, ( LOCATE( ':', host ) - 1 ) ) as host, count(*) as count
FROM `information_schema`.processlist where Command = 'Sleep'
GROUP BY user, LEFT( host, ( LOCATE( ':', host ) - 1 ) )
ORDER BY count DESC

Explanation of the functions used:
MySQL LOCATE() returns the position of the first occurrence of a string within a string.

MySQL LEFT() returns a specified number of characters from the left of the string. Both the number and the string are supplied as arguments of the function.


Found this query from Ben Nadel:

https://www.bennadel.com/blog/3054-grouping-the-mysql-processlist-by-ip-address-to-view-connection-counts.htm

SELECT
 tmp.ipAddress,

 -- Calculate how many connections are being held by this IP address.
 COUNT( * ) AS ipAddressCount,

 -- For each connection, the TIME column represent how many SECONDS it has been in
 -- its current state. Running some aggregates will give us a fuzzy picture of what
 -- the connections from this IP address is doing.
 FLOOR( AVG( tmp.time ) ) AS timeAVG,
 MAX( tmp.time ) AS timeMAX
FROM
 -- Let's create an intermediary table that includes an additional column representing
 -- the client IP address without the port.
 (

  SELECT
   -- We don't actually need all of these columns for the demo. But, I'm
   -- including them here to demonstrate what fields COULD be used in the
   -- processlist system.
   pl.id,
   pl.user,
   pl.host,
   pl.db,
   pl.command,
   pl.time,
   pl.state,
   pl.info,

   -- The host column is in the format of "IP:PORT". We want to strip off
   -- the port number so that we can group the results by the IP alone.
   LEFT( pl.host, ( LOCATE( ':', pl.host ) - 1 ) ) AS ipAddress
  FROM
   INFORMATION_SCHEMA.PROCESSLIST pl

 ) AS tmp
GROUP BY
 tmp.ipAddress
ORDER BY
 ipAddressCount DESC