mysql 主从配置

ewr23

                      近期部分机器要求做主从，之前都是每天定时全备份，但是影响到线上服务。所以对部分活跃的服务器做了主从，解决mysql备份的时候导致玩家卡壳问题。
slave 物理机，多实例，一台物理机基本开到6个实例。

master配置文件  my.cnf


[client]
port                = 3306
socket                = /log/mysql/mysql.sock

[mysqld]
skip-name-resolve = ON
port                = 3306
#bind-address    = 127.0.0.1

log-error        = /log/mysql/mysqld.log
pid-file         = /log/mysql/mysqld.pid
socket           = /log/mysql/mysql.sock
datadir          = /data/mysql

slow-query_log   = ON
slow_query_log_file   = /log/mysql/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 1
log-bin         = /data/bin_log/3306/mysql-bin-3306
max_binlog_size = 256M
log-slave-updates = 0
expire_logs     = 7
binlog_format   = "MIXED"

innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql
innodb_log_group_home_dir = /data/mysql
innodb_open_files = 65535

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 65535
pid-file         = /log/mysqlmysqld.pid

slave配置文件  my.cnf
[client]
port                = 3306
#socket                = /log/mysql/mysql.sock

[mysqld_multi]
mysqld = /app/mysql/bin/mysqld_safe
mysqladmin = /app/mysql/bin/mysqladmin
log = /log/mysql/mysqld_multi.log
user = admin
password = admin

[mysqld3306]
skip-name-resolve = ON
port                = 3306
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3306/mysqld.log
pid-file         = /log/mysql/3306/mysqld.pid
socket           = /log/mysql/3306/mysql.sock
datadir          = /data/mysql/3306

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3306/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3306/bin-log/mysql-bin-3306
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3306
innodb_log_group_home_dir = /data/mysql/3306
innodb_open_files = 65535
innodb_file_per_table = 1

[mysqld3307]
skip-name-resolve = ON
port                = 3307
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3307/mysqld.log
pid-file         = /log/mysql/3307/mysqld.pid
socket           = /log/mysql/3307/mysql.sock
datadir          = /data/mysql/3307

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3307/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3307/bin-log/mysql-bin-3307
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3307
innodb_log_group_home_dir = /data/mysql/3307
innodb_open_files = 65535
innodb_file_per_table = 1


[mysqld3308]
skip-name-resolve = ON
port                = 3308
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3308/mysqld.log
pid-file         = /log/mysql/3308/mysqld.pid
socket           = /log/mysql/3308/mysql.sock
datadir          = /data/mysql/3308

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3308/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3306/bin-log/mysql-bin-3306
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3308
innodb_log_group_home_dir = /data/mysql/3308
innodb_open_files = 65535
innodb_file_per_table = 1

[mysqld3309]
skip-name-resolve = ON
port                = 3309
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3309/mysqld.log
pid-file         = /log/mysql/3309/mysqld.pid
socket           = /log/mysql/3309/mysql.sock
datadir          = /data/mysql/3309

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3309/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3309/bin-log/mysql-bin-3309
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3309
innodb_log_group_home_dir = /data/mysql/3309
innodb_open_files = 65535
innodb_file_per_table = 1

[mysqld3310]
skip-name-resolve = ON
port                = 3310
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3310/mysqld.log
pid-file         = /log/mysql/3310/mysqld.pid
socket           = /log/mysql/3310/mysql.sock
datadir          = /data/mysql/3310

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3310/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3310/bin-log/mysql-bin-3310
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3310
innodb_log_group_home_dir = /data/mysql/3310
innodb_open_files = 65535
innodb_file_per_table = 1

[mysqld3311]
skip-name-resolve = ON
port                = 3311
#bind-address    = 127.0.0.1
basedir          = /app/mysql

log-error        = /log/mysql/3311/mysqld.log
pid-file         = /log/mysql/3311/mysqld.pid
socket           = /log/mysql/3311/mysql.sock
datadir          = /data/mysql/3311

slow-query_log   = ON
slow_query_log_file   = /log/mysql/3311/slow.log
long_query_time = 1

back_log = 512
max_connections = 16384
max_connect_errors = 100
interactive_timeout = 86400
wait_timeout = 86400
table_cache = 65535
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 5461
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
memlock
thread_stack = 192K
tmp_table_size = 64M

skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 16M
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

#skip-networking

server-id       = 2
replicate-wild-ignore-table=mysql.%
#log-bin         = /data/mysql/3311/bin-log/mysql-bin-3311
#max_binlog_size = 256M
#binlog_cache_size       = 128k
#binlog-ignore           = mysql
#log-slave-updates = 0
#expire_logs     = 7
#binlog_format   = "MIXED"

innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment = 500M
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_data_home_dir  = /data/mysql/3311
innodb_log_group_home_dir = /data/mysql/3311
innodb_open_files = 65535
innodb_file_per_table = 1

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 65535
pid-file         = /log/mysqlmysqld.pid


从多实例我使用了 mysqld_multi

多实例启动脚本

#!/bin/sh  
#  
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.  
# This script assumes that my.cnf file exists either in /etc/my.cnf or  
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the  
# mysqld_multi documentation for detailed instructions.  
#  
# This script can be used as /etc/init.d/mysql.server  
#  
# Comments to support chkconfig on RedHat Linux  
# chkconfig: 2345 64 36  
# description: A very fast and reliable SQL database engine.  
#  
# Version 1.0  
#
basedir=/app/mysql
bindir=/app/mysql/bin
conf=/app/conf/mysql/my.cnf
export PATH=$PATH:$bindir
if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi
case "$1" in
    'start' )
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    'stop' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        ;;
    'report' )
        "$mysqld_multi" --defaults-extra-file=$conf report $2
        ;;
    'restart' )
        "$mysqld_multi" --defaults-extra-file=$conf stop $2
        "$mysqld_multi" --defaults-extra-file=$conf start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac

mysql初始化
/app/mysql/scripts/mysql_install_db --basedir=/app/mysql --datadir=/data/mysql/3311 --user=mysql

master同步用户权限设置
GRANT SUPER, REPLICATION SLAVE ON *.* TO 'sync'@'%' IDENTIFIED BY 'sync';
flush privileges;
多实例数据库关闭
grant shutdown on *.* to 'admin'@'localhost' identified by 'admin';
flush privileges;

根据master bin-log 点进行同步

CHANGE MASTER TO master_host='192.168.1.2',master_user='sync',master_password='sync',master_port=3306,master_log_file='mysql-bin-3306.000001',master_log_pos=2946738;