MySQL Replication

These are the steps to set up Master/Slave replication on MySQL

mysql> grant replication slave, replication client on *.* to
repl@'10.%' identified by 'rvisitor';

mysql> show master status;

Change on my.cnf on salve
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replicate/mysql-bin
server_id = 20
relay_log = mysql-relay-bin
log_slave_updates = 1
log-slow-queries = mysql-slow-queries
long_query_time = 1
log-queries-not-using-indexes

replicate-do-db=test

replicate-do-db=test2

binlog_cache_size=128K


Change on my.cnf on Master
table_cache=750
query_cache_size = 32M
query_cache_limit = 512K
#replication settings
log_bin = replication/mysql-bin
binlog-do-db=test
binlog-do-db=test2
binlog-do-db=test2
expire_logs_days=10
server_id = 10
log-slow-queries = mysql-slow-queries
long_query_time = 1

Then restart mysql on both

mysql>
change master to master_host='witwicky',
master_user='repl',
master_password='rvisitor',
master_log_file='mysql-bin.000001',
master_log_pos=0;

mysql> show slave status;
mysql> start slave;
mysql> show slave status;

SHOW MASTER STATUS;

SHOW SLAVE STATUS;
SHOW MASTER LOGS;
PURGE MASTER LOGS TO 'bin-log.XYZ';

To stop execution of the binary log from the master, use STOP SLAVE:

mysql> STOP SLAVE;

When execution is stopped, the slave does not read the binary log from the master (the IO_THREAD) and stops processing events from the relay log that have not yet been executed (the SQL_THREAD). You can pause either the IO or SQL threads individually by specifying the thread type. For example:

mysql> STOP SLAVE IO_THREAD;

mysql> LOAD DATA FROM MASTER;