mysql单机安装_MySQL安装-单机多实例(9)

单机多实例1

简单来说就是在一台机器上开启多个不同的服务端口(3306,3307)运行多个MySQL服务进程，共用一套安装程序，这些服务进程通过不同的socket监听不同的服务端口来提供各自的服务。

环境设置

1 关闭防火墙

systemctl stop firewalld.service && systemctl disable firewalld.service

2 关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

grep SELINUX=disabled /etc/selinux/config

3 设置计算机名

hostnamectl set-hostname MySQLmu.test.com

4 删除mariadb数据库

rpm -qa mariadb

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

5 重新启动系统

reboot

编译安装MySQL

1 安装依赖

yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libxml libgcrypt libtool bison lrzsz

2 下载源码

https://dev.MySQL.com/downloads/MySQL/

Select Operating System: Source Code

Select OS Version:Generic Linux

3 下载带boost库的源码

wget --no-check-certificate http://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz

wget https://dev.MySQL.com/get/Downloads/MySQL-5.7/MySQL-5.7.26.tar

添加用户和组

groupadd -r MySQL && useradd -r -g MySQL -s /sbin/nologin -M MySQL

解压

tar -zxvf boost_1_59_0

tar -zxvf MySQL-5.7.26

1 添加文件夹并赋予权限

mkdir -p /usr/local/MySQL/tmp

chown -R MySQL:MySQL /usr/local/MySQL

2 创建文件夹可以批量创建

mkdir -pv /data/MySQL/{3306,3307}/{pid,cnf,data,sockt,tmp,log,log-bin}

chown -R MySQL:MySQL /data/MySQL

find /data/ -type f -name "MySQL" |xargs chmod +x

find /data/ -type f -name "MySQL" |xargs ls -l

3 解压文件包

tar -zxvf MySQL-5.7.26.tar.gz && tar -zxvf boost_1_59_0.tar.gz

cd MySQL-5.7.26/

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/MySQL \

-DMYSQL_DATADIR=/data/MySQL \

-DWITH_BOOST=../boost_1_59_0 \

-DSYSCONFDIR=/etc \

-DMYSQL_UNIX_ADDR=/usr/local/MySQL/tmp/MySQL.sock \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DENABLE_DTRACE=0 \

-DDEFAULT_CHARSET=utf8mb4 \

-DDEFAULT_COLLATION=utf8mb4_general_ci \

-DWITH_EMBEDDED_SERVER=1

4 编译安装

make -j$(cat /proc/cpuinfo| grep "processor"| wc -l) && make install

ls -lrt /usr/local/MySQL

cp /usr/local/MySQL/support-files/MySQL.server /etc/init.d/MySQLd

chmod +x /etc/init.d/MySQLd

echo -e '\n\nexport PATH=/usr/local/MySQL/bin:$PATH\n' >> /etc/profile && source /etc/profile

2.2.7.4设置配置文件1

3306实例配置文件

vim /MySQLdata/3306/cnf/my3306.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=/data/MySQL/3306/data

socket=/data/MySQL/3306/sockt/MySQL.sock3306

symbolic-links=0

pid-file=/data/MySQL/3306/pid/MySQLd.pid

log-error=/data/MySQL/3306/log/MySQLd.log

[MySQLd_safe]

log-error=/data/MySQL/3306/log/MySQLd.log

3307实例配置文件

vim /MySQLdata/3307/cnf/my3307.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=/data/MySQL/3307/data

socket=/data/MySQL/3307/sockt/MySQL.sock3307

symbolic-links=0

pid-file=/data/MySQL/3307/pid/MySQLd.pid

log-error=/data/MySQL/3307/log/MySQLd.log

[MySQLd_safe]

log-error=/data/MySQL/3307/log/MySQLd.log

chown -R MySQL:MySQL /usr/local/MySQL

chown -R MySQL:MySQL /data/MySQL

实列初始化

MySQLd --defaults-file=/data/MySQL/3306/cnf/my3306.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/data/MySQL/3306/data/

MySQLd --defaults-file=/data/MySQL/3307/cnf/my3307.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/data/MySQL/3307/data/

查找零时密码

cat /data/MySQL/3306/log/MySQLd.log |grep password =LoLOQmp1Nx

cat /data/MySQL/3307/log/MySQLd.log |grep password ht,F:IWo3GX

使用配置文件启动MySQL

MySQLd_safe --defaults-file=/data/MySQL/3306/cnf/my3306.cnf&

MySQLd_safe --defaults-file=/data/MySQL/3307/cnf/my3307.cnf&

查看MySQL启动

ps -ef|grep MySQLd

netstat -lntup |grep 330

使用MySQL.sock链接实例，输入之前前的零时密码登录MySQL

MySQL -uroot -p -S /data/MySQL/3306/sockt/MySQL.sock3306

修改密码

set password for root@localhost = password('P@ssw0rd');

use MySQL;

update user set host='%' where user='root';

flush privileges;

查看MySQL账号状态

select user,host from MySQL.user;

使用MySQL.sock链接实例，输入之前前的零时密码登录MySQL

MySQL -uroot -p -S /data/MySQL/3307/sockt/MySQL.sock3307

set password for root@localhost = password('P@ssw0rd');

use MySQL;

update user set host='%' where user='root';

flush privileges;

select user,host from MySQL.user;

停止服务

MySQLadmin -uroot -p -S /data/MySQL/3306/sockt/MySQL.sock3306 shutdown

停止3306实例:

MySQLadmin -uroot -p -S /data/MySQL/3306/sockt/MySQL.sock3306 shutdown

启动MySQL服务

启动3306实例:

MySQLd_safe --defaults-file=/data/MySQL/3306/cnf/my3306.cnf&

链接MySQL

连接3306实例:

MySQL -uroot -p -S /data/MySQL/3306/sockt/MySQL.sock3306

设置配置文件2

配置文件

vi /etc/my.cnf

[mysqld_multi]

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

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

log = /tmp/mysql_multi.log

[mysqld1]

datadir = /data/mysql_data1

socket = /tmp/mysql.sock1

log_error = /data/mysql/mysql_data1/mysql1-error.log

port = 3306

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

[mysqld2]

datadir = /data/mysql_data2

socket = /tmp/mysql.sock2

log_error = /data/mysql/mysql_data2/mysq2-error.log

port = 3307

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

[mysqld3]

datadir = /data/mysql_data3

socket = /tmp/mysql.sock3

log_error = /data/mysql/mysql_data3/mysq3-error.log

port = 3308

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

实列初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 出现随机密码 6FhFI7d&A;di

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2 出现随机密码 ysDafadvC9%=

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3 出现随机密码 dSrk*yDDy4/:

修改密码

mysql -uroot -p -S /data/mysql/3307/sockt/mysql.sock3307

set password for root@localhost = password('P@ssw0rd');

use mysql;

update user set host='%' where user='root';

flush privileges;

select user,host from mysql.user;

向启动项添加启动文件

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

chmod +x /etc/init.d/mysqld_multi

chkconfig --add mysqld_multi

mysqld_multi start

mysqld_multi report

启动实例

mysqld_multi start 3306

mysqld_multi start 3307

mysqld_multi start 3308

连接实例

mysql -uroot -S /tmp/mysql.sock1 -p

mysql -uroot -S /tmp/mysql.sock2 -p

mysql -uroot -S /tmp/mysql.sock3 -p

set password for 用户名@localhost = password('新密码');

set password for root@local = password('P@ssw0rd');

停止实例

mysqladmin -u root -pP@ssw0rd -S /tmp/mysql.sock1 shutdown

mysqladmin -u root -pP@ssw0rd -S /tmp/mysql.sock2 shutdown

mysqladmin -u root -pP@ssw0rd -S /tmp/mysql.sock3 shutdown

netstat -lntup |grep 330

grep log-error mysq3-error.log | tail -100