一台服务器两个mysql_【MySQL】一台服务器上搭建两个mysql节点

环境:

CentOS 6.8

memory：1G

Mysql 5.7 二进制安装包

1.安装相关的环境包

yum -y install gcc glibc libaio libstdc++ libstdc ncurses-libs

2.查看系统中是否存在已经安装的mysql，如果有的话，将其卸载掉

rpm -qa | grep mysql

rpm -e mysql    #普通删除模式

rpm -e --nodeps mysql    #强力删除模式

3.创建组和用户

groupadd mysql

useradd -r -g mysql -s /bin/false myqsl

4.解压mysql5.7二进制安装包

tar -xvf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz

mv /root/mysql-5.7.15-linux-glibc2.5-x86_64 /usr/local/mysql

5.创建相关目录

mkdir -p /mydata/data330{6..7}

chown mysql.mysql /mydata -R

6.修改my.cnf文件

vi /etc/my.cnf

[mysqld_multi]

mysqld     = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

#user       = root

#password   = rootpwd

[mysqld3306]    //这里最好不要写其他名称，开始的时候我加了下划线，但是命令无法识别

port = 3306

server_id = 3306

basedir =/usr/local/mysql

datadir =/mydata/data3306

socket =/tmp/mysql3306.sock

log-error =/var/log/mysqld_3306.log

pid-file =/mydata/data3306/mysqld3306.pid

[mysqld3307]

port = 3307

server_id = 3307

basedir =/usr/local/mysql

datadir =/mydata/data3307

socket =/tmp/mysql3307.sock

log-error =/var/log/mysqld_3307.log

pid-file =/mydata/data3306/mysqld3307.pid

修改好后保存退出

7.添加环境变量

echo "PATH=$PATH:/usr/local/mysql/bin  " >> /etc/profile

source /etc/profile

8.初始化数据库

bin/mysqld --initialize --user=mysql --datadir=/mydata/data3306 --basedir=/usr/local/mysql --explicit_defaults_for_timestamp

bin/mysqld --initialize --user=mysql --datadir=/mydata/data3307 --basedir=/usr/local/mysql --explicit_defaults_for_timestamp

这里会提示一个临时密码，一定要记住！！

9.启动两个不同端口的mysql

mysqld_multi report 查看下两个服务

Reporting MySQL servers

No groups to be reported (check your GNRs)

这样输出的话，就证明/etc/my.cnf文件配置错误了

实例没有起来

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3307 is not running

显示的如上的话，证明实例已经识别了

在执行如下命令开启实例

mysqld_multi start 3306,3307

再次查看状态

mysqld_multi report

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

查看ps  aux | grep mysql

netstat -antpl | grep 330

如果有相关的进程，证明启动成功

10.访问数据库

mysql -u root -p -S /tmp/mysql3306.sock  //这里一定要指定sock文件，否则启动失败的

Enter password:

mysql -u root -p -S /tmp/mysql3307.sock

Enter password:

11.修改root密码

mysql -u root -p -S /tmp/mysql3307.sock

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

mysql> grant all privileges on *.* to root@'192.168.1.%';

mysql> flush privileges;

同样，登陆到另一个节点修改密码

mysql -u root -p -S /tmp/mysql3306.sock

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

mysql> grant all privileges on *.* to root@'192.168.1.%';

mysql> flush privileges;

12.关闭某个实例

mysqladmin -uroot -prootpwd -S /tmp/mysql3306.sock shutdown

13.开启自启动

echo "/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306,3307" >> /etc/rc.local