首页 > 代码库 > MYSQL的单机多实例部署
MYSQL的单机多实例部署
Centos6.6安装并配置单机多实例的MYSQL数据库
本文介绍安装单机多实例的MYSQL数据库的环境如下：
系统平台环境：Centos6.6
Mysql软件包：Mysql-5.5.32.tar.gz
一：安装MYSQL需要的依赖包和编译软件
#yum install ncurses-devel libaio-devel -y
二：安装编译MYSQL需要的软件（mysql5.2版本以后都需要使用cmake来编译安装MYSQL)
#rpm -ivh cmake-2.8.12.2-4.el6.x86_64(光盘中会自带，也可自行编译安装）
如果是自行编译安装cmake可以#which cmake查看到全路径，以便后面编译安装MYSQL
三：开始安装MYSQL数据库
1：创建mysql用户账号
# useradd -s /sbin/nologin -M mysql && id mysql
2: 获取MYSQL的源代码包(如连接失效可自行找到相应链接）
# wget http://dev.mysql.com/downloads/mysql
3：采用编译方式安装MYSQL
# tar xf mysql-5.5.32.tar.gz
# cd mysql-5.5.32
# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 - DMYSQL_DATADIR=/application/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
# make && make install
# ln -s /application/mysql-5.5.32/ /application/mysql
至此MYSQL-5.5.32的源代码包采用cmake方式安装就算成功了！！
既然是配置安装单机多实例数据库，那么我们接下来要做的就是为每个实例创建必须的数据文件,配置文件.以及启动文件。
四：实例创建必须的数据文件,配置文件.以及启动文件。
1:为每个实例创建数据目录
#mkdir -p /data/{3306,3307}/data
2:为每个实例创建配置文件（ls -l /application/mysql/suport-files/my*.cnf 数据库默认提供的多个 配置文件模板，用户可根据服务器硬件配置选择相应的模板)
#vim /data/3306/my.cnf (配置实例如下 #为注释行）
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
port = 3306
user = mysql
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
back_log = 100
max_connections = 800
max_connect_errors = 100
table_open_cache = 2048
external-locking = FALSE
binlog_cache_size = 1M
max_heap_table_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 8
query_cache_size = 2M
query_cache_limit = 2M
#ft_min_word_len = 4
#default-storage-engine = InnoDB
thread_stack = 192K
#transaction_isolation = REPEATABLE-READ
tmp_table_size = 2M
#binlog_format=mixed
#slow_query_log
long_query_time = 2
pid-file = /data/3306/mysqld.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
max_binlog_cache_size = 1M
max_binlog_size = 2M
key_buffer_size =16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors =1032,1062
replicate-ignore-db = mysql
bulk_insert_buffer_size = 1M
server-id = 1
#log-bin = /data/3306/logbin/mysql-bin
#log-bin-index = /data/3306/logbin/log-bin.index
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 8
#innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 24M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 5M
[mysqld_safe]
log-error=/data/3306/mysql_niutianwen3306.err
pid-file=/data/3306/mysqld.pid
#cp /data/3306/my.cnf /data/3307/my.cnf
#sed ‘s/3306/3307/g‘ /data/3307/my.cnf
3:创建MYSQL多实例的启动文件
#vim /data/3306/mysqld(启动脚本配置实例如下，供参考）
#!/bin/bash
#this scripts is created by niutianwen at 2016-10-20
#site :www.niutianwen.com
##############################
port=3306
mysql_user="root"
mysql_pwd="niutianwen" #<==要与数据库密码一致>
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
txtfile="/data/${port}/txt"
declare -i value
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MYSQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
echo > ${txtfile}
#echo ${txtfile}
#netstat -tnlp | grep ":$port " > $txtfile
#value=http://www.mamicode.com/`cat $txtfile|awk ‘{print $4}‘|awk -F":" ‘{print $2}‘`
sleep 10
value=http://www.mamicode.com/`netstat -tnlp | grep":$port " | awk ‘{print $4}‘ | awk -F‘:‘ ‘{print $NF}‘`
#echo $value
if [ "${value}" -eq "${port}" ];then
printf "MYSQL is up...!\n"
else
printf "MYSQL is not startup...!\n"
fi
else
printf "MYSQL is runing...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MYSQL is stopped...\n"
exit
else
printf "Stoping MYSQL...\n"
${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown && echo "MYSQL is down...!"
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting NYSQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage:/data/${port}/mysql {start|stop|restart}\n"
esac
# cp /data/3306/mysqld /data/3307/mysqld
# sed ‘s/3306/3307/g‘ /data/3307/mysqld
至此两个实例的数据文件，配置文件，启动文件都已完成配置。
五：配置MYSQL多实例的文件权限
1:授权mysql用户管理整个多实例的根目录/data
#chown -R mysql.mysql /data
2:因为启动文件中有数据库的密码信息，因此应设置700权限最佳
#find /data -name mysqld | xargs chmod 700
六：将Mysql命令加入全局路径，方便使用。
# echo ‘export PATH=/application/mysql/bin:$PATH‘ >>/etc/profile
# source /etc/profile (直接生效）
# echo $PATH （查看配置是否成功）
七：初始化MYSQL多实例的数据库文件（初始化数据库的实质就是创建基础的数据库的库文件）
# cd /application/mysql/scripts
# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
八：启动MYSQL多实例数据库
# /data/3306/mysqld start(stop|restart)
九：配置MYSQL数据库开机自启动
# echo "#mysql multi Instances" >> /etc/rc.local
# echo "/data/3306/mysql start" >> /etc/rc.local
# echo "/data/3307/mysql start" >> /etc/rc.local
十：登陆测试以及安全配置(为mysql设置密码登陆）
# mysql -S /data/3306/mysql.sock
# mysqladmin -uroot -S /data/3306/mysql.sock password ‘niutianwen‘
至此单机MYSQL双实例的配置已经完成！如要再增加MYSQL实例上去只要重复上述过程即可！谢谢大家。专注linux之路让大家不在孤单！
本文出自 “专注Linux技术交流” 博客，请务必保留此出处http://niutianwen.blog.51cto.com/10716187/1865228
MYSQL的单机多实例部署