読者です 読者をやめる 読者になる 読者になる

MySQLレプリケーション設定メモ

今日はMySQLレプリケーション設定のやり方メモです。


MySQLのバージョンは5.0.77(CentOSのパッケージをそのまま)、
データベースエンジンは何も考えずにMyISAMを使用しました。

下準備


まずは元データのサンプルを用意します。
今回は日本郵便が公開している郵便番号データを使う事にしました。


ダウンロードしてきて文字コードUTF-8にします。
CSVデータの2〜9項目目を使います。

$ wget http://www.post.japanpost.jp/zipcode/dl/oogaki/zip/ken_all.zip
$ unzip ken_all.zip
$ nkf -g KEN_ALL.CSV
Shift_JIS
$ nkf -w -Lu < KEN_ALL.CSV > translate.csv
$ cut -d, -f 2-9 translate.csv > zipcode.csv


データベースを作成します。

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE jpzip DEFAULT CHARACTER set utf8;

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| jpzip              | 
| mysql              | 
| test               | 
+--------------------+
4 rows in set (0.11 sec)

mysql> SHOW CREATE DATABASE jpzip;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| jpzip    | CREATE DATABASE `jpzip` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit;
Bye


テーブル作成用のSQLを適当に作成します。

# vi yuubin_table.sql
create table jpzipcode (
    oldpost text,
    newpost text,
    prefecture_kana text,
    area_kana text,
    town_kana text,
    prefecuture text,
    area text,
    town text
);

# mysql -u root -p jpzip < yuubin_table.sql
Enter password:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE jpzip;

mysql> SHOW TABLES;
+-----------------+
| Tables_in_jpzip |
+-----------------+
| jpzipcode       | 
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW FIELDS FROM jpzipcode;
+-----------------+------+------+-----+---------+-------+
| Field           | Type | Null | Key | Default | Extra |
+-----------------+------+------+-----+---------+-------+
| oldpost         | text | YES  |     | NULL    |       | 
| newpost         | text | YES  |     | NULL    |       | 
| prefecture_kana | text | YES  |     | NULL    |       | 
| area_kana       | text | YES  |     | NULL    |       | 
| town_kana       | text | YES  |     | NULL    |       | 
| prefecuture     | text | YES  |     | NULL    |       | 
| area            | text | YES  |     | NULL    |       | 
| town            | text | YES  |     | NULL    |       |
+-----------------+------+------+-----+---------+-------+
8 rows in set (0.00 sec)


データをインポートします。

mysql> LOAD DATA LOCAL INFILE "/usr/local/src/Yuubin/zipcode.csv" INTO TABLE jpzipcode FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 123006 rows affected (0.43 sec)
Records: 123006  Deleted: 0  Skipped: 0  Warnings: 0


元データの準備はこれでOK。

レプリケーション設定(マスター)


「/etc/my.cnf」に以下の設定を追加して再起動。

server_id=1
log_bin=mysql-bin
max_binlog_size=100M
expire_logs_days=30
sync_binlog=1


レプリケーション用のユーザーを作成します。
ついでにマスターの状態を確認。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx' IDENTIFIED BY '****' ;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |       98 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     | 
+---------------+-------+
1 row in set (0.00 sec)


設定が終わったので、「mysqldump」コマンド。

# mysqldump -u root -p --all-databases --master-data=2 --flush-logs > dumpfile.sql

レプリケーション設定(スレーブ)


「/etc/my.cnf」に以下の設定を追加します。

server_id=1000


「--skip-slave」オプションを付けて、MySQLを起動します。

# /usr/bin/mysqld_safe --skip-slave &


マスター側のダンプをインポートします。

# mysql -u root -p < /home/myuser/dumpfile.sql
Enter password:


バイナリログのポインタを確認します。

# head -100 /home/myuser/dumpfile.sql | grep CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;


後は「CHANGE MASTER TO」で各種パラメータを指定し、「START SLAVE」を実行すればOK。

mysql> CHANGE MASTER TO
    -> MASTER_HOST='xxx.xxx.xxx.xxx',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='****',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=98,
    -> MASTER_CONNECT_RETRY=10
    -> ;

mysql> START SLAVE;Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Connecting to master
                Master_Host: xxx.xxx.xxx.xxx
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: mysql-bin.000002
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000001
              Relay_Log_Pos: 98
      Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: No
          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: 98
            Relay_Log_Space: 98
            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: NULL
1 row in set (0.00 sec)


MySQLのログからも確認ができました。

111009 10:10:45 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 232, relay log './mysqld-relay-bin.000003' position: 369
111009 10:10:45 [Note] Slave I/O thread: connected to master 'repl@xxx.xxx.xxx.xxx:3306',  replication started in log 'mysql-bin.000002' at position 232


今日はこんな所で。