MySQL database replication

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