MySQL database replication setup

August 10, 2010

I have recently setup a master and slave in MySQL.

the main reason I did this is so I could do some queries and light data mining looking for patterns in our datasets. The issue is that the Live database is optimized for inserts and updates, not for views or summaries.

in creating a replicated database, I was able to index my slave differently, and allow it to be better suited for pulling this report views.

I am NO MySQL database expert, but was able to follow the simple directions on the mysql.com website and have this up and running relatively quickly and painlessly.

This is how I did it:

  • I configured my master by adding the following to my.cnf and restarting the mysql instance:
  • server-id=1276024641
    log-bin=mysql-bin
    log-error=mysql-bin.err
    binlog_do_db=<my_target_db_name>
  • the db_name restricts only 1 db to be replicated. I think omitting this line will replicate all the databases on the master to the slave
  • I added a unique, random number as my server-id to my slave machine’s my.cnf file. the line I added looked like this : server-id=1281421047
  • I then restarted the slave mysql instance
  • I then setup the replication user on the master. I used the phpMyAdmin scripts, by clicking on the “Replication” tab, then the “add slave replication user” link. I saved this info
  • because I am running a live database, I needed to locate the master’s bin log coordinates. To do this, you need to ensure no one is changing the data, the manual recommends locking the tables, but on our transaction heavy system, I just closed the db for the next step.
  • I then proceeeded to output a mysqldump: mysqldump -p my_database_name > db_script.sql
  • while this was saving to file, I went into phpmyadmin under replication and clicked “show master status”, which displayed
  • Variable     Value
    File                mysql-bin.000120
    Position       83336594
    Binlog_Do_DB    my_databaswe_name
    Binlog_Ignore_DB
  • Once the data dump was complete, I re-enabled traffic back into the master db.
  • I then copied the .sql file to the slave machine and imported it using: mysql -p my_database_name < db_script.sql
  • Once the db snapshot was imported, I needed to tell the slave to start running the slave process thread, by running the query:
  • CHANGE MASTER TO
    MASTER_HOST=’master_host_name’,
    MASTER_USER=’replication_user_name’,
    MASTER_PASSWORD=’replication_password’,
    MASTER_LOG_FILE=’recorded_log_file_name’,
    MASTER_LOG_POS=recorded_log_position;
  • from there, the slave should keep in sync with the master
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: