Differences
This shows you the differences between two versions of the page.
mysql:replication [2008/09/09 17:49] a |
mysql:replication [2009/05/25 00:35] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== 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 ===== | ||
- | 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 | ||
- | |||
- | see **[[http:// | ||
- | |||
- | |||
- | ==== Script To Check If MySQL Master Master Replication Is Working Correctly ==== | ||
- | |||
- | This short article explains how you can use a short script to check whether your MySQL master master replication is working as expected or not. | ||
- | |||
- | First add a user named " | ||
- | |||
- | | ||
- | |||
- | GRANT SUPER , REPLICATION CLIENT ON * . * TO ' | ||
- | |||
- | Then add this bash script to your /root directory. Add it to your crontab if needed : | ||
- | |||
- | <code bash|check-master-master.sh> | ||
- | #!/bin/bash | ||
- | PATH=/ | ||
- | |||
- | ###check if already notified### | ||
- | cd /root | ||
- | if [ -f slave_problem.txt ]; then | ||
- | exit 1; | ||
- | fi | ||
- | |||
- | ###Check if slave running### | ||
- | (echo "show slave status \G;") | mysql -u check -pYOURPASSWORD 2>&1 | grep " | ||
- | if [ " | ||
- | echo " | ||
- | fi | ||
- | |||
- | ###Send notification if replication down### | ||
- | cd /root | ||
- | if [ -f slave_problem.txt ]; then | ||
- | mail -s " | ||
- | fi | ||
- | </ | ||