Monday, January 16, 2012

[Level 3] Notes for MySQL replication

#################################################### master server
set server_id=0; ## master server_id must be 0
grant replication slave on *.* to 'slave'@'%' identified by 'slave';
show master status;
# +-----------------------+----------+--------------+------------------+
# | File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
# +-----------------------+----------+--------------+------------------+
# | Stanley-NB-bin.000103 |      377 |              |                  | 
# +-----------------------+----------+--------------+------------------+

## in my.cnf
[mysqld]
server-id=0


#################################################### slaver server
set server_id=1;
# shell> mysqldump -uroot -p test t1 > /tmp/t.sql
# mysql> create database if not exist test;
# mysql> use test;
# mysql> source /tmp/t.sql
change master to
  master_host='192.168.1.100',
  master_user='slave',
  master_password='slave',
  master_port=3306,
  master_log_file='Stanley-NB-bin.000103',
  master_log_pos=337;

## in my.cnf
[mysqld]
server-id=1
master-host=192.168.1.100
master-user=slave
master-password=slave
master-port=3306

#################################################### command
start/stop slave;
start/stop slave io_thread;
start/stop slave sql_thread;
Wish this helps.
regards,
Stanley Huang