MySQL Master Slave replication

1 set up replication

1.1 add replication config

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

# changes made for master slave replication (master=1, slave=2)
server-id = 1

# log-bin is only needed on master
log-bin = /var/lib/mysql/mysql-bin
binlog-do-db=cockpit
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#log-slow-queries = /var/lib/mysql/mysqld_slow-queries.log

# this section is needed only on slave
#relay-log = /var/lib/mysql/relay-bin
#relay-log-index = /var/lib/mysql/relay-bin.index
#relay-log-info-file = /var/lib/mysql/relay-log.info
#master-info-file = /var/lib/mysql/master.info

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1.2 restart mysqld on master and slave

mysql> grant replication permission on master server
mysql> grant replication slave on *.* to repluser@'cop2' identified by 'rep11cation';
mysql> grant replication slave on *.* to repluser@'cop1' identified by 'rep11cation';
mysql> grant replication slave on *.* to repluser@'cop2.it.bmx.ch' identified by 'rep11cation';
mysql> grant replication slave on *.* to repluser@'cop1.it.bmx.ch' identified by 'rep11cation';

1.3 export db on master and copy it to slave

mysqldump -u root cop -F --single-transaction --master-data=2 > masterdump_cop.sql

1.4 import db on slave

mysql> create db cockpit;
mysql < masterdump_cop.sql

1.5 get status on master

mysql -e 'show master status\G'

1.6 initalize replication on slave

set file and position from "show master status"

mysql> CHANGE MASTER TO MASTER_HOST='cop1', MASTER_USER='repluser', MASTER_PASSWORD='rep11cation...', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98, MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status;

1.7 check if sync

mysql -e 'show slave status\G' | grep Slave_

2 useful notes

2.1 if you have to clean up the slave, do this

service mysqld stop
cd /var/lib/mysql/
rm -fv mysql-bin.* relay-* master.info
service mysqld start
ls -l

2.2 some comands

mysql> stop slave;
mysql> stop master;
mysql> show slave status;
mysql> show master status;
mysql> start slave;
mysqlbinlog /var/lib/mysql/zhbdzvos-ug0007-relay-bin.000001
mysqladmin processlist --verbose
mysql> create table table1 (c1 char(3));