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多實例如何安裝配置”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！