mysql 主从配置

近期部分机器要求做主从，之前都是每天定时全备份，但是影响到线上服务。所以对部分活跃的服务器做了主从，解决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;