A MSSQL diehard begins MySQL, Part 1

December 8, 2013

Today I started down the patch of open source RDBMS's. I've dealt with PostgreSQL and MySQL before, but as a developer. A friend of mine gave me the following To-Do list over Facebook:

1. Install mysql

2. Install 2 instances of mysql

3. Setup master -> slave replication

4. insert data on slave

5. Cause replication to break by inserting data on master.

6. Determine how you would fix this using DML ...

7. Name 2 ways you could rebuild the data on the slave without impact on the master. Describe requirements and caveats of each.

8. Add a floating IP to the current master.

9. Perform a failover while an application accesses the db.

10. Write a small script that will failover using application logic.

11. Setup master/master replication.

12. Determine how you could take writes on both sides without conflicting data. (hint: options *autoincrement*)

13. Why should you almost never do master/master writes on both.

14. Perform a floating IP failover.

15. Describe 3 ways you can take a backup of mysql.

16. Why should anyone using myISAM in production be fired ?

17. How do RAID controller write-back caches work ?

18. What hardware problems can cause IO to suffer without the box dying ?

My response to the list wasn't anything I care to relate for posterity, but I'm working on #1. I've decided to document my steps both for my own reference and for any other folks who end up in my shoes.

1. Install a Linux VM

I just bought a new Macbook and had a Parallels license laying around, so I installed Parallels instead of my friend's recommendation of Vagrant. I'm going to use CentOS, so I went here. On this page I learned that "i386" is 32 bit and "x86_64" is 64 bit. I chose 64 bit to match my OS. The next page, after I chose a mirror, had more options. I don't like "choose this one" without knowing why. I knew I wanted an iso (every time I see "iso" I think of Olivia Wilde in TRON) but didn't know the difference between "minimal" and "netinstall". It's simple: netinstall includes only enough stuff to connect to the network and download the entire operating system. Minimal includes the base OS in the iso image so you can install a working OS without network connectivity. I went with minimal.

After downloading Olivia, err, the .iso, Parallels took me through an easy step-by-step and I finished installing CentOS. I rebooted. Then I wanted my mouse back so I could check eBay: on a Mac keyboard to release the mouse from your VM hit Ctrl+Alt. It's the little things.

I had installed a linux VM previously and anticipated some setup. First, I wanted to upgrade yum. The setting for "use fastest mirror" was already set (thanks CentOS!) but I had no network connectivity. Instead of letting my boyfriend (Linux Engineer at Square) spoon feed me, I shooed him away and hit Google. I found this link which explains "RHEL/CentOS 6 does not configure network interfaces on default installation" (Duh) and has instructions on how to fix. For DHCP, edit /etc/sysconfig/network-scripts/ifcfg-eth0 to look like

DEVICE="eth0" HWADDR=specifc mac address NM_CONTROLLED="no" ONBOOT="yes" BOOTPROTO="dhcp"

‚ÄčEdit that baby in VI ... my MAC address was already filled in (whew) ... and I didn't use the double quotes. How to edit and save in VI? When in INSERT mode, hit "Esc" then type :x and hit Enter. Last, restart your network.‚Äč

/etc/init.d/network restart

I then updated yum: yum update. I now go decide which "flavor" of MySQL to install. MariaDB is my new favorite. Next steps coming soon ...