===== Mysqlレプリケーション構築 =====

○ Mysql
　◆ インストール
<code>
# yum -y install mysql-server
</code>

　◆ Master
<code>
# vi /etc/my.cnf
</code>

<code>
[mysqld]
log-bin=mysql-bin
server-id=1
</code>

<code>
# /etc/rc.d/init.d/mysqld start
# mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.80.0/255.255.255.0' IDENTIFIED BY 'slavepass';
mysql> FLUSH PRIVILEGES;
</code>

　◆ Slave
<code>
# vi /etc/my.cnf
</code>

<code>
[mysqld]
server-id=2
</code>

<code>
# /etc/rc.d/init.d/mysqld start
# mysql -u root
mysql> GRANT USAGE ON *.* TO 'repl'@'localhost' IDENTIFIED BY 'slavepass';
mysql> GRANT ALL ON *.* TO 'repl'@'localhost';
mysql> FLUSH PRIVILEGES;
</code>

　◆ Master
<code>
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      327 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
</code>

　● 別のターミナルで以下の2コマンド実行
<code>
# mysqldump -u root -p --all-databases --lock-all-tables > dbdump.d
Enter password: 

mysql> UNLOCK TABLES;
</code>
　
　◆ Slave
<code>
# mysql -uroot -p  < dbdump.d
# mysql -u root
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.80.97',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='slavepass',
    -> MASTER_LOG_FILE='mysql-bin.000003',
    -> MASTER_LOG_POS=327;
mysql> START SLAVE;
</code>

◆◆◆ 以下、サンプルでの確認 ◆◆◆

◆ Master
<code>
# mysql -u root
mysql> create database user_db;
mysql> use user_db;
mysql> create table jpzipcode(
    -> oldpost text,
    -> newpost text not null,
    -> pref text,
    -> area text,
    -> addr text not null
    -> );
mysql> show fields from jpzipcode;
+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| oldpost | text | YES  |     | NULL    |       | 
| newpost | text | NO   |     | NULL    |       | 
| pref    | text | YES  |     | NULL    |       | 
| area    | text | YES  |     | NULL    |       | 
| addr    | text | NO   |     | NULL    |       | 
+---------+------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> INSERT INTO jpzipcode
    ->   (oldpost,newpost,pref,area,addr)
    -> VALUES('154','154-0002','東京都','世田谷区','下馬');
mysql> INSERT INTO jpzipcode
    ->   (oldpost,newpost,pref,area,addr)
    -> VALUES('468','468-0039','愛知県','名古屋市天白区','西入町');
mysql> select * from jpzipcode;
+---------+----------+-----------+-----------------------+-----------+
| oldpost | newpost  | pref      | area                  | addr      |
+---------+----------+-----------+-----------------------+-----------+
| 154     | 154-0002 | 東京都 | 世田谷区          | 下馬    | 
| 468     | 468-0039 | 愛知県 | 名古屋市天白区 | 西入町 | 
+---------+----------+-----------+-----------------------+-----------+
2 rows in set (0.00 sec)
</code>

◆ Slave
<code>
# mysql -u root
mysql> use user_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from jpzipcode;
+---------+----------+-----------+-----------------------+-----------+
| oldpost | newpost  | pref      | area                  | addr      |
+---------+----------+-----------+-----------------------+-----------+
| 154     | 154-0002 | 東京都 | 世田谷区          | 下馬    | 
| 468     | 468-0039 | 愛知県 | 名古屋市天白区 | 西入町 | 
+---------+----------+-----------+-----------------------+-----------+
2 rows in set (0.00 sec)

mysql> 
</code>






　◆ 参考
　http://open-groove.net/mysql/shell-and-mysql/
　
　サンプルコマンド
　/usr/bin/mysql -e 'show slave status \G' | grep -i Last_Error