OSS Fan ~OSSでLinuxサーバ構築~

作成日: 2011/02/26
更新日: 2016/02/24

OSSでLinuxサーバ構築

MySQL 5.0のレプリケーション設定

トップページOSSでLinuxサーバ構築 > MySQL 5.0のレプリケーション設定
このエントリーをはてなブックマークに追加

構成

サーバ構成

OSバージョン

CentOS 5.5 x86_64

パッケージ一覧

  • perl-DBI-1.52-2.el5.x86_64.rpm
  • mysql-5.0.77-4.el5_4.2.x86_64.rpm
  • perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm
  • mysql-server-5.0.77-4.el5_4.2.x86_64.rpm

サーバ構築

設定

 MySQLがインストールされたサーバを2台準備しておきます。 OSのバージョンは必ずしも同じでなくても良いと思いますが、 MySQLのバージョンは統一する方が安全だと思います。 マスターサーバからスレーブサーバへデータをレプリケーションする 形となります。そのため、当然2台で設定も異なりますので注意してください。

(1)スレーブサーバ設定1

# 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> grant all on *.* to repl@'172.18.0.59' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
# /etc/rc.d/init.d/mysqld stop
MySQL を停止中:  [  OK  ]
# cd /etc/
# vi my.cnf
ファイル名:/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

server-id=2 ←この行を追加

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

(2) マスターサーバ設定

# mysql -u root -p
Enter password: (パスワードを入力)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

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

mysql> grant replication slave on *.* to repl@'172.18.0.59' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
# /etc/rc.d/init.d/mysqld stop
MySQL を停止中:  [  OK  ]
# cd /etc/
# vi my.cnf
ファイル名:/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

server-id=1 ←この行を追加
log-bin ←この行を追加
set-variable = expire_logs_days=30 ←この行を追加

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# cd /var/lib/mysql/
# tar cvf ./mysql.tar ./*
./ibdata1
./ib_logfile0
./ib_logfile1
(中略)
./mysql/procs_priv.frm
./mysql/help_topic.MYI
./test/
# scp mysql.tar 172.18.0.59:/tmp/
The authenticity of host '172.18.0.59 (172.18.0.59)' can't be established.
RSA key fingerprint is d6:d5:85:e6:6a:65:e6:44:a9:f1:41:77:4d:d2:97:54.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.18.0.59' (RSA) to the list of known hosts.
root@172.18.0.59's password: (パスワードを入力)
mysql.tar                                       0%    0     0.0KB/s   --:-- ETAmysql.tar                                     100%   21MB  20.7MB/s   00:00    
# rm mysql.tar
rm: remove regular file `mysql.tar'? yes
# /etc/rc.d/init.d/mysqld start
MySQL を起動中:  [  OK  ]

(3) スレーブサーバ設定2

# cd /var/lib/mysql/
# rm -rf ./*
# mv /tmp/mysql.tar .
# tar xvf ./mysql.tar 
./ibdata1
./ib_logfile0
./ib_logfile1
(中略)
./mysql/procs_priv.frm
./mysql/help_topic.MYI
./test/
# rm mysql.tar
rm: remove regular file `mysql.tar'? yes
# /etc/rc.d/init.d/mysqld start
MySQL を起動中:  [  OK  ]
# mysql -u root -p
Enter password: (パスワードを入力)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

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

mysql> change master to master_host='172.18.0.57', master_user='repl', master_password='repl';
Query OK, 0 rows affected (0.01 sec)

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

mysql> exit
Bye

設定ファイル(マスターサーバ)

 /etc/my.cnf

設定ファイル(スレーブサーバ)

 /etc/my.cnf

動作テスト

 マスターサーバで「File」と「Position」を確認します。

# 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-log Source distribution

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

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |       98 |              |                  | 
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> set password for root@"localhost"=password("root");
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

 続いてスレーブサーバで「Master_Log_File」と「Read_Master_Log_Pos」を確認し、 それぞれマスターサーバの「File」と「Position」と同じ値になっていればOKです。

# mysql -u root -p
Enter password: (パスワードを入力)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution

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

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 | 172.18.0.57 | repl        |        3306 |            60 | mysqld-bin.000001 |                  98 | mysqld-relay-bin.000002 |           236 | mysqld-bin.000001     | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                  98 |             236 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | 
+----------------------------------+-------------+-------------+-------------+---------------+-------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

プロフィール

らのっち

損害保険会社のIT企画部に勤務するSEです。OSSを勉強中です。

<所属>
日本PostgreSQLユーザ会とくしまOSS普及協議会


第000414号