mysql mysqld_multi 多实例 keepalived 配置

mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running
 

 

一机

cat /etc/my.cnf
[client]

[mysqld_multi]
user = xxxx
pass = xxx
mysqld = /var/lib/mysql/bin/mysqld_safe  
mysqladmin = /var/lib/mysql/bin/mysqladmin  
log = /var/lib/mysql/mysqld_multi.log  

[mysqld]
collation-server = utf8_unicode_ci
init-connect ='SET NAMES utf8'
character-set-server = utf8
user=mysql  
basedir = /var/lib/mysql  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3306
port=3306
server_id=1
socket=/var/lib/mysql/data/3306/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3306/slow.log
log-error = /var/lib/mysql/data/3306/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 400M
innodb_buffer_pool_size=4096M

binlog-do-db = logagent
binlog-do-db = userlogdb
binlog-do-db = monitordb
binlog-do-db = notifydb
binlog-do-db = transformer
binlog-do-db = faultdb
binlog-do-db = imgsearch
binlog-do-db = config
binlog-do-db = sysdb
binlog-do-db = uam
binlog-do-db = logdb
binlog-do-db = oas
binlog-do-db = appstore
binlog-do-db = hadoop
binlog-do-db = unzip

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3306/mysql-bin
binlog_format=mixed
long_query_time = 2
relay-log=/var/lib/mysql/data/3306/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3306/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3307]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3307
port=3307
server_id=2
socket=/var/lib/mysql/data/3307/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3307/slow.log
log-error = /var/lib/mysql/data/3307/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = userdb_0
binlog-do-db = userdb_1
binlog-do-db = userdb_2
binlog-do-db = userdb_3

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3307/mysql-bin
binlog_format=ROW
long_query_time = 2
relay-log=/var/lib/mysql/data/3307/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3307/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3308]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3308
port=3308
server_id=3
socket=/var/lib/mysql/data/3308/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3308/slow.log
log-error = /var/lib/mysql/data/3308/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = userdb_4
binlog-do-db = userdb_5
binlog-do-db = userdb_6
binlog-do-db = userdb_7

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3308/mysql-bin
binlog_format=ROW
long_query_time = 2
relay-log=/var/lib/mysql/data/3308/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3308/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3309]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3309
port=3309
server_id=4
socket=/var/lib/mysql/data/3309/mysql_3309.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3309/slow.log
log-error = /var/lib/mysql/data/3309/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = ds_fileobjectdb_1
binlog-do-db = ds_fileobjectdb_2
binlog-do-db = ds_sysdb

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3309/mysql-bin
binlog_format=mixed
long_query_time = 2
relay-log=/var/lib/mysql/data/3309/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3309/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

global_defs {
router_id offcial_mysql
}
vrrp_script checkMysql {
script "/etc/check_mysql.sh"
interval 5
}
vrrp_instance VI_1 {
        state BACKUP
        interface eth0
        lvs_sync_daemon_interface eth0
        garp_master_delay 5
        virtual_router_id 72
        priority 100
        advert_int 1
        nopreempt
authentication {
        auth_type PASS
        auth_pass 111
}
track_script {
                checkMysql
}
virtual_ipaddress {
        xxx
}
}

----------------------------基本一样，除了server_id

 cat /etc/my.cnf
[client]

[mysqld_multi]
user = xxxx
pass = xxxx
mysqld = /var/lib/mysql/bin/mysqld_safe  
mysqladmin = /var/lib/mysql/bin/mysqladmin  
log = /var/lib/mysql/mysqld_multi.log  

[mysqld]
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
user=mysql  
basedir = /var/lib/mysql  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3306
port=3306
server_id=5
socket=/var/lib/mysql/data/3306/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3306/slow.log
log-error = /var/lib/mysql/data/3306/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 400M
innodb_buffer_pool_size=4096M

binlog-do-db = logagent
binlog-do-db = userlogdb
binlog-do-db = monitordb
binlog-do-db = notifydb
binlog-do-db = transformer
binlog-do-db = faultdb
binlog-do-db = imgsearch
binlog-do-db = config
binlog-do-db = sysdb
binlog-do-db = uam
binlog-do-db = logdb
binlog-do-db = oas
binlog-do-db = appstore
binlog-do-db = hadoop
binlog-do-db = unzip

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3306/mysql-bin
binlog_format=mixed
long_query_time = 2
relay-log=/var/lib/mysql/data/3306/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3306/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3307]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3307
port=3307
server_id=6
socket=/var/lib/mysql/data/3307/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3307/slow.log
log-error = /var/lib/mysql/data/3307/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = userdb_0
binlog-do-db = userdb_1
binlog-do-db = userdb_2
binlog-do-db = userdb_3

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3307/mysql-bin
binlog_format=ROW
long_query_time = 2
relay-log=/var/lib/mysql/data/3307/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3307/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3308]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3308
port=3308
server_id=7
socket=/var/lib/mysql/data/3308/mysql_3308.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3308/slow.log
log-error = /var/lib/mysql/data/3308/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = userdb_4
binlog-do-db = userdb_5
binlog-do-db = userdb_6
binlog-do-db = userdb_7

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3308/mysql-bin
binlog_format=ROW
long_query_time = 2
relay-log=/var/lib/mysql/data/3308/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3308/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90

[mysqld3309]  
mysqld=mysqld
mysqladmin=mysqladmin
datadir=/var/lib/mysql/data/3309
port=3309
server_id=8
socket=/var/lib/mysql/data/3309/mysql_3309.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/data/3309/slow.log
log-error = /var/lib/mysql/data/3309/error.log
skip-name-resolve
symbolic-links=0

explicit_defaults_for_timestamp=true
skip-grant-tables=false
lower_case_table_names=1
max_connections=5000
innodb_log_file_size = 256M
max_allowed_packet = 100M
innodb_buffer_pool_size=4096M

binlog-do-db = ds_fileobjectdb_1
binlog-do-db = ds_fileobjectdb_2
binlog-do-db = ds_sysdb

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = test
replicate-wild-ignore-table = syncdb.%

log-bin=/var/lib/mysql/data/3309/mysql-bin
binlog_format=mixed
long_query_time = 2
relay-log=/var/lib/mysql/data/3309/slave-relay-bin
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

log-warnings = 2
back_log = 50
max_connect_errors = 10
binlog_cache_size = 1M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
relay-log-index=/var/lib/mysql/data/3309/slave-relay-bin.index
relay_log_recovery = 1
slave-skip-errors = 1062,1032

expire_logs_days=90
 

cat  /etc/keepalived/keepalived.conf
global_defs {
router_id offcial_mysql
}
vrrp_script checkMysql {
script "/etc/check_mysql.sh"
interval 5
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    lvs_sync_daemon_interface eth0
    garp_master_delay 5
    virtual_router_id 72
    priority 80
    advert_int 1
    nopreempt
authentication {
    auth_type PASS
    auth_pass 111
}
track_script {
        checkMysql
}
virtual_ipaddress {
    xxx
}
}

cat /etc/check_mysql.sh
#!/bin/bash
portStatus1=`netstat -anp|grep :::3306|grep LISTEN|grep -v grep|wc -l`
portStatus2=`netstat -anp|grep :::3307|grep LISTEN|grep -v grep|wc -l`
portStatus3=`netstat -anp|grep :::3308|grep LISTEN|grep -v grep|wc -l`
portStatus4=`netstat -anp|grep :::3309|grep LISTEN|grep -v grep|wc -l`
if [ "$portStatus1" = 1 ] && [ "$portStatus2" = 1 ] && [ "$portStatus3" = 1 ] && [ "$portStatus4" = 1 ] ;then
echo success
    exit 0
else
    echo error
    exit 1

fi
 

mysql      2076      1  0 1月05 ?       00:00:53 mysqld --datadir=/var/lib/mysql/data/3307 --port=3307 --server_id=2 --socket=/var/lib/mysql/data/3307/mysql_3307.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/var/lib/mysql/data/3307/slow.log --log-error=/var/lib/mysql/data/3307/error.log --skip-name-resolve --symbolic-links=0 --explicit_defaults_for_timestamp=true --skip-grant-tables=false --lower_case_table_names=1 --max_connections=5000 --innodb_log_file_size=256M --max_allowed_packet=100M --innodb_buffer_pool_size=4096M --binlog-do-db=userdb_0 --binlog-do-db=userdb_1 --binlog-do-db=userdb_2 --binlog-do-db=userdb_3 --binlog-ignore-db=information_schema --binlog-ignore-db=performance_schema --binlog-ignore-db=test --replicate-wild-ignore-table=syncdb.% --log-bin=/var/lib/mysql/data/3307/mysql-bin --binlog_format=ROW --long_query_time=2 --relay-log=/var/lib/mysql/data/3307/slave-relay-bin --key_buffer_size=32M --myisam_sort_buffer_size=128M --log-warnings=2 --back_log=50 --max_connect_errors=10 --binlog_cache_size=1M --max_heap_table_size=64M --join_buffer_size=8M --query_cache_limit=2M --ft_min_word_len=4 --thread_stack=192K --transaction_isolation=REPEATABLE-READ --relay-log-index=/var/lib/mysq/data/3307/slave-relay-bin.index --relay_log_recovery=1 --slave-skip-errors=1062,1032 --expire_logs_days=90
mysql      2079      1  0 1月05 ?       00:00:53 mysqld --datadir=/var/lib/mysql/data/3308 --port=3308 --server_id=3 --socket=/var/lib/mysql/data/3308/mysql_3308.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/var/lib/mysql/data/3308/slow.log --log-error=/var/lib/mysql/data/3308/error.log --skip-name-resolve --symbolic-links=0 --explicit_defaults_for_timestamp=true --skip-grant-tables=false --lower_case_table_names=1 --max_connections=5000 --innodb_log_file_size=256M --max_allowed_packet=100M --innodb_buffer_pool_size=4096M --binlog-do-db=userdb_4 --binlog-do-db=userdb_5 --binlog-do-db=userdb_6 --binlog-do-db=userdb_7 --binlog-ignore-db=information_schema --binlog-ignore-db=performance_schema --binlog-ignore-db=test --replicate-wild-ignore-table=syncdb.% --log-bin=/var/lib/mysql/data/3308/mysql-bin --binlog_format=ROW --long_query_time=2 --relay-log=/var/lib/mysql/data/3308/slave-relay-bin --key_buffer_size=32M --myisam_sort_buffer_size=128M --log-warnings=2 --back_log=50 --max_connect_errors=10 --binlog_cache_size=1M --max_heap_table_size=64M --join_buffer_size=8M --query_cache_limit=2M --ft_min_word_len=4 --thread_stack=192K --transaction_isolation=REPEATABLE-READ --relay-log-index=/var/lib/mysq/data/3308/slave-relay-bin.index --relay_log_recovery=1 --slave-skip-errors=1062,1032 --expire_logs_days=90
mysql      2082      1  1 1月05 ?       00:10:02 mysqld --datadir=/var/lib/mysql/data/3309 --port=3309 --server_id=4 --socket=/var/lib/mysql/data/3309/mysql_3309.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/var/lib/mysql/data/3309/slow.log --log-error=/var/lib/mysql/data/3309/error.log --skip-name-resolve --symbolic-links=0 --explicit_defaults_for_timestamp=true --skip-grant-tables=false --lower_case_table_names=1 --max_connections=5000 --innodb_log_file_size=256M --max_allowed_packet=100M --innodb_buffer_pool_size=4096M --binlog-do-db=ds_fileobjectdb_1 --binlog-do-db=ds_fileobjectdb_2 --binlog-do-db=ds_sysdb --binlog-ignore-db=information_schema --binlog-ignore-db=performance_schema --binlog-ignore-db=test --replicate-wild-ignore-table=syncdb.% --log-bin=/var/lib/mysql/data/3309/mysq-bin --binlog_format=mixed --long_query_time=2 --relay-log=/var/lib/mysql/data/3309/slave-relay-bin --key_buffer_size=32M --myisam_sort_buffer_size=128M --log-warnings=2 --back_log=50 --max_connect_errors=10 --binlog_cache_size=1M --max_heap_table_size=64M --join_buffer_size=8M --query_cache_limit=2M --ft_min_word_len=4 --thread_stack=192K --transaction_isolation=REPEATABLE-READ --relay-log-index=/var/lib/mysql/data/3309/slave-relay-bin.index --relay_log_recovery=1 --slave-skip-errors=1062,1032 --expire_logs_days=90
mysql     20338      1  0 1月05 ?       00:04:54 mysqld --datadir=/var/lib/mysql/data/3306 --port=3306 --server_id=1 --socket=/var/lib/mysql/data/3306/mysql_3306.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/var/lib/mysql/data/3306/slow.log --log-error=/var/lib/mysql/data/3306/error.log --skip-name-resolve --symbolic-links=0 --explicit_defaults_for_timestamp=true --skip-grant-tables=false --lower_case_table_names=1 --max_connections=5000 --innodb_log_file_size=256M --max_allowed_packet=400M --innodb_buffer_pool_size=4096M --binlog-do-db=logagent --binlog-do-db=userlogdb --binlog-do-db=monitordb --binlog-do-db=notifydb --binlog-do-db=transformer --binlog-do-db=faultdb --binlog-do-db=imgsearch --binlog-do-db=config --binlog-do-db=sysdb --binlog-do-db=uam --binlog-do-db=logdb --binlog-do-db=oas --binlog-do-db=appstore --binlog-do-db=hadoop --binlog-do-db=unzip --binlog-ignore-db=information_schema --binlog-ignore-db=performance_schema --binlog-ignore-db=test --replicate-wild-ignore-table=syncdb.% --log-bin=/var/lib/mysql/data/3306/mysql-bin --binlog_format=mixed --long_query_time=2 --relay-log=/var/lib/mysql/data/3306/slave-relay-bin --key_buffer_size=32M --myisam_sort_buffer_size=128M --log-warnings=2 --back_log=50 --max_connect_errors=10 --binlog_cache_size=1M --max_heap_table_size=64M --join_buffer_size=8M --query_cache_limit=2M --ft_min_word_len=4 --thread_stack=192K --transaction_isolation=REPEATABLE-READ --relay-log-index=/var/lib/mysql/data/3306/slave-relay-bin.index --relay_log_recovery=1 --slave-skip-errors=1062,1032 --expire_logs_days=90
root      99779  82099  0 10:47 pts/0    00:00:00 grep --color mysql
[root@db01-16-108 ~]#