最好不要用最新版的mysql，之前用了5.6.36安装一直有bug,后来换了5.5.32
安装mysql多实例的前提是先将mysql的基础安装完毕，只需到make && make install。
1、mysql多实例的作用与问题
1）有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务
2）节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立提供服务，而且，需要主从同步等技术时，多实例就再好不过了
3）资源互相抢占问题
当某个服务实例并发很高或者有慢查询时，整个实例会消耗更多的内存、cpu、磁盘、io资源，导致服务器上的其他的实例提供服务的质量下降。
2、多实例的应用场景
1）资金紧张型公司的选择
2）并发访问不是很大的业务
3）门户网站应用mysql多实例
3、多实例配置
1）创建实例目录
mkdir -p /data/{3306,3307}/data
[root@promote ~]# tree /data
/data 总的多实例根目录
├── 3306 3306实例的目录
│ └── data 3306实例的数据文件目录
└── 3307
└── data
想创建几个实例就创建一个目录，每个的实例的配置文件都是独立的配置文件
2）权限更改
chown -R mysql.mysql /data
3）复制配置文件
[root@promote ~]# cp data/3306/my.cnf /data/3306 [root@promote ~]# cp data/3307/my.cnf /data/3307
[root@promote ~]# cp data/3306/mysql /data/3306 [root@promote ~]# cp data/3307/mysql /data/3307
让mysql脚本可以执行 find /data -type f -name "mysql"|xargs chmod 700
4）更改环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/etc/profile source /etc/profile
5）多实例启动文件启动mysql服务
初始化数据库 ./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3306/data/ --user=mysql ./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql
启动实例数据库 /data/3306/mysql start /data/3307/mysql start
查看：netstat -lntup|grep 330
6)登录数据库
登录mysql实例 3306，-S /data/3306/mysql.sock 用于区别登录不同实例 mysql -S /data/3306/mysql.sock mysql -S /data/3307/mysql.sock
重启对应的实例数据库 /data/3306/mysql stop /data/3307/mysql start
设置mysql不同实例登录密码 [root@MySQL scripts]# mysqladmin -uroot password oldboy123 -S /data/3306/mysql.sock [root@MySQL scripts]# mysqladmin -uroot password oldboy456 -S /data/3307/mysql.sock
带密码登录不同实例数据库方法 mysql -uroot -poldboy456 -S /data/3307/mysql.sock mysql -uroot -poldboy123 -S /data/3306/mysql.sock
关于多实例MySQL登录问题小结 1、多实例本地登录MySQL 多实例本地登录一般通过socket文件来指定具体登录到那个实例，此文件具体位置是在mysql编译过程或者my.cnf文件里指定的。在本地登录数据库时，登录程序通过socket文件来判断登录的是那个数据库实例 2、远程连接登录MySQL多实例 远程登录MySQl多实例其中的一个实例，通过TCP端口（port）来指定所要登录的MySQL实例，此端口的配置是在mysql配置文件my.cnf中指定的 端口 port 例如：mysql -uoldboy -p'oldboy' -h 192.168.10.129 -P 3307 #-P为端口参数
配置文件：
3306my.cnf
[client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 #log_long_format #log-error = /data/3306/error.log #log-slow-queries = /data/3306/slow.log pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_oldboy3306.err pid-file=/data/3306/mysqld.pid
3306mysql：
#init
port=3306
mysql_user="root"
mysql_pwd="oldboy"
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...
"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...
"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...
"
exit
else
printf "Stoping MySQL...
"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL...
"
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}
"
esac
3307my.cnf
[client] port = 3307 socket = /data/3307/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /usr/local/mysql datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #default_table_type = InnoDB thread_stack = 192K #transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M #long_query_time = 1 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/3307/slow.log pid-file = /data/3307/mysql.pid #log-bin = /data/3307/mysql-bin relay-log = /data/3307/relay-bin relay-log-info-file = /data/3307/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M #myisam_sort_buffer_size = 1M #myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G #myisam_repair_threads = 1 #myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3307/mysql_oldboy3307.err pid-file=/data/3307/mysqld.pid
3307mysql
#init
port=3307
mysql_user="root"
mysql_pwd="oldboy"
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...
"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...
"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...
"
exit
else
printf "Stoping MySQL...
"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL...
"
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}
"
esac
一些脚本故障的解决，如果此时想要登录进数据库，进不去，开启mysql的时候，提示mysql is running……，需要删除 rm -rf /data/3306mysql.sock /data/3306/*.pid
然后再开启，/data/3306/mysql start,然后登录