MySQL Master-Slave Replication

gistfile1.txt

# www.fduran.com
MySQL Master-Slave Replication Notes


Master
/etc/mysql/my.cnf
server-id               = 100
log_bin                 = /var/log/mysql/mysql-bin.log
binlog-do-db            = wpdb
binlog-ignore-db        = mysql

commands:
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'apassword';
FLUSH PRIVILEGES;
GRANT RELOAD ON *.* TO 'replication_user'@'%';
GRANT SUPER ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
USE dbname;
FLUSH TABLES WITH READ LOCK;
 
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1380 | dbname       | mysql            |
+------------------+----------+--------------+------------------+

Slave
my.cfn :
server-id               = 101
master-host             = dbnode
master-user             = replication_user
master-password         = apassword
master-port             = 3306
master-connect-retry    = 60
replicate-do-db         = dbname
binlog-ignore-db        = mysql
log_bin                 = /var/log/mysql/mysql-bin.log

sudo mysqld_safe --skip-slave-start &
from master: mysqldump --compact --databases dbname -u root -p |mysql -u dbuser -pdbuserpasswd -h 10.0.0.10
check db
stop mysqld_safe (mysqladmin shutdown better way than sudo killall mysqld ?)
sudo service mysqld restart

STOP SLAVE;
change master to MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1380;
START SLAVE;

Check slave:
show processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
| 47 | root        | localhost | dbname | Query   |    0 | NULL                                                                  | show processlist |
| 56 | system user |           | NULL | Connect |  151 | Waiting for master to send event                                      | NULL             |
| 57 | system user |           | NULL | Connect |  151 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

SHOW SLAVE STATUS;
show status like 'Slave%';

  Slave_running              | ON

(LOAD DATA FROM MASTER;)

In master: 

USE dbname;
UNLOCK TABLES;