mysql多实例安装步骤以及解决Mysql多实例（同ip不同端口）...
mysql多实例安装步骤以及解决Mysql多实例（同ip不同端口）重启后登录不上1 mysql多实例安装和配置yum install ncurses-devel libaio-devel-y ##安装Mysql相关依赖包
rpm -qa ncurses-devel libaio-devel##检查
cat /etc/cache/yum.conf
keepcache=1 ##改为1，存为yum包
ll /var/cache/yum/ ##yum包保存目录
tar xf cmake-2.8.8.tar.gz ##安装cmake包，mysql编译软件
cd cmake-2.8.8
./configure
gmake
gmake install
useradd mysql -s /sbin/nologin -M
tar zxf mysql-5.5.32.tar.gz
cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32\ #安装路径
-DMYSQL_DATADIR=/application/mysql/data \ #数据文件存放位置
-DWITH_MYISAM_STORAGE_ENGINE=1 \ #支持MyIASM引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #支持InnoDB引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 \ #支持Memory引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \ #允许从本地导入数据
-DWITH_PARTITION_STORAGE_ENGINE=1\#安装支持数据库分区
-DEXTRA_CHARSETS=all \ #安装所有的字符集
-DDEFAULT_CHARSET=utf8 \ #默认字符
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_READLINE=1 \
-DWITH_DEBUG=0
make&& make install
ln -s /application/mysql-5.5.32//application/mysql
mkdir -p /data/{3306,3307}/data
tree /data
ls -l /application/mysql/support-files/my*.cnf##mysql配置文件
my-innodb-heavy-4G.cnf##my.cnf详细配置文件解释
vim /data/3306/my.cnf##创建mysql配置文件
vim /data/3307/my.cnf
3306 my.cnf
3307 my.cnf
port = 3306
socket = /data/3306/mysql.sock
no-auto-rehash
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /aplication/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
pid-file = /data/3306/mysql.pid
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
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
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
quick
max_allowed_packet = 2M
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid
##客户端
port= 3307
socket =/data/3307/mysql.sock
no-auto-rehash
##服务端
user = mysql
port = 3307
socket= /data/3307/mysql.sock
basedir = /aplication/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
pid-file =/data/3307/mysql.pid
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
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
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
quick
max_allowed_packet = 2M
log-error=/data/3307/mysql_3307.err##错误信息
pid-file=/data/3307/mysqld.pid
vim /data/3306/mysql ##mysql多实例启动文件
vim /data/3307/mysql
3306/mysql
3307/mysql
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="a13593029788"
CmdPath="/aplication/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf2>&1 >/dev/null&
else
printf "MySQL is running ...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S
/data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
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}\n"
esac
#/bin/sh
#init
port=3307
mysql_user="root"
mysql_pwd="a13593029788"
CmdPath="/aplication/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1>/dev/null &
else
printf "MySQL is running ...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sockshutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
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}\n"
esac
chown -R mysql.mysql /data
find /data -type f -name ”my.cnf” |xargs ls-l
find /data -type f -name ”mysql” |xargschmod 700 ##控制权限，除了mysql和root，其他看不了
ls -l /aplication/mysql/bin/mysqlecho ’exportPATH=/aplication/mysql/bin:$PATH’>>/etc/profilesource/etc/profileecho$PATHwhichmysql 或：ln -s /aplication/mysql/bin/*/usr/local/sbin/##通过软链接设置环境变量ls -s /usr/local/sbin/mysql*
初始化mysql数据：
cd /aplication/mysql/scripts/
./mysql_install_db --basedir=/aplication/mysql/--datadir=/data/3306/data/ --user=mysql
./mysql_install_db --basedir=/aplication/mysql/--datadir=/data/3307/data/ --user=mysql
有2个OK即成功。有error不能忽略。
/data/3306/mysql start##启动
/data/3307/mysql start
netstat -lntup |grep 330##查看启动进程
tree /data##查看data目录结构
排错：
grep log-error my.cnf|tail -1
tail -100 /data/3306/mysql_3306.err ##查看错误日志
2 mysql多实例登录登录：
mysqladmin -uroot password oldboy -S /data/3306/mysql.sock##给3306数据库创建账号密码
mysql -S /data/3306/mysql.sock##登录3306数据库
或 mysql -uroot-pa13593029788-S/data/3307/mysql.sock##带账号密码登录3307数据库（一定注意命令格式，否则容易登不上）
sed -n ’13p’ /data/3306/mysql
sed -e ’13 s#oldboy#oldboy123#g’/data/3306/mysql ##改密码
sed -e ’13 s#oldboy#oldboy456#g’/data/3307/mysql
/data/3306/mysql stop##停止数据库
ss -lntuo|grep 3306 ##查看进程是否停止
本地登陆，根据mysql.sock文件建立本地连接，确定具体登陆到那个实例。
远程登录，通过TCP端口port制定所需登录的mysql实例：端口port——例：mysql –uroot –pa13593029788 –h10.0.0.7 –P 3307，-P为端口参数，接具体实例端口。提前在10.0.0.7对root用户做授权。
3 增加一个Mysql实例mkdir –p /data/3308/data
\cp /data/3306/my.cnf/data/3308/
\cp /data/3306/mysql/data/3308/
sed –i ’s/3306/3308/g’ /data/3308/my.cnf
sed –i ’s/server-id=1/ server-id=8/g’/data/3308/my.cnf
sed –i ’s/3306/3308/g’ /data/3308/mysql
chown –R mysql:mysql /data/3308
chmod 700 /data/3308/mysql
cd /aplication/mysql/scripts
./mysql_install_db--basedir=/aplication/mysql/ --datadir=/data/3308/data/ --user=mysql
chown –R mysql:mysql /data/3308
/data/3308/mysql start
sleep 5
netstat –lntup|grep 3308
mysql –S /data/3308/mysql.sock
4 重启后多实例登录不上解决办法
service mysqld stop##mysqld占用3306端口，与多实例的3306冲突，先关掉。
cd /data/3306
rm -rf mysql.sock mysqld.pid##删掉.pid文件和.sock文件，重新生成
cd /data/3307rm -rf mysql.sock mysqld.pidcd /data/3308rm -rf mysql.sock mysqld.pid/data/3306/mysql start##再次开启mysql服务
/data/3307/mysql start
/data/3308/mysql start
netstat -lntup|grep 330##查看端口服务是否开启
若出现mysql的PID文件找不到，则杀掉相关进程。
# service mysqld status
ERROR! Multiple MySQL running but PID file could not be found (5209 4477 3710)
# kill 5209
# kill 4488
# kill 3710
# service mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.. SUCCESS!
# service mysqld stop
页:
[1]