MySQL

How to Synchronize WordPress Live and Development Databases

SitePoint runs through a few options that one can use to synchronize WordPress live and development databases. I’ve linked to some of these options before, but it’s nice to have them all conveniently together. The solutions discussed include WordPress-specific tools:

as well as generic tools, such mysqldump, mysqlpump, rsync, and git.

Overall, it’s a pretty complete list of tools. The one I’d like to add though is WP CLI, which allows a great deal of automation when it comes to WordPress, including things like database imports and exports, post and option management, and more.

MySQL 8 is coming

OpenSource.com covers the upcoming release of the MySQL 8.

What happened to 6 & 7?

Years ago, before the Sun Microsystems purchase of MySQL AB, there was a version of MySQL with the number 6. Sadly, it was a bit ambitious and the change of ownership left it to wither. The MySQL Cluster product has been using the 7 series for years. With the new changes for MySQL 8, developers feel they have modified it enough to bump the big number.

The new version brings a whole lot of changes to filesystem organization, indexes, faster ALTER TABLE, and more.

GitHub to MySQL

GitHub to MySQL is a handy little app in PHP that pulls labels, milestones and issues from GitHub into your local MySQL database. This is useful for analysis and backup purposes.

There are a few example queries provided that show issues vs. pull requests, average number of days to merge a pull request over the past weeks, average number of pull requests open every day, and total number of issues.

I think this tool can be easily extended to pull other information from GitHub, such as release notes, projects, web hooks. Also, if you are using multiple version control services, such as BitBucket and GitLab, extending this tool can help with merging data from multiple sources and cross-referencing it with the company internal tools (bug trackers, support ticketing systems, CRM, etc).

This is not something I’ll be doing now, but I’m sure the future is not too far away.

Amazon RDS and Amazon Virtual Private Cloud (VPC)

Yesterday I helped a friend to figure out why he couldn’t connect to his Amazon RDS database inside the Amazon VPC (Virtual Private Cloud). It was the second time someone asked me to help with the Amazon Web Services (AWS), and it was the first time I was actually helpful. Yey!

While I do use quite a few of the Amazon Web Services, I don’t have any experience with the Amazon RDS yet, as I’m managing my own MySQL instances. It was interesting to get my toes wet in the troubleshooting.

Here are a few things I’ve learned in the process.

Lesson #1: Amazon supports two different ways of accessing the RDS service. Make sure you know which one you are using and act accordingly.

gs-vpc-network

If you run an Amazon RDS instance in the VPC, you’ll have to setup your networking and security access properly. This page – Connecting to a DB Instance Running the MySQL Database Engine – will only be useful once everything else is taken care of. It’s not your first and only manual to visit.

Lesson #2 (sort of obvious): Make sure that both your Network ACL and Security Groups allow all the necessary traffic in and out. Double-check the IP addresses in the rules. Make sure you are not using a proxy server, when looking up your external IP address on WhatIsMyIP.com or similar.

Lesson #3: Do not use ICMP traffic (ping and such) as a troubleshooting tool. It looks like Amazon RDS won’t be ping-able even if you allow it in your firewalls. Try with “telnet your-rds-end-point-server your-rds-end-point-port” (example: “telnet 1.2.3.4 3306” or with a real database client, like the command-line MySQL one.

Lesson #4: Make sure your routing is setup properly. Check that the subnet in which your RDS instance resides has the correct routing table attached to it, and that the routing table has the default gateway (0.0.0.0/0) route configured to either the Internet Gateway or to some sort of NAT. Chances are your subnet is only dealing with private IP range and has no way of sending traffic outside.

Lesson #5: When confused, disoriented, and stuck, assume it’s not Amazon’s fault. Keep calm and troubleshoot like any other remote connection issue. Double-check your assumptions.

There’s probably lesson 6 somewhere there, about contacting support or something along those lines. But in this particular case it didn’t get to that. Amazon AWS support is excellent though. I had to deal with those guys twice in the last two-something years, and they were awesome.

Taking the Pain Out of MySQL Schema Changes

“Taking the Pain Out of MySQL Schema Changes” covers the following approaches to deploying MySQL schema changes:

Schema Change in Downtime
Role Swap (cluster setup)
pt-online-schema-change

The last one is the usage of pt-online-schema-change tool developed by Percona guys, as part of their Percona Toolkit – an Open Source set of command-line tools for MySQL.