Setup Mysql Replication

First it is probably a good idea to make sure the two servers are the same version with the exact same configuration (including global variables configured the same). The first time I set this up I did not, and I think it ended up causing me a lot of trouble because by default in a windows mysql server installation "lower_case_table_names=1" and by default in a linux mysql server installation "lower_case_table_names=0". From the mysql webiste:

By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X.

Thus when I setup tables in my windows mysql server (master) I carelessly named them using CamelCase. I then did a dump of all the databases and loaded them into the linux mysql installation (slave). The dump did not preserve the casing I used when i originally created the tables and thus all table names in the linux installation were lowercase. When the replication process began the windows server would write queries using the casing that was used when the tables were created. Linux, being case sensitive by default, did not accept these queries because Error 'Table '' doesn't exist.

You can check to see lower_case_table_names is  enabled by connecting to the server and typing
SHOW GLOBAL VARIABLES like 'lower%';
Since lower_case_table_names is not a dynamic variable it cannot be set from a client connected to the server. It instead must be added to the config file for mysql.

• nano /etc/my.cnf
• add the following under [mysqld]: set-variable = lower_case_table_names=1
• Stop the server currently running: service mysqld restart

Getting the Installation Back in Working Order
By the time I figured this out, the replication process was so out of sync I had to start again. To clear the replication logs. I turned off the master installation on the windows machine by executing the following at the cli:
myqladmin -u root -p shutdown 
I then executed the following commands in a mysql client connected to the slave to reset the slave settings and drop any databases to prepare it to be loaded with the most recent data from the master:

STOP SLAVE;
RESET SLAVE; 
drop database [database];



I stopped the mysqld service on the slave and then restarted the mysqld service on the master. From a client connected to the master I executed the following command
RESET MASTER;

I then performed a dump on the master from the slave and loaded the information in to the slave;
mysqldump -u root -h -p --databases [list of databases separated with spaces] > db.sql
mysql -u root -p < db.sql
Finally I reconfigured the slave and started the slave:

CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='';
SLAVE START;

Initial Configuration
Other than that, configuring mysql replication was pretty straight forward.
To configure the master:
create the folder C:\Program Files\MySQL\MySQL Server 6.0\replication
put the following in C:\Program Files\MySQL\MySQL Server 6.0\my.ini under [mysqld]
### START REPLICATION CONFIG  ###
server-id = 1
relay-log = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql-relay-bin"
relay-log-index = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql-relay-bin.index"
log-error = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql.err"
master-info-file = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql-master.info"
relay-log-info-file = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql-relay-log.info"
datadir = "C:/Program Files/MySQL/MySQL Server 6.0/data"
log-bin = "C:/Program Files/MySQL/MySQL Server 6.0/replication/mysql-bin"
### END REPLICATION CONFIG ###


To configure the slave:
put the following in /etc/my.cnf
# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup