MySQL多实例管理(mysqld_multi)

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL多实例管理(mysqld_multi)

小麦苗 2017-08-27 20:55:47 浏览1780
展开阅读全文
MySQL多实例管理(mysqld_multi)




  官方文档:  https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html
https://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html


1.MySQL多实例介绍
1.1.什么是MySQL多实例
MySQL多实例就是在一台机器上开启多个不同的服务端口(如:3306,3307),运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务:;
1.2.MySQL多实例的特点有以下几点
1:有效利用服务器资源,当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务。
2:节约服务器资源
3:资源互相抢占问题,当某个服务实例服务并发很高时或者开启慢查询时,会消耗更多的内存、CPU、磁盘IO资源,导致服务器上的其他实例提供服务的质量下降;
1.3.部署mysql多实例的两种方式
第一种是使用多个配置文件启动不同的进程来实现多实例,这种方式的优势逻辑简单,配置简单,缺点是管理起来不太方便;
第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例,这种方式定制每个实例的配置不太方面,优点是管理起来很方便,集中管理;
1.4.同一开发环境下安装两个数据库,必须处理以下问题
  • 配置文件安装路径不能相同
  • 数据库目录不能相同
  • 启动脚本不能同名
  • 端口不能相同
  • socket文件的生成路径不能相同
2.Mysql多实例安装部署
2.1.部署环境
Red Hat Enterprise Linux Server release 6.4
2.2.安装mysql软件版本
2.2.1.免编译二进制包
mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
2.3.解压和迁移
tar -xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.21-linux-glibc2.5-x86_64 /usr/local/mysql
2.4.关闭iptables
临时关闭:service iptables stop 
永久关闭:chkconfig iptables off
2.5.关闭selinux
vi /etc/sysconfig/selinux  
将SELINUX修改为DISABLED,即SELINUX=DISABLED 
2.6.创建mysql用户
groupadd -g 27 mysql
useradd -u 27 -g mysql mysql
id mysql
uid=501(mysql) gid=501(mysql) groups=501(mysql)
2.7.创建相关目录
mkdir -p /data/mysql/ {mysql_3306,mysql_3307}
mkdir /data/mysql/mysql_3306/ {data,log,tmp}
mkdir /data/mysql/mysql_3307/ {data,log,tmp}
2.8.更改目录权限
chown -R mysql:mysql /data/mysql/ 
chown -R mysql:mysql /usr/local/mysql/
2.9. 添加环境变量
echo 'export PATH=$PATH:/usr/local/mysql/bin' >>  /etc/profile 
source /etc/profile  
2.10.复制my.cnf文件到etc目录
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
2.11.修改my.cnf(在一个文件中修改即可)
[client]  
port=3306  
socket=/tmp/mysql.sock  
 
[mysqld_multi]  
mysqld = /usr/local/mysql /bin/mysqld_safe  
mysqladmin = /usr/local/mysql /bin/mysqladmin  
log = /data/mysql/mysqld_multi.log  
 
[mysqld]  
user=mysql  
basedir = /usr/local/mysql  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
 
[mysqld3306]  
mysqld=mysqld  
mysqladmin=mysqladmin  
datadir=/data/mysql/mysql_3306/data  
port=3306  
server_id=3306  
socket=/tmp/mysql_3306.sock  
log-output=file  
slow_query_log = 1  
long_query_time = 1  
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log  
log-error = /data/mysql/mysql_3306/log/error.log  
binlog_format = mixed  
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin  
   
[mysqld3307]  
mysqld=mysqld  
mysqladmin=mysqladmin  
datadir=/data/mysql/mysql_3307/data  
port=3307  
server_id=3307  
socket=/tmp/mysql_3307.sock  
log-output=file  
slow_query_log = 1  
long_query_time = 1  
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log  
log-error = /data/mysql/mysql_3307/log/error.log  
binlog_format = mixed  
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
2.12. 初始化数据库
2.12.1. 初始化3306数据库 
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf  
2.12.2. 初始化3307数据库 
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf  
2.12.3. 检查数据库是否初始化成功
出现两个”OK”
 
 
 

 
 
 
 
2.12.4. 查看数据库是否初始化成功(2)
查看3306数据库
[root@mysql ~]# cd /data/mysql/mysql_3306/data
[root@mysql data]# ls
auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.pid  performance_schema  test
 
查看3307数据库
[root@mysql ~]# cd /data/mysql/mysql_3307/data
[root@mysql data]# ls
auto.cnf  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.pid  performance_schema  test
2.13.设置启动文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
2.14.mysqld_multi进行多实例管理
启动全部实例:/usr/local/mysql/bin/mysqld_multi start
查看全部实例状态:/usr/local/mysql/bin/mysqld_multi report 
启动单个实例:/usr/local/mysql/bin/mysqld_multi start 3306 
停止单个实例:/usr/local/mysql/bin/mysqld_multi stop 3306 
查看单个实例状态:/usr/local/mysql/bin/mysqld_multi report 3306 
2.14.1.启动全部实例
[root@mysql ~]# /usr/local/mysql/bin/mysqld_multi start
[root@mysql ~]# /usr/local/mysql/bin/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
2.15.查看启动进程  
 
 
2.16.修改密码
mysql的root用户初始密码是空,所以需要登录mysql进行修改密码,下面以3306为例: 
mysql -S /tmp/mysql_3306.sock   
set password for root@'localhost'=password('123456'); 
flush privileges; 
下次登录:
[root@mysql ~]# mysql -S /tmp/mysql_3306.sock -p
Enter password:
2.17.新建用户及授权
一般新建数据库都需要新增一个用户,用于程序连接,这类用户只需要insert、update、delete、select权限。
新增一个用户,并授权如下: 
grant select,delete,update,insert on *.* to admin@'192.168.0.%' identified by '123456'; 
flush privileges
2.18.外部软件登录数据库
 
 
2.19.测试成功
 
3.源码安装常见报错信息
1:安装mysql报错
checking for tgetent in -lncurses... no
checking for tgetent in -lcurses... no
checking for tgetent in -ltermcap... no
checking for tgetent in -ltinfo... no
checking for termcap functions library... configure: error: No curses/termcap library found
原因:
缺少ncurses安装包
解决方法:
yum list|grep ncurses
yum -y install ncurses-devel
yum install ncurses-devel
2:.../depcomp: line 571: exec: g++: not found
make[1]: *** [my_new.o] 错误 127
make[1]: Leaving directory `/home/justme/software/mysql-5.1.30/mysys'
make: *** [all-recursive] 错误 1
解决方法:
yum install gcc-c++
3:.../include/my_global.h:909: error: redeclaration of C++ built-in type `bool'
make[2]: *** [my_new.o] Error 1
make[2]: Leaving directory `/home/tools/mysql-5.0.22/mysys'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/tools/mysql-5.0.22'
make: *** [all] Error 2
是因为gcc-c++是在configure之后安装的,此时只需重新configure后再编译make即可。
4:初始化数据库报错
报错现象:
root@mysql mysql-6.0.11-alpha]# scripts/mysql_install_db --basedir=/usr/local/mysql/ --user=mysql
Installing MySQL system tables...
ERROR: 1136  Column count doesn't match value count at row 1
150414  7:15:56 [ERROR] Aborting
150414  7:15:56 [Warning] Forcing shutdown of 1 plugins
150414  7:15:56 [Note] /usr/local/mysql//libexec/mysqld: Shutdown complete
Installation of system tables failed!  Examine the logs in
/var/lib/mysql for more information.
You can try to start the mysqld daemon with:
shell> /usr/local/mysql//libexec/mysqld --skip-grant &
and use the command line tool /usr/local/mysql//bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/local/mysql//bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths.  Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com/.  Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS.  Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us!  And remember, if
you do mail us, you MUST use the /usr/local/mysql//scripts/mysqlbug script!
原因:
原有安装的mysql信息没有删除干净
解决方法:
删除/var/lib/mysql目录

 




MySQL 5.7 多实例安装部署实例


1. 背景
  MySQL数据库的集中化运维,可以通过在一台服务器上,部署运行多个MySQL服务进程,通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的,每个实例的datadir, port, socket, pid都是不同的。

2. 多实例特点
  * 有效利用服务器资源,当单个服务器资源有剩余时,可以充分利用剩余的资源提供更多的服务。
  * 资源互相抢占问题,当某个服务实例服务并发很高时或者开启慢查询时,会消耗更多的内存、CPU、磁盘IO资源,导致服务器上的其他实例提供服务的质量下降。

3. 环境 [ 关闭SeLinux ]
[root@MySQL ~]# cat /etc/RedHat-release  
CentOS release 6.9 (Final) 
  
[root@MySQL ~]# uname -r 
2.6.32-696.3.2.el6.x86_64 
  
[root@MySQL ~]# getenforce  
Disabled

4. 部署 [ 4个实例 ]
  * 下载 MySQL 5.7 二制包 [ 推荐官方下载 ] 此下载版本大于5.7.5
[root@MySQL ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

* 解压 MySQL 5.7 二进制包到指定目录
[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

* 创建 MySQL 软链接
[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

* 创建 MySQL 用户
[root@MySQL ~]# useradd -r -s /sbin/nologin mysql

* 在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]
[root@MySQL ~]# mkdir -v /usr/local/mysql/mysql-files 
mkdir: created directory `/usr/local/mysql/mysql-files'

* 创建多实例数据目录
[root@MySQL ~]# mkdir -vp /data/mysql_data{1..4} 
mkdir: created directory `/data' 
mkdir: created directory `/data/mysql_data1' 
mkdir: created directory `/data/mysql_data2' 
mkdir: created directory `/data/mysql_data3' 
mkdir: created directory `/data/mysql_data4'

* 修改 MySQL 二进制包目录的所属用户与所属组
1 [root@MySQL ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

* 修改 MySQL 多实例数据目录与 数据导入/导出专放目录的所属用户与所属组
[root@MySQL ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

* 配置 MySQL 配置文件 /etc/my.cnf
[mysqld_multi] 
mysqld    = /usr/local/mysql/bin/mysqld  
mysqladmin = /usr/local/mysql/bin/mysqladmin
log        = /tmp/mysql_multi.log 
  
[mysqld1] 
# 设置数据目录 [多实例中一定要不同] 
datadir = /data/mysql_data1
# 设置sock存放文件名 [多实例中一定要不同] 
socket = /tmp/mysql.sock1 
# 设置监听开放端口 [多实例中一定要不同] 
port = 3306 
# 设置运行用户 
user = mysql 
# 关闭监控 
performance_schema = off 
# 设置innodb 缓存大小 
innodb_buffer_pool_size = 32M 
# 设置监听IP地址 
bind_address = 0.0.0.0 
# 关闭DNS 反向解析 
skip-name-resolve = 0 
  
[mysqld2] 
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2 
port = 3307 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0 
  
[mysqld3] 
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3 
port = 3308 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0 
  
[mysqld4] 
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4 
port = 3309 
user = mysql 
performance_schema = off 
innodb_buffer_pool_size = 32M 
bind_address = 0.0.0.0 
skip-name-resolve = 0

* 初始化各个实例 [ 初始化完成后会自带随机密码在输出日志中 ]
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2 
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3 
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

* 各实例开启 SSL 连接
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2 
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3 
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

* 复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]
[root@MySQL ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

* 添加脚本执行权限
[root@MySQL ~]# chmod +x /etc/init.d/mysqld_multi

* 添加进service服务管理
[root@MySQL ~]# chkconfig --add mysqld_multi

5. 启动测试
  * 查个多实例状态
[root@MySQL ~]# /etc/init.d/mysqld_multi report 
Reporting MySQL servers 
MySQL server from group: mysqld1 is not running 
MySQL server from group: mysqld2 is not running 
MySQL server from group: mysqld3 is not running 
MySQL server from group: mysqld4 is not running

* 启动多实例
 [root@MySQL ~]# /etc/init.d/mysqld_multi start

* 查看多实例状态
Reporting MySQL servers 
MySQL server from group: mysqld1 is running 
MySQL server from group: mysqld2 is running 
MySQL server from group: mysqld3 is running 
MySQL server from group: mysqld4 is running

* 查看实例监听端口
[root@MySQL ~]# netstat -lntp | grep mysqld 
tcp        0      0 0.0.0.0:3306        0.0.0.0:*                LISTEN      2673/mysqld        
tcp        0      0 0.0.0.0:3307        0.0.0.0:*                LISTEN      2676/mysqld        
tcp        0      0 0.0.0.0:3308        0.0.0.0:*                LISTEN      2679/mysqld        
tcp        0      0 0.0.0.0:3309        0.0.0.0:*                LISTEN      2682/mysqld

6. 连接测试
  * 实例1
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1  -p'z+Ilo*>s:3kw' 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 6 
Server version: 5.7.18 
  
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
  
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  
mysql> set password = '123456'; 
Query OK, 0 rows affected (0.00 sec)

  * 实例2
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2  -p'b*AHUrTgu1rl' 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 7 
Server version: 5.7.18 
  
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
  
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  
mysql> set password = '123456'; 
Query OK, 0 rows affected (0.00 sec)



mysqld_multi安装多个mysql实例 

一个机器上安装多个mysql实例,除了将每个实例在不同的目录下编译安装,为每个实例指定不同的端口,socket,配置文件,安装目录等。还可以使用mysqld_multi方式。

mysqld_multi是mysql设计的专门用来管理不同端口,不同socket的mysql实例的工具。它可以同时启动,停止多个mysql实例,也可以查看这些实例的状态。

mysqld_multi会去配置文件my.cnf(或者是—defaults-file参数指定的配置文件)中检查每一个以[mysqldN]为开头的一组配置(N这里是数字)。这个N是mysqld_multi用来区分每一个mysql实例用的。用法如下:

mysqld_multi start|stop|reload|report N1, N2, N3,…   //reload等价于stop和start。

mysqld_multi需要的信息记录在配置文件my.cnf中的[mysqld_multi]组下。

注意,为了能用mysqld_multi统计管理所有的mysql实例,该管理账号必须存在于所有的mysql实例上,而且密码也一致。

 

以下是我做的测试以及安装步骤:

1、 编写配置文件my.cnf,如下:

[root@CentOSMysql1 etc]# cat my.cnf

[mysqld_multi]

mysqld=mysqld_safe

#表示用mysqld_safe来启动mysql

mysqladmin=/usr/local/mysql/bin/mysqladmin

#指定mysqladmin工具的路径

log=/usr/local/mysql/mysqld_multi.log

#指定mysqld_multi的日志文件

user=root

pass=123456

#指定使用mysqld_multi工具的用户和密码

 

[mysqld3307]

#指定实例编号为3307的一些配置参数

datadir=/opt/data3307

port=3307

socket=/tmp/mysql.sock3307

 

[mysqld3308]

#指定实例编号为3308的一些配置参数

datadir=/opt/data3308

port=3308

socket=/tmp/mysql.sock3308

 

[mysqld56]

#指定实例编号为56的一些配置参数

basedir=/usr/local/mysql56/

datadir=/opt/data56

socket=/tmp/mysql.sock56

port=3310

 

2、 准备安装之前工作

(1)查看是否有mysql用户和mysql组

[root@CentOSMysql1 etc]# cat group |grep mysql

[root@CentOSMysql1 etc]# groupadd mysql

[root@CentOSMysql1 mysql]# cat /etc/passwd|grep mysql

[root@CentOSMysql1 etc]# useradd -g mysql mysql

(2)下载mysql的安装文件。这里我下载的都是linux generic类型的

[root@CentOStest1 opt]# wget http://downloads.mysql.com/archives/get/file/mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz

[root@CentOStest1 opt]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

 (3)解压这些安装包

[root@CentOSMysql1 opt]# tar -zxf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz

[root@CentOSMysql1 opt]# tar -zxf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

 

3、 安装编号为3307的mysql实例,这里我计算用mysql5.7的版本。

[root@CentOSMysql1 opt]#mv mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local

[root@CentOSMysql1 opt]#cd /usr/local/

[root@CentOSMysql1 local]#ln –s mysql-5.7.16-linux-glibc2.5-x86_64 mysql

[root@CentOSMysql1 local]# cd mysql

[root@CentOSMysql1 mysql]# chown -R mysql:mysql .

[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3307

//这条命令执行成功之后,会在结尾打印出临时密码,如下:

2017-12-21T02:08:32.598340Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:08:33.002195Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:08:33.090315Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:08:33.179548Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d898cae9-e5f3-11e7-9e66-000c299323ae.

2017-12-21T02:08:33.181477Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:08:33.184759Z 1 [Note] A temporary password is generated for root@localhost: L;0/g:XaweYi

[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup  --datadir=/opt/data3307

到这里,编号为3307的实例初步安装完毕,还剩下一个步骤就是修改临时密码。改密码之前需要把其所在mysql服务启动起来,这里我们用mysqld_multi启动,先查看下状态,如下:

[root@CentOSMysql1 bin]# mysqld_multi report

-bash: mysqld_multi: command not found

解决方法为将mysqld_multi工具所在的路径添加到环境变量中去:

[root@CentOSMysql1 bin]# vi /etc/profile

//在文件尾部添加以下语句

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

"/etc/profile" 79L, 1828C written

[root@CentOSMysql1 bin]# source /etc/profile

环境变量生效之后重试

[root@CentOSMysql1 mysql]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 mysql]# mysqld_multi start 3307

[root@CentOSMysql1 mysql]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

可以看到编号3307的mysql实例已经启动,然后修改root密码。因为my.cnf里面配置了root用户的密码是123456,所以现在我们将root的密码改为“123456”:

[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3307

Enter password:

//这里输出刚才安装时出现的临时密码。

mysql> set password=password("123456");

到这里,编号为3307的实例安装完毕。

 

4、 安装编号为3308的mysql实例,这里仍用mysql5.7的版本,但是端口号以及datadir要根据my.cnf中的来。

[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3308

2017-12-21T02:36:31.294011Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:36:31.779696Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:36:31.881428Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:36:31.948774Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c138a63c-e5f7-11e7-8f7a-000c299323ae.

2017-12-21T02:36:31.950062Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:36:31.953758Z 1 [Note] A temporary password is generated for root@localhost: whKyXCtxz9;g

//临时密码同样在尾处生成。

[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup  --datadir=/opt/data3308

拉起编号3308的mysql实例服务

[root@CentOSMysql1 mysql]# mysqld_multi start 3308

修改编号3308的mysql实例中root用户的密码为“123456”。

[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3308

Enter password:

//这里输出刚才安装时出现的临时密码。

mysql> set password=password("123456");

到这里,编号为3308的实例安装完毕。

 

5、 安装编号为56的mysql实例,这里使用mysql5.6的版本,由安装目录,端口号以及datadir根据my.cnf中的来。

[root@CentOSMysql1 opt]#mv mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local

[root@CentOSMysql1 opt]#cd /usr/local/

[root@CentOSMysql1 local]#ln –s mysql-5.6.34-linux-glibc2.5-x86_64 mysql56

[root@CentOSMysql1 local]# cd mysql56

[root@CentOSMysql1 mysql56]# chown -R mysql:mysql .

[root@CentOSMysql1 mysql56]# scripts/mysql_install_db --user=mysql  --datadir=/opt/data56

这一步和mysql5.7有些不一样,不会生成临时密码。Mysql5.6的临时密码默认是空。

[root@CentOSMysql1 local]# mysqld_multi start 56

拉起编号56的mysql实例服务

[root@CentOSMysql1 local]# mysql -uroot -p -S /tmp/mysql.sock56

修改编号56的mysql实例中root用户的密码为“123456”。

mysql> set password="123456";

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

mysql> set password=password("123456");

到这里,编号为56的实例安装完毕。

 

6、 最后,试试用mysqld_multi统一起停所有的mysql实例

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~]# mysqld_multi stop

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 ~]# mysqld_multi start

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

 



About Me

.............................................................................................................................................

● 本文整理自网络

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店

.............................................................................................................................................

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解群1
DBA笔试面试讲解群2
欢迎与我联系



网友评论

登录后评论
0/500
评论