一、需求
在一台服务器上安装mysql5.7,并且部署两个实例：3306用于本机主库，3307用于其他MYSQL服务器的从库
二、下载mysql二进制包
[root@push-5-222 src]# mkdir /usr/local/src/mysql-5.7.26-linux-glibc2.12 [root@push-5-222 src]# cd /usr/local/src/mysql-5.7.26-linux-glibc2.12 [root@push-5-222 mysql-5.7.26-linux-glibc2.12]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
三、解压、添加环境变量
[root@push-5-222 mysql-5.7.26-linux-glibc2.12]# tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.26
[root@push-5-222 /]# vim /etc/profile
PATH=$PATH:/usr/local/mysql-5.7.26/bin
root@push-5-222 /]# source /etc/profile
四、配置3306和3307各自对应的目录
数据目录: /home/mysql-5.7.26/data/3306 /home/mysql-5.7.26/data/330X
日志目录:/home/mysql-5.7.26/log/3306 /home/mysql-5.7.26/log/330X
socket：/var/lib/mysql/330X/mysql.sock
pid-file：/var/run/mysqld/330X/mysqld.pid
log-error：/home/mysql-5.7.26/log/330X/mysqld.log
[root@push-5-222 home]# mkdir -p /home/mysql-5.7.26/data/3306 [root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/data/3307 [root@push-5-222 mysql-5.7.26]# mkdir -p /var/lib/mysql/3306 [root@push-5-222 mysql-5.7.26]# mkdir -p /var/lib/mysql/3307 [root@push-5-222 mysql-5.7.26]# mkdir -p /var/run/mysqld/3306 [root@push-5-222 mysql-5.7.26]# mkdir -p /var/run/mysqld/3307 [root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/log/3306 [root@push-5-222 mysql-5.7.26]# mkdir -p /home/mysql-5.7.26/log/3307
五、将对应的目录授权给mysql用户
[root@push-5-222 mysql-5.7.26]# chown -R mysql:mysql /home/mysql-5.7.26/ [root@push-5-222 /]# chown -R mysql:mysql /var/lib/mysql [root@push-5-222 /]# chown -R mysql:mysql /var/run/mysqld
六、编辑my-330x配置文件
[root@push-5-222 /]# mkdir /etc/mysql [root@push-5-222 /]# chown -R mysql:mysql /etc/mysql/ [root@push-5-222 /]# vim /etc/mysql/my-3306.cnf [mysqld] #innodb_buffer_pool_size = 128M # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M user=mysql port=3306 datadir=/home/mysql-5.7.26/data/3306 socket=/var/lib/mysql/3306/mysql.sock server_id=2223306 #log-bin=master-221 #binlog_format=row #skip-grant-tables symbolic-links=0 pid-file=/var/run/mysqld/3306/mysqld.pid log-error=/home/mysql-5.7.26/log/3306/mysqld.log [mysqld_safe] log-error=/home/mysql-5.7.26/log/3306/mysqld.log
[root@push-5-222 /]# vim /etc/mysql/my-3307.cnf [mysqld] # innodb_buffer_pool_size = 128M # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M user=mysql port=3307 datadir=/home/mysql-5.7.26/data/3307 socket=/var/lib/mysql/3307/mysql.sock server_id=2223307 symbolic-links=0 pid-file=/var/run/mysqld/3307/mysqld.pid log-error=/home/mysql-5.7.26/log/3307/mysqld.log [mysqld_safe] log-error=/home/mysql-5.7.26/log/3307/mysqld.log
七、初始化3306和3307数据库
[root@push-5-222 /]# mysqld --defaults-file=/etc/mysql/my-3306.cnf --initialize --basedir=/usr/local/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/3306 [root@push-5-222 /]#
没有报错，查看数据目录是否有系统数据文件
[root@push-5-222 /]# ll /home/mysql-5.7.26/data/3306 总用量 110628 -rw-r----- 1 mysql mysql 56 5月 27 08:46 auto.cnf -rw-r----- 1 mysql mysql 419 5月 27 08:46 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 5月 27 08:46 ibdata1 -rw-r----- 1 mysql mysql 50331648 5月 27 08:46 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 5月 27 08:46 ib_logfile1 drwxr-x--- 2 mysql mysql 4096 5月 27 08:46 mysql drwxr-x--- 2 mysql mysql 8192 5月 27 08:46 performance_schema drwxr-x--- 2 mysql mysql 8192 5月 27 08:46 sys
再查看日志文件查找生成的随机root密码，用于第一次登陆数据库使用
[root@push-5-222 /]# cat /home/mysql-5.7.26/log/3306/mysqld.log |grep password 2019-05-27T00:46:31.406309Z 1 [Note] A temporary password is generated for root@localhost: -QOhO4-KccHl
此时，数据库初始化完成了，3307也是同样的操作
[root@push-5-222 /]# mysqld --defaults-file=/etc/mysql/my-3307.cnf --initialize --basedir=/usr/local/mysql-5.7.26/ --datadir=/home/mysql-5.7.26/data/3307 [root@push-5-222 /]# ll /home/mysql-5.7.26/data/3307 总用量 110628 -rw-r----- 1 mysql mysql 56 5月 27 08:49 auto.cnf -rw-r----- 1 mysql mysql 419 5月 27 08:49 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 5月 27 08:49 ibdata1 -rw-r----- 1 mysql mysql 50331648 5月 27 08:49 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 5月 27 08:49 ib_logfile1 drwxr-x--- 2 mysql mysql 4096 5月 27 08:49 mysql drwxr-x--- 2 mysql mysql 8192 5月 27 08:49 performance_schema drwxr-x--- 2 mysql mysql 8192 5月 27 08:49 sys [root@push-5-222 /]# cat /home/mysql-5.7.26/log/3307/mysqld.log |grep password 2019-05-27T00:49:44.147398Z 1 [Note] A temporary password is generated for root@localhost: <,XBJ?ksp4ZQ
八、分别启动3306和3307数据库
[root@push-5-222 /]# mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf& [1] 15879 [root@push-5-222 /]# 2019-05-27T00:55:47.964704Z mysqld_safe Logging to '/home/mysql-5.7.26/log/3306/mysqld.log'. 2019-05-27T00:55:48.031905Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data/3306
查看是否有mysqld进程
[root@push-5-222 /]# ps -ef|grep mysqld root 15879 14538 0 08:55 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf mysql 16053 15879 0 08:55 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3306.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3306 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3306/mysqld.log --pid-file=/var/run/mysqld/3306/mysqld.pid --socket=/var/lib/mysql/3306/mysql.sock --port=3306 root 16120 14538 0 08:56 pts/0 00:00:00 grep --color=auto mysqld
[root@push-5-222 /]# mysqld_safe --defaults-file=/etc/mysql/my-3307.cnf& [2] 16163 [root@push-5-222 /]# 2019-05-27T00:57:23.035649Z mysqld_safe Logging to '/home/mysql-5.7.26/log/3307/mysqld.log'. 2019-05-27T00:57:23.095446Z mysqld_safe Starting mysqld daemon with databases from /home/mysql-5.7.26/data/3307 ^C [root@push-5-222 /]# ps -ef|grep mysqld root 15879 14538 0 08:55 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf mysql 16053 15879 0 08:55 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3306.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3306 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3306/mysqld.log --pid-file=/var/run/mysqld/3306/mysqld.pid --socket=/var/lib/mysql/3306/mysql.sock --port=3306 root 16163 14538 0 08:57 pts/0 00:00:00 /bin/sh /usr/local/mysql-5.7.26/bin/mysqld_safe --defaults-file=/etc/mysql/my-3307.cnf mysql 16337 16163 3 08:57 pts/0 00:00:00 /usr/local/mysql-5.7.26/bin/mysqld --defaults-file=/etc/mysql/my-3307.cnf --basedir=/usr/local/mysql-5.7.26 --datadir=/home/mysql-5.7.26/data/3307 --plugin-dir=/usr/local/mysql-5.7.26/lib/plugin --user=mysql --log-error=/home/mysql-5.7.26/log/3307/mysqld.log --pid-file=/var/run/mysqld/3307/mysqld.pid --socket=/var/lib/mysql/3307/mysql.sock --port=3307 root 16371 14538 0 08:57 pts/0 00:00:00 grep --color=auto mysqld
此时可以看到3306和3307两个mysqld_safe进程都启动了
八、分别登录3306和3307数据库修改密码
[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 Copyright (c) 2000, 2019, 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>
mysql> alter user 'root'@'localhost' identified by 'xxxxxxxxxxxx'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
注意，这里一定要使用 -S 方式来连接mysql，修改3307数据库密码
[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3307/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26 Copyright (c) 2000, 2019, 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 'root'@'localhost' identified by 'xxxxxxxxx'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
退出，并重新用新密码登录。
九、设置远程访问权限
[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set host='%' where user='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
，然后通过其他机器客户端连接3306成功。
3307也是同样操作
[root@push-5-222 /]# mysql -uroot -p -S /var/lib/mysql/3307/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.26 MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set host='%' where user='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
此时，双实例安装成功。
十、多实例的启动和停止
停止3306实例: mysqladmin -uroot -p -S /var/lib/mysql/3307/mysql.sock shutdown
启动3306实例: mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf &
连接3306实例:mysql -uroot -p -S /var/lib/mysql/3306/mysql.sock