MySQL單機多實例的配置筆記
- 2019 年 10 月 5 日
- 筆記
網上的大部分都是mysqld_multi方式啟動的多實例。
以前在老男孩老師的影片里看到的另一種方法，如下。
參考：http://www.ilanni.com/?p=8020
環境
CentOS6.7x86_64【2.6.32-573.el6.x86_64】
mysql-5.6.30通用二進位安裝包
創建mysql的獨立配置文件目錄
mkdir /data/{3306,3307} -pv
建立賬戶
groupadd mysql
useradd -s /sbin/nologin -g mysql -Mmysql
創建mysql的數據目錄
mkdir -p /data/{3306,3307}/data
安裝Mysql及準備配置文件
tar xf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz-C /usr/local
cd /usr/local
ln -s mysql-5.6.30-linux-glibc2.5-x86_64mysql
chown mysql.mysql mysql/ -R
cp /usr/local/mysql/support-files/my-default.cnf /data/3306/my.cnf
修改/data/3306/my.cnf
[client]
user = root
socket =/data/3306/mysql.sock
port = 3306
[mysql]
no-auto-rehash
default_character_set = utf8
prompt = [d] >
[mysqld]
# Basic
port = 3306
socket =/data/3306/mysql.sock
basedir =/usr/local/mysql
datadir =/data/3306/data
user = mysql
skip-external-locking
skip_name_resolve = ON
character-set-server = utf8
key_buffer_size = 16M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_allowed_packet = 50M
max_connections = 1000
thread_cache_size = 80
connect_timeout = 200
wait_timeout = 100
interactive_timeout = 100
server_id = 1
# query cache
query_cache_type=OFF
query_cache_size=0
# logs
slow_query_log=ON
slow_query_log_file =/data/3306/data/localhost-slow.log
long_query_time = 5
log_error = /data/3306/data/mysql.err
log_warnings = 2
# binlogs
log-bin=mysql-bin
binlog_format=mixed
expire_logs_days = 10
binlog_cache_size=2M
# InnoDB Optimize
innodb_file_per_table = ON
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_buffer_pool_dump_at_shutdown= ON
innodb_buffer_pool_load_at_startup= ON
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size =128M
innodb_data_home_dir = /data/3306/data
innodb_data_file_path =ibdata1:10M:autoextend
innodb_log_group_home_dir= /data/3306/data
innodb_log_file_size = 512M
innodb_log_buffer_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqld_safe]
log-error=/data/3306/3306.err
pid-file=/data/3306/3306.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
修改/data/3307/my.cnf
cp /data/3306/my.cnf /data/3307/my.cnf
sed -i 's#3306#3307#g' /data/3307/my.cnf
初始化mysql多實例
/usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/data/3306/data –user=mysql
/usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql –datadir=/data/3307/data –user=mysql
chown mysql.mysql/data/{3306,3307} -R
啟動mysql多實例
/usr/local/mysql/bin/mysqld_safe–defaults-file=/data/3306/my.cnf &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/data/3307/my.cnf&
ps aux |grep mysqld
ss -lnt|egrep "3306|3307"
配置啟動腳本
vim /etc/init.d/mysqld_3306
#!/bin/sh
# chkconfig: 345 80 15
# description: A very fast andreliable SQL database engine.
#init
port=3306
mysql_user="root"
mysql_pwd=""
CmdPath="/usr/local/mysql/bin"
#startup function
function_start_mysql()
{
printf "Starting MySQL…n"
/bin/sh ${CmdPath}/mysqld_safe –defaults-file=/data/${port}/my.cnf2>&1 > /dev/null &
}
#stop function
function_stop_mysql()
{
printf "Stoping MySQL…n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/data/${port}/mysql.sock shutdown
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL…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
vim /etc/init.d/mysqld_3307
#!/bin/sh
# chkconfig: 345 81 16
# description: A very fast andreliable SQL database engine.
#init
port=3307
mysql_user="root"
mysql_pwd=""
CmdPath="/usr/local/mysql/bin"
#startup function
function_start_mysql()
{
printf "Starting MySQL…n"
/bin/sh ${CmdPath}/mysqld_safe –defaults-file=/data/${port}/my.cnf2>&1 > /dev/null &
}
#stop function
function_stop_mysql()
{
printf "Stoping MySQL…n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/data/${port}/mysql.sock shutdown
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL…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
設置開機自啟動
chmod +x /etc/init.d/mysqld_3306
chmod +x /etc/init.d/mysqld_3307
chkconfig –add mysqld_3306
chkconfig –add mysqld_3307
登錄測試
mysql -uroot -S /data/3306/mysql.sock
mysql -uroot -S /data/3307/mysql.sock
都是可以登錄的，後面和普通的資料庫一樣操作就行了。
