mysqld_multi + master / slave replication

這邊在製作一台 MySQL 在 mysqldump 的時候沒有 lock，所以需建立 Master / Slave 然後對 Slave 來做 dump，而希望在同一台這樣速度最快，當然 dump 下來的 sql file 需放在別台機器上來做異地備份，以下是順序

  #增加設定值（/etc/mysql/my.cnf）
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log # if replication

[mysqld2]
server_id = 1002
pid-file  = /var/run/mysqld/mysqld2.pid
socket    = /var/run/mysqld/mysqld2.sock
port  = 3307
datadir   = /var/lib/mysql2
log_bin = /var/log/mysql/mysql-bin2.log # if replication

  #要建立額外的檔案，包含pid和sock（這邊沒做會開不起來...）
sudo -u mysql touch /var/run/mysqld/mysqld2.pid
sudo -u mysql touch /var/run/mysqld/mysqld2.sock

  #建立空白資料集
sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2

  #之後就可以測試mysqld_multi
mysqld_multi report   #check status
mysqld_multi start 2  #open 2
mysqld_multi report   #recheck
#mysqld_multi stop 2  #if need stop

  #建立連線2改用
mysql -u root -p -S /var/run/mysqld/mysqld2.sock

#==========[[master]]

sudo /etc/init.d/mysql restart #重開 master server (因為有設定 log_bin)

  #next need 2 session , mysql client + bash

  #設定帳號密碼
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';
  #鎖定整個資料庫
FLUSH TABLES WITH READ LOCK;
  # remember 顯示資訊要記好，類似以下要記下來
  #+------------------+----------+--------------+------------------+ 
  #| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  #+------------------+----------+--------------+------------------+
  #| mysql-bin.000002 |      267 |              |                  |
  #+------------------+----------+--------------+------------------+
SHOW MASTER STATUS;
  #dump所有的資料庫，需加master-data（這邊要另外的session來做，原來的client不能中斷）
mysqldump -u root -p --master-data --all-databases > all_mysql_db.sql
  #解除寫入
UNLOCK TABLES;

#==========[[slave]]

  #dump回去
mysql -u root -p -S /var/run/mysqld/mysqld2.sock < all_mysql_db.sql
  #設定繫結，這邊要填入 File => MASTER_LOG_FILE & Position => MASTER_LOG_POS
CHANGE MASTER TO MASTER_HOST='localhost',MASTER_USER='repl',MASTER_PASSWORD='PASSWORD_HERE',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=267;
  #開始
START SLAVE;
  #check master & slave
SHOW MASTER STATUS;
SHOW SLAVE STATUS;
  #如果是 "Waiting for master to send event" 就ok

打完收工 (rock)