Mysql多实例安装配置

     这次写的东西，主要是结合mysql官方文档做的，在下面给出了具体的链接
软件安装
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
mysqld_muti的使用方法
https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

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
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
8. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
11. Oracle is a registered trademark of Oracle Corporation and/or its
12. affiliates. Other names may be trademarks of their respective
13. owners.
16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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的用户。这些通过官方文档的模板可以很快的看出来。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30202921/viewspace-2146377/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30202921/viewspace-2146377/