单机MySQL多实例配置运行

一、解压5包

二.添加用户及用户组
# groupadd 
# useradd -g  -s /sbin/nologin -M 
# passwd 

./configure –prefix=/usr/local/  –with-charset=utf8 –with-unix-socket-path=/tmp/.sock  –with-extra charsets=all –enable-assembler –with-ssl  –with-plugins=all –with-embedded-server  –with-big-tables –with-readline  –enable-thread-safe-client  –with-d-user=  

三、更改目录权限
chown -R .  /usr/local/

四、新建多实例数据存放位置
mkdir -p /data/
mkdir /data//7727
mkdir /data//7728
mkdir /data//7729
chown -R .  /data

五、创建配置文件my.cnf及添加下面内容（路径上负权限）
cp /usr/local//share//my-medium.cnf /etc/my.cnf  
vi /etc/my.cnf  

[d_multi]
d = /usr/local//bin/d_safe
admin = /usr/local//bin/admin

[d1]
port    = 7727
socket    = /tmp/7727.sock
pid-file = /data//7727/7727.pid
datadir = /data//7727
log-error = /data//7727/7727.err
default-character-set=utf8

[d2]
port    = 7728
socket    = /tmp/7728.sock
pid-file = /data//7728/7728.pid
datadir = /data//7728
log-error = /data//7728/7728.err

[d3]
port    = 7729
socket    = /tmp/7729.sock
pid-file = /data//7729/7729.pid
datadir = /data//7729
log-error = /data//7729/7729.err

六、初始化

/usr/local//bin/_install_db –datadir=/data//7727 –user=

/usr/local//bin/_install_db –datadir=/data//7728 –user=

/usr/local//bin/_install_db –datadir=/data//7729 –user=

chown -R : /data/*

七、原因：由于你安装的 bin 路径不在默认的搜索path中，导致在启动时无法正确的传送参数给/usr/local//libexec/d

解决方法：
export PATH=/usr/local//bin:$PATH
重启系统使系统变量生效

八、启动

/usr/local//bin/d_multi –config-file=/etc/my.cnf start 1
/usr/local//bin/d_multi –config-file=/etc/my.cnf start 2
/usr/local//bin/d_multi –config-file=/etc/my.cnf start 3

 

九、验证。#netstat -alpn|grep 77
 -u root -h localhost –S /tmp/7727.sock
 -u root -h localhost –S /tmp/7728.sock
 -u root -h localhost –S /tmp/7729.sock
# -u root -h 127.0.0.1 -P 7727
连上后可以看看datadir是不是对的：> show variables like '%datadir%'

六. 实现自动启动添加到/etc/rc.local：
[root@localhost local]# vi /etc/rc.local
/usr/local//bin/d_multi  –config-file=/usr/local/my_multi.cnf start 1,2，3

启动可能出现如下报错信息：

WARNING: my_print_defaults command not found.
Please make sure you have this command available and
in your path. The command is available from the latest
 distribution.
WARNING: –config-file is deprecated and will be removed
in  5.6.  Please use –defaults-extra-file instead
ABORT: Can't find command 'my_print_defaults'.
This command is available from the latest 
distribution. Please make sure you have the command
in your PATH.

原因：由于你安装的 bin 路径不在默认的搜索path中，导致在启动时无法正确的传送参数给/usr/local//libexec/d

解决方法：

echo "PATH=/usr/local//bin:\$PATH" >> /etc/profile

重启系统使系统变量生效

一、基础，很重要

每个实例应该在自己的datadir下配置my.cnf文件。
首先说下配置文件的查找顺序
第一搜，首先读取/etc/my.cnf
第二搜，$datadir/my.cnf，在data目录下寻找此配置文件，每个实例应该在自己的datadir下配置my.cnf文件。
第三搜，defaultfile=/path/my.cnf 通常写在命令行上，d_safe defaultfile=/tmp/my.cnf &等执行。
第四搜，~/my.cnf 当前用户下的配置文件。

 

 

下面是my.cnf配置例子
[zd@zjxwqm12 ~]$ more /etc/my.cnf
# Example  config file for medium systems.
#
# This is for a system with little memory (32M – 64M) where  plays
# an important part, or systems up to 128M where  is used together with
# other programs (such as a web server)
#
#  programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev..com/doc//en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "–help" option.

# The following options will be passed to all  clients
[client]
#password       = your_password
port            = 3306
#socket         = /var/lib//.sock

# Here follows entries for some specific programs

# The  server
[d]
#port           = 3306
#socket         = /var/lib//.sock
#skip-locking
character_set_server = utf8
skip-external-locking
skip-name-resolve
federated
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

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to d run on the same host.
# All interaction with d must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render d useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=-bin

# binary logging format – mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 – 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
#    MASTER_USER=, MASTER_PASSWORD= ;
#
#    where you replace , , by quoted strings and
#     by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave – required
#master-host     =  
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user     =  
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password =  
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port     =  
#
# binary logging – not required for slaves, but recommended
#log-bin=-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[d_multi]
d          = /usr/bin/d_safe
admin      = /usr/bin/admin
user            = multi
password        = multi
log             = /var/lib//log-multi.err

[d1]
#user           = sms
port            = 7306
socket          = /tmp/1.sock
datadir         = /var/lib//1
pid_file        = /var/run//d1.pid
#log-err         = /var/log//1/zjxwqm12.err
default-character-set=utf8

[d2]
#user            = sms
port            = 7307
socket          = /tmp/2.sock
datadir         = /var/lib//2
pid_file        = /var/run//d2.pid
#log-err         = /var/log//2/zjxwqm12.err

[dump]
quick
max_allowed_packet = 16M

[]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

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

[hotcopy]
interactive-timeout

来源：