Mysql多实例如何安装配置

57次阅读
没有评论

共计 5968 个字符,预计需要花费 15 分钟才能阅读完成。

这篇文章主要为大家展示了“Mysql 多实例如何安装配置”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让丸趣 TV 小编带领大家一起研究并学习一下“Mysql 多实例如何安装配置”这篇文章吧。

1.OS 用户创建 / 软件解压 /OS 权限配置

点击 (此处) 折叠或打开

[root@wbg software]# groupadd mysql

[root@wbg software]# useradd -r -g mysql -s /bin/false mysql

[root@wbg software]# cd /usr/local

[root@wbg local]# tar zxvf ~/software/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

解压中。。。

[root@wbg local]# ln -s mysql-5.7.18-linux-glibc2.5-x86_64/ mysql

[root@wbg mysql]# mkdir mysql-files

[root@wbg mysql]# chmod 750 mysql-files

[root@wbg mysql]# chown -R mysql .

[root@wbg mysql]# chgrp -R mysql .

2. 编写 /etc/my.cnf

点击 (此处) 折叠或打开

[mysqld_multi]

mysqld = /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqld_safe

mysqladmin = /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqladmin

user = root

password = gg

log     =/root/multi.log

[mysqld3306]

socket = /tmp/mysql.sock3306

port = 3306

pid-file = /mydata/3306/hostname.pid.3306

datadir = /mydata/3306/

#language = /usr/local/mysql/share/mysql/english

user = mysql

[mysqld3308]

mysqld = /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqld_safe

#ledir = /path/to/mysqld-binary/

mysqladmin = /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/bin/mysqladmin

socket = /tmp/mysql.sock3308

port = 3308

pid-file = /mydata/3308/hostname.pid.3308

datadir = /mydata/3308/

#language = /usr/local/mysql/share/mysql/swedish

user = mysql

[client]

#port=3306

#socket=/tmp/mysql.sock3306

#port=3308

#socket=/tmp/mysql.sock3308

我用的是官方的模板来做的,用的参数比较的少,而且我还在官方的模板基础上注释掉了几个参数。

3. 为每个实力单独创建目录

点击 (此处) 折叠或打开

[root@wbg mysql]# mkdir /mydata

[root@wbg mysql]# cd /mydata

[root@wbg mydata]# mkdir 3306

[root@wbg mydata]# mkdir 3308

[root@wbg mydata]# chown -R mysql:mysql /mydata/

4. 初始化实例(2 个都要做,这里只贴一个了)(前面 2 个是反面教程,最后一个是对的)

点击 (此处) 折叠或打开

[root@wbg mysql]# bin/mysqld –initialize –user=mysql defaults-file=/etc/my.cnf

2017-10-23T09:13:53.872808Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

2017-10-23T09:13:54.385216Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:13:54.547668Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:13:54.630447Z 0 [ERROR] Too many arguments (first extra is defaults-file=/etc/my.cnf).

2017-10-23T09:13:54.630493Z 0 [ERROR] Aborting

需要添加 –explicit_defaults_for_timestamp 这个配置,要不然有警告。

点击 (此处) 折叠或打开

[root@wbg mysql]# bin/mysqld –initialize –user=mysql –defaults-file=/etc/my.cnf   –datadir=/mydata/3308/ –explicit_defaults_for_timestamp

2017-10-23T09:45:43.659977Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:45:43.806836Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:45:43.912638Z 0 [ERROR] unknown variable defaults-file=/etc/my.cnf

2017-10-23T09:45:43.912685Z 0 [ERROR] Aborting

initialize 和 defaults-file 的顺序也要注意,我在这里耽误了很多时间。

点击 (此处) 折叠或打开

[root@wbg mysql]# bin/mysqld –defaults-file=/etc/my.cnf  –initialize –user=mysql  –datadir=/mydata/3308/ –explicit_defaults_for_timestamp

2017-10-23T09:50:48.217054Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-23T09:50:48.430856Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-23T09:50:48.500114Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a5c39a70-b7d7-11e7-a63a-080027736559.

2017-10-23T09:50:48.510651Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.

2017-10-23T09:50:48.513336Z 1 [Note] A temporary password is generated for root@localhost: 6epfT_PyU+EG

最后的这个是正确的。

5. 安装 SSL

点击 (此处) 折叠或打开

[root@wbg mysql]# bin/mysql_ssl_rsa_setup

Generating a 2048 bit RSA private key

………………….+++

…………………………………………+++

writing new private key to ca-key.pem

—–

Generating a 2048 bit RSA private key

……………………………………………………………………………+++

……………………………………………………………………………………………………+++

writing new private key to server-key.pem

—–

Generating a 2048 bit RSA private key

…………………………+++

………..+++

writing new private key to client-key.pem

—–

在 MySQL 5.7.6 以上的版本,需要安装这个,不然会有如下的警告

点击 (此处) 折叠或打开

2017-10-23T18:15:21.506002Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key

6. 在 profile 中增加一段配置

点击 (此处) 折叠或打开

PATH=$PATH:/usr/local/mysql/bin

7. 设置每个实例的登陆口令(2 个实例都要做)

设置密码,利用默认密码

点击 (此处) 折叠或打开

[root@wbg ~]# mysql -S /tmp/mysql.sock3308 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql alter user user() identified by bb

Query OK, 0 rows affected (0.00 sec)

8. 测试一下

点击 (此处) 折叠或打开

[root@wbg ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3308 is not running

[root@wbg ~]# mysqld_multi start

[root@wbg ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3308 is running

到这里多实例就配置完成了。

额外说明:

1. 这种配置最后只有一个实例可以用 tcp/ip。在 my.cnf 的 [client] 下,最后只能有一组配置可以生效。所有的实例都可以用 socket 去连接。
2. 我这里演示的,是用 root 作为 administrative account,而且它们的密码都是一样的,如果 root 的密码不一样,官方文档也有解决方案。

点击 (此处) 折叠或打开

Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin program) has the same user name and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different user names or passwords for the administrative accounts, you might want to create an account on each server that has the same user name and password. For example, you might set up a common multi_admin account by executing the following commands for each server:

点击 (此处) 折叠或打开

shell mysql -u root -S /tmp/mysql.sock -p

Enter password:

mysql CREATE USER multi_admin @ localhost IDENTIFIED BY multipass

mysql GRANT SHUTDOWN ON *.* TO multi_admin @ localhost

3. 在编辑 my.cnf 的时候,要注意 [mysqld_multi] 下的 user 是 mysqld_multi 的 administrative account,这是 mysql 的用户。
[mysqldN]下的 user 是 OS 的用户。这些通过官方文档的模板可以很快的看出来。

以上是“Mysql 多实例如何安装配置”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-27发表,共计5968字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)