多配置文件部署mysql单机多实例

1.安装gcc-c++、ncurses依赖包

# yum install gcc-c++  ncurses-devel

2.安装cmake，用来编译mysql

# tar -xvf cmake-3.2.0-.tar.gz
# mv cmake-3.2.0 cmake
# mv cmake /tmp/
# cd /tmp/cmake/
# ./bootstrap
# make
# make install
# cmake --version

3.安装bison

# tar -xvf bison-3.0.tar.gz
# mv bison-3.0 bison
# mv bison /tmp
# cd /tmp/bison/
# ./cofigure
# make
# make install
# bison --version

4.创建mysql用户

# /usr/sbin/groupadd mysql
# useradd -s /sbin/nologin -g  mysql -M mysql

5.编译安装

配置

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DEXTRA_CHARSETS=all 
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk 
-DWITH_MYISAM_STORAGE_ENGINE=1 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DENABLED_LOCAL_INFILE=1 
-DMYSQL_TCP_PORT=3306 
-DMYSQL_USER=mysql 

生成可执行文件

# make

安装

# make install

6.创建多实例的数据文件目录

# mkdir -p /data/{3306,3307}/data/
# mkdir -p /data/{3306,3307}/log/
# tree /data
/data                            #多实例的根目录
├── 3306                         #3306实例的根目录
│   ├── data                     #3306实例的数据目录
│   └── log                      #3306实例的日志目录
│   └── tmp                      #3306实例的临时文件目录
└── 3307                         #3307实例的根目录
    ├── data                     #3307实例的数据目录
    └── log                      #3307实例的日志目录
    └── tmp                      #3307实例的临时文件目录

data  - 存放数据文件：数据，索引，二进制日志（bin log），重放日志（replay log）。
log    - 存放日志文件：慢查询日志，错误日志。
tmp  - 存放临时文件：进程ID文件。

7.配置多实例的配置文件
为每个实例建立一个配置文件

# vi /data/3306/my.cnf
[client]
default-character-set = utf8
port = 3306
socket = /data/3306/tmp/mysql.sock

[mysqld]
server-id = 1
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
port        = 3306
socket      = /data/3306/tmp/mysql.sock
datadir     = /data/3306/data/
log-error   = /data/3306/log/mysql.err
pid-file     = /data/3306/tmp/mysql.pid
log-bin     = /data/3306/data/mysql-bin
relay_log     = /data/3306/data/relay-bin
slow_query_log_file = /data/3306/log/slowquery.log
general_log_file     = /data/3306/log/general.log

skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
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
binlog_format = mixed
slow_query_log = 1
long_query_time = 1
general_log = off

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# vi /data/3307/my.cnf
[client]
default-character-set = utf8
port = 3307
socket = /data/3307/tmp/mysql.sock

[mysqld]
server-id = 2
collation-server = utf8_unicode_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
port        = 3307
socket      = /data/3307/tmp/mysql.sock
datadir     = /data/3307/data/
log-error   = /data/3307/log/mysql.err
pid-file     = /data/3307/tmp/mysql.pid
log-bin     = /data/3307/data/mysql-bin
relay_log     = /data/3307/data/relay-bin
slow_query_log_file = /data/3307/log/slowquery.log
general_log_file     = /data/3307/log/general.log

skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
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
binlog_format = mixed
slow_query_log = 1
long_query_time = 1
general_log = off

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

8.配置mysql命令的环境变量

方法一：

# echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile

# source /etc/profile

方法二：
把/usr/local/mysql/bin/下面的命令拷贝到全局系统命令路径/usr/local/sbin/下，或者做个link

9.初始化数据库,创建基础的数据库文件

# cd /usr/local/mysql/scripts/

#./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql

#./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql

10.配置多实例的文件权限
授权mysql用户和组管理多实例目录/data

# chown -R mysql.mysql /data

11.启动

# mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &

# mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &

12.修改的root用户的密码并且限制只能本机登陆

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -h 'localhost' password 'xxxx'

# mysqladmin -S /data/3307/tmp/mysql.sock -u root -h 'localhost' password 'xxxx'

13.使用root用户登陆

# mysql -S  /data/3306/tmp/mysql.sock -u root -p

# mysql -S  /data/3307/tmp/mysql.sock -u root -p

14.停止实例

# mysqladmin -S /data/3306/tmp/mysql.sock -u root -p shutdown

# mysqladmin -S /data/3307/tmp/mysql.sock -u root -p shutdown