mysql多實例

一般情況下是1-4個實例，1-2個居多

一、安裝環(huán)境

二、多實例添加（安裝mysql）

三、配置文件的添加

四、啟動腳本的添加

五、文件權(quán)限及所屬

六、配置全局路徑

七、初始化數(shù)據(jù)庫

八、mysql啟動、關(guān)閉、登錄

一、安裝環(huán)境

CentOS?Linux?release?7.2.1511

安裝目錄：/application/mysql

安裝版本：mysql-5.7.9

二、實例的添加（安裝mysql）

見博文mysql安裝?https://blog.51cto.com/13006632/2457769

mkdir?/data/{3306,3307}/data?-p

三、配置文件my.cnf的添加

[client]
port?		=?3306????????????????????????#實例端口，另一個為3307
socket		=?/data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user?=?mysql
port?=?3306
socket?=/data/3306/mysql.sock
basedir?=?/application/mysql
datadir?=?/data/3306/data
open_files_limit?=?1024
back_log?=?600
max_connections?=?800
max_connect_errors?=?3000
table_open_cache?=?614
external-locking?=?FALSE
max_allowed_packet?=?8M
sort_buffer_size?=?1M
join_buffer_size?=?1M
thread_cache_size?=?100
query_cache_size?=?2M
query_cache_limit?=?1M
#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_extra_sort_file_size?=?10G?
#uyisam_repair_threads?=?1?
#yisam_recover
lower_case_table_names?=?1
skip-name-resolve
slave-skip-errors?=?1032,1062
replicate-ignore-db?=?mysql
server-id?=?8?
innodb_buffer_pool_size?=?32M
innodb_data_file_path?=?ibdata1:128M:autoextend?
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

四、mysql腳本的添加

#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="123456"
CmdPath="/application/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 /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf

停止原理

/application/mysq/bin/mysqladmin -uroot -poldboy -S /data/3306/mysql.sock shutdown

將my.cnf修改為3306和3307后和mysql腳本，放在/data/3306和/data/3307下

[root@mysql?data]#?tree
.
├──?3306
│???├──?data
│???├──?my.cnf
│???└──?mysql
└──?3307
????├──?data
????├──?my.cnf
????└──?mysql

4?directories,?4?files

五、文件權(quán)限及所屬

chown?-R?mysql.mysql?/data
find?/data?-type?f?-name?"mysql"?|xargs?chmod?700
find?/data?-type?f?-name?"mysql"?|xargs?ls?-l

六、全局路徑配置

echo?'export?PATH=/application/mysq/bin:$PATH'?>>/etc/profile
tail?-1?/etc/profile
source?/etc/profile

七、初始化數(shù)據(jù)庫

cd?/application/mysql/
./bin/mysqld??--defaults-file=/data/3306/my.cnf??--initialize?--basedir=/application/mysql/?--datadir=/data/3306/data/?--user=mysql
./bin/mysqld?--defaults-file=/data/3307/my.cnf??--initialize?--basedir=/application/mysql/?--datadir=/data/3307/data/?--user=mysql

八、mysql啟動、關(guān)閉、登錄

/data/3307/mysql?start
/data/3307/mysql?stop

#mysql登錄
mysql?-S?/data/3307/mysql.sock????????????????????????????????
#修改密碼
mysqladmin?-uroot?-p'password'?password?'123456'?-S?/data/3307/mysql.sock
#遠程登錄多實例
mysql?-uuser?-p'password'?-h292.168.1.23?-P?3307

九、報錯處理

報錯1：

啟動不起來，日志顯示：
2019-12-12T17:51:06.643562Z?0?[ERROR]?InnoDB:?The?Auto-extending?innodb_system?data?file?'./ibdata1'?is?of?a?different?size?768?pages?(rounded?down?to?MB)?than?specified?in?the?.cnf?file:?initial?8192?pages,?max?0?(relevant?if?non-zero)?pages!
2019-12-12T17:51:06.643597Z?0?[ERROR]?InnoDB:?Plugin?initialization?aborted?with?error?Generic?error
2019-12-12T17:51:06.945987Z?0?[ERROR]?Plugin?'InnoDB'?init?function?returned?error.
2019-12-12T17:51:06.946185Z?0?[ERROR]?Plugin?'InnoDB'?registration?as?a?STORAGE?ENGINE?failed.
2019-12-12T17:51:06.946203Z?0?[ERROR]?Failed?to?initialize?plugins.
2019-12-12T17:51:06.946214Z?0?[ERROR]?Aborting

解決方法：

初始化沒有加配置文件重新初始化

./bin/mysqld???--initialize?--defaults-file=/data/3307/my.cnf???--basedir=/application/mysql/?--datadir=/data/3307/data/?--user=mysql

參數(shù)順序有問題如下：報錯2

報錯2：

2019-12-12T18:24:10.667460Z?0?[ERROR]?unknown?variable?'defaults-file=/data/3307/my.cnf'
2019-12-12T18:24:10.667497Z?0?[ERROR]?Aborting

原因見博客：https://www.cnblogs.com/jerry-rock/p/7380922.html

解決方法，修改成如下：

./bin/mysqld?--defaults-file=/data/3307/my.cnf??--initialize?--basedir=/application/mysql/?--datadir=/data/3307/data/?--user=mysql

報錯3：

2019-12-12T18:22:50.814935Z?0?[ERROR]?--initialize?specified?but?the?data?directory?has?files?in?it.?Aborting.
2019-12-12T18:22:50.814979Z?0?[ERROR]?Aborting

原因：/data/3307/data/ 目錄有舊的初始化文件需要刪除清空。

解決方法：

rm?-fr?/data/3307/data/*

報錯4：

2019-12-12T18:29:32.841614Z?0?[ERROR]?unknown?variable?'table_cache=64'????????????????????????????#改為table_open_cache????
2019-12-12T18:29:32.841644Z?0?[ERROR]?unknown?variable?'thread_concurrency=2'??????????????????????#需刪除??
2019-12-12T19:04:17.320405Z?0?[ERROR]?unknown?variable?'innodb_additional_mem_pool_size=4M'????????#需刪除
2019-12-12T19:14:21.886638Z?0?[ERROR]?unknown?variable?'innodb_file_io_threads=4'??????????????????#需刪除
2019-12-12T18:29:32.841644Z?0?[ERROR]?Aborting

原因見博客：https://www.cnblogs.com/putihk/p/7967829.html

mysql版本5.7，我用的my.cnf配置文件中參數(shù)名字改動或刪減