レプリケーション
前提条件
初期設定によりマスタの設定を行っていること
C:\>mysql -u root --port=3309
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.48-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb1;
Database changed
mysql> select * from testtbl1;
+------+-------+-------+
| key1 | data1 | data2 |
+------+-------+-------+
| 1 | aaaa | bbbb |
| 2 | cccc | dddd |
| 3 | eeee | ffff |
+------+-------+-------+
3 rows in set (0.00 sec)
mysql>
マスタ用サーバにレプリケーション用ユーザー作成
mysql> GRANT REPLICATION SLAVE ON *.* TO repuser@'192.168.10.123' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql>
マスタをロックして、Positionを確認する
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log_bin.000001 | 1222 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>
データベースバックアップ
c:\>mysqldump -u root --port=3309 --all-databases --lock-all-tables >dbdump.db
c:\>
スレーブの「my.ini」の設定
[client]
#password = your_password
port = 3310
##socket = /tmp/mysql.sock
default-character-set=cp932
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
#allow-suspicious-udfs
#ansi
##ログファイル用設定
general-log
general_log_file
log=test.log
log-error=error.log
##log-long-format
log-short-format
log-output
log-queries-not-using-indexes
log-slave-updates
log-slow-admin-statements
log-slow-slave-statements
long-query-time=10
log-tc
log-tc-size
log-warnings
slow-query-log
slow_query_log_file=slow-query.log
##バイナリログファイル用設定
log-bin=log_bin.log
log-bin-index=log_bin_index.log
log-bin-trust-function-creators
binlog-row-event-max-size=1024
basedir="C:\Program Files\MySQL\MySQL5.1/"
datadir="C:\ProgramData\MySQL\data2/"
character-set-server=cp932
default-storage-engine=INNODB
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
port = 3310
#socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
マスタのデータを使用して、データを設定する
C:\>mysql -u root --port=3310 <dbdump.db
c:\>mysql -u root --port=3310
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.48-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb1;
Database changed
mysql> select * from testtbl1;
+------+-------+-------+
| key1 | data1 | data2 |
+------+-------+-------+
| 1 | aaaa | bbbb |
| 2 | cccc | dddd |
| 3 | eeee | ffff |
+------+-------+-------+
3 rows in set (0.00 sec)
mysql>
レプリケーション開始
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.10.123',
-> MASTER_PORT = 3309,
-> MASTER_USER = 'repuser',
-> MASTER_PASSWORD = 'password',
-> MASTER_LOG_FILE = 'log_bin.000001',
-> MASTER_LOG_POS = 1222;
Query OK, 0 rows affected (0.35 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
データ更新確認(スレーブ確認)
c:\>mysql -u root --port=3310
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.48-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb1;
Database changed
mysql> select * from testtbl1;
+------+-------+-------+
| key1 | data1 | data2 |
+------+-------+-------+
| 1 | aaaa | bbbb |
| 2 | cccc | dddd |
| 3 | eeee | ffff |
+------+-------+-------+
9 rows in set (0.00 sec)
mysql>
データ更新確認(マスタ更新)
C:\Users\m_shige1979>mysql -u root --port=3309
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.48-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb1;
Database changed
mysql> insert into testtbl1(key1, data1, data2) values( 4, 'aaa', 'bbb');
Query OK, 1 row affected (0.06 sec)
mysql> insert into testtbl1(key1, data1, data2) values( 5, 'ccc', 'ddd');
Query OK, 1 row affected (0.06 sec)
mysql> insert into testtbl1(key1, data1, data2) values( 6, 'fff', 'ggg');
Query OK, 1 row affected (0.03 sec)
mysql> insert into testtbl1(key1, data1, data2) values( 7, 'aaa', 'bbb');
Query OK, 1 row affected (0.06 sec)
mysql> insert into testtbl1(key1, data1, data2) values( 8, 'aaa', 'bbb');
Query OK, 1 row affected (0.06 sec)
mysql> insert into testtbl1(key1, data1, data2) values( 9, 'aaa', 'bbb');
Query OK, 1 row affected (0.06 sec)
mysql>
データ更新確認(スレーブ確認)
c:\>mysql -u root --port=3310
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.48-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use testdb1;
Database changed
mysql> select * from testtbl1;
+------+-------+-------+
| key1 | data1 | data2 |
+------+-------+-------+
| 1 | aaaa | bbbb |
| 2 | cccc | dddd |
| 3 | eeee | ffff |
| 4 | aaa | bbb |
| 5 | ccc | ddd |
| 6 | fff | ggg |
| 7 | aaa | bbb |
| 8 | aaa | bbb |
| 9 | aaa | bbb |
+------+-------+-------+
9 rows in set (0.00 sec)
mysql>
最終更新：2010年07月05日 21:12
