Mysql多实例部署

本次实验采用二进制包mysql，mysql版本为5.6.49，操作系统版本CentOS7.5，系统地址：192.168.1.193

基于一个mysql应用，初始化三次，产生三个三个独立的mysql数据目录；

1.安装依赖软件包

yum -y install make bison-devel ncures-devel libaio perl-Data-Dumper net-tools bison bison-devel gcc gcc-c++ cmake ncurses ncurses-developenssl openssl-devel curses-devel ncurses-devel ncurses-compat-libs

2.下载二进制包

root@localhost ~]#  wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz

3.创建用户及组，关闭防火墙

[root@localhost yum.repos.d]# groupadd -r mysql
[root@localhost ~]# useradd -M -r -s /sbin/nologin -g mysql mysql

-M  不创建用户主目录   -r  创建一个系统账户
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

4.解压安装包

[root@localhost soft]# tar -zxvf mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost soft]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.6.49-linux-glibc2.12-x86_64  sbin  share  src
[root@localhost local]# ln -s mysql-5.6.49-linux-glibc2.12-x86_64/ mysql

5.更改文件所属用户及组

[root@localhost local]# chown -R mysql:mysql mysql

6.设置环境变量

[root@localhost bin]# vi /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost bin]# source /etc/profile
[root@localhost bin]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

7、创建每个实例存放数据的目录

[root@localhost ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ~]# chown -R mysql:mysql /opt/data/

8.初始化实例

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

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

[root@localhost scripts]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/opt/data/3308 --basedir=/usr/local/mysql

 9.编辑配置文件

vi  /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

10.启动数据库

[root@localhost performance_schema]# mysqld_multi start 3306
[root@localhost performance_schema]# mysqld_multi start 3307
[root@localhost performance_schema]# mysqld_multi start 3308
[root@localhost performance_schema]# netstat -ntlp | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      3255/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      3420/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      3585/mysqld    

 11.登陆并设置密码

[root@localhost ~]# mysql -uroot -p  -S /tmp/mysql3306.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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 = password('123456');
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456  -S /tmp/mysql3306.sock 
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.6.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 

另外一种设置密码的方法：

 

[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# mysqladmin -uroot -S /tmp/mysql3308.sock password
New password: 
Confirm new password: 

登陆验证：
[root@localhost ~]# mysql -p -S /tmp/mysql3308.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>  

 

 

 12.用Navicat连接

连接报错：

 

 解决办法：授权

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

 13.其他服务器（192.168.1.192）连接数据库

[root@localhost ~]# mysql -uroot -p -h192.168.1.193 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.6.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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.