Donnerstag, 19. November 2009
MySQL master / slave replication
Geschrieben von Harald Lapp in Datenbanken um 09:35
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
Trackback für spezifische URI dieses Eintrags
Keine Trackbacks
Kommentare
Ansicht der Kommentare:
(Linear | Verschachtelt)
Noch keine Kommentare
Kommentar schreiben
