Replication for MySQL
http://www.mysql.com/doc/en/Replication_Options.html
http://www.mysql.com/doc/en/Replication_SQL.html
http://www.mysql.com/doc/en/Replication_FAQ.html
http://www.mysql.com/doc/en/Replication.html
http://dev.mysql.com/doc/mysql/en/mysqlbinlog.html (for manipulating binlog files)
MASTER:
Specific user specific database
grant file on databasename.tablename to user@"hostname" identified by 'password';
flush privileges;
Generic user for all databases
GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY 'password';
In /etc/my.cnf under the [mysqld] section:
# Replication stuff for master
log-bin=/var/log/mysql/replication
server-id=1
binlog-do-db=database
# Replication stuff for slave
master-host=192.168.1.6
master-user=replicate
master-password=heks32
master-port=3306
server-id=10
replicate-wild-do-table=database.table - if you want to replicate certain tables from a db only...
# Settings above are for continous replication. Below if you want to not start replication on startup
in /etc/my.cnf
skip-slave-start
# it 'stops' replication on errors, and we don't want that!
slave-skip-errors=all
# for with slower lines
slave_net_timeout=5 ???
THEN in a cronjob - start the slaving and stop the slaving after a few hours (see mysql commands below).
mysql commands:
MASTER:
master stop;
master reset;
master start;
SLAVE:
slave stop;
reset slave;
slave start;
show slave status;
# to only update the slave to a certain point:
START SLAVE UNTIL
MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
http://dev.mysql.com/doc/mysql/en/start-slave.html
# to get round weird errors:
from mysqlbinlog Error in Log_event::read_log_event(): 'Event too small', data_len=0,event_type=0
in the slave error log mysql Got fatal error 1236: 'bogus data in log event' from master when reading data from binary log
in the "show processlist;" command show processlist Has read all relay log; waiting for the slave I/O thread to update it
use mysqlbinlog -j to get to a point where the errors no longer appear. then
either:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
or
CHANGE MASTER TO MASTER_LOG_POS = xxx; and then
CHANGE MASTER TO MASTER_LOG_FILE = 'xxx';
http://dev.mysql.com/doc/mysql/en/change-master-to.html