MySQL多实例安装教程

MySQL的多实例

目录
  • MySQL的多实例

    • 实验准备:
    • 准备阶段:
    • 实验阶段

实验准备:

1. 一个干净的centos7系统2. 关闭防火墙和selinux3. 之前已经二进制安装过的MySQL数据库4. 准备文件夹

准备阶段:

  1. 准备文件目录
[root@centos7 data]#mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}mkdir: created directory ‘/mysql’mkdir: created directory ‘/mysql/3306’mkdir: created directory ‘/mysql/3306/data’mkdir: created directory ‘/mysql/3306/etc’mkdir: created directory ‘/mysql/3306/socket’mkdir: created directory ‘/mysql/3306/log’mkdir: created directory ‘/mysql/3306/bin’mkdir: created directory ‘/mysql/3306/pid’mkdir: created directory ‘/mysql/3307’mkdir: created directory ‘/mysql/3307/data’mkdir: created directory ‘/mysql/3307/etc’mkdir: created directory ‘/mysql/3307/socket’mkdir: created directory ‘/mysql/3307/log’mkdir: created directory ‘/mysql/3307/bin’mkdir: created directory ‘/mysql/3307/pid’mkdir: created directory ‘/mysql/3308’mkdir: created directory ‘/mysql/3308/data’mkdir: created directory ‘/mysql/3308/etc’mkdir: created directory ‘/mysql/3308/socket’mkdir: created directory ‘/mysql/3308/log’mkdir: created directory ‘/mysql/3308/bin’mkdir: created directory ‘/mysql/3308/pid’(生成之后的效果)[root@centos7 data]#tree /data/data├── mariadb-10.2.29-linux-x86_64.tar.gz├── mariadb-install.sh├── my.cnf└── mysql    ├── aria_log.00000001    ├── aria_log_control    ├── centos7.localdomain.pid    ├── ib_buffer_pool    ├── ibdata1    ├── ib_logfile0    ├── ib_logfile1    ├── ibtmp1    ├── multi-master.info    ├── mysql    │   ├── columns_priv.frm    │   ├── columns_priv.MYD    │   ├── columns_priv.MYI    │   ├── column_stats.frm    │   ├── column_stats.MYD    │   ├── column_stats.MYI    │   ├── db.frm    │   ├── db.MYD    │   ├── db.MYI    │   ├── db.opt    │   ├── event.frm    │   ├── event.MYD    │   ├── event.MYI    │   ├── func.frm    │   ├── func.MYD    │   ├── func.MYI    │   ├── general_log.CSM    │   ├── general_log.CSV    │   ├── general_log.frm    │   ├── gtid_slave_pos.frm    │   ├── gtid_slave_pos.ibd    │   ├── help_category.frm    │   ├── help_category.MYD    │   ├── help_category.MYI    │   ├── help_keyword.frm    │   ├── help_keyword.MYD    │   ├── help_keyword.MYI    │   ├── help_relation.frm    │   ├── help_relation.MYD    │   ├── help_relation.MYI    │   ├── help_topic.frm    │   ├── help_topic.MYD    │   ├── help_topic.MYI    │   ├── host.frm    │   ├── host.MYD    │   ├── host.MYI    │   ├── index_stats.frm    │   ├── index_stats.MYD    │   ├── index_stats.MYI    │   ├── innodb_index_stats.frm    │   ├── innodb_index_stats.ibd    │   ├── innodb_table_stats.frm    │   ├── innodb_table_stats.ibd    │   ├── plugin.frm    │   ├── plugin.MYD    │   ├── plugin.MYI    │   ├── proc.frm    │   ├── proc.MYD    │   ├── proc.MYI    │   ├── procs_priv.frm    │   ├── procs_priv.MYD    │   ├── procs_priv.MYI    │   ├── proxies_priv.frm    │   ├── proxies_priv.MYD    │   ├── proxies_priv.MYI    │   ├── roles_mapping.frm    │   ├── roles_mapping.MYD    │   ├── roles_mapping.MYI    │   ├── servers.frm    │   ├── servers.MYD    │   ├── servers.MYI    │   ├── slow_log.CSM    │   ├── slow_log.CSV    │   ├── slow_log.frm    │   ├── tables_priv.frm    │   ├── tables_priv.MYD    │   ├── tables_priv.MYI    │   ├── table_stats.frm    │   ├── table_stats.MYD    │   ├── table_stats.MYI    │   ├── time_zone.frm    │   ├── time_zone_leap_second.frm    │   ├── time_zone_leap_second.MYD    │   ├── time_zone_leap_second.MYI    │   ├── time_zone.MYD    │   ├── time_zone.MYI    │   ├── time_zone_name.frm    │   ├── time_zone_name.MYD    │   ├── time_zone_name.MYI    │   ├── time_zone_transition.frm    │   ├── time_zone_transition.MYD    │   ├── time_zone_transition.MYI    │   ├── time_zone_transition_type.frm    │   ├── time_zone_transition_type.MYD    │   ├── time_zone_transition_type.MYI    │   ├── user.frm    │   ├── user.MYD    │   └── user.MYI    ├── performance_schema    │   └── db.opt    ├── tc.log    └── test        └── db.opt4 directories, 103 files
  1. 更改所有者所属组
[root@centos7 data]#id mysql  (因为之前已经安装过MySQL数据库所以有这个用户)uid=987(mysql) gid=981(mysql) groups=981(mysql)[root@centos7 data]#chown -R mysql.mysql /mysql/[root@centos7 data]#ll mysql/total 122936-rw-rw---- 1 mysql mysql    16384 Nov 19 18:06 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Nov 19 18:06 aria_log_control-rw-rw---- 1 mysql mysql        5 Nov 19 18:06 centos7.localdomain.pid-rw-rw---- 1 mysql mysql      938 Nov 19 18:06 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:06 ibdata1-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:06 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:06 ib_logfile1-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:06 ibtmp1-rw-rw---- 1 mysql mysql        0 Nov 19 18:06 multi-master.infodrwx------ 2 mysql mysql     4096 Nov 19 18:06 mysqldrwx------ 2 mysql mysql       20 Nov 19 18:06 performance_schema-rw-rw---- 1 mysql mysql    24576 Nov 19 18:06 tc.logdrwx------ 2 mysql mysql       20 Nov 19 18:06 test

实验阶段

  1. 分别创建数据库并确认数据库文件都生成了,再查看所有者所属组是否正确。
143  /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3306/data --user=mysql144  /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3307/data --user=mysql145  /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3308/data --user=mysql[root@centos7 data]#ll /mysql/3307total 0drwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 bindrwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 datadrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 etcdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 logdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 piddrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 socket[root@centos7 data]#ll /mysql/3308total 0drwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 bindrwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 datadrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 etcdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 logdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 piddrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 socket[root@centos7 data]#ll /mysql/3306total 0drwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 bindrwxr-xr-x 5 mysql mysql 181 Nov 19 18:39 datadrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 etcdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 logdrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 piddrwxr-xr-x 2 mysql mysql   6 Nov 19 18:25 socket[root@centos7 data]#ll /mysql/3306/data/total 110620-rw-rw---- 1 mysql mysql    16384 Nov 19 18:39 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Nov 19 18:39 aria_log_control-rw-rw---- 1 mysql mysql      938 Nov 19 18:39 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 Nov 19 18:39 ibdata1-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:39 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 Nov 19 18:39 ib_logfile1drwx------ 2 mysql mysql     4096 Nov 19 18:39 mysqldrwx------ 2 mysql mysql       20 Nov 19 18:39 performance_schemadrwx------ 2 mysql mysql       20 Nov 19 18:39 test
  1. 准备配置文件
[root@centos7 data]#cp /etc/my.cnf /mysql/3306/etc/[root@centos7 data]#vim /mysql/3306/etc/my.cnf   (根据自己规划的路径更改)[mysqld]port=3306socket=/mysql/3306/socket/mysqld.sockdatadir=/mysql/3306/datainnodb_file_per_table=1[mysqld_safe]log-error=/mysql/3306/log/mysqld.logpid-file=/mysql/3306/pid/mysqld.pid~            (准备另外两个数据库的配置文件)[root@centos7 data]#cp /mysql/3306/etc/my.cnf /mysql/3307/etc/[root@centos7 data]#cp /mysql/3306/etc/my.cnf /mysql/3308/etc/(使用sed替换)[root@centos7 data]#sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf[root@centos7 data]#sed -i 's/3306/3308/' /mysql/3308/etc/my.cnf(确认已经替换掉)[root@centos7 data]#cat /mysql/3308/etc/my.cnf[mysqld]port=3308socket=/mysql/3308/socket/mysqld.sockdatadir=/mysql/3308/datainnodb_file_per_table=1[mysqld_safe]log-error=/mysql/3308/log/mysqld.logpid-file=/mysql/3308/pid/mysqld.pid[root@centos7 data]#cat /mysql/3307/etc/my.cnf[mysqld]port=3307socket=/mysql/3307/socket/mysqld.sockdatadir=/mysql/3307/datainnodb_file_per_table=1[mysqld_safe]log-error=/mysql/3307/log/mysqld.logpid-file=/mysql/3307/pid/mysqld.pid
  1. 设置服务启动脚本(只有多实例安装需要自己手写脚本其他安装都是自动生成的)
[root@centos7 data]#cd /mysql/3306/bin/[root@centos7 bin]#ls[root@centos7 bin]#vim mysqld#!/bin/bashport=3306mysql_user="root"mysql_pwd=""    (这个就为空就行)cmd_path="/usr/local/mysql/bin"   (这个路径按你自己的配置更改)mysql_basedir="/mysql"mysql_sock="${mysql_basedir}/${port}/socket/mysqld.sock"  (这个之前改过名就在mysql后面加了个d)function_start_mysql(){    if [ ! -e "$mysql_sock" ];then      printf "Starting MySQL...\n"      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &    else      printf "MySQL is running...\n"      exit    fi}function_stop_mysql(){    if [ ! -e "$mysql_sock" ];then       printf "MySQL is stopped...\n"       exit    else       printf "Stoping MySQL...\n"       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown   fi}function_restart_mysql(){    printf "Restarting MySQL...\n"    function_stop_mysql    sleep 2    function_start_mysql}case $1 instart)    function_start_mysql;;stop)    function_stop_mysql;;restart)    function_restart_mysql;;*)    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"esac[root@centos7 bin]#chmod +x mysqld    (加上执行权限)[root@centos7 bin]#pwd/mysql/3306/bin(在这一步的时候可以先试启动一下如果没问题就把剩下两个启动脚本一起设置好)
  1. 试启动3306端口的数据库
[root@centos7 bin]#ss -ntl   (确定3306端口没有占用,这里是因为我之前二进制安装的数据库占用了关掉就好了)State       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              LISTEN      0      128                  *:111                              *:*                  LISTEN      0      128                  *:6000                             *:*                  LISTEN      0      5        192.168.122.1:53                               *:*                  LISTEN      0      128                  *:22                               *:*                  LISTEN      0      128          127.0.0.1:631                              *:*                  LISTEN      0      100          127.0.0.1:25                               *:*                  LISTEN      0      128          127.0.0.1:6010                             *:*                  LISTEN      0      128          127.0.0.1:6011                             *:*                  LISTEN      0      80                  :::3306     (这里端口还开着)       :::*                  LISTEN      0      128                 :::111                             :::*                  LISTEN      0      128                 :::6000                            :::*                  LISTEN      0      128                 :::22                              :::*                  LISTEN      0      128                ::1:631                             :::*                  LISTEN      0      100                ::1:25                              :::*                  LISTEN      0      128                ::1:6010                            :::*                  LISTEN      0      128                ::1:6011                            :::*           [root@centos7 bin]#service mysqld stop   (关闭数据库)Stopping mysqld (via systemctl):                           [  OK  ][root@centos7 bin]#ss -ntlState       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              LISTEN      0      128                  *:111                              *:*                  LISTEN      0      128                  *:6000                             *:*                  LISTEN      0      5        192.168.122.1:53                               *:*                  LISTEN      0      128                  *:22                               *:*                  LISTEN      0      128          127.0.0.1:631                              *:*                  LISTEN      0      100          127.0.0.1:25                               *:*                  LISTEN      0      128          127.0.0.1:6010                             *:*                  LISTEN      0      128          127.0.0.1:6011                             *:*                  LISTEN      0      128                 :::111                             :::*                  LISTEN      0      128                 :::6000                            :::*                  LISTEN      0      128                 :::22                              :::*                  LISTEN      0      128                ::1:631                             :::*                  LISTEN      0      100                ::1:25                              :::*                  LISTEN      0      128                ::1:6010                            :::*                  LISTEN      0      128                ::1:6011                            :::*                  [root@centos7 bin]#./mysqld    (以为是自己写的启动脚本支持基本的三个参数)Usage: /mysql/3306/bin/mysqld {start|stop|restart}[root@centos7 bin]#./mysqld startStarting MySQL...[root@centos7 bin]#ss -ntlState       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              LISTEN      0      128                  *:111                              *:*                  LISTEN      0      128                  *:6000                             *:*                  LISTEN      0      5        192.168.122.1:53                               *:*                  LISTEN      0      128                  *:22                               *:*                  LISTEN      0      128          127.0.0.1:631                              *:*                  LISTEN      0      100          127.0.0.1:25                               *:*                  LISTEN      0      128          127.0.0.1:6010                             *:*                  LISTEN      0      128          127.0.0.1:6011                             *:*                  LISTEN      0      80                  :::3306   (启动成功)              :::*                  LISTEN      0      128                 :::111                             :::*                  LISTEN      0      128                 :::6000                            :::*                  LISTEN      0      128                 :::22                              :::*                  LISTEN      0      128                ::1:631                             :::*                  LISTEN      0      100                ::1:25                              :::*                  LISTEN      0      128                ::1:6010                            :::*                  LISTEN      0      128                ::1:6011                            :::*             [root@centos7 bin]#mysql -S /mysql/3306/socket/mysqld.sock   (这里要登陆的话可以指定socket文件路径进行登录)Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 8Server version: 10.2.29-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;   (查看数据库)+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)MariaDB [(none)]> create database db3306;  (建立一个数据库)Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> quitBye[root@centos7 bin]#cd ..[root@centos7 3306]#lsbin  data  etc  log  pid  socket[root@centos7 3306]#ls data/aria_log.00000001  ib_buffer_pool  ib_logfile1        mysql               testaria_log_control   ibdata1         ibtmp1             performance_schemadb3306             ib_logfile0     multi-master.info  tc.log(建立的数据库就是生成一个新的文件夹)(到这里说明已经成功了,可以把之前没做完的3307,3308做完)
  1. 继续设置另外两个启动脚本
[root@centos7 3306]#cp bin/mysqld /mysql/3307/bin/[root@centos7 3306]#cp bin/mysqld /mysql/3308/bin/  (把脚本拷贝过去)[root@centos7 3306]#sed -i 's/3306/3307/' /mysql/3307/bin/mysqld  (sed替换)[root@centos7 3306]#sed -i 's/3306/3308/' /mysql/3308/bin/mysqld [root@centos7 3306]#cat /mysql/3307/bin/mysqld   (确认替换掉了)#!/bin/bashport=3307....[root@centos7 3306]#cat /mysql/3308/bin/mysqld#!/bin/bashport=3308....
  1. 启动所有脚本
[root@centos7 3306]#/mysql/3307/bin/mysqld start (3306也可以这样启动)Starting MySQL...[root@centos7 3306]#/mysql/3308/bin/mysqld startStarting MySQL...[root@centos7 3306]#ss -ntlState       Recv-Q Send-Q   Local Address:Port                  Peer Address:Port              LISTEN      0      128                  *:111                              *:*                  LISTEN      0      128                  *:6000                             *:*                  LISTEN      0      5        192.168.122.1:53                               *:*                  LISTEN      0      128                  *:22                               *:*                  LISTEN      0      128          127.0.0.1:631                              *:*                  LISTEN      0      100          127.0.0.1:25                               *:*                  LISTEN      0      128          127.0.0.1:6010                             *:*                  LISTEN      0      128          127.0.0.1:6011                             *:*                  LISTEN      0      80                  :::3306                            :::*                  LISTEN      0      80                  :::3307   (成功)                   :::*                  LISTEN      0      80                  :::3308                            :::*                  LISTEN      0      128                 :::111                             :::*                  LISTEN      0      128                 :::6000                            :::*                  LISTEN      0      128                 :::22                              :::*                  LISTEN      0      128                ::1:631                             :::*                  LISTEN      0      100                ::1:25                              :::*                  LISTEN      0      128                ::1:6010                            :::*                  LISTEN      0      128                ::1:6011                            :::*                  (记得添加一下PATH变量要不使用命令工具很麻烦)[root@centos7 mysql]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@centos7 mysql]#. /etc/profile.d/mysql.sh
(0)

相关推荐

  • docker学习4-docker安装mysql环境

    前言 docker安装mysql环境非常方便,简单的几步操作就可以了 拉取mysql镜像 先拉取mysql的镜像,可以在docker的镜像仓库找到不同TAG标签的版本https://hub.docke ...

  • Docker MySQL 启动和binlog配置修改

    docker run -d --name mysql -p 3306:3306 --restart=always \ --privileged=tru -e MYSQL_ROOT_PASSWORD=r ...

  • mysql安装过程及无法启动mysql的办法

    在安装位置(譬如:C:\mysql-8.0.17-winx64)中创建文件my.ini,并存入如下内容: [mysqld]port=3306basedir=c:\mysql-8.0.17-winx64 ...

  • 编译安装redis

    编译安装redisd 目录 编译安装redisd 安装方法: yum安装 编译安装 安装方法: yum安装 查看yum仓库redis版本 [root@centos ~]# yum list redis ...

  • MySQL备份与恢复

    博文目录 一.MySQL完全备份与恢复 二.数据库备份类型 三.常见的备份方法 四.数据库完全备份操作 五.MySQL增量备份与恢复 一.MySQL完全备份与恢复 备份的主要目的是灾难恢复,备份还可以 ...

  • 大牛都是这样装MySql嘛?MySQL 零基础安装教程

    什么是MySql? MySQL 是最流行的关系型数据库管理系统,数据库(Database)是按照数据结构来组织.存储和管理数据的仓库. 在没有使用数据库之前,我们一般都是把数据存储在文件中的,但是对于 ...

  • Mysql的安装教程(5.5版本)

    今天在学python爬虫的时候遇到一个项目,需要爬取斗图网的大量图片并存储到数据库中去,刚开始看视频教程的时候,看老师就安装了一个pymysql(这只是一个python的第三方库),以为这样就可以存储 ...

  • mysql详细安装教程8.0.25

    mysql是最流行的一款关系型数据量管理系统,具有体积小.速度快.成本低的特点,可使用C和 C++编写,并使用了多种编译器进行测试,保证了源代码的可移植性,既增加了速度又提高了灵活性,再加上是开放源码 ...

  • MySQL安装教程2021年最新超详细(8.0.25|window 64位)

    一.MySQL官网下载安装包 https://dev.mysql.com/downloads/mysql/ 温馨提示:下载挺快的,就不用浪费那个积分在csdn里下载了,官网东西还是靠谱的! 二.解压安 ...

  • MySQL 8.0 最新安装教程

    回复"666",领取全网最火的Java核心知识总结 MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的关系数据库管理系统应用软件之一.今天跟大家 ...

  • mysql8.0.21下载安装详细教程,mysql安装教程

    MySQL最新版8.0.21安装教程 RDBMS(Relational Database Management System):关系型数据库管理系统 官网:https://www.mysql.com/ ...

  • 中央空调工程安装教程及十大禁区

    建筑one(JZXSW2015)--数万建筑人订阅的微信精品号,讲述搬砖人的心路历程,分享工程人的酸甜苦辣.点击标题下蓝字"建筑one"关注,我们将为您提供有价值.有内容的阅读享受 ...

  • IDM高效率万能多线程下载详细安装教程和使用方法

    使用了IDM插件和脚本之后,真的很香这大大提高了我们电脑的使用效率 但我还没有介绍另一个下载工件.我觉得如果我不写,不知道的人会怀念它.它是著名的互联网下载管理器Internet Download M ...

  • 一个功能强大又好用的RSS订阅器:FreshRSS安装教程

    说明:前面说了很多RSS订阅器了,查看:Miniflux 2,Telegram RSS.今天再介绍个FreshRSS订阅器,免费,界面很美观,支持导入/导出为OPML文件,过滤.标记.搜索等,有助于轻 ...