2016-06-23

单启动多个mysql实例mysqld_multi配置

Web
Views: 13219 | Add Comments

1. 创建 mysqld_multi.conf 配置

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe  
mysqladmin=/usr/bin/mysqladmin 
user=AAA
password=BBB


[mysqld3306]
port=3306
datadir=/var/lib/mysql3306
socket=/var/lib/mysql3306/mysql.sock
pid-file=/var/lib/mysql3306/mysqld.pid
log-error=/var/log/mysqld_3306.log
user=mysql
symbolic-links=0
character-set-server = utf8
skip-name-resolve

server-id=123
log-bin=mysql-bin
log-slave-updates
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
master-connect-retry=10
max_connections=1000

sort_buffer_size = 300M
join_buffer_size = 300M
key_buffer_size  = 300M
innodb_buffer_pool_size = 4000M
query_cache_size=200M

注意, 要配置 user 和 password, 一会将要创建一个帐号用于 mysqld_multi.

2. 初始化空数据库

sudo mysql_install_db --datadir=/var/lib/mysql3306 --user=mysql

3. 启动实例

/etc/init.d/mysqld_multi start 3306

4. 修改 root 密码

修改 root 密码要用到 /usr/bin/mysql_secure_installation 脚本, 但这个脚本默认是通过 /var/run/mysql.sock 本地连接. 所以不对, 你需要修改它.

make_config() {
    echo "# mysql_secure_installation config file" >$config
    echo "[mysql]" >>$config
    echo "user=root" >>$config
    echo "host=127.0.0.1" >>$config
    echo "port=3306" >>$config
    esc_pass=`basic_single_escape "$rootpass"`
    echo "password='$esc_pass'" >>$config
    #sed 's,^,> ,' < $config  # Debugging
}

4. 创建管理帐号

GRANT SHUTDOWN ON *.* TO 'AAA'@'localhost' IDENTIFIED BY 'BBB';

5. 启动脚本

#!/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
#
. /etc/rc.d/init.d/functions
. /etc/sysconfig/network
basedir=/usr
bindir=/usr/bin

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

if [ ! -z "$2" ];then
	prgname="Server $2"
else
	prgname="All Servers"
fi

start(){
	"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf start $1
	ret=$?
	if [ $ret -eq 0 ];then
		action $"Starting $prgname: " /bin/true
	else
		action $"Starting $prgname: " /bin/false
	fi
}

stop(){
	"$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf stop $1
	ret=$?
	if [ $ret -eq 0 ];then
		action $"Stopping $prgname: " /bin/true
	else
		action $"Stopping $prgname: " /bin/false
	fi
}

case "$1" in
    'start' )
        start $2
        ;;
    'stop' )
        stop $2
        ;;
    'report' )
        "$mysqld_multi" --defaults-extra-file=/etc/my_multi.cnf report $2
        ;;
    'restart' )
		stop $2
		sleep 3
		start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|report|restart}" >&2
        ;;
esac

在管理的时候, 如果你想单独操作某个实例, 则这样操作:

sudo /etc/init.d/mysqld_multi restart 3306

注意, 参数不用带配置文件中的 mysqld 前缀.

Related posts:

  1. MySQL 数据库双主配置
  2. Nginx + PHP 配置和启动脚本
  3. Linux下编译安装Apache/Nginx/Lighttpd+PHP+MySQL
  4. MySQL binlog查看和清理
  5. WordPress分页代码
Posted by ideawu at 2016-06-23 16:27:32

Leave a Comment