Recently one of our DBA’s decided to quit and left us with a huge MySQL database to maintain. I am by no means a DBA, thefore this has been a real learning experience for me. After further review we realised that there are no real backups being done. The only thing we were relying on were snapshots on the SAN on which the DATA volumes were sitting.
We are talking about a database with billions of rows. The loss of that data would’ve most likely shut down the company in question. I have been hearing from the systems guys that SAN snapshots do not work well when restoring a database and a standard MySQL backup would be better. That is exactly what we decided to do.
The goal was to create two MySQL instances, a MASTER instance and a SLAVE instance. Backup would be taken on the SLAVE instance in order not to affect the performance of the production environment. The database was ~400GB (not including indexes and logs) in size and it would take few hours to perform a dump.
We will use AWS as our platform in this tutorial. Similar to the DBA part, I am not an AWS Admin, but I know enough to be dangerous.
One thing you might want to do beforehand is setup a Security Group that will be shared between both servers (you can create it during EC2 creation, but I decided to do it first). Now I am not a AWS security expert, but I did read in few places that in order to replicate between two MySQL servers they will need to be on the same security group. For the inbound rules, I have added “All traffic” from 0.0.0.0/0. WARNING: this opens up your server to all traffic. I highly recommend that you do not do that in a production server. The only reason why I set that up was that I was testing MySQL functionality and I was not working on anything valuable.
You can create this setup on physical servers, but this tutorial will show you how to do it on Amazon’s AWS EC2 instances.
Given that the current production environment was running Windows Server 2012, I decided to create 2 instances running Windows 2012 Server.
For the purpose of the tutorial we will use AWS Free tier. I do have to warn that setting up the MySQL server will take a lot of time due to the lack of CPU power and RAM on the free tier micro instance.
You can most likely skip “Step 3: Configure Instance Details”. We will be launching a single instance to act as the MASTER server.
Same as above for “Step 4: Add Storage”. Free tier allows 30GB of Elastic Block Storage.
For “Step 5: Tag Instance” I have added
Name=Master for the MASTER instance.
In “Step 6: Configure Security Group” you will select the group you have created in the Security Group section of this tutorial.
At this point you are ready to launch the instance. When you hit the launch button you will be prompted to either select or create a private/public key pair that will be used to retrieve passwords for the RDP session.
Name it, download it, and store it in a secure spot.
It will take few minutes before you can RDP into the server.
Once your instance is ready, you will be able to access under “Instances”. Click on the checkbox next to your instance and hit connect.
You will click on “Get Password” which will ask you to upload the Key you saved in the previous step. The password will show up in place of the button once you are done.
Next click on “Download Remote Desktop File” and double click on the file. Use the username and password from the modal window to access your server.
Follow the same steps for your SLAVE server. Set the tag in Step5 to
We are now ready to proceed to the MySQL installation and setup. Part 2 coming shortly.