MySQL源码编译及使用单机多实例管理

本文的测试环境为 Ubuntu 14.04.2 LTS 64 bit + MySQL 5.6

下载源码并安装及初始化数据库

cd ~/Downloads

wget -c http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.26.tar.gz

tar -xvf mysql-5.6.26.tar.gz

cd mysql-5.6.26

sudo groupadd mysql

sudo useradd -r -g mysql mysql

sudo apt-get install cmake

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6 -DMYSQL_USER=mysql

sudo mkdir /usr/local/mysql-5.6

sudo make install

sudo chown mysql:mysql -R /usr/local/mysql-5.6

sudo mkdir /usr/local/mysql-data/3306 -p
sudo mkdir /usr/local/mysql-data/3307 -p

sudo chown mysql:mysql /usr/local/mysql-data/3306 -R
sudo chown mysql:mysql /usr/local/mysql-data/3307 -R

sudo su

su mysql

cd /usr/local/mysql-5.6

scripts/mysql_install_db --datadir=/usr/local/mysql-data/3306 --basedir=/usr/local/mysql-5.6 --user=mysql
scripts/mysql_install_db --datadir=/usr/local/mysql-data/3307 --basedir=/usr/local/mysql-5.6 --user=mysql

配置my.conf

文件位置： /usr/local/mysql-5.6/my.cnf

内容

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld_multi]
mysqld     = /usr/local/mysql-5.6/bin/mysqld_safe
mysqladmin = /usr/local/mysql-5.6/bin/mysqladmin
log        = /usr/local/mysql-5.6/log.err
user       = root
password   = yang

# The MySQL server
[mysqld1]
port       = 3306
socket     = /usr/local/mysql-data/3306/3306.socket
datadir    = /usr/local/mysql-data/3306
pid-file   = /usr/local/mysql-data/3306/3306.pid
log-error    = /usr/local/mysql-data/3306/3306.err
slow_query_log = 1
long_query_time = 0.3
slow_query_log_file = /usr/local/mysql-data/3306/query-slow.log
general_log = /usr/local/mysql-data/3306/general-log.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld2]
port       = 3307
socket     = /usr/local/mysql-data/3307/3307.socket
datadir    = /usr/local/mysql-data/3307
pid-file   = /usr/local/mysql-data/3307/3307.pid
log-error    = /usr/local/mysql-data/3307/3307.err
slow_query_log = 1
long_query_time = 0.3
slow_query_log_file = /usr/local/mysql-data/3307/query-slow.log
general_log = /usr/local/mysql-data/3307/general-log.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

启动各个实例

export PATH=/usr/local/mysql-5.6/bin:$PATH

查看各个实例状态

mysqld_multi --defaults-file=/usr/local/mysql-5.6/my.cnf report

启动某个实例

mysqld_multi --defaults-file=/usr/local/mysql-5.6/my.cnf start 1

这表示启动my.conf文件里[mysqld1]配置的实例

一次性启动多个实例

mysqld_multi --defaults-file=/usr/local/mysql-5.6/my.cnf start 1-2

这表示启动my.conf文件里的[mysqld1]和[mysqld2]

停止某个实例

mysqld_multi --defaults-file=/usr/local/mysql-5.6/my.cnf stop 1

一次性停止多个实例

mysqld_multi --defaults-file=/usr/local/mysql-5.6/my.cnf stop 1-2

修改默认密码

/usr/local/mysql-5.6/bin/mysqladmin -S /usr/local/mysql-data/3306/3306.socket -u root password 'yang'
/usr/local/mysql-5.6/bin/mysqladmin -S /usr/local/mysql-data/3307/3307.socket -u root password 'yang'

Contents