Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
165 lines (134 sloc) 4.99 KB

http://hs-www.hyogo-dai.ac.jp/~kawano/?Install%20Log%2FCentOS5%2FMySQL_repl

非同期レプリ。準同期は 6.0 からかプラグインを入れて試せる。同期レプリは?

master

repl 用ユーザの作成

$ mysql -uroot
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'password';

/etc/my.cnf

[mysqld]
server-id=1
sync_binlog=0 # 0 is only for isucon!
log-bin=mysqld-bin
log-bin-index=mysqld-bin
relay-log=relay-bin
relay-log-index=relay-bin

datadir=/var/lib/mysql
tmpdir=/var/tmp
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

slow_query_log      = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time     = 0.1
log-queries-not-using-indexes # sudo mysqldumpslow -s c /var/lib/mysql/slow.log

max_connections=1024
thread_cache       = 600
thread_concurrency = 8
table_cache        = 8192 # 5.5
# table_open_cache   = 8192 # 5.6
back_log           = 10240

query_cache_size    =    0
query_cache_type    =    0

# global buffer
key_buffer_size                 = 32M
innodb_buffer_pool_size         = 10G # メモリ全体の75%ほど
innodb_log_buffer_size          = 8M
innodb_additional_mem_pool_size = 10M

# thread buffer
sort_buffer_size        = 1M
myisam_sort_buffer_size = 64K
read_buffer_size        = 1M

# innodb
innodb_log_files_in_group       = 3
innodb_log_file_size            = 512M # ディスク食うので注意
innodb_flush_log_at_trx_commit  = 0 # Don't use 0 except ISUCON. cf. http://www.mysqlpracticewiki.com/index.php/--innodb-flush-log-at-trx-commit
innodb_lock_wait_timeout        = 5
innodb_flush_method             = O_DIRECT
innodb_adaptive_hash_index      = 0
innodb_thread_concurrency       = 30
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
innodb_io_capacity              = 200
innodb_stats_on_metadata        = Off

# innodb plugin for mysql >= 5.1.38, comment out for mysql >= 5.5 because it is default. 
# ignore-builtin-innodb
# plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

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

slave

マスタ設定

mysql> CHANGE MASTER TO MASTER_HOST = 'master', MASTER_USER = 'repl', MASTER_PASSWORD = 'password';

スレーブを開始する

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
[mysqld]
server-id=2
read_only
log-slave-updates
log-bin=mysqld-bin
log-bin-index=mysqld-bin
relay-log=relay-bin
relay-log-index=relay-bin
master-host=ip_addr_of_master
master-user=repl
master-password=password

datadir=/var/lib/mysql
tmpdir=/var/tmp
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

slow_query_log      = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time     = 0.1
log-queries-not-using-indexes # sudo mysqldumpslow -s c /var/lib/mysql/slow.log

max_connections=1024
thread_cache       = 600
thread_concurrency = 8
table_cache        = 8192
back_log           = 10240

query_cache_size    =    0
query_cache_type    =    0

# global buffer
key_buffer_size                 = 32M
innodb_buffer_pool_size         = 10G # メモリ全体の75%ほど
innodb_log_buffer_size          = 8M
innodb_additional_mem_pool_size = 10M

# thread buffer
sort_buffer_size        = 1M
myisam_sort_buffer_size = 64K
read_buffer_size        = 1M

# innodb
innodb_log_files_in_group       = 3
innodb_log_file_size            = 512M # ディスク食うので注意
innodb_flush_log_at_trx_commit  = 0 # Don't use 0 except ISUCON. cf. http://www.mysqlpracticewiki.com/index.php/--innodb-flush-log-at-trx-commit
innodb_lock_wait_timeout        = 5
innodb_flush_method             = O_DIRECT
innodb_adaptive_hash_index      = 0
innodb_thread_concurrency       = 30
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
innodb_io_capacity              = 200
innodb_stats_on_metadata        = Off

# innodb plugin for mysql >= 5.1.38, comment out for mysql >= 5.5 because it is default. 
# ignore-builtin-innodb
# plugin-load = innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

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

Troubleshooting

  1. /var/log/mysqld.log を見る
  2. データおかしかったら最終手段として sudo rm -r /var/lib/mysql/* && sudo mysql_install_db
  3. master-host 設定は >= 5.5 でもう使えない cf. http://do9b.wordpress.com/2012/06/28/mysql5-5%E3%81%ABupgrade%E3%81%97%E3%81%9F%E3%82%89%E3%83%8F%E3%83%9E%E3%81%A3%E3%81%9F%E3%80%82replication/