CentOS安装MySQL多实例服务器--multi

最新推荐文章于 2024-11-15 22:11:31 发布

weixin_34124651

最新推荐文章于 2024-11-15 22:11:31 发布

阅读量93

点赞数

文章标签：数据库开发工具操作系统

原文链接：http://blog.51cto.com/daniang/852395

版权

操作系统：CentOS 5.6 x86_64

硬件环境：

CPU:Intel(R) Xeon(R) CPU E5620 @ 2.40GHz

内存：32G

硬盘：300G*2 10K

软件环境：

gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)

mysql 5.1.59.tar.gz

libunwind-1.0.tar.gz

google-perftools-1.8.3.tar.gz

上述软件假设存放在目录/root/soft下

实验目的：在CentOS下安装MySQL并实现多实例数据库服务器及innodb-plugin应用

步骤：

#创建日志和运行目录

mkdir -p /opt/logs /opt/run

chmod 777 /opt/run

#添加组和用户

groupadd mysql

useradd -g mysql -s /sbin/nologin mysql

#安装libunwind

tar zxf libunwind-1.0.tar.gz
cd libunwind-1.0
autoreconf -fi
CHOST="x86_64-redhat-linux" \
CFLAGS="-O3 -fPIC -fomit-frame-pointer \
-pipe -march=nocona -mfpmath=sse -m128bit-long-double \
-mmmx -msse -msse2 -maccumulate-outgoing-args -m64 \
-ftree-loop-linear -fprefetch-loop-arrays -fno-omit-frame-pointer \
-freg-struct-return -fgcse-sm -fgcse-las -frename-registers \
-fforce-addr -fivopts -ftree-vectorize -ftracer -frename-registers \
-minline-all-stringops -fbranch-target-load-optimize2" CXXFLAGS="${CFLAGS}" ./configure
make && make install

复制代码

#安装google-perftools

echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf

tar zxf google-perftools-1.8.3.tar.gz
cd google-perftools-1.8.3
CHOST="x86_64-redhat-linux" \
CFLAGS="-O3 -fomit-frame-pointer -pipe -march=nocona \
-mfpmath=sse -m128bit-long-double -mmmx -msse -msse2 \
-maccumulate-outgoing-args -m64 -ftree-loop-linear \
-fprefetch-loop-arrays -fno-omit-frame-pointer \
-freg-struct-return -fgcse-sm -fgcse-las \
-fforce-addr -fivopts -ftree-vectorize -ftracer \
-frename-registers -minline-all-stringops \
-fbranch-target-load-optimize2" \
CXXFLAGS="${CFLAGS} -DTCMALLOC_LARGE_PAGES" \
./configure \
--disable-heap-checker \
--disable-debugalloc \
--enable-frame-pointers
make && make install

复制代码

echo "/usr/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf

echo "/usr/lib64" >> /etc/ld.so.conf.d/usr_local_lib.conf

echo "/usr/local/lib64" >> /etc/ld.so.conf.d/usr_local_lib.conf

/sbin/ldconfig

#安装MySQL

#创建数据库数据存放目录

tar zxf mysql-5.1.59.tar.gz
cd mysql-5.1.59
CXX=gcc CHOST="x86_64-redhat-linux" 、
CFLAGS="-O3 -fomit-frame-pointer -pipe -march=nocona -mfpmath=sse \
-m128bit-long-double -mmmx -msse -msse2 -maccumulate-outgoing-args \
-m64 -ftree-loop-linear -fprefetch-loop-arrays \
-freg-struct-return -fgcse-sm -fgcse-las -fforce-addr \
-fivopts -ftree-vectorize -ftracer -frename-registers \
-minline-all-stringops -fno-exceptions -fno-omit-frame-pointer \
-fbranch-target-load-optimize2" CXXFLAGS="${CFLAGS}" \
LDFLAGS="-lrt -lunwind -ltcmalloc_minimal -lstdc++ " \
./configure \
--prefix=/opt/mysql \
--with-server-suffix=-greenshore \
--with-mysqld-user=mysql \
--with-plugins=federated,partition,innodb_plugin,myisam \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=gbk,utf8,ascii \
--with-big-tables \
--with-fast-mutexes \
--with-zlib-dir=bundled \
--with-readline \
--with-pthread \
--enable-assembler \
--enable-profiling \
--enable-local-infile \
--enable-thread-safe-client \
--without-embedded-server \
--without-geometry \
--without-debug \
--without-ndb-binlog
--without-ndb-debug
make && make install

复制代码

mkdir /opt/mysql/var

#添加mysql的目录到系统环境变量

echo "export PATH=$PATH:/opt/mysql/bin" >> /etc/profile

source /etc/profile //让环境变量生效

echo "/opt/mysql/lib/mysql" >> /etc/ld.so.conf

/sbin/ldconfig

ln -s /opt/mysql/lib/mysql /usr/lib64/mysql 创建库链接

ln -s /opt/mysql/include/mysql /usr/include/mysql 创建库链接

#创建mysql多实例启动脚本

vim /etc/init.d/rc.mysqlmulti

脚本内容：

#!/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=/opt/mysql
bindir=/opt/mysql/bin
user=mysql
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=/etc/my.cnf start $2
;;
'stop' )
"$mysqld_multi" --defaults-extra-file=/etc/my.cnf stop $2
;;
'report' )
"$mysqld_multi" --defaults-extra-file=/etc/my.cnf report $2
;;
'restart' )
"$mysqld_multi" --defaults-extra-file=/etc/my.cnf stop $2
"$mysqld_multi" --defaults-extra-file=/etc/my.cnf start $2
;;
*)
echo "Usage: $0 {start|stop|report|restart}" >&2
;;
esac

复制代码

#编辑/etc/my.cnf
vim /etc/my.cnf

输入一下内容

[mysqld_multi]
mysqld = /opt/mysql/bin/mysqld_safe
mysqladmin = /opt/mysql/bin/mysqladmin
user = root
password = VxAyENUSlAqWY2YTZx6G
[mysqld0001]
bind-address = 10.2.1.6
default-storage-engine = innodb
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
basedir = /opt/mysql
datadir = /opt/mysql/var/0001
ft_min_word_len = 4
user = mysql
## File
back_log = 500
open-files-limit = 102400
open-files = 4096
port = 3301
socket = /tmp/mysql0001.sock
pid-file = /opt/run/mysql0001.pid
skip-external-locking
skip-name-resolve
## Logging
log_error = /opt/mysql/var/0001/mysql-error.err
log_warnings
log_bin = /opt/mysql/var/0001/mysql-bin
expire_logs_days = 2
#log-slow-queries =/opt/mysql/var/0001/slowquery.log
#long_query_time = 1
max_binlog_size = 512M #max size for binlog before rolling
binlog_format = mixed
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 8M #default: 2M, larger may cause perf issues
read_buffer_size = 2M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 16M #default: 256K
join_buffer_size = 8M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 64M #global buffer
query_cache_limit = 2M #max query result size to put in cache
## Connections
max_connections = 4000 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30
max_allowed_packet = 64M #max size of incoming data to allow
net_buffer_length = 1024K
## Default Table Settings
sql_mode = NO_AUTO_CREATE_USER
## Table and TMP settings
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
tmp_table_size = 1G
tmpdir = /dev/shm
## Table cache settings
table_cache = 2048
table_open_cache = 2048
## Thread settings
thread_concurrency = 32
thread_cache_size = 100
## MyISAM Specific options
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
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
innodb_io_capacity = 400
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 16M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_table_locks = 0
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 32
innodb_commit_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
skip-innodb-doublewrite
## Binlog sync settings
sync_binlog = 0
## TX Isolation
transaction-isolation = REPEATABLE-READ
[mysqld0002]
bind-address = 10.2.1.6
default-storage-engine = innodb
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
basedir = /opt/mysql
datadir = /opt/mysql/var/0002
ft_min_word_len = 4
user = mysql
## File
back_log = 500
open-files-limit = 102400
open-files = 4096
port = 3302
socket = /tmp/mysql0002.sock
pid-file = /opt/run/mysql0002.pid
skip-external-locking
skip-name-resolve
## Logging
log_error = /opt/mysql/var/0002/mysql-error.err
log_warnings
log_bin = /opt/mysql/var/0002/mysql-bin
expire_logs_days = 2
#log-slow-queries =/opt/mysql/var/0002/slowquery.log
#long_query_time = 1
max_binlog_size = 512M #max size for binlog before rolling
binlog_format = mixed
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 8M #default: 2M, larger may cause perf issues
read_buffer_size = 2M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 16M #default: 256K
join_buffer_size = 8M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 64M #global buffer
query_cache_limit = 2M #max query result size to put in cache
## Connections
max_connections = 4000 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30
max_allowed_packet = 64M #max size of incoming data to allow
net_buffer_length = 1024K
## Default Table Settings
sql_mode = NO_AUTO_CREATE_USER
## Table and TMP settings
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
tmp_table_size = 1G
tmpdir = /dev/shm
## Table cache settings
table_cache = 2048
table_open_cache = 2048
## Thread settings
thread_concurrency = 32
thread_cache_size = 100
## MyISAM Specific options
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
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
innodb_io_capacity = 400
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 16M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_table_locks = 0
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 32
innodb_commit_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
skip-innodb-doublewrite
## Binlog sync settings
sync_binlog = 0
## TX Isolation
transaction-isolation = REPEATABLE-READ
[mysqld0003]
bind-address = 10.2.1.6
default-storage-engine = innodb
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
basedir = /opt/mysql
datadir = /opt/mysql/var/0003
ft_min_word_len = 4
user = mysql
## File
back_log = 500
open-files-limit = 102400
open-files = 4096
port = 3303
socket = /tmp/mysql0003.sock
pid-file = /opt/run/mysql0003.pid
skip-external-locking
skip-name-resolve
## Logging
log_error = /opt/mysql/var/0003/mysql-error.err
log_warnings
log_bin = /opt/mysql/var/0003/mysql-bin
expire_logs_days = 2
#log-slow-queries =/opt/mysql/var/0003/slowquery.log
#long_query_time = 1
max_binlog_size = 512M #max size for binlog before rolling
binlog_format = mixed
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 8M #default: 2M, larger may cause perf issues
read_buffer_size = 2M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 16M #default: 256K
join_buffer_size = 8M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 64M #global buffer
query_cache_limit = 2M #max query result size to put in cache
## Connections
max_connections = 4000 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30
max_allowed_packet = 64M #max size of incoming data to allow
net_buffer_length = 1024K
## Default Table Settings
sql_mode = NO_AUTO_CREATE_USER
## Table and TMP settings
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
tmp_table_size = 1G
tmpdir = /dev/shm
## Table cache settings
table_cache = 2048
table_open_cache = 2048
## Thread settings
thread_concurrency = 32
thread_cache_size = 100
## MyISAM Specific options
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
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
innodb_io_capacity = 400
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 16M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_table_locks = 0
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 32
innodb_commit_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
skip-innodb-doublewrite
## Binlog sync settings
sync_binlog = 0
## TX Isolation
transaction-isolation = REPEATABLE-READ
[mysqld0004]
bind-address = 10.2.1.6
default-storage-engine = innodb
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
basedir = /opt/mysql
datadir = /opt/mysql/var/0004
ft_min_word_len = 4
user = mysql
## File
back_log = 500
open-files-limit = 102400
open-files = 4096
port = 3304
socket = /tmp/mysql0004.sock
pid-file = /opt/run/mysql0004.pid
skip-external-locking
skip-name-resolve
## Logging
log_error = /opt/mysql/var/0004/mysql-error.err
log_warnings
log_bin = /opt/mysql/var/0004/mysql-bin
expire_logs_days = 2
#log-slow-queries =/opt/mysql/var/0004/slowquery.log
#long_query_time = 1
max_binlog_size = 512M #max size for binlog before rolling
binlog_format = mixed
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 8M #default: 2M, larger may cause perf issues
read_buffer_size = 2M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 16M #default: 256K
join_buffer_size = 8M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 64M #global buffer
query_cache_limit = 2M #max query result size to put in cache
## Connections
max_connections = 4000 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30
max_allowed_packet = 64M #max size of incoming data to allow
net_buffer_length = 1024K
## Default Table Settings
sql_mode = NO_AUTO_CREATE_USER
## Table and TMP settings
max_heap_table_size = 1G
bulk_insert_buffer_size = 1G
tmp_table_size = 1G
tmpdir = /dev/shm
## Table cache settings
table_cache = 2048
table_open_cache = 2048
## Thread settings
thread_concurrency = 32
thread_cache_size = 100
## MyISAM Specific options
key_buffer_size = 256M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
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
innodb_io_capacity = 400
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_log_file_size = 512M
innodb_log_files_in_group = 4
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 16M
innodb_status_file = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_table_locks = 0
innodb_log_buffer_size = 128M
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 32
innodb_commit_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
skip-innodb-doublewrite
## Binlog sync settings
sync_binlog = 0
## TX Isolation
transaction-isolation = REPEATABLE-READ
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 102400

复制代码

#执行如下命令用于关闭/etc/my.cnf跟innodb的参数

sed -i 's:^innodb:#innodb:' /etc/my.cnf

sed -i 's:skip-innodb-doublewrite:#skip-innodb-doublewrite:' /etc/my.cnf

sed -i 's:default-storage-engine = innodb:default-storage-engine = myisam:' /etc/my.cnf

#初始化数据库以及设置root密码

for (( i=1;i<=4;i++ ))
do
mkdir /opt/mysql/var/000$i
chown -R mysql.mysql /opt/mysql/var/000$i
echo "Inition DB 000$i"
/opt/mysql/bin/mysql_install_db --user=mysql --datadir=/opt/mysql/var/000$i > /dev/null 2>&1
echo "Start MySQL instance 000$i"
/etc/init.d/rc.mysqlmulti start 000$i
sleep 30
echo "Set root Password"
/opt/mysql/bin/mysql --user=root --password='' -S /tmp/mysql000$i.sock -e "grant all privileges on *.* to root@'localhost' identified by 'VxAyENUSlAqWY2YTZx6G';grant all privileges on *.* to root@'127.0.0.1' identified by '**********';"
/etc/init.d/rc.mysqlmulti stop 000$i
rm -rf /opt/mysql/var/000$i/ib*
done;