1248747 - Migrate database to RDS

Status: RESOLVED FIXED

(Release Engineering Graveyard :: Applications: Balrog (backend), defect)

Description

[Benson Wong [:mostlygeek]]

Determine the requirements and process for migrating the database to RDS. 

Things to look at: 

- size of database (what to keep/leave behind) 
- security requirements around the data
- migration process
- RDS sizing requirements (how much load the DB handles)
- MySQL version requirements

Comment 1

[[e-mail] (:bhearsum)]

If it helps, the db server sends lots of data to New Relic. I can pull the stats if you don't have access to it.

Comment 2

[[e-mail] (:bhearsum)]

One thing I realized last night is that we don't need to bother with a database import for dev - we can start fresh there and add what little data we need. If we still want to a dev db import as a way of making sure it'll work for production, that's fine with me, but it's not necessary from my standpoint.

Comment 3

[Benson Wong [:mostlygeek]]

It's mainly for the production data which I assume we need to move. 

Do you have any information on the MySQL version of the database? 
Does Balrog have any specific MySQL requirements?

Comment 4

[[e-mail] (:bhearsum)]

Sorry - missed this comment.

(In reply to Benson Wong [:mostlygeek] from comment #3)
> It's mainly for the production data which I assume we need to move. 
> 
> Do you have any information on the MySQL version of the database? 

I'm not sure which version we're running...probably should check with the Data team.

> Does Balrog have any specific MySQL requirements?

We don't use anything in the way of fancy features AFAIK. However, we do have migration scripts that we use to change the schema. I assume we'll be able to run those against RDS still? All they need is to be able to connect through a mysql:/// URI.

Comment 5

[Benson Wong [:mostlygeek]]

Migration scripts can still be run like normal. RDS is really just managed MySQL, for the most part nothing will change.

Comment 6

[Benson Wong [:mostlygeek]]

For the migration plan we will try to use Amazon's recommended process [1] for reduced downtime. In a nutshell this is: 

 - create an RDS instance, seed it w/ mysqldump
 - set up replication between RDS and the main DB 
 - run SHOW SLAVE STATUS, wait until Seconds_behind_master is 0

At that point all data will be kept in sync and we will be able repoint services to AWS and maintain a consistent database state. 

[1] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

Comment 7

[Nick Thomas [:nthomas] (UTC+12)]

https://aws.amazon.com/blogs/aws/aws-database-migration-service/ seems to be a newly-available wrapper around that process.

Comment 8

[Chris Kolosiwsky [:ckolos] (ckolos has left the building)]

RDS stack is up and running in stage.

Comment 9

[Sheeri Cabral [:sheeri]]

To enable SSL, I downloaded the certificate authority - rds-ca-2015-root.pem

copied it to /usr/local/etc on aus4-1 and aus4-2.db.phx1.mozilla.com.

added the following to /etc/my.cnf:
ssl-ca=/usr/local/etc/rds-ca-2015-root.pem

took aus4-1 out of the load balancer (it's the slave) and restarted MySQL, and all is good.

Comment 10

[Sheeri Cabral [:sheeri]]

[[e-mail]1 ~]# date; /etc/init.d/mysqld restart; date
Wed Jun  1 17:04:28 UTC 2016
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
Wed Jun  1 17:04:46 UTC 2016


mysql> show variables like '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /usr/local/etc/rds-ca-2015-root.pem |

aus4-2 has certificates now.

Comment 11

[Justin Lazaro [:jlaz] (use needinfo)]

This work has been completed.

Comment 12

[Chris Kolosiwsky [:ckolos] (ckolos has left the building)]

It has *not* been completed. Reopening.

Comment 13

[Benson Wong [:mostlygeek]]

Database has been migrated to AWS. We're not rolling back.