MySQL multi の設定

ローカルでレプリケーションを組みたかったので設定
MacだとMySQL Sandboxのほうが便利

sudo mkdir master-data slave-data
sudo chown _mysql:wheel master-data slave-data
sudo ./scripts/mysql_install_db --datadir=/usr/local/mysql/master-data --user=_mysql
sudo ./scripts/mysql_install_db --datadir=/usr/local/mysql/slave-data --user=_mysql

こんな感じで開始
sudo ./bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start
停止はこちら
sudo ./bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf stop

レプリケーション設定

sudo ./bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 1

mysql -uroot -P 3306 -S/usr/local/mysql/master-data/mysql.sock

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

mysql> show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 411
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

sudo ./bin/mysqld_multi --defaults-file=/usr/local/mysql/my.cnf start 2
CHANGE MASTER TO MASTER_HOST='localhost',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS= 411;
START SLAVE;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 411
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 411
              Relay_Log_Space: 452
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e5278e0e-12ea-11e4-838d-eb799ca050ad
             Master_Info_File: /usr/local/mysql/slave-data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

[root@CentOS mysql]# cat my.cnf
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld]

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/usr/local/mysql
user=_mysql
character-set-server=utf8

[mysqld1]
server_id=1
datadir=/usr/local/mysql/master-data
socket=/usr/local/mysql/master-data/mysql.sock
pid-file=/usr/local/mysql/master-data/mysql.pid
log_bin=/usr/local/mysql/master-data/mysql-bin.log
relay-log=/usr/local/mysql/master-data/mysql-relay.log
port=3306

[mysqld2]
server_id=2
datadir=/usr/local/mysql/slave-data
socket=/usr/local/mysql/slave-data/mysql.sock
pid-file=/usr/local/mysql/slave-data/mysql.pid
log_bin=/usr/local/mysql/slave-data/mysql-bin.log
relay-log=/usr/local/mysql/slave-data/mysql-relay.log
port=3307

[client]
default-character-set=utf8