Seven Step MySQL Replication

MySQL replication may sound complicated, but it can be done easily. Here's a quick 7-step guide:

1) Create a replication user on the master:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

2) On the master server, add the following to the [mysqld] section in my.cnf and restart MySQL:

server-id = 1
relay_log=mysqldrelay
log-bin
expire_logs_days = 7

3) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL:

server-id = 2

4) Create a mysqldump file on the master server which includes a global lock:

# mysqldump -u user -ppassword -FQqx --opt --master-data=1 --databases db1,db2 > databases.sql

5) Configure the slave:

# mysql -u user -ppassword
mysql> CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='repl', MASTER PASSWORD='repl';

6) Move the dump to the slave server and import it:

mysql -u user -ppassword < databases.sql

7) Start the slave:

mysql -u user -ppassword
mysql> START SLAVE;

Printed from: http://rackerhacker.com/2007/12/31/seven-step-mysql-replication/ .
© Major Hayden 2012.

3 Comments   »

  • caleb says:

    If a stored procedure or function is associated with the database you will want to add the -R flag to the mysqldump command. If a stored procedure or function is referenced in the backup file and does not exist on the slave the import will error out.

  • Looks good, but I recommend using paths for both 'relay-log' and 'relay-log-index'. That way, if MySQL ever changes the defaults, replication won't break.

    Also, it's better to replicate everything due to the "USE db" problem and, if databases need to be filtered out, using 'replicate-wild-do-table' to handle any filtering.

Trackbacks/Pingbacks

  1. Racker Hacker » Database » MySQL Replication: Redundancy

RSS feed for comments on this post

Leave a Reply

 

  • Welcome! I started this blog as a way to give back to all of the other system administrators who have taught me something in the past. Writing these posts brings me a lot of enjoyment and I hope you find the information useful. If you spot something that's incorrect or confusing, please write a comment and let me know. Drop me a line if there's something you want to know more about and I'll do my best to write a post on the topic.
    -- Major Hayden

    Flattr this