Mysql多实例如何安装配置

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

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

点击(此处)折叠或打开

1. [root@wbg software]# groupadd mysql

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

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

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

5. 解压中。。。

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

7. [root@wbg mysql]# mkdir mysql-files

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

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

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

2.编写/etc/my.cnf

点击(此处)折叠或打开

1. [mysqld_multi]

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

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

4. user = root

5. passWord = gg

6. log     =/root/multi.log

7. 
   

8. [mysqld3306]

9. Socket = /tmp/mysql.sock3306

10. port = 3306

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

12. datadir = /mydata/3306/

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

14. user = mysql

15. 
    

16. [mysqld3308]

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

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

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

20. socket = /tmp/mysql.sock3308

21. port = 3308

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

23. datadir = /mydata/3308/

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

25. user = mysql

26. 
    

27. [client]

28. #port=3306

29. #socket=/tmp/mysql.sock3306

30. #port=3308

31. #socket=/tmp/mysql.sock3308

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

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

点击(此处)折叠或打开

1. [root@wbg mysql]# mkdir /mydata

2. [root@wbg mysql]# cd /mydata

3. [root@wbg mydata]# mkdir 3306

4. [root@wbg mydata]# mkdir 3308

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

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

点击(此处)折叠或打开

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

2. 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).

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

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

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

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

需要添加--explicit_defaults_for_timestamp这个配置，要不然有警告。

点击(此处)折叠或打开

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

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

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

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

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

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

点击(此处)折叠或打开

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

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

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

4. 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.

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

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

最后的这个是正确的。

5.安装SSL

点击(此处)折叠或打开

1. [root@wbg mysql]# bin/mysql_ssl_rsa_setup

2. Generating a 2048 bit RSA private key

3. ......................+++

4. ................................................+++

5. writing new private key to 'ca-key.pem'

6. -----

7. Generating a 2048 bit RSA private key

8. .......................................................................................+++

9. ..................................................................................................................+++

10. writing new private key to 'server-key.pem'

11. -----

12. Generating a 2048 bit RSA private key

13. ..............................+++

14. ...........+++

15. writing new private key to 'client-key.pem'

16. -----

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

点击(此处)折叠或打开

1. 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中增加一段配置

点击(此处)折叠或打开

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

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

设置密码，利用默认密码

点击(此处)折叠或打开

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

2. Enter password:

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

4. Your MySQL connection id is 12

5. Server version: 5.7.18

6. 
   

7. 
   

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

9. 
   

10. 
    

11. Oracle is a reGIStered trademark of Oracle Corporation and/or its

12. affiliates. Other names may be trademarks of their respective

13. owners.

14. 
    

15. 
    

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

17. 
    

18. 
    

19. mysql> alter user user() identified by 'bb';

20. Query OK, 0 rows affected (0.00 sec)

8.测试一下

点击(此处)折叠或打开

1. [root@wbg ~]# mysqld_multi report

2. Reporting MySQL Servers

3. MySQL server from group: mysqld3306 is not running

4. MySQL server from group: mysqld3308 is not running

5. [root@wbg ~]# mysqld_multi start

6. [root@wbg ~]# mysqld_multi report

7. Reporting MySQL servers

8. MySQL server from group: mysqld3306 is running

9. MySQL server from group: mysqld3308 is running

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

额外说明：

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

点击(此处)折叠或打开

1. 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:

点击(此处)折叠或打开

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

2. Enter password:

3. mysql> CREATE USER 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

4. mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost';

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

以上是“Mysql多实例如何安装配置”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网数据库频道！