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、资源失效,请下方留言,欢迎分享资源链接
文章评论