Migrating 1.2 TB Database From Aurora to MySQL

We have one database server that is running on an old version of Aurora based on MySQL 5.6. AWS is deprecating that version soon and it needs to be upgraded, so I have been working on replacing it. Upgrading the existing 5.6 server to 5.7, then to 8.0 isn’t an option due to an impossibly huge InnoDB transaction history list that will never fix itself. Plus, I want to improve a couple of other things along the way.

I made several attempts and migrating from Aurora 5.6 to Aurora 8.0, but during that process, I grew tired of Aurora quirks and costs. Here are some of my raw notes on what was an embarrassingly long migration of a database server from Aurora to MySQL. Going from MySQL to Aurora took just a couple of clicks. But converting from Aurora back to MySQL took months and a lot of headaches.

TLDR: Along the way, I tried Using Amazon’s Database Migration Service, but eventually gave up for a good old closely monitored mysqldump and custom scripts.

I had a few goals/requirements:

  • Get rid of or soon-to-be-deprecated Aurora instance based on MySQL 5.6
  • Stop Paying for Storage IOPS (often over $100/day)
  • Convert tables from utf8mb3 to utf8mb4
  • Minimal downtime or customer disruption. Some disruption during low-usage times is okay.

A new MySQL 8 instance with a GP3 storage volume and the recently announced RDS Optimized Writes means that MySQL should be able to handle the workload with no problem, and gets this server back into the MySQL realm, where all of our other servers are, and with which we are more comfortable.

Attempts at using AWS Database Migration Service (DMS)

This service looked promising, but has a learning curve. I eventually gave up using it because of repeated problems that would have taken too much effort to try and resolve.

First attempts:
On the surface, it seems like you configure a source, configure a destination, and then tell DMS to sync one to the other and keep them in sync. It does this in two Phases: the Full Dump, and the Change Data Capture (CDC). I learned the hard way that the Full Dump doesn’t include any indexes on the tables! This is done to make it as fast as possible. The second, CDC Phase, just executes statements from the binary log, so without indexes on a 400+G table, they take forever and this will never work.

I also concluded that one of our 300+GB tables can actually be done in a separate process, after the rest of the data is loaded. It contains historic information that will make some things in the application look incomplete until it is loaded, but the application will work with it empty.

Second attempts:
Used DMS for the full dump, the configured it to stop after the full dump, before starting the CDC Process. While it is stopped, I added the database indexes and foreign keys. I tried this several times with varying degrees of success and trying to minimize the amount of time that it took to add the indexes. Some tables were done instantly, some took a couple hours, and some were 12+ hours. At one point I had figured it would take about 62 hours to add the indexes. I think I got that down to 39 hours by increasing the IOPS, running some ALTER TABLES in parallel, etc.

After indexes were added, I started the second phase of DMS – the Change Data Capture is supposed to pick up in time where the Full Dump was taken, and then apply all of the changes from the Binary Logs to the new server. That process didn’t go smoothly. Again, the first attempts looked promising, but then the binary logs on the server were deleted, so it couldn’t continue. I increased the number of days that binary logs were kept, and made more attempts, but they had problems with foreign key and unique constraints on tables.

The biggest problem with these attempts was that it took about 24 hours for the data migration, and about 48 hours to add indexes. So each attempt was several days effort.

Third and last attempts at using DMS:
After getting pretty familiar DMS, I ended up creating the schema via `mysqldump –no-data` then manually editing the file to exclude indexes on some of the biggest tables that would cause the import to go slow. I excluded the one large, historic table. My overall process looked like this:

  • code>mysqldump –defaults-group-suffix=dumpschema –no-data thedatabase |sed “s/utf8 /utf8mb4 /” | sed “s/utf8_/utf8mb4_/” > /tmp/schema-limited-indexes.sql
  • Edit /tmp/schema-limited-indexes.sql and remove foreign keys and indexes on large tables
  • cat /tmp/schema-limited-indexes.sql | mysql –defaults-group-suffix=newserver thedatabase
  • On the new server, run ALTER TABLE the_historic_table ENGINE=blackhole;
  • Start DMS process, make sure to have it stop between Full Load and CDC.
  • Wait ~24+ hours for Full load to complete
  • Add Indexes back that were removed from the schema. I had a list of ALTER TABLE statements to run, with an estimate time that each should take. That was estimated at 39 hours
  • Start second Phase (CDC) of the DMS Task
  • Wait for CDC to complete (time estimate unknown. The faster the above steps worked, the less it had to replay)

Unfortunately, a couple of attempts at this had the CDC phase still fail with Foreign key constraints. I tried several times and don’t know why this happened. Finding the offending rows took many hours since the queries didn’t have indexes and had to do full table scans. In some cases, there were just a few, to a few-dozen rows that existed in one table without the corresponding row in the foreign table. Its as if the binary log position taken when the snapshot was started was off by a few seconds and the dumps of different tables were started at slightly different positions.

After several attempts (taking a couple weeks), I finally gave up on the DMS approach.

Using MySQL Dump

Using mysqldump to move data from one database server to another is a process I have done thousands of times and written many scripts around. It is pretty well understood and predictable. I did a few trial runs to put together this process:

Temporarily Stop all processes on the master server

  • Stop all background processes that write to the server
  • Change the password so that no processes can write to the master
  • Execute SHOW BINARY LOGS on master and note the last binary log file and position. Do this a few times to make sure that it does not change. (Note that this would be easier if RDS allowed FLUSH TABLES WITH READ LOCK, but since it doesn’t, this process should work.

Dump the schema to the new server

This has the sed commands in the middle to convert the old “utf8” colations to the desired “utf8mb4” versions. When dumping 1TB+ of data, I found it helped performance a bit to do the schema changes with the sed commands first. That way the bulk of the data doesn’t have to go through these two commands.

  • mysqldump --defaults-group-suffix=dumpschema --no-data thedatabase |sed "s/utf8 /utf8mb4 /" | sed "s/utf8_/utf8mb4_/" | mysql thedatabase
  • .my.cnf contains this section with the relevant parameters for the dump
    [clientdumpschema]
    host=thehostname.cluster-czizrrfoedlm.us-east-1.rds.amazonaws.com
    port=3306
    user=dumper
    password=thepassword
    ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA
    quick
    compress
    set-gtid-purged=OFF
    max_allowed_packet=1024M
    single-transaction=TRUE
    column_statistics=0
    net_buffer_length=256k
    

Move the data

To move the data, I ran this command. Note that it starts with time so that I could see how long it takes. Also, it includes

time mysqldump --defaults-group-suffix=dumpdata --no-create-info thedatabase | pv |mysql thedatabase

My .my.cnf contains this section for the import

host=thehostname.cluster-czizrrfoedlm.us-east-1.rds.amazonaws.com
port=3306
user=dumper
password=thepassword
ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA
quick
ignore-table=thedatabase.the_big_table
compress
set-gtid-purged=OFF
max_allowed_packet=1024M
single-transaction=TRUE
column_statistics=0
net_buffer_length=256k

Note that the above command includes the linux pv in between which is a nice way to monitor the progress. It displays a simple line to stderr that allows you to see the total transfer size, elapsed time, and current speed.

266.5GiB 57:16:47 [ 100KiB/s] [             <=>         ]

I experimented with several values for the NET_BUFFER_LENGTH parameter by dumping the same multi-GB table over and over with different values for NET_BUFFER_LENGTH. The size of this value determines how many values are included in the INSERT INTO statement generated by mysqldump. I was hoping that a larger value would improve performance, but I found that larger values slowed down. I found the best value was to use 256k.

NET_BUFFER_LENGTH value Elapsed Time
64k 13m 44s
256k 8m 27s
256k 7m 20s
1M 10m 23s
16M 11m 32s

After Migration is Started

After the mysqldump has been started, I re-enabled traffic back to the master server by setting the password back to the original. I kept all background jobs disabled to minimize the amount of data that had to be copied over afterwards.

Final attempt to use DMS

After the mysqldump was finished, I attempted to use the DMS Change Data Capture process to copy over the data that had changed on the master. You can start a Database Migration Task that begins at a specific point in the Master Log position. Maybe. I tried, it, but it failed pretty quickly with a duplicate key constraint. I gave up on DMS and figured I would just move over any data needed manually via custom scripts.

Other findings

In attempting to maximimize the speed of the transfer, I attempted to increase the IOPS on the GP3 volume from its base level of 12,000 to 32,000. Initially that helped, but for some reason I still don’t understand, the throughput was then limited very strictly to 6,000 IOPS. As seen in the chart below, it bursted above that for some short parts, but it was pretty strictly constrained for most of the time. I think this has to do with how RDS uses multiple volumes to store the data. I suspect that each volume has 6,000 capacity, and all of my data was going to a single volume.

RDS IOPS
RDS IOPS Maxed at 6,000

That concludes the notes that I wanted to take. Hopefully somebody else finds these learnings or settings useful. If this has been helpful, or if you have any comments on some of the problems that I experienced, please let me know in the comments below.

Ubuntu 20.04 Cloud-Init Example to Create a User That Can Use sudo

Use the steps below and example config to create a cloud-init file that creates a user, sets their password, and enables SSH access. The Cloud Config documentation has some examples, but they don’t actually work for being able to ssh into a server and run commands via sudo

First, create a password hash with mkpasswd command:

$ mkpasswd -m sha-512
Password:  
$6$nq4v1BtHB8bg$Oc2TouXN1KZu7F406ELRUATiwXwyhC4YhkeSRD2z/I.a8tTnOokDeXt3K4mY8tHgW6n0l/S8EU0O7wIzo.7iw1

Make note of the output string. You need to enter it exactly in the passwd line of your cloud-init config.

This is the minimal configuration to create a user using cloud-init:

users:
  - name: brandon
    groups: [ sudo ]
    shell: /bin/bash
    lock_passwd: false
    passwd: "$6$nq4v1BtHB8bg$Oc2TouXN1KZu7F406ELRUATiwXwyhC4YhkeSRD2z/I.a8tTnOokDeXt3K4mY8tHgW6n0l/S8EU0O7wIzo.7iw1"
    ssh-authorized-keys:
    - ssh-ed25519 AAAAC3NzaC1lZDI1zzzBBBGGGg3BZFFzTexMPpOdq34a6OlzycjkPhsh4Qg2tSWZyXZ my-key-name

A few things that are noteworthy:

  • The string in the passwd field is enclosed in quotes
  • lock_passwd: false is required to use sudo. Otherwise, the system user account created will have a disabled password and will be unable to use sudo. You’ll just continually be asked for a password, even if you enter it correctly.
  • I prefer the method of adding the user to the sudo group to grant access to sudo. There are other ways to make that work as well, but I feel like this is the cleanest.
  • Adding any users, will prevent the default ubuntu user from being created.
  • Solving ECS Stuck in Pending and Frozen / Stalled ECS Hosts Problems

    We’ve had a strange, hard to track-down problem for months now. It has felt like a bug with Amazon ECS, but everything seems to have been working correctly.

    The main way that we’ve observed this problem is that ECS would say that it was launching tasks, but they would stay in a “PENDING” state forever. Conversely, when tasks needed to be killed, the desired state would change to Stopped, but the ECS Console would indicate that they were still running. We discovered quickly, that some of our ECS Host Servers would become completely unresponsive. Sometimes with 100% CPU usage, sometimes with near zero CPU Usage. Terminating the instance, and having the Auto-Scaling group recreate it would generally solve the problem, but its never good to have things frozen without understanding why.

    Often, the host servers would be completely unresponsive. We were usually unable to SSH into the server to investigate. When able to access them, looked through logs and found it full of failures about being unable to talk to external resources. After diving pretty deep, we figured out that the route table was missing a default gateway. It’s hard to talk to anything when you can only use a local network.

    This is an example of a missing default gateway.

    [ec2-user@ip-172-31-45-74 ~]$ route
    Kernel IP routing table
    Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
    172.17.0.0      0.0.0.0         255.255.0.0     U     0      0        0 docker0
    172.31.32.0     0.0.0.0         255.255.240.0   U     0      0        0 eth0
    

    On a functioning instance, it should look like this. Notice the destination of 0.0.0.0 with the IP Address to the Default Gateway:

    [ec2-user@ip-172-31-39-228 ~]$ route -n
    Kernel IP routing table
    Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
    0.0.0.0         172.31.32.1     0.0.0.0         UG    0      0        0 eth0
    169.254.169.254 0.0.0.0         255.255.255.255 UH    0      0        0 eth0
    172.17.0.0      0.0.0.0         255.255.0.0     U     0      0        0 docker0
    172.31.32.0     0.0.0.0         255.255.240.0   U     0      0        0 eth0
    

    It was puzzling how the machine would work for a while, and then its default gateway would disappear.

    I’m still not certain how exactly that is happening. However, the system log indicates that there is a period of extremely high load
    and it gets frozen for minutes (maybe hours) at a time.

    Some of these log entries are indicitive of major delays:

    Jan 20 13:26:44 ip-172-31-123-45.ec2.internal crond[21992]: (root) INFO (Job execution of per-minute job scheduled for 13:25 delayed into subsequent minute 13:26. Skipping job run.)
    
    Jan 17 21:20:31 ip-172-31-45-166.ec2.internal chronyd[2696]: Forward time jump detected!
    

    Notice how these logs are out of order too:

    Jan 20 13:39:22 ip-172-31-123-45.ec2.internal kernel: R13: 00007faf9dc777a8 R14: 00000000000031f9 R15: 00007faf9dc7d510
    Jan 20 13:28:30 ip-172-31-123-45.ec2.internal dockerd[4660]: http: superfluous response.WriteHeader call from github.com/docker/docker/api/server/httputils.MakeErrorHandler.func1 (httputils.go:107)
    Jan 20 13:36:03 ip-172-31-123-45.ec2.internal dhclient[3275]: XMT: Solicit on eth0, interval 129760ms.
    Jan 20 13:28:30 ip-172-31-123-45.ec2.internal dockerd[4660]: http: superfluous response.WriteHeader call from github.com/docker/docker/api/server/httputils.MakeErrorHandler.func1 (httputils.go:107)
    

    Finally, this may be the thing that ultimately disables the networking. It looks like `oom-killer` killed the `dhclient-script`, which maybe left the network in an very bad state:

    Jan 20 15:28:36 ip-172-31-45-74.ec2.internal kernel: dhclient-script invoked oom-killer: gfp_mask=0x14201ca(GFP_HIGHUSER_MOVABLE|__GFP_COLD), nodemask=(null),  order=0, oom_score_adj=0
    Jan 20 15:28:36 ip-172-31-45-74.ec2.internal kernel: dhclient-script cpuset=/ mems_allowed=0
    

    You can simply run

    sudo dhclient eth0

    to have it grab the default gateway from DHCP again. But its best to put other memory limits in place to prevent it from running out of resources to begin with.

    Find MySQL indexes that can be removed to free up disk space and improve performance

    I wrote this handy query to find indexes that can be deleted because they have not been in use. It
    queries the performance_schema database for usage on the indexes, and joins on INFORMATION_SCHEMA.TABLES
    to see the index size.

    Indexes that have zero reads and writes are obvious candidates for removal. They take extra write overhead to keep them
    updated, and you can improve performance on a busy server by removing them. You can also free up some disk space
    without them. The size column below helps to understand where you have the most opportunity for saving on disk
    usage.

    mysql>
    SELECT. OBJECT_NAME,
            index_name,
            SUM(INDEX_LENGTH) AS size,
            SUM(count_star) AS count_star,
            SUM(count_read) AS count_read,
            SUM(count_write) AS count_write
    FROM  table_io_waits_summary_by_index_usage
    JOIN information_schema.TABLES
        ON table_io_waits_summary_by_index_usage.OBJECT_SCHEMA = TABLES.TABLE_SCHEMA
       AND table_io_waits_summary_by_index_usage.OBJECT_NAME = TABLES.TABLE_NAME
    WHERE OBJECT_SCHEMA LIKE 'mydatabase%'
    GROUP BY object_name, index_name
    ORDER BY count_star ASC, size DESC
    LIMIT 20;
    
    +------------------------------+---------------------------------+-------------+------------+------------+-------------+
    | OBJECT_NAME                  | index_name                      | size        | count_star | count_read | count_write |
    +------------------------------+---------------------------------+-------------+------------+------------+-------------+
    | transactions                 | order_id                        | 42406641664 |          0 |          0 |           0 |
    | transactions                 | msku-timestamp                  | 42406641664 |          0 |          0 |           0 |
    | transactions                 | fkTransactionsBaseEvent         | 42406641664 |          0 |          0 |           0 |
    | baseEvent                    | PRIMARY                         | 33601945600 |          0 |          0 |           0 |
    | baseEvent                    | eventTypeId                     | 33601945600 |          0 |          0 |           0 |
    | orders                       | modified                        | 20579876864 |          0 |          0 |           0 |
    | orders                       | buyerId-timestamp               | 20579876864 |          0 |          0 |           0 |
    | productReports               | productAd-date-venue            |  8135458816 |          0 |          0 |           0 |
    | shipmentEvent                | id                              |  7831928832 |          0 |          0 |           0 |
    | shipmentEvent                | eventTypeId                     |  7831928832 |          0 |          0 |           0 |
    | historyEvents                | timestamp_venue_entity          |  4567531520 |          0 |          0 |           0 |
    | targetReports                | venueId-date-targetId           |  3069771776 |          0 |          0 |           0 |
    | productAds                   | venue-productAd                 |  1530888192 |          0 |          0 |           0 |
    | keywords                     | venue-keyword                   |   895598592 |          0 |          0 |           0 |
    | targetingExpressions         | venue-target                    |   215269376 |          0 |          0 |           0 |
    | targetingExpressions         | rType-rValue                    |   215269376 |          0 |          0 |           0 |
    | serviceFeeEvent              | PRIMARY                         |    48234496 |          0 |          0 |           0 |
    | serviceFeeEvent              | id                              |    48234496 |          0 |          0 |           0 |
    | serviceFeeEvent              | eventTypeId                     |    48234496 |          0 |          0 |           0 |
    | adGroups                     | venue-adGroup                   |    42336256 |          0 |          0 |           0 |
    

    PHP Sessions with Redis Cluster (using AWS Elasticache)

    I’ve recently been moving some of our project from a single Redis server (or server with a replica) to the more modern Redis Cluster configuration. However, when trying to set up PHP sessions to use the cluster, I found there wasn’t a lot of documentation or examples. This serves as a walk-through for setting up PHP sessions to use a redis Cluster, specifically with Elasticache on AWS.

    First, create your Elasticache Redis Instance like so. Note the “Cluster Mode Enabled” is what causes redis to operate in Cluster mode.

    AWS Elasticache Redis Creation

    Once there servers are launched, make note of the Configuration Endpoint which should look something like: my-redis-server.dltwen.clustercfg.usw1.cache.amazonaws.com:6379

    Finally, use these settings in your php.ini file. The exact location of this file will depend on your OS, but on modern Ubuntu instances, You can place it in /etc/php/7.0/apache2/conf.d/30-redis-sessions.ini

    Note the special syntax for the save_path where is has seed[]=. You only need to put the main cluster configuration endpoint here. Not all of the individual instances as other examples online appear to use.


    session.save_handler = rediscluster
    session.save_path = "seed[]=my-redis-server.dltwen.clustercfg.usw1.cache.amazonaws.com:6379"
    session.gc_maxlifetime = 1296000

    That’s it. Restart your webserver and sessions should now get saved to your Redis cluster.

    IIn the even that something goes wrong, you might see something like this in your web server log files:


    PHP Warning: Unknown: Failed to write session data (redis). Please verify that the current setting of session.save_path is correct (tcp://my-redis-server.dltwen.clustercfg.use1.cache.amazonaws.com:6379) in Unknown on line 0

    Docker Syslog Container for Sending Logs to CloudWatch

    AWS’s CloudWatch Logs was first available about a year ago, and to my estimation has gone largely unnoticed. The initial iteration was pretty rough, but some recent changes have made it more useful, including the ability to search logs, and generate events for monitoring in CloudWatch from log content.

    Unfortunately, the Cloudwatch Logs agent just watches log files on disk and doesn’t act as a syslog server. An AWS blog post explained how to get the the Cloudwatch Logs Agent running inside a container and monitoring the log output from rsyslogd, but the instructions used Amazon’s ECS service, which still doesn’t quite offer the flexibility that CoreOS or Deis offer IMHO. ECS does some magic behind the scenes in passing credentials around that you have to do yourself when using CoreOS.

    I’ve just provided a GitHub repository with the tools to make this work pretty easily, as well as a Docker Image with some reasonable defaults.

    When trying to pull all of this together to work, I discovered a problem due to a bug in the overlayfs that is in current Deis Releases which causes the AWS Logs agent not to notice changes in the syslog files. A workaround is available that reformats the host OS back to btrfs to solve that particular problem

    Note when running on Deis 561+ to revert to btrfs

    Deis add Key from an ssh-agent

    Evidently it is not possible to add an SSH key directly from an SSH agent. Instead, you can grep the public key from your ~/.ssh/authorized_keys file then, have deis use that key. Or if you only have one line in ~/.ssh/authorized_keys, you can just tell deis to use that file directly with the command

    deis keys:add ~/.ssh/authorized_keys
    

    Unattended install of Cloudwatch Logs Agent

    So far, I’m pretty impressed with cloudwatch logs. The interface for it isn’t as fancy, and search capability isn’t as deep as other tools like PaperTrail or Loggly, but the cost is significantly less, and I like the fact that you can store different log groups for different lengths of time.

    I’m trying to get the cloudwatch logs agent to install as part of an automated script, and couldn’t find any easy instructions to do that, so here is how I got it working with a shell script on an Ubuntu 14.04 host

    echo Creating cloudwatch config file in /root/awslogs.conf
    cat <<EOF >/root/awslogs.conf
    [general]
    state_file = /var/awslogs/state/agent-state
    ## Your config file would have a lot more with the logs that you want to monitor and send to Cloudwatch
    EOF
    
    echo Creating aws credentials in /root/.aws/credentials
    cat <<EOF > /root/.aws/credentials
    [default]
    aws_access_key_id = YOUR_AWS_ACCESS_KEY_HERE
    aws_secret_access_key = YOUR_AWS_SECRET_KEY_HERE
    EOF
    
    echo Downloading cloudwatch logs setup agent
    cd /root
    wget https://s3.amazonaws.com/aws-cloudwatch/downloads/latest/awslogs-agent-setup.py
    echo running non-interactive cloudwatch-logs setup script
    python ./awslogs-agent-setup.py --region us-west-2 --non-interactive --configfile=/root/awslogs.conf
    

    DKIM / SPF / SpamAssassin test moved to dkimvalidator.com

    For over 7 years, I’ve hosted an email validation tool on this site. I developed the tool back when I was doing a lot with email, and when DKIM and SPF was still pretty tricky to get working. Over the years it has become the single most popular page on the site, and Google likes it pretty well for certain DKIM and SPF keywords. (And strangely “brandon checketts dkim” pops up on Google search suggest you you try to google my name.

    In any case, I’ve moved that functionality over to its own site now at http://dkimvalidator.com/ so that it has its own place to call home. It also got a (albeit weak) visual makeover, and all of the underlying libraries have been updated to the latest versions so that they are once again accurate and up-to-date.

    Troubleshooting /etc/cron.d/ on Ubuntu

    On Debian-based systems, files in /etc/cron.d:
    – must be owned by root
    – must not be group or world writable
    – may be symlinked, but the destination must follow the ownership and file permissions above
    – Filenames must contain ONLY letters, numbers, underscores and hyphens. (not periods)
    – must contain a username in the 6th column

    From the man page:

    Files in this directory have to be owned by root, do not need to be executable (they are configuration files, just like /etc/crontab) and must conform to the same naming convention as used by run-parts(8): they must consist solely of upper- and lower-case letters, digits, underscores, and hyphens. This means that they cannot contain any dots.

    The man page also provides this explanation to this strange rule:

    For example, any file containing dots will be ignored. This is done to prevent cron from running any of the files that are left by the Debian package management
    system when handling files in /etc/cron.d/ as configuration files (i.e. files ending in .dpkg-dist, .dpkg-orig, and .dpkg-new).