I’m about done with my computer shuffling which I started a month or so ago. I have a 300g drive at work and a 120g drive at home. The idea is to replicate stuff like the MP3 collection, photos, system backups, etc. in both places, to guard against losing data to a disk crash.

The next challenge is to set up an mp3act server at home that replicates the one at work. I’m going to try doing this with MySQL replication. The idea here is to replicate the mostly-static music library tables with work being the master and home being the slave. Then, each site would have its own copies of the dynamic tables like playlists and playlist history. This lets me use one-way replication and avoid setting up a dual master/slave configuration, which I don’t think would work well with the dynamic tables, particularly WRT simultaneous access etc.

Yesterday and this morning I took the first steps toward doing this, which involved locking down my MySQL server at home, then setting it up to allow remote TCP connections (comment out the bind_address option in my.cnf). Then I needed to create a slave user, which I did by doing

GRANT REPLICATION SLAVE ON *.* TO slave@homedsl IDENTIFIED BY 'password'

The grant command will create the user if it doesn’t already exist.

Then, I needed to edit /etc/mysql/my.cnf on both the master and the slave, to give each one a unique server ID. I gave the master an ID of 1 and the slave an ID of 2. This is accomplished in the [mysql] section of the config file, with a line like this:

server-id=1

Next, I followed the instructions in the documentation (see link above) to lock the tables on the master and create tar archives of each of the databases I wanted to replicate (on my Debian box, each database has its own directory under /var/lib/mysql). I then untarred the databases into /var/lib/mysql on the slave. For each database I was copying, I added a line like this to the slave’s config file:

replicate-do-db=database-name

I found that if I wasn’t replicating all the databases from the master, the replication would fail unless I explicitly listed the databases like this. The slave expects the tables it’s replicating to already be present — it does not “automagically” create tables as part of the replication process. I wasn’t really clear on this point after reading the documentation; it was only clear after I actually tried it.

With the tables copied over and the configurations tweaked accordingly, I followed the docs to record the master’s ‘state’ info, point the slave at the master, and start the replication threads on the slave. This all worked as expected. All in all, it wasn’t too hard to set up, so I’ll see how well it works going forward.