Differences

This shows you the differences between two versions of the page.

--- mysql:replication [2008/06/08 12:38]
193.164.137.52 + how to fix mysql replication
+++ mysql:replication [2008/09/03 18:57]
a
@@ Line 20: / Line 20: @@
    mysql> START SLAVE;
    mysql> SHOW SLAVE STATUS \G
+===== MySQL binary log rotation =====
+If you’ve run MySQL in a replication environment, or if you’ve enabled binary logging for transactional integrity, you know that the binary logs can grow rather quickly. The only safe way to delete the logs is to use PURGE MASTER LOGS in MySQL, but if you want MySQL to automatically remove the logs after a certain period of time, add this in your my.cnf:
+expire_log_days = 14
+see: [[http://dev.mysql.com/doc/refman/5.0/en/binary-log.html|5.11.3. The Binary Log]]
 ===== Master + Slave =====
+MySQL replication may sound complicated, but it can be done easily. Here’s a quick 7-step guide:
+) Create a replication user on the master:
+   mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';
+) 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
+) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL:
+   server-id = 2
+) 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
+) Configure the slave:
+   # mysql -u user -ppassword
+   mysql> CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='repl', MASTER PASSWORD='repl';
+) Move the dump to the slave server and import it:
+mysql -u user -ppassword < databases.sql
+) Start the slave:
+   mysql -u user -ppassword
+   mysql> START SLAVE;
 ===== Master Master  =====