MySQL - 单服务器节点安装多套数据库示例

1、实例安装规划

PORT 数据目录 socket file
3306 /home/mysql/mysqldata3306 /home/mysql/mysqldata3306/sock/mysql.sock
3307 /home/mysql/mysqldata3307 /home/mysql/mysqldata3307/sock/mysql.sock
3308 /home/mysql/mysqldata3308 /home/mysql/mysqldata3308/sock/mysql.sock

2、安装步骤

查看系统是否自带mysql数据库

--查看是否安装低版本的mysql或mariadb rpm -qa | egrep -i 'mysql|mariadb' --使用如下命令删除低版本数据库 rpm -e mysql-5.1.73-5.el6_6.x86_64 --nodeps rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps rpm -e qt-mysql-4.8.7-2.el7.x86_64 --nodeps rpm -e akonadi-mysql-1.9.2-4.el7.x86_64 --nodeps --删除用户和组 userdel mysql groupdel mysql

解压缩

xz -d mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz tar xvf mysql-8.0.24-linux-glibc2.12-x86_64.tar

创建mysql用户及用户组,文件目录

id mysql --userdel mysql groupadd mysql useradd mysql -r -g mysql -s /sbin/nologin id mysql mkdir /home/mysql/{program,conf} -p mkdir /home/mysql/mysqldata3306/{binlog,innodb_log,innodb_ts,log,slowlog,sock,tmpdir,innodb_undo,relaylog} -p mkdir /home/mysql/mysqldata3307/{binlog,innodb_log,innodb_ts,log,slowlog,sock,tmpdir,innodb_undo,relaylog} -p mkdir /home/mysql/mysqldata3308/{binlog,innodb_log,innodb_ts,log,slowlog,sock,tmpdir,innodb_undo,relaylog} -p

将数据库输出值安装路径

mv /soft/mysql-8.0.24-linux-glibc2.12-x86_64/* /home/mysql/program/ chown -R mysql:mysql /home/mysql ll /home/mysql/program/ ln -s /home/mysql/program /usr/local/mysql ll /usr/local/mysql/

添加mysql用户限制

vi /etc/security/limits.conf #=> insert mysql hard nofile 65535 mysql soft nofile 65535

路径加入环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile echo 'export LD_LIBRARY_PATH=/home/mysql/program/include/' >> /etc/profile tail -2 /etc/profile bash /etc/profile

配置my.cnf文件

参考:https://www.modb.pro/download/255125

--分别生成3306,3307,3308实例对应的my.cnf vim /etc/my3306.cnf vim /etc/my3307.cnf vim /etc/my3308.cnf #=>插入默认my.cnf参数列表,注意修改port和datadir chmod 644 /etc/my*.cnf ls -ltr /etc/my*.cnf

分别初始化三套数据库实例

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3306/mydata/ ***************************************** [root@localhost mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3306/mydata/ 2021-05-13T00:44:48.723226+08:00 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run. 2021-05-13T00:44:48.724184+08:00 6 [Note] [MY-011061] [Server] Creating the system database. 2021-05-13T00:44:48.724273+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ktuQ>>BlO2YB 2021-05-13T00:44:48.724421+08:00 6 [Note] [MY-011061] [Server] Creating the system tables. 2021-05-13T00:44:49.080508+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 1. 2021-05-13T00:44:49.093757+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 2. 2021-05-13T00:44:49.948042+08:00 6 [Note] [MY-011061] [Server] Filling in the mysql.help table. 2021-05-13T00:44:50.316952+08:00 6 [Note] [MY-011061] [Server] Creating the system users for internal usage. 2021-05-13T00:44:50.330625+08:00 6 [Note] [MY-011061] [Server] Creating the sys schema. 2021-05-13T00:44:51.188189+08:00 6 [Note] [MY-010456] [Server] Bootstrapping complete 2021-05-13T00:44:51.212769+08:00 0 [Note] [MY-010067] [Server] Giving 0 client threads a chance to die gracefully 2021-05-13T00:44:51.212798+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads 2021-05-13T00:44:51.212808+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 0 remaining clients 2021-05-13T00:44:51.214308+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2021-05-13T00:44:51.692334+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2021-05-13T00:44:52.509609+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2021-05-13T00:44:53.347320+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 17091286 2021-05-13T00:44:53.347414+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1" ***************************************** /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3307/mydata/ ***************************************** [root@localhost mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3307/mydata/ 2021-05-13T00:45:32.390993+08:00 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run. 2021-05-13T00:45:32.391673+08:00 6 [Note] [MY-011061] [Server] Creating the system database. 2021-05-13T00:45:32.391728+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: rplT*o*0oi*q 2021-05-13T00:45:32.391790+08:00 6 [Note] [MY-011061] [Server] Creating the system tables. 2021-05-13T00:45:32.533359+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 1. 2021-05-13T00:45:32.545602+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 2. 2021-05-13T00:45:32.966763+08:00 6 [Note] [MY-011061] [Server] Filling in the mysql.help table. 2021-05-13T00:45:33.165410+08:00 6 [Note] [MY-011061] [Server] Creating the system users for internal usage. 2021-05-13T00:45:33.179412+08:00 6 [Note] [MY-011061] [Server] Creating the sys schema. 2021-05-13T00:45:33.589379+08:00 6 [Note] [MY-010456] [Server] Bootstrapping complete 2021-05-13T00:45:33.660438+08:00 0 [Note] [MY-010067] [Server] Giving 0 client threads a chance to die gracefully 2021-05-13T00:45:33.660469+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads 2021-05-13T00:45:33.660480+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 0 remaining clients 2021-05-13T00:45:33.662221+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2021-05-13T00:45:34.285465+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2021-05-13T00:45:35.091408+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2021-05-13T00:45:35.994186+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 17091391 2021-05-13T00:45:35.994274+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1" ***************************************** /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3308.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3308/mydata/ ***************************************** [root@localhost mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/mysqldata3308/mydata/ 2021-05-13T00:46:43.384624+08:00 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run. 2021-05-13T00:46:43.385359+08:00 6 [Note] [MY-011061] [Server] Creating the system database. 2021-05-13T00:46:43.385417+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: mkB0swDvsx-i 2021-05-13T00:46:43.385475+08:00 6 [Note] [MY-011061] [Server] Creating the system tables. 2021-05-13T00:46:43.597804+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 1. 2021-05-13T00:46:43.611460+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 2. 2021-05-13T00:46:44.489077+08:00 6 [Note] [MY-011061] [Server] Filling in the mysql.help table. 2021-05-13T00:46:44.807961+08:00 6 [Note] [MY-011061] [Server] Creating the system users for internal usage. 2021-05-13T00:46:44.876783+08:00 6 [Note] [MY-011061] [Server] Creating the sys schema. 2021-05-13T00:46:45.385900+08:00 6 [Note] [MY-010456] [Server] Bootstrapping complete 2021-05-13T00:46:45.416812+08:00 0 [Note] [MY-010067] [Server] Giving 0 client threads a chance to die gracefully 2021-05-13T00:46:45.416843+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads 2021-05-13T00:46:45.416853+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 0 remaining clients 2021-05-13T00:46:45.418511+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2021-05-13T00:46:46.373486+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2021-05-13T00:46:46.882984+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2021-05-13T00:46:47.738508+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 17091336 2021-05-13T00:46:47.738600+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1" *****************************************

记录初始化密码

3306 fL?7X=!9XhDV 3307 o7cdgjPP(Ag3 3308 mkB0swDvsx-i

启动mysql服务

touch /home/mysql/mysqldata3306/log/error.log touch /home/mysql/mysqldata3307/log/error.log touch /home/mysql/mysqldata3308/log/error.log chown mysql:mysql /home/mysql/mysqldata3306/log/error.log chown mysql:mysql /home/mysql/mysqldata3307/log/error.log chown mysql:mysql /home/mysql/mysqldata3308/log/error.log mkdir -p /var/log/mysql ln -s /home/mysql/mysqldata3306/log/error.log /var/log/mysql/mysql3306.log ln -s /home/mysql/mysqldata3307/log/error.log /var/log/mysql/mysql3307.log ln -s /home/mysql/mysqldata3308/log/error.log /var/log/mysql/mysql3308.log chown -R mysql:mysql /var/log/mysql ll /var/log/mysql/ vim /etc/my3306.cnf vim /etc/my3307.cnf vim /etc/my3308.cnf #=>去掉log_error前面的注释 --which mysqld_safe /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3306.cnf --user=mysql & /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql & /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3308.cnf --user=mysql & netstat -anp|grep 3306 netstat -anp|grep 3307 netstat -anp|grep 3308 netstat -anp|grep mysqld ***************************************** [root@localhost bin]# netstat -anp|grep 3306 tcp6 0 0 :::3306 :::* LISTEN 96009/mysqld tcp6 0 0 :::33060 :::* LISTEN 96009/mysqld unix 2 [ ACC ] STREAM LISTENING 311671 96009/mysqld /home/mysql/mysqldata3306/sock/mysql_3306.sock [root@localhost bin]# netstat -anp|grep 3307 tcp6 0 0 :::3307 :::* LISTEN 97413/mysqld unix 2 [ ACC ] STREAM LISTENING 313592 97413/mysqld /home/mysql/mysqldata3307/sock/mysql_3307.sock [root@localhost bin]# netstat -anp|grep 3308 tcp6 0 0 :::3308 :::* LISTEN 98803/mysqld unix 2 [ ACC ] STREAM LISTENING 313598 98803/mysqld /home/mysql/mysqldata3308/sock/mysql_3308.sock *****************************************

登陆mysql,修改密码

/usr/local/mysql/bin/mysql -S /home/mysql/mysqldata3306/sock/mysql.sock -u root -p alter user 'root'@'localhost' identified by 'root123'; flush privileges; quit; /usr/local/mysql/bin/mysql -S /home/mysql/mysqldata3307/sock/mysql.sock -u root -p alter user 'root'@'localhost' identified by 'root123'; flush privileges; quit; /usr/local/mysql/bin/mysql -S /home/mysql/mysqldata3308/sock/mysql.sock -u root -p alter user 'root'@'localhost' identified by 'root123'; flush privileges; quit;

关闭mysql,开启系统自启动

/usr/local/mysql/bin/mysqladmin -S /home/mysql/mysqldata3306/sock/mysql.sock -uroot -proot123 shutdown /usr/local/mysql/bin/mysqladmin -S /home/mysql/mysqldata3307/sock/mysql.sock -uroot -proot123 shutdown /usr/local/mysql/bin/mysqladmin -S /home/mysql/mysqldata3308/sock/mysql.sock -uroot -proot123 shutdown

免责声明:

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论