MySQL多实例安装教程
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 赞 (0)