mysql多实例部署
软件下载
[root@localhost ~]# ls # 用xftp把提前下载好的软件包拉过来 anaconda-ks.cfg mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
配置用户和组并解压二进制程序至/usr/local/
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql #创建用户 [root@localhost ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ # 解压到usr/local/ [root@localhost ~]# ls /usr/local/ bin games lib libexec sbin src etc include lib64 mysql-5.7.37-linux-glibc2.12-x86_64 share [root@localhost ~]# cd /usr/local/ [root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql # 更改名字 [root@localhost local]# ls bin games lib libexec sbin src etc include lib64 mysql share
修改mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql/ [root@localhost local]# ll 总用量 0 drwxr-xr-x. 2 root root 6 5月 19 2020 bin drwxr-xr-x. 2 root root 6 5月 19 2020 etc drwxr-xr-x. 2 root root 6 5月 19 2020 games drwxr-xr-x. 2 root root 6 5月 19 2020 include drwxr-xr-x. 2 root root 6 5月 19 2020 lib drwxr-xr-x. 3 root root 17 6月 29 01:01 lib64 drwxr-xr-x. 2 root root 6 5月 19 2020 libexec drwxr-xr-x. 9 mysql mysql 129 7月 2 07:15 mysql drwxr-xr-x. 2 root root 6 5月 19 2020 sbin drwxr-xr-x. 5 root root 49 6月 29 01:01 share drwxr-xr-x. 2 root root 6 5月 19 2020 src
配置环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysqld.sh # 设置环境变量 [root@localhost local]# source /etc/profile.d/mysqld.sh # 读一下
配置头文件，man文档，lib库
[root@localhost local]# ls mysql/ bin docs include lib LICENSE man README share support-files [root@localhost local]# ln -s /usr/local/mysql/include/ /usr/include/mysql #设置头文件 [root@localhost local]# vi /etc/man_db.conf # 设置man文档 MANDATORY_MANPATH /usr/local/mysql/man # 加入此行 [root@localhost local]# vi /etc/ld.so.conf.d/mysqld.conf /usr/local/mysql/lib # 加入此行 [root@localhost local]# ldconfig # 读一下
创建各实例数据存放的目录
[root@localhost local]# mkdir -p /opt/data/{3306,3307,3308} # 创建3个目录
[root@localhost local]# cd /opt/data/
[root@localhost data]# ls
3306 3307 3308 #多实例3个数据库的目录
初始化
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306 # 首先初始化3306实例 2022-07-01T23:59:15.235406Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestampserver option (see documentation for more details). 2022-07-01T23:59:15.545533Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-01T23:59:15.585591Z 0 [Warning] InnoDB: Creating foreign keyconstraint system tables. 2022-07-01T23:59:15.657984Z 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: cfff7106-f999-11ec-801c-000c29eeb670. 2022-07-01T23:59:15.660678Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-01T23:59:16.436713Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-01T23:59:16.436788Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-01T23:59:16.437604Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-01T23:59:16.640884Z 1 [Note] A temporary password is generated for root@localhost: NJD9:Z3xwZQm [root@localhost ~]# echo 'NJD9:Z3xwZQm' > 3306 #零时密码保存一下 [root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307 # 初始化3307实例 2022-07-02T00:01:59.932820Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestampserver option (see documentation for more details). 2022-07-02T00:02:00.208403Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-02T00:02:00.272503Z 0 [Warning] InnoDB: Creating foreign keyconstraint system tables. 2022-07-02T00:02:00.282260Z 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: 321f1d0f-f99a-11ec-8461-000c29eeb670. 2022-07-02T00:02:00.283327Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-02T00:02:01.604800Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-02T00:02:01.604827Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-02T00:02:01.605092Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-02T00:02:01.729971Z 1 [Note] A temporary password is generated for root@localhost: kGjitQdfW0)9 [root@localhost ~]# echo 'kGjitQdfW0)9' > 3307 #零时密码保存到3307 [root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308 # 初始化3308实例 2022-07-02T00:02:34.423274Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestampserver option (see documentation for more details). 2022-07-02T00:02:34.671421Z 0 [Warning] InnoDB: New log files created, LSN=45790 2022-07-02T00:02:34.700259Z 0 [Warning] InnoDB: Creating foreign keyconstraint system tables. 2022-07-02T00:02:34.705348Z 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: 46a3a976-f99a-11ec-8722-000c29eeb670. 2022-07-02T00:02:34.705876Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2022-07-02T00:02:35.576881Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2022-07-02T00:02:35.576906Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2022-07-02T00:02:35.577203Z 0 [Warning] CA certificate ca.pem is self signed. 2022-07-02T00:02:36.350372Z 1 [Note] A temporary password is generated for root@localhost: pM.f8!8Cs,(1 [root@localhost ~]# echo 'pM.f8!8Cs,(1' > 3308 #零时密码保存到3308
安装perl
[root@localhost ~]# dnf -y install perl
配置配置文件/etc/my.cnf
[root@localhost ~]# 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
启动各实例
[root@localhost ~]# ls /usr/local/mysql/bin/ mysqld_multi [root@localhost ~]# file /usr/local/mysql/bin/mysqld_multi /usr/local/mysql/bin/mysqld_multi: Perl script text executable # 是一个Perl的脚本所以要安装perl的包 [root@localhost ~]# mysqld_multi start 3306 # 启动 [root@localhost ~]# mysqld_multi start 3307 [root@localhost ~]# mysqld_multi start 3308 [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:* # 都起来了端口号也有了
初始化密码
[root@localhost ~]# dnf -y install ncurses-compat-libs # 首先安装这个包 # 修改3306的密码 [root@localhost ~]# mysql -uroot -p'NJD9:Z3xwZQm' -S /tmp/mysql3306.sock # 用零时密码练进来3306 mysql> set password = password('123456'); # 修改密码 Query OK, 0 rows affected, 1 warning (0.00 sec) #修改3307的密码 [root@localhost ~]# cat 3307 kGjitQdfW0)9 [root@localhost ~]# mysql -uroot -p'kGjitQdfW0)9' -S /tmp/mysql3307.sock mysql> set password = password('123456'); Query OK, 0 rows affected, 1 warning (0.01 sec) # 修改3308的密码 [root@localhost ~]# cat 3308 pM.f8!8Cs,(1 [root@localhost ~]# mysql -uroot -p'pM.f8!8Cs,(1' -S /tmp/mysql3308.sock mysql> set password = password('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec)
# 验证密码是否设置成功，用新密码连接一下 # 新密码连接3306 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sock 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 3 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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> #新密码连接3307 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3307.sock 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 3 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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> quit #新密码连接3308 [root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3308.sock 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 3 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sock # 连到3306 mysql: [Warning] Using a password on the command lineinterface can be insecure. Welcome to the MySQL monitor. Commands end with ; or\g. Your MySQL connection id is 4 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 thecurrent input statement. mysql> create database school; # 创建一个school库 Query OK, 1 row affected (0.00 sec) mysql> show database; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1 mysql> show databases; # 查看 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.01 sec) [root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 -P3307 # 连到3307 mysql: [Warning] Using a password on the command lineinterface can be insecure. Welcome to the MySQL monitor. Commands end with ; or\g. Your MySQL connection id is 4 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 thecurrent input statement. mysql> show databases; # 查看数据库列表没有school库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) [root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1-P3308 #连到3308 mysql: [Warning] Using a password on the command lineinterface can be insecure. Welcome to the MySQL monitor. Commands end with ; or\g. Your MySQL connection id is 4 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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 thecurrent input statement. mysql> show databases; # 查看也没有school库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) # 说明3306.3307.3308是3台独立的数据库之间不会关联
设置开机自启
[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/my3306.serice #复制一个改名3306 [root@localhost ~]# vim /usr/lib/systemd/system/my3306.service [Unit] Description=3306 server daemon After=network.target sshd-keygen.target [Service] Type=forking ExecStart=/usr/local/mysql/bin/mysqld_multi start 3306 ExecStop=ps -ef|grep 3306|grep -v grep|awk '{print $2}' |xargs kill -9 ExecReload=/bin/kill -HUP $MAINPID # 修改如下 [Install] WantedBy=multi-user.target [root@localhost ~]# systemctl daemon-reload [root@localhost ~]# systemctl status my3306.service # 查看状态 ● my3306.service - 3306 server daemon Loaded: loaded (/usr/lib/systemd/system/my3306.service; disabled;> Active: inactive (dead) State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:* [root@localhost ~]# vi /etc/selinux/config # 关闭selinux SELINUX=disabled #修改如下 [root@localhost ~]# ln -s /usr/local/mysql/bin/my_print_defaults /usr/bin/ #做个链接，让它能找到 [root@localhost ~]# systemctl start my3306 # 启动 [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:* # 有端口号启动了 [root@localhost ~]# systemctl enable my3306 # 设置开机自启 Created symlink /etc/systemd/system/multi-user.target.wants/my3306.service → /usr/lib/systemd/system/my3306.service. [root@localhost ~]# systemctl status my3306 #查看状态开机自启设置成功 ● my3306.service - 3306 server daemon Loaded: loaded (/usr/lib/systemd/system/my3306.service; enabled; > Active: active (running) since Sat 2022-07-02 21:35:55 CST; 3min > Main PID: 15407 (mysqld_safe) Tasks: 28 (limit: 11202) Memory: 182.1M CGroup: /system.slice/my3306.service ├─15407 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadi> └─15546 /usr/local/mysql/bin/mysqld --basedir=/usr/local/> 7月 02 21:35:55 localhost.localdomain systemd[1]: Starting 3306 serv> 7月 02 21:35:55 localhost.localdomain mysqld_multi[15402]: Wide char> 7月 02 21:35:55 localhost.localdomain systemd[1]: Started 3306 serve> [root@localhost ~]# cd /usr/lib/systemd/system [root@localhost system]# cp my3306.service my3307.service # 复制一份给3307和3308用 [root@localhost system]# cp my3306.service my3308.service [root@localhost system]# vim my3307.service # 把里面的6换成7即可 [root@localhost system]# vim my3308.service #把里面的6换成8即可 [root@localhost ~]# systemctl start my3306 [root@localhost ~]# systemctl start my3307 [root@localhost ~]# systemctl start my3308 [root@localhost ~]# ss -antl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:* [root@localhost ~]# systemctl enable my3307 # 3307设置开机自启 Created symlink /etc/systemd/system/multi-user.target.wants/my3307.service → /usr/lib/systemd/system/my3307.service. [root@localhost ~]# systemctl enable my3308 #3308设置开机自启 Created symlink /etc/systemd/system/multi-user.target.wants/my3308.service → /usr/lib/systemd/system/my3308.service.
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· AI与.NET技术实操系列（六）：基于图像分类模型对图像进行分类