MySQL多实例安装教程
程序员文章站
2022-03-08 14:46:57
MySQL的多实例 [toc] 实验准备： 准备阶段： 1. 准备文件目录 2. 更改所有者所属组 实验阶段 1. 分别创建数据库并确认数据库文件都生成了，再查看所有者所属组是否正确。 2. 准备配置文件 3. 设置服务启动脚本（只有多实例安装需要自己手写脚本其他安装都是自动生成的） 4. 试启动3 ......
目录
mysql的多实例
实验准备：
1. 一个干净的centos7系统 2. 关闭防火墙和selinux 3. 之前已经二进制安装过的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.opt
4 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.info drwx------ 2 mysql mysql 4096 nov 19 18:06 mysql drwx------ 2 mysql mysql 20 nov 19 18:06 performance_schema -rw-rw---- 1 mysql mysql 24576 nov 19 18:06 tc.log drwx------ 2 mysql mysql 20 nov 19 18:06 test
实验阶段
- 分别创建数据库并确认数据库文件都生成了，再查看所有者所属组是否正确。
143 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3306/data --user=mysql 144 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3307/data --user=mysql 145 /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3308/data --user=mysql [root@centos7 data]#ll /mysql/3307 total 0 drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 bin drwxr-xr-x 5 mysql mysql 181 nov 19 18:39 data drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 etc drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 log drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 pid drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 socket [root@centos7 data]#ll /mysql/3308 total 0 drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 bin drwxr-xr-x 5 mysql mysql 181 nov 19 18:39 data drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 etc drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 log drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 pid drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 socket [root@centos7 data]#ll /mysql/3306 total 0 drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 bin drwxr-xr-x 5 mysql mysql 181 nov 19 18:39 data drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 etc drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 log drwxr-xr-x 2 mysql mysql 6 nov 19 18:25 pid drwxr-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_logfile1 drwx------ 2 mysql mysql 4096 nov 19 18:39 mysql drwx------ 2 mysql mysql 20 nov 19 18:39 performance_schema drwx------ 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=3306 socket=/mysql/3306/socket/mysqld.sock datadir=/mysql/3306/data innodb_file_per_table=1 [mysqld_safe] log-error=/mysql/3306/log/mysqld.log pid-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=3308 socket=/mysql/3308/socket/mysqld.sock datadir=/mysql/3308/data innodb_file_per_table=1 [mysqld_safe] log-error=/mysql/3308/log/mysqld.log pid-file=/mysql/3308/pid/mysqld.pid [root@centos7 data]#cat /mysql/3307/etc/my.cnf [mysqld] port=3307 socket=/mysql/3307/socket/mysqld.sock datadir=/mysql/3307/data innodb_file_per_table=1 [mysqld_safe] log-error=/mysql/3307/log/mysqld.log pid-file=/mysql/3307/pid/mysqld.pid
- 设置服务启动脚本（只有多实例安装需要自己手写脚本其他安装都是自动生成的）
[root@centos7 data]#cd /mysql/3306/bin/
[root@centos7 bin]#ls
[root@centos7 bin]#vim mysqld
#!/bin/bash
port=3306
mysql_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 in
start)
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 -ntl
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 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 start
starting mysql...
[root@centos7 bin]#ss -ntl
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]#mysql -s /mysql/3306/socket/mysqld.sock （这里要登陆的话可以指定socket文件路径进行登录）
welcome to the mariadb monitor. commands end with ; or \g.
your mariadb connection id is 8
server version: 10.2.29-mariadb mariadb server
copyright (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)]> quit
bye
[root@centos7 bin]#cd ..
[root@centos7 3306]#ls
bin data etc log pid socket
[root@centos7 3306]#ls data/
aria_log.00000001 ib_buffer_pool ib_logfile1 mysql test
aria_log_control ibdata1 ibtmp1 performance_schema
db3306 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/bash port=3307 . . . . [root@centos7 3306]#cat /mysql/3308/bin/mysqld #!/bin/bash port=3308 . . . .
- 启动所有脚本
[root@centos7 3306]#/mysql/3307/bin/mysqld start （3306也可以这样启动） starting mysql... [root@centos7 3306]#/mysql/3308/bin/mysqld start starting mysql... [root@centos7 3306]#ss -ntl 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 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
推荐阅读