Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision Next revision Both sides next revision | ||
mysql:replication [2006/10/29 19:17] a created |
mysql:replication [2008/09/03 18:57] a |
||
---|---|---|---|
Line 1: | Line 1: | ||
====== MySQL replication ====== | ====== MySQL replication ====== | ||
+ | * [[http:// | ||
+ | |||
+ | | ||
+ | ... | ||
+ | | ||
+ | Last_Errno: 1146 | ||
+ | Last_Error: Error 'Table ' | ||
+ | Query: ' | ||
+ | SET thread.views = thread.views + aggregate.views | ||
+ | WHERE thread.threadid = aggregate.threadid' | ||
+ | ... | ||
+ | |||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use **'' | ||
+ | |||
+ | | ||
+ | | ||
+ | |||
+ | ===== MySQL binary log rotation ===== | ||
+ | |||
+ | If you’ve run MySQL in a replication environment, | ||
+ | |||
+ | expire_log_days = 14 | ||
+ | |||
+ | see: [[http:// | ||
+ | |||
+ | |||
===== Master + Slave ===== | ===== Master + Slave ===== | ||
+ | MySQL replication may sound complicated, | ||
+ | |||
+ | 1) Create a replication user on the master: | ||
+ | |||
+ | | ||
+ | |||
+ | 2) On the master server, add the following to the [mysqld] section in my.cnf and restart MySQL: | ||
+ | |||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | 3) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL: | ||
+ | |||
+ | | ||
+ | |||
+ | 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 | ||
+ | | ||
+ | |||
+ | 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 | ||
+ | | ||
===== Master Master | ===== Master Master |