mysql登陆不同的实例_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.cnf3306   my.cnf3307   my.cnf

[client]

port   = 3306

socket = /data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

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

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3306/mysql_3306.err

pid-file=/data/3306/mysqld.pid[client]    ##客户端

port  = 3307

socket =  /data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]    ##服务端

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

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3307/mysql_3307.err  ##错误信息

pid-file=/data/3307/mysqld.pid

vim /data/3306/mysql   ##mysql多实例启动文件

vim /data/3307/mysql3306/mysql3307/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.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.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.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

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/mysql

echo ’exportPATH=/aplication/mysql/bin:$PATH’  >>/etc/profile

source/etc/profile

echo$PATH

whichmysql

或：

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/3307

rm -rf mysql.sock mysqld.pid

cd /data/3308

rm -rf mysql.sock mysqld.pid

/data/3306/mysql start  ##再次开启mysql服务

/data/3307/mysql start

/data/3308/mysql start

netstat -lntup|grep 330  ##查看端口服务是否开启

若出现mysql的PID文件找不到，则杀掉相关进程。

[root@leslie data]# service mysqld status

ERROR! Multiple MySQL running but PID file could not be found (5209 4477 3710)

[root@leslie data]# kill 5209

[root@leslie data]# kill 4488

[root@leslie data]# kill 3710

[root@leslie data]# service mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.. SUCCESS!

[root@leslie 3306]# service mysqld stop