i-generation

●レプリケーション用ユーザの作成（マスタ側）
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.103' IDENTIFIED BY 'slavepass';

●マスタの設定

マスタのサーバーの/etc/my.cnfファイル内で以下のように設定

[root@db2 ~]# vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1001

[MySQL再起動]

[root@db2 ~]# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

●スレーブの設定

スレーブのサーバーの/etc/my.cnfファイル内で以下の通り設定

[root@web2 ~]# vi /etc/my.cnf

[mysqld]
server-id=1002

[MySQL再起動]

[root@web2 ~]# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

●データのスナップショット作成

マスタで以下のコマンドを実行して、FileとPositionの値をメモしておく

[root@db2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4704
Server version: 5.0.77-log Source distribution

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

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.08 sec)

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

ターミナル１で以下のコマンドを打つ。

[root@db2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5023
Server version: 5.0.77-log Source distribution

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

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

ターミナル２で以下のコマンドを打つ。

[root@db2 ~]# mysqldump -u root -p aff > dbdump.db
Enter password:

ターミナル１で以下のコマンドを打つ。

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

●DBダンプのコピー
[root@db2 ~]# scp dbdump.db admin@192.168.2.103:/home/admin
The authenticity of host '192.168.2.103 (192.168.2.103)' can't be established.
RSA key fingerprint is 79:5c:8c:ad:03:18:4a:a7:a1:34:bf:d1:bc:35:07:4f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.103' (RSA) to the list of known hosts.
admin@192.168.2.103's password:
dbdump.db 100% 751MB 57.8MB/s 00:13

●スレーブの設定(2)

「データベースの作成」

[root@web2 ~]# cd /home/admin/
[root@web2 admin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77-log Source distribution

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

mysql> create database aff;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on aff.* to mysql@192.168.2.103 identified by 'Gnke8d9C';
Query OK, 0 rows affected (0.00 sec)

「データのインポート」

[root@web2 admin]# mysql -u root -p aff < dbdump.db
Enter password:

「インポート確認」

[root@web2 admin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.77-log Source distribution

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

mysql> use aff;
Database changed
mysql> show tables;
------------------------

Tables_in_aff

access_action_log_200901

access_action_log_200902

access_action_log_200903

access_action_log_200904

access_action_log_200905

access_action_log_200906

「マスタ情報の登録」

mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.104', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=5047956;
Query OK, 0 rows affected (0.01 sec)

「SLAVEのスタート」

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

「動作の確認」

mysql> show slave status;
-----------------------------------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
-----------------------------------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Waiting for master to send event | 192.168.2.104 | repl | 3306 | 60 | mysql-bin.000014 | 5288433 | mysqld-relay-bin.000002 | 240712 | mysql-bin.000014 | Yes | Yes | | | | | | | 0 | | 0 | 5288433 | 240712 | None | | 0 | No | | | | | | 0 |
-----------------------------------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

※Slave_IO_Running、Slave_SQL_RunningがYesになっていること。