Datenbank-Replikation mit MySQL

Konfiguration des Master-Servers (/etc/mysql/my.cnf)

#bind-address            = 127.0.0.1

# replication stuff
# we're using bin-logs
log-bin
binlog-do-db=replication1
# this is our master
server-id       = 1
replicate-do-db=exampledb

/etc/init.d/mysql restart

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

The last command will show something like this:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Konfiguration des Slave-Server (/etc/mysql/my.cnf)

# replication stuff
# we're using bin-logs
log-bin
# this is our slave
server-id       = 2
master-host=<IP des Master-Servers>
master-user=slave_user
master-password=<Passwort von slave_user>
master-connect-retry=60
replicate-do-db=replication1

/etc/init.d/mysql restart

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;