====== MySQL replication ======
   * [[http://www.howtoforge.com/how-to-repair-mysql-replication|How to repait MySQL replication]]

   mysql> SHOW SLAVE STATUS \G
   ...
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 1146
                    Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. 
    Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
           SET thread.views = thread.views + aggregate.views
           WHERE thread.threadid = aggregate.threadid'
   ...


   mysql> STOP SLAVE;
   mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

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 **''SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2''**; instead and so on.

   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:

1) Create a replication user on the master:

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

2) 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

3) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL:

   server-id = 2

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
   mysql> CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='repl', MASTER PASSWORD='repl';

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
   mysql> START SLAVE;


===== Master Master  =====

see **[[http://www.howtoforge.com/mysql_master_master_replication|this]]** for Step-by-step configuration.


==== 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 "check" with the following mysql queries:

   CREATE USER 'check'@'localhost' IDENTIFIED BY 'YOURPASSWORD';

   GRANT SUPER , REPLICATION CLIENT ON * . * TO 'check'@'localhost' IDENTIFIED BY 'YOURPASSWORD' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

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=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

###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 "Slave_IO_Running: No"
if [ "$?" -ne "1" ]; then
  echo "Replication failed" > /root/slave_problem.txt
fi

###Send notification if replication down###
cd /root
if [ -f slave_problem.txt ]; then
  mail -s "Replication problem" admin@example.com < /root/slave_problem.txt
fi
</code>