|
|
mysql+drdb+HA
DB10:192.168.8.73 DRBD primary DB09:192.168.8.72 DRBD secondary
一 DRBD安装1. yum install yum install *drbd83*
2. 编辑文件/etc/drbd.conf, 两台机器配置文件一样:
common { syncer
Unknown macro: { rate 1950M; } }
resource okooo {
protocol C;
disk
Unknown macro: { on-io-error detach; } net
Unknown macro: { after-sb-0pri disconnect; rr-conflict disconnect; } syncer
Unknown macro: { rate 195M; al-extents 257; } on db09
Unknown macro: { device /dev/drbd0; disk /dev/cciss/c0d0p2; address 192.168.8.72} on db10
Unknown macro: { device /dev/drbd0; disk /dev/cciss/c0d0p2; address 192.168.8.73} 3. 分别在2个主机上上创建相应的元数据保存的数据块
确认drbd使用的磁盘分区处于umount状态，这里检查分区 /dev/cciss/c0d0p2；
创建drbd元数据文件：drbdadm create-md okooo #okooo是drbd.conf中的resource名称
2次输入"yes"， 最后出现提示信息：New drbd meta data block sucessfully created.
如果出现如下错误，说明使用的那个分区有文件系统，需要清空，执行：dd if=/dev/zero bs=1M count=1 of=/dev/sda2; sync
然后再运行： drbdadm create-md okooo
Found ext3 filesystem which uses 134158812 kB
current configuration leaves usable 134154680 kB
Device> would corrupt data and result in
'access beyond end of device' errors.
You need to either
* use external meta data (recommended)
* shrink that filesystem first
* zero out the device (destroy the filesystem)
Operation refused.
4. 两台机器上运行：/etc/init.d/drbd start
在启动第一个节点上的DRBD服务的时候，他会等待第2个节点的DRBD服务的启动，此时启动第2个节点上的DRBD服务即可。
5. 两台机器上运行：drbdadm attach okooo
/dev/drbd0: Failure: (124) Device is attached to a disk (use detach first)
Command 'drbdsetup /dev/drbd0 disk /dev/sda2 /dev/sda2 internal --set-defaults --create-device --on-io-error=detach' terminated with exit code 10
碰到这个错误，运行drbdadm detach okooo, 再重新attach
6. 两台机器上运行：drbdadm syncer okooo
8. 设置主节点，我们设置db10为主节点，在db10上运行： drbdadm primary db
如错误：State change failed: (-2) Refusing to be Primary without at least one UpToDate disk
Command 'drbdsetup /dev/drbd0 primary' terminated with exit code 11
则运行： drbdsetup /dev/drbd0 primary -o， 然后drbdadm primary db
查看/proc/drbd
version: 8.3.2 (api:88/proto:86-90)
GIT-hash: dd7985327f146f33b86d4bff5ca8c94234ce840e build by mockbuild@v20z-x86-64.home.localhttp://www.okooo.net/images/icons/mail_small.gif, 2009-08-29 14:07:55
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r----
ns:48045752 nr:0 dw:0 dr:48053824 al:0 bm:2931 lo:1 pe:168 ua:253 ap:0 ep:1 wo:b oos:371484248
[=>..................] sync'ed: 11.5% (362776/409692)M
finish: 4:41:25 speed: 21,936 (18,312) K/sec
finish: 43:12:08 speed: 320 (320) K/sec
#如果看到这里速度比较低，那么可以这样做
1) 主节点停止同步：drbdadm pause-sync all
2) 在两台机器修改文件/etc/drbd.conf中的syncer
Unknown macro: { rate 10M; } 将速率修改比较大的数值1000M，充分利用带宽
3) 在两台机器运行/etc/init.d/drbd> 4) 主节点恢复同步：drbdadm resume-sync all
查看/proc/drbd，可以看到速度提升了，注意同步完成后，将速率改回一个合理的值
此时可以看到，已经开始同步数据了。设置完之后的第一次同步耗时比较长，因为需要把整个分区的数据全部同步一遍。
同步完成后cat /proc/drbd:
version: 8.2.6 (api:88/proto:86-88)
GIT-hash: 3e69822d3bb4920a8c1bfdf7d647169eba7d2eb4 build by buildsvn@c5-x8664-buildhttp://www.okooo.net/images/icons/mail_small.gif, 2008-06-21 08:48:13
0: cs:Connected st:Primary/Secondary ds:UpToDate/UpToDate C r---
ns:63583240 nr:0 dw:1116432 dr:62467033 al:525 bm:3813 lo:0 pe:0 ua:0 ap:0 oos:
注意ds状态变成了UpToDate/UpToDate，说明同步完成。
9. 第一次同步完成之后，就可以对drbd的设备创建文件系统了。 在主节点A执行 mkfs.ext3 /dev/drbd0
10. 主节点db10上创建挂载目录，如mkdir drbddata
11. 主节点db10上执行挂载命令 mount /dev/drbd0 /drbddata （从设备是不让挂载的）
如果想改变主备关系，操作如下：
11.1 主节点db10： umount /drbddata/ ; drbdadm secondary okooo
11.2 备节点db09： drbdadm primary okooo; mount /dev/drbd0 /drbddata
完成后，主备进行了调换
二 HeartBeat安装1.db10， db09上安装, 执行yum install heartbeat 下载软件
=============================================================================
Package Arch Version Repository > =============================================================================
Installing:
heartbeat x86_64 2.1.3-3.el5.centos extras 1.8 M
Installing for dependencies:
PyXML x86_64 0.8.4-4 base 1.1 M
heartbeat-pils x86_64 2.1.3-3.el5.centos extras 220 k
heartbeat-stonith x86_64 2.1.3-3.el5.centos extras 348 k
Transaction Summary
=============================================================================
出现错误：
error: %pre(heartbeat-2.1.3-3.el5.centos.x86_64) scriptlet failed, exit status 9
error: install: %pre scriptlet failed (2), skipping heartbeat-2.1.3-3.el5.centos
再运行下yum install heartbeat 解决
2. 配置 /etc/ha.d/ha.cf
logfile /var/log/ha-log
logfacility local0
keepalive 2 # 发送心跳间隔
deadtime 30 # 30s内没有检测到心跳，确定主机宕机，切换服务
warntime 10 # 10s内没有检测到心跳，产生警告信息
initdead 120 # 指定heartbeat首次启动时deadtime应该为多久。因为当heartbeat在引导时启动时，还需要给网络启动留出时间，所以这个时间要设得长一些。
udpport 694 # 心跳端口
ucast eth1 192.168.8.72 # 使用eth1网卡发送udp单播心跳
ucast eth1 192.168.8.73
auto_failback off # 主服务器恢复正常后是否需要再自动切换回来，一般都设为off，不切换
node db09 # 主服务器节点，bbs01这个值需要与uname -n保持一致
node db10 # 备用服务器节点，与uname -n保持一致
3. 配置 /etc/ha.d/authkeys
auth 2
#1 crc
2 sha1 HI!
#3 md5 Hello!
chmod 600 /etc/ha.d/authkeys
4. 配置 /etc/ha.d/haresources
db10 192.168.8.66 drbddisk::okooo Filesystem::/dev/drbd0::/home::ext3
db10：主服务器， 192.168.8.66：主备服务器对外提供服务的虚IP，主备切换时会随之转移，heartbeat自己控制的。
drbddisk::okooo ：heartbeat控制的drbd资源，okooo是drbd中我们创建的资源名称
Filesystem::/dev/drbd0::/home::ext3 ：heartbeat控制的文件系统资源，/dev/drbd0为drbd的磁盘分区 ；/home为挂载目录
5. 以上三种配置文件的内容，主备服务器需要保持一致。
6. 修改主备服务器的/etc/sudoers 文件。注释掉 Default requiretty 一行 #Default requiretty
7. 确定heartbeat 和 drbd开机自启动. drbd先于heartbeat启动 chkconfig --add heartbeat
8. 确定需要由heartbeat切换的服务drbd
9. 主备服务器上启动heartbeat： /etc/init.d/heartbeat start
三. 主备服务器上安装mysql1. 首先在主备服务器的 /home 分区上 安装 mysql, 以确保都能正常运行 mysql, 测试完可以删除
2. 确认后，在主服务器 /drbd 分区上 安装 mysql, 此时不必在从服务器上安装，drbd 已经同步过去, 并确认可以正常启动,手动执行 bin/mysqld_safe & 确认
四. 配置 heartbeat 管理 mysqld 资源(主从一致) vim /etc/ha.d/haresources
flare01 192.168.8.179 drbddisk::okooo Filesystem::/dev/drbd0::/drbd::ext3 mysqld
五. 启动heartbeat, 检查各项资源正常: ifconfig 查看 VIP , ps -ef |grep mysqld 查看 mysqld, cat /proc/drbd 查看 drbd 状态
六. 切换测试, 检查 VIP 是否飘移，备机接管后 drbd 挂载 mysql 进程 启动 是否正常
七. heartbeat 接管状态处理 heartbeat 切换后可能会存在 脑裂问题， 参考安装文档解决
八. crontab 增加mysql监控程序
九.数据库my.cnf 参数配置
db02库
[client]
socket = /home/okooo/apps/mysql/tmp/mysql.sock
port = 3306
[mysqld]
basedir = /home/okooo/apps/mysql
datadir = /home/okooo/apps/mysql/data
user = okooo
default-character-set=latin1
max_connections = 1024
max_connect_errors = 1024
max_user_connections = 900
open_files_limit = 4096
port = 3306
socket = /home/okooo/apps/mysql/tmp/mysql.sock
pid-file = /home/okooo/apps/mysql/tmp/mysql.pid
max_allowed_packet = 96M
server-id = 19281
master-user = okooo_rep
master-password = okooo_replication
replicate-ignore-db=mysql
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql/logs/logbin
relay-log = /home/okooo/apps/mysql/logs/relay
log-bin-index = /home/okooo/apps/mysql/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql/logs/relay.index
master-info-file = /home/okooo/apps/mysql/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql/logs/relay.info
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-queries-not-using-indexes = TRUE
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql/logs/slow.log
log-error = /home/okooo/apps/mysql/logs/error.log
external-locking = FALSE
skip-name-resolve
key_buffer_size = 512M
back_log = 1024
table_cache = 2048
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 2048
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 350M
wait_timeout=60
thread_concurrency = 8
thread_stack = 256K
tmp_table_size = 1G
max_tmp_tables = 256
max_heap_table_size = 1G
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 8M
myisam_repair_threads = 1
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 1G
innodb_log_buffer_size = 4M
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 12
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 30
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
db01 库
[client]
socket = /home/okooo/apps/mysql/tmp/mysql.sock
port = 3306
[mysqld]
basedir = /home/okooo/apps/mysql
datadir = /home/okooo/apps/mysql/data
user = okooo
default-character-set=latin1
max_connections = 2048
max_connect_errors = 1024
open_files_limit = 4096
port = 3306
socket = /home/okooo/apps/mysql/tmp/mysql.sock
pid-file = /home/okooo/apps/mysql/tmp/mysql.pid
max_allowed_packet = 48M
server-id = 59
replicate-ignore-db=mysql
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql/logs/logbin
relay-log = /home/okooo/apps/mysql/logs/relay
log-bin-index = /home/okooo/apps/mysql/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql/logs/relay.index
master-info-file = /home/okooo/apps/mysql/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql/logs/relay.info
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql/logs/slow.log
log-error = /home/okooo/apps/mysql/logs/error.log
external-locking = FALSE
skip-name-resolve
key_buffer_size = 4G
back_log = 1024
table_cache = 2048
sort_buffer_size = 16M
join_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 512
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 1G
wait_timeout=60
thread_concurrency = 16
thread_stack = 256K
tmp_table_size = 2G
max_tmp_tables = 256
max_heap_table_size = 96M
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 16M
myisam_repair_threads = 2
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 24G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 22
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
db07 Data 库
[client]
socket = /home/okooo/apps/mysql.Data/tmp/mysql.sock
port = 3310
[mysqld]
basedir = /home/okooo/apps/mysql.Data/
datadir = /home/okooo/apps/mysql.Data/data
user = okooo
default-character-set=latin1
max_connections = 1024
max_connect_errors = 1024
open_files_limit = 4096
port = 3310
socket = /home/okooo/apps/mysql.Data/tmp/mysql.3310.sock
pid-file = /home/okooo/apps/mysql.Data/tmp/mysql.3310.pid
max_allowed_packet = 96M
server-id = 283310
master-port = 3310
master-user = okooo_rep
master-password = okooo_replication
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql.Data/logs/logbin
relay-log = /home/okooo/apps/mysql.Data/logs/relay
log-bin-index = /home/okooo/apps/mysql.Data/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql.Data/logs/relay.index
master-info-file = /home/okooo/apps/mysql.Data/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql.Data/logs/relay.info
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql.Data/logs/slow.log
log-error = /home/okooo/apps/mysql.Data/logs/error.log
external-locking = FALSE
skip-name-resolve
key_buffer_size = 4G
back_log = 1024
table_cache = 2048
sort_buffer_size = 16M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 2048
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 256M
wait_timeout=60
thread_concurrency = 8
thread_stack = 256K
tmp_table_size = 2G
max_tmp_tables = 256
max_heap_table_size = 96M
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 8M
myisam_repair_threads = 1
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql.Data/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql.Data/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 14G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
db07 Statistic库
socket = /home/okooo/apps/mysql.Statistic/tmp/mysql.sock
port = 3308
[mysqld]
basedir = /home/okooo/apps/mysql.Statistic/
datadir = /home/okooo/apps/mysql.Statistic/data
user = okooo
default-character-set=latin1
max_connections = 1024
max_connect_errors = 1024
open_files_limit = 4096
port = 3308
socket = /home/okooo/apps/mysql.Statistic/tmp/mysql.3308.sock
pid-file = /home/okooo/apps/mysql.Statistic/tmp/mysql.3308.pid
max_allowed_packet = 96M
server-id = 283308
master-user = okooo_rep
master-password = okooo_replication
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql.Statistic/logs/logbin
relay-log = /home/okooo/apps/mysql.Statistic/logs/relay
log-bin-index = /home/okooo/apps/mysql.Statistic/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql.Statistic/logs/relay.index
master-info-file = /home/okooo/apps/mysql.Statistic/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql.Statistic/logs/relay.info
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql.Statistic/logs/slow.log
log-error = /home/okooo/apps/mysql.Statistic/logs/error.log
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
external-locking = FALSE
skip-name-resolve
key_buffer_size = 4G
back_log = 1024
table_cache = 2048
sort_buffer_size = 16M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 2048
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 256M
wait_timeout=60
thread_concurrency = 8
thread_stack = 256K
tmp_table_size = 2G
max_tmp_tables = 256
max_heap_table_size = 96M
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 8M
myisam_repair_threads = 1
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql.Statistic/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql.Statistic/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 6G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
db4,db05,db08 Data库
[client]
socket = /home/okooo/apps/mysql.Data/tmp/mysql.sock
port = 3310
[mysqld]
basedir = /home/okooo/apps/mysql.Data/
datadir = /home/okooo/apps/mysql.Data/data
user = okooo
default-character-set=latin1
max_connections = 1024
max_connect_errors = 1024
open_files_limit = 4096
port = 3310
socket = /home/okooo/apps/mysql.Data/tmp/mysql.3310.sock
pid-file = /home/okooo/apps/mysql.Data/tmp/mysql.3310.pid
max_allowed_packet = 96M
server-id = 263310
master-port = 3310
master-user = okooo_rep
master-password = okooo_replication
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql.Data/logs/logbin
relay-log = /home/okooo/apps/mysql.Data/logs/relay
log-bin-index = /home/okooo/apps/mysql.Data/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql.Data/logs/relay.index
master-info-file = /home/okooo/apps/mysql.Data/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql.Data/logs/relay.info
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql.Data/logs/slow.log
log-error = /home/okooo/apps/mysql.Data/logs/error.log
external-locking = FALSE
skip-name-resolve
key_buffer_size = 4G
back_log = 1024
table_cache = 2048
sort_buffer_size = 16M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 2048
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 512M
wait_timeout=60
thread_concurrency = 8
thread_stack = 256K
tmp_table_size = 2G
max_tmp_tables = 256
max_heap_table_size = 96M
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 8M
myisam_repair_threads = 1
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql.Data/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql.Data/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 12G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
db4,db05,db08 Statistic库
[client]
socket = /home/okooo/apps/mysql.Statistic/tmp/mysql.sock
port = 3308
[mysqld]
basedir = /home/okooo/apps/mysql.Statistic/
datadir = /home/okooo/apps/mysql.Statistic/data
user = okooo
default-character-set=latin1
max_connections = 1024
max_connect_errors = 1024
open_files_limit = 4096
port = 3308
socket = /home/okooo/apps/mysql.Statistic/tmp/mysql.3308.sock
pid-file = /home/okooo/apps/mysql.Statistic/tmp/mysql.3308.pid
max_allowed_packet = 96M
server-id = 263308
master-user = okooo_rep
master-password = okooo_replication
skip-slave-start
set-variable="slave-skip-errors=0,1007,1050,1053,1061,1062,1213,1158"
master-connect-retry = 10
master-retry-count = 100000000
log-slave-updates
slave_net_timeout = 20
log-bin = /home/okooo/apps/mysql.Statistic/logs/logbin
relay-log = /home/okooo/apps/mysql.Statistic/logs/relay
log-bin-index = /home/okooo/apps/mysql.Statistic/logs/logbin.index
relay-log-index = /home/okooo/apps/mysql.Statistic/logs/relay.index
master-info-file = /home/okooo/apps/mysql.Statistic/logs/master.info
relay-log-info-file = /home/okooo/apps/mysql.Statistic/logs/relay.info
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
net_retry_count = 100000000
log-short-format=FALSE
long_query_time=1
log-slow-admin-statements=TRUE
log-slow-queries=/home/okooo/apps/mysql.Statistic/logs/slow.log
log-error = /home/okooo/apps/mysql.Statistic/logs/error.log
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
external-locking = FALSE
skip-name-resolve
key_buffer_size = 6G
back_log = 1024
table_cache = 2048
sort_buffer_size = 16M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache_size = 2048
query_cache_limit = 8M
query_cache_min_res_unit = 3k
query_cache_size = 512M
wait_timeout=60
thread_concurrency = 8
thread_stack = 256K
tmp_table_size = 2G
max_tmp_tables = 256
max_heap_table_size = 96M
binlog_cache_size = 64M
max_binlog_size = 128M
max_binlog_cache_size = 1024M
max_relay_log_size = 256M
bulk_insert_buffer_size = 8M
myisam_repair_threads = 1
myisam-recover = DEFAULT
innodb_data_home_dir = /home/okooo/apps/mysql.Statistic/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/okooo/apps/mysql.Statistic/logs
innodb_file_per_table = 1
innodb_support_xa = 0
innodb_status_file = 1
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 1G
innodb_log_buffer_size = 1G
innodb_lock_wait_timeout = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = 'O_DIRECT'
set-variable="transaction-isolation=READ-COMMITTED"
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
default-table-type=InnoDB
[mysqldump]
quick
max_allowed_packet = 128M
十.my.cnf参数更改记录
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-9-26 13:46:32
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡