MySQL的多实例

MySQL的多实例

实验准备：

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

准备阶段：

[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

更改所有者所属组

[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

实验阶段

分别创建数据库并确认数据库文件都生成了，再查看所有者所属组是否正确。

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

准备配置文件

[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

设置服务启动脚本（只有多实例安装需要自己手写脚本其他安装都是自动生成的）

[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（在这一步的时候可以先试启动一下如果没问题就把剩下两个启动脚本一起设置好）

试启动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做完）

继续设置另外两个启动脚本

[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....

启动所有脚本

[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

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。