Too Cool for Internet Explorer

MySQL master / slave replication


There are tons of tutorials about setting up master / slave replication for MySQL. Here are my own quick notes:

1. Master: /etc/mysql/my.cnf

[mysqld]
server-id = 1
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 1
max_binlog_size  = 100M

2. Slave: /etc/mysql/my.cnf

[mysqld]
server-id = 2
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size  = 100M

3. Master: granting privileges for slave user on database master

GRANT REPLICATION SLAVE ON . 
TO '<slave_username>'@'<slave_ip>' 
IDENTIFIED BY '<slave_password>';

4. Master: creating database dump

Start mysql console as database root and enter the following command:

FLUSH TABLES WITH READ LOCK;

DON'T shut down the mysql client, otherwise the table lock is lost. Open a second shell to the database master and enter the following command on commandline:

mysqldump -u root -p... --databases ... --opt > masterdump.sql

Next, switch back to your mysql console and enter the following command:

SHOW MASTER STATUS;

The output will look something like:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 40140874 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> 

Write down "File" and "Position" ... you will need it later for starting replication.

Now you can unlock the tables:

UNLOCK TABLES;

5. Slave: import database dump

Copy masterdump.sql to the slave server and import the database:

mysql -u root -p... < masterdump.sql

This may take quite some time ...

6. Slave: start replication

Start mysql client on slave and enter the following commands:

CHANGE MASTER TO 
MASTER_HOST='<master_host>', 
MASTER_USER='<slave_username>', 
MASTER_PASSWORD='<slave_password>', 
MASTER_LOG_FILE='<mysql-bin file name you've written down in step 4>', 
MASTER_LOG_POS=<master position you've written down in step 4>;

START SLAVE;


Trackbacks

Keine Trackbacks

Kommentare
Ansicht der Kommentare: (Linear | Verschachtelt)

Noch keine Kommentare

Kommentar schreiben

Umschließende Sterne heben ein Wort hervor (*wort*), per _wort_ kann ein Wort unterstrichen werden.
Standard-Text Smilies wie :-) und ;-) werden zu Bildern konvertiert.

Um maschinelle und automatische Übertragung von Spamkommentaren zu verhindern, bitte die Zeichenfolge im dargestellten Bild in der Eingabemaske eintragen. Nur wenn die Zeichenfolge richtig eingegeben wurde, kann der Kommentar angenommen werden. Bitte beachten Sie, dass Ihr Browser Cookies unterstützen muss, um dieses Verfahren anzuwenden.
CAPTCHA