rerolpxE tenretnI tfosorciM - (#-> LostInfo.Com <-#)



	dlostboy (at) lostinfo (dot) com Home \| Journal \| Multimedia \| Files \| Other \| Links \| About

FreeBSD 4.4 HOWTO for setting up replication of mySQL

Here's the scenario: We have a mySQL server (pre-4.0) and we want to replicate the data from it to another mySQL server so that in the event that the primary server has a catastrophic failure, we'd have a spare copy of the data. If you're using a 4.0+ mySQL server, this won't apply the same way as they are making it much easier to implement in the newer versions of the database.

We're going to assume that you already have 2 mySQL servers set up and running. Using the ports, this is a piece of cake anyway. It's a really well written port.

Go to your primary (we'll call it #1) mySQL server. First thing to do is to make a my.cnf file. It will need to go into /etc (it should check /usr/local/etc but it doesn't). Create a file called my.cnf and populate it with this information


	[mysqld]
	socket=/tmp/mysql.sock
	server-id=1
	log-bin

Now goto your soon-to-be-slave machine and create the same file, this time using:


	[mysqld]
	socket=/tmp/mysql.sock
	server-id=2
	master-host=server_#1's_IP_address
	master-user=whatever_username_you_want
	master-password=whatever_password_you_want

If you're only wanting to replicate 1 database, add this line to that file:


	replicate-do-db=db_to_replicate_name

When deciding on a username. I chose to add a user named "replicate" to mine. This user will not need access to any of the databases, so it'd probably help if it wasn't a normal database. Especially since you're having to put the user/pass combination in clear text. Next to do is to copy the data over. You will need to lock the tables so they can be copied. This can be done by either shutting down the server and copying the files:

mysqladmin -p shutdown

tar -cvf /tmp/mysql-snapshot.tar /var/db/mysql

/usr/local/etc/rc.d/mysql-server.sh start
Or you can optionally lock the tables and do the copy while the server is "hot"

mysql -p
Once in mysql, execute a "FLUSH TABLES WITH READ LOCK;" then do the

tar -cvf /tmp/mysql-snapshot.tar /var/db/mysql
Followed by doing a "UNLOCK TABLES;". I went with the former version since it was "easier" and you wind up having to restart the server anyway (usually).

Once you restart the server, the mysql server will now be logging all of it's actions to a file in the /var/db/mysql directory. This is where it stores all the changes to the database. This file will be accessed by your slave machines to catch up to where they should be. Copy your database .tar file to the slave machine and extract it somewhere safe. I'd use the tar -xvf options. Now with your backup in hand, and your slave machine's server stopped, we'll copy the databases in. Databases are stored one-per-directory in the /var/db/mysql directory. If you want to mirror the whole #1 server, then copy the entire contents of the tar into the #2 machines' directory. If you just want one database, then copy only the one directory to /var/db/mysql.

Now go back to the first machine, that's now running, and issue the mySQL command: "GRANT FILE ON *.* TO username@slave_ip_address IDENTIFIED BY 'password';" then a "FLUSH PRIVILEGES;".

Now go back to the #2 machine, and start the mySQL server. It should immediately create a file called master.info in your /var/db/mysql directory which contains internal information as to where the master server is, and whereabouts in the log file that it's caught up to.

Connect to the #2 machine and execute the SQL "show slave status;" What you should see is a return set containing the Master_Host and Master_User among other things. You want to see something in the column "Log_File" and in the "Slave_Running". If there's an error, the replication will not occur and the error message will be in the "Last_error" column. If the "Log_File" column is empty, then it has not successfully connected to the master yet. Regrettibly, this connection should be instant so if it's not populated, something is wrong. Lastly, as it's updating you should see the "Pos" column increment.

If it's not working, verify that the master server is actually set up to replicate by connecting to it and running a "show master status;" command. That should have the filename that it's outputting the log to.

Congratulations on a working master-slave mySQL setup.