同时启动多个mysql实例

为了学习mysql的主从复制，尝试着在本地启动了多个mysql实例。记录一下期间遇到的问题，方便以后排查。

初始化数据目录

在同一台机器部署多个Mysql服务，不同的数据库实例要对应不同的数据目录，否则会使用相同的数据。这在业务上是没有适用的场景的。
官方也提出，这样并不会增加线程优势，反而会带来不可预估的问题。
初始化数据目录:

mysql_install_db --user=csw --datadir=/usr/local/var/mysql3

报错：

FATAL ERROR: Could not find ./bin/my_print_defaults

If you compiled from source, you need to run 'make install' to
copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.

这是因为没有指定basedir，脚本无法找到对应的可执行文件，加上–basedir参数就好了

---

修正之后再来
命令：

mysql_install_db --user=csw --datadir=/usr/local/var/mysql3 --basedir=/usr/local/Cellar/mysql@5.6/5.6.41

依旧报错:

Installing MySQL system tables...2020-05-21 15:17:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-21 15:17:04 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
2020-05-21 15:17:04 0 [Note] /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld (mysqld 5.6.41) starting as process 76330 ...
2020-05-21 15:17:04 76330 [Warning] Can't create test file /usr/local/var/mysql3/XYBJM01617.lower-test
2020-05-21 15:17:04 76330 [Warning] Can't create test file /usr/local/var/mysql3/XYBJM01617.lower-test
/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld: Can't change dir to '/usr/local/var/mysql3/' (Errcode: 2 - No such file or directory)
2020-05-21 15:17:04 76330 [ERROR] Aborting

2020-05-21 15:17:04 76330 [Note] Binlog end
2020-05-21 15:17:04 76330 [Note] /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld: Shutdown complete

只看error部分，很显然是数据目录不存在，我们把目录建好至好，并赋予当前用户权限在试一下

#创建新的数据目录
sudo mkdir /usr/local/var/mysql3

#把新建的目录赋予当前用户
sudo chown csw:admin mysql3

---

again:

mysql_install_db --user=csw --datadir=/usr/local/var/mysql3 --basedir=/usr/local/Cellar/mysql@5.6/5.6.41

终于成功了，至少我是这样。

WARNING: Found existing config file /usr/local/Cellar/mysql@5.6/5.6.41/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/Cellar/mysql@5.6/5.6.41/my-new.cnf,
please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

输出内容里，有两句话要着重注意下。因为我们已经有一个mysqld实例，在安装目录下是有my.cnf的，程序不会直接替换掉他，而是生成了一个新的配置文件my-new.cnf。启动mysqld实例时会默认使用 my.cnf,除非我们使用 –defaults-file指定配置文件。

---

启动多个mysql实例

至此，我们已经在本地初始化了两个（我本地是三个）mysql实例。我们尝试着启动他们。
需要注意几个参数:

–socket:指定sock文件，每个实例指定单独的socket文件
–port:实例监听端口也要唯一
–user:实例启动用户，只有使用root用户时该配置项才生效，否则会使用当前用户

/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --socket=/tmp/mysql.sock --port=3306  --datadir=/usr/local/var/mysql --user=csw

...
Version: '5.6.41'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew

如果你看到这个信息，恭喜你已经启动了一个mysql实例,那我们来试试看，启动多个实例。
刚才已经启动了实例1-3306,我们再来启动实例2-3307，命令类似，修改对应的socket文件和端口就好了

/usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --socket=/tmp/mysql.sock2 --port=3307  --datadir=/usr/local/var/mysql2 --user=csw

同样你会看到

Version: '5.6.41'  socket: '/tmp/mysql.sock2'  port: 3307  Homebrew

我们再查看下进程，是否正常启动

ps aux | grep mysql

会看到两个mysql服务进程

csw              76959   0.0  2.7  4916944 453656 s012  S+    3:58下午   0:00.50 /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --socket=/tmp/mysql.sock2 --port=3307 --datadir=/usr/local/var/mysql2 --user=csw
csw              76772   0.0  2.8  4920016 462068 s011  S+    3:58下午   0:00.82 /usr/local/Cellar/mysql@5.6/5.6.41/bin/mysqld --socket=/tmp/mysql.sock --port=3306 --datadir=/usr/local/var/mysql --user=csw

连接对应的实例

我们已经启动了，两个mysql实例，那我怎么连接呢。
连接的时候指定socket文件就可以连接对应的实例了。
-u:指定登录用户
-S:指定socket文件
-p:系统初始化，会创建无密码的root用户，所以这里没有指定密码连接

mysql -u root -S /tmp/mysql.sock2

连接成功

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41 Homebrew

Copyright (c) 2000, 2018, 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>

那我不指定socket文件，指定端口可以吗？答案当时肯定的,不过这个时候要指定host，也就是本机了。

mysql -h 127.0.0.1 -u root -P 3307

完结