Mysql多实例安装
|
320
|
0
|
MySQL

1788 字
|
21 分钟

Mysql安装多实例

一、多实例安装两种方法

  • 每个实例使用单独的参数文件(配置多个参数文件)启动各自的进程来实现多实例
  • mysql_mutil配置方法指多个实例共用一个参数文件来启动各自的实例来实现多实例

二、注意事项

多实例安装，datadir，port，socket三个参数不能设置相同值，另外pid-file和server_id建议设置不一样，例如在主从环境中server_id要求唯一。

三、配置多个参数文件方法

1.安装操作系统补丁包

[root@mysql ~]# yum install libaio

2.创建用户和组

[root@mysql ~]# groupadd -g 600 mysql

[root@mysql ~]# useradd -r -g mysql -u 600 -s /bin/false mysql

温馨提示: -r参数指的是创建系统用户，该用户没有登录权限。

[root@mysql ~]# id mysql
uid=600(mysql) gid=600(mysql) groups=600(mysql)

3.解压安装包

[root@mysql soft]# tar zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

温馨提示:如果tar不支持z选项，则可以使用gunzip命令

gunzip < mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz |tar xvf -

4.创建软件链接

[root@mysql soft]# cd /usr/local/

[root@mysql local]# ln -s mysql-5.6.36-linux-glibc2.5-x86_64 mysql

[root@mysql local]# pwd
/usr/local
[root@mysql local]# ll
total 44
drwxr-xr-x. 2 root root 4096 Jul 12 2017 bin
drwxr-xr-x. 2 root root 4096 Sep 23 2011 etc
drwxr-xr-x. 2 root root 4096 Sep 23 2011 games
drwxr-xr-x. 2 root root 4096 Sep 23 2011 include
drwxr-xr-x. 2 root root 4096 Sep 23 2011 lib
drwxr-xr-x. 2 root root 4096 Sep 23 2011 lib64
drwxr-xr-x. 2 root root 4096 Sep 23 2011 libexec
lrwxrwxrwx 1 root root 34 Jan 12 13:49 mysql -> mysql-5.6.36-linux-glibc2.5-x86_64
drwxr-xr-x 13 root root 4096 Jan 12 13:49 mysql-5.6.36-linux-glibc2.5-x86_64
drwxr-xr-x. 2 root root 4096 Sep 23 2011 sbin
drwxr-xr-x. 5 root root 4096 Oct 29 2016 share
drwxr-xr-x. 2 root root 4096 Sep 23 2011 src

5.添加PATH环境变量

[root@mysql mysql]# vim /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

温馨提示: 也可以使用sed命令添加

[root@mysql mysql]# sed -i '$a export PATH=/usr/local/mysql/bin:$PATH\n' /etc/profile

[root@mysql mysql]# source /etc/profile

[root@mysql mysql]# tail -2 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH

6.为多实例创建数据目录

[root@mysql mysql]# mkdir -p /opt/mysql/{3306,3307}/data

7.创建my.cnf参数文件

[root@mysql mysql]# cp /usr/local/mysql/support-files/my-default.cnf /opt/mysql/3306/my.cnf
[root@mysql mysql]# cp /usr/local/mysql/support-files/my-default.cnf /opt/mysql/3307/my.cnf

修改my.cnf参数文件:

[root@mysql mysql]# vim /opt/mysql/3306/my.cnf

[client]
port            = 3306
socket          = /opt/mysql/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
socket  = /opt/mysql/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /opt/mysql/3306/data
pid-file = /opt/mysql/3307/mysql.pid
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
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 = /opt/mysql/3306/error.log
#log-slow-queries = /opt/mysql/3306/slow.log
pid-file = /opt/mysql/3306/mysql.pid
#log-bin = /opt/mysql/3306/mysql-bin
relay-log = /opt/mysql/3306/relay-bin
relay-log-info-file = /opt/mysql/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 = 111

innodb_buffer_pool_size = 32M
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

[root@mysql mysql]# vim /opt/mysql/3307/my.cnf

[client]
port            = 3307
socket          = /opt/mysql/3307/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user    = mysql
port    = 3307
socket  = /opt/mysql/3307/mysql.sock
basedir = /usr/local/mysql
datadir = /opt/mysql/3307/data
pid-file = /opt/mysql/3307/mysql.pid
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
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 = /opt/mysql/3307/error.log
#log-slow-queries = /opt/mysql/3307/slow.log
#log-bin = /opt/mysql/3307/mysql-bin
relay-log = /opt/mysql/3307/relay-bin
relay-log-info-file = /opt/mysql/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 = 222

innodb_buffer_pool_size = 32M
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
8.创建启动文件

[root@mysql mysql]# cp /usr/local/mysql/support-files/mysql.server /opt/mysql/3306/mysql
[root@mysql mysql]# cp /usr/local/mysql/support-files/mysql.server /opt/mysql/3307/mysql

修改启动文件:

[root@mysql 3306]# vim /opt/mysql/3306/mysql

[root@mysql 3306]# vim /opt/mysql/3307/mysql

#!/bin/sh

#init
port=3306
mysql_user="root"
mysql_pwd=""
CmdPath="/usr/local/mysql/bin"
mysql_sock="/opt/mysql/${port}/mysql.sock"
#startup function
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/opt/mysql/${port}/my.cnf --user=mysql 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 /opt/mysql/${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: /opt/mysql/${port}/mysql {start|stop|restart}\n"
esac

温馨提示:

也可以将mysql.server启动文件copy至/etc/init.d，例如:

[root@mysql mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql3306

[root@mysql mysql]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql3307

9分配目录权限

[root@mysql mysql]# chown -R mysql.mysql /opt/mysql

10.初始化目录

[root@mysql 3306]# cd /usr/local/mysql/
[root@mysql mysql]#

[root@mysql mysql]# scripts/mysql_install_db --datadir=/opt/mysql/3306/data --user=mysql

[root@mysql mysql]# scripts/mysql_install_db --datadir=/opt/mysql/3307/data --user=mysql

11.启动数据库

[root@mysql mysql]# mysqld_safe --defaults-file=/opt/mysql/3306/my.cnf --user=mysql &

[root@mysql mysql]# mysqld_safe --defaults-file=/opt/mysql/3307/my.cnf --user=mysql &

报错:

[root@mysql mysql]# mysqld_safe --defaults-file=/opt/mysql/3306/my.cnf --user=mysql &

[1] 3633

[root@mysql 3306]# 180113 05:15:55 mysqld_safe error: log-error set to '/opt/mysql/3306/error.log', however file don't exists. Create writable for user 'mysql'.

这是在mysql新版本：5.6.35，5.6.36的一个bug

原文：https://bugs.mysql.com/bug.php?id=84427

Posted by developer:

Noted in 5.5.55, 5.6.36, 5.7.18, 8.0.1 changelogs

mysqld_safe failed if the error log file named by the --log-erroroption was a FIFO.

解决办法:
[root@mysql mysql]# cd /opt/mysql/3306/

[root@mysql 3306]# touch error.log

[root@mysql 3306]# chown mysql.mysql error.log

或者

[root@mysql ~]# /opt/mysql/3306/mysql start

[root@mysql ~]# /opt/mysql/3307/mysql start

11.检查Mysql进程

[root@mysql mysql]# netstat -nlutp |grep 330
tcp 0 0 :::3307 ::: LISTEN 9068/mysqld
tcp 0 0 :::3306 ::: LISTEN 6853/mysqld

12.修改root密码

[root@mysql ~]# mysqladmin -uroot password 'root' -S /opt/mysql/3306/mysql.sock

[root@mysql ~]# mysqladmin -uroot password 'root' -S /opt/mysql/3307/mysql.sock

13.删除Test库相关信息

[root@mysql ~]# mysql_secure_installation 

[root@mysql mysql]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

解决办法:
[root@mysql mysql]# rm -rf /tmp/mysql.sock
[root@mysql mysql]# ln -s /opt/mysql/3306/mysql.sock /tmp/mysql.sock
[root@mysql mysql]# ll /tmp/
total 12
lrwxrwxrwx  1 root root   26 Jan 13 07:35 mysql.sock -> /opt/mysql/3306/mysql.sock
drwx------  2 gdm  gdm  4096 Jan 13 06:55 orbit-gdm
drwx------. 2 root root 4096 Jul 13  2017 pulse-E6lP6OuKdxCO
drwx------. 2 gdm  gdm  4096 Jan 13 06:55 pulse-W61KBScyxE4S

[root@mysql mysql]# mysql_secure_installation

执行完后，删除/tmp/mysql.sock
[root@mysql mysql]# rm -rf /tmp/mysql.sock
14.登录数据库

[root@mysql mysql]# mysql -uroot -p -S /opt/mysql/3306/mysql.sock

[root@mysql mysql]# mysql -uroot -p -S /opt/mysql/3307/mysql.sock

温馨提示:

使用mysql_config_editor登录更简洁。

mysql_config_editor set -G3306 -uroot -p -S /opt/mysql/3306/mysql.sock

mysql_config_editor set -G3307 -uroot -p -S /opt/mysql/3307/mysql.sock

[root@mysql mysql]# mysql --login-path=3306

[root@mysql mysql]# mysql --login-path=3307

15.关闭数据库

[root@mysql ~]# mysqladmin -p shutdown -S /opt/mysql/3306/mysql.sock

[root@mysql ~]# mysqladmin -p shutdown -S /opt/mysql/3307/mysql.sock

或者

[root@mysql ~]# /opt/mysql/3306/mysql stop

[root@mysql ~]# /opt/mysql/3307/mysql stop

四、mysql_multi配置方法

1.步骤三(1-6)
2.创建mysql_multi参数文件

[root@mysql ~]# vim /etc/my.cnf

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
pass = 123456
log = /usr/local/mysql/mysqld_multi.log

[mysqld1]

socket = /opt/mysql/3306/mysql.sock1
port = 3306
pid-file = /opt/mysql/3306/mysql.pid1
datadir = /opt/mysql/3306/data
server_id = 111

[mysqld2]

socket = /opt/mysql/3307/mysql.sock2
port = 3307
pid-file = /opt/mysql/3307/mysql.pid2
datadir = /opt/mysql/3307/data
server_id = 222

温馨提示:

读取my.cnf顺序是从上到下，下面的mysqld1会继承上面的[mysqld]的配置，如果有相同的参数值，下面的mysqld1会覆盖上面的参数。

[mysqld_multi]里的密码参数，需要使用pass而不是password，否则mysqld_multi stop 1关不掉数据库。

3.分配目录权限

[root@mysql mysql]# chown -R mysql.mysql /opt/mysql

4.初始化目录

[root@mysql 3306]# cd /usr/local/mysql/
[root@mysql mysql]#

[root@mysql mysql]# scripts/mysql_install_db --datadir=/opt/mysql/3306/data --user=mysql

[root@mysql mysql]# scripts/mysql_install_db --datadir=/opt/mysql/3307/data --user=mysql

5.mysqld_multi命令

mysqld_multi命令语法:

mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] ...]

命令详解:

命令 描述
mysqld_multi start 1 单个实例启动
mysqld_multi start 1,5 多个实例启动
mysqld_multi start 4,5-10 按范围启动多个实例
mysqld_multi stop 1,5 关闭实例1和实例2
mysqld_multi report 1 查看实例1
mysqld_multi report 查看所有实例
mysqld_multi --defaults-extra-file=/etc/my.cnf report 指定配置文件查看实例
6.mysqld_multi启动数据库

[root@mysql mysql]# mysqld_multi start 1,2

7.查看数据库状态

[root@mysql mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

8.登录数据库

[root@mysql mysql]# mysql -uroot -p -S /opt/mysql/3306/mysql.sock1

[root@mysql mysql]# mysql -uroot -p -S /opt/mysql/3307/mysql.sock2

暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇