MySQL在Windows和Linux平台上多版本多实例安装配置方法（5.5、5.6、5.7、8.0）

原创

小麦苗123 2022-03-10 11:23:24 ©著作权

文章标签 mysql sed .net 文章分类 运维

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL在Windows和Linux平台上多版本多实例安装配置方法（5.5、5.6、5.7、8.0）

Linux下MySQL 5.5、5.6和5.7的RPM、二进制和源码安装

参考: http://blog.itpub.net/26736162/viewspace-2144261/

Windows下安装MySQL

------Windows下安装MySQL：使用管理员权限，已在windows 10、Windows7、XP上测试通过，XP不支持5.7及其以上版本
--- 5.5、5.6、5.7、8.0 默认密码都为空，8.0数据库需要8.0的客户端才能无密码登录
D:\Program_Files\MySQL\mysql-8.0.15-winx64\bin\mysqld --initialize-insecure --user=mysql  --console --basedir=D:\Program_Files\MySQL\mysql-8.0.15-winx64 --datadir=D:\Program_Files\MySQL\mysql-8.0.15-winx64\data803314
D:\Program_Files\MySQL\mysql-8.0.15-winx64\bin\mysqld install mysql803314 --defaults-file="D:\Program_Files\MySQL\mysql-8.0.15-winx64\mysql803314.ini"
mysqld --initialize-insecure
mysqld install mysql573306
net start  mysql573311
mysql -uroot -p -P3308
--5.5、5.6、5.7修改密码和允许远程登陆
update mysql.user set authentication_string=password("lhr") where user="root";
update mysql.user set Host="%" where Host="127.0.0.1";
update mysql.user set Host="%" where Host="localhost";
set password=password('lhr');
grant all privileges on *.* to root@'%' identified by 'lhr';
flush privileges;
--8.0修改密码和允许远程登陆
update mysql.user set Host="%" where Host="localhost";
update mysql.user set authentication_string='' where user='root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lhr'; -- 多执行几次
flush privileges;
select user,host from mysql.user;
--mysql 8.0远程连接，在参数文件的[mysqld]下添加：
default_authentication_plugin=mysql_native_password
--mysql 8.0.15忘记密码，修改密码:http://blog.itpub.net/26736162/viewspace-2650907/
1、停止服务
2、管理员权限执行：D:\Program_Files\MySQL\mysql-8.0.15-winx64\bin\mysqld --defaults-file="D:\Program_Files\MySQL\mysql-8.0.15-winx64\mysql803314.ini" --console --skip-grant-tables --shared-memory
3、mysql -uroot -p -P3308 #无密码登录
update mysql.user set authentication_string='' where user='root';
4、退出，关闭第2步的console，重新空密码登录
5、修改密码：ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lhr';
----同版本安装多实例
1、拷贝源库的data文件夹为data3307，删除里边的业务数据库，对于5.6版本需要保留默认数据库mysql、performance_schema、test，对于5.7和8.0版本需要所有数据库都删除
2、拷贝源库的my.ini文件到data3307目录下命名my3307.ini，并修改相关参数：basedir、datadir、port等
2、初始化：
D:\Program_Files\MySQL\mysql-8.0.15-winx64\bin\mysqld --initialize-insecure --user=mysql --basedir=D:\Program_Files\MySQL\mysql-8.0.15-winx64 --datadir=D:\Program_Files\MySQL\mysql-8.0.15-winx64\data3307
3、安装服务：
D:\Program_Files\MySQL\mysql-8.0.15-winx64\bin\mysqld install mysql563307 --defaults-file="D:\Program_Files\MySQL\mysql-8.0.15-winx64\data3307\my3307.ini"
注意：
     1、可以不拷贝源软件为新的文件夹。当然也可以拷贝一份全新的环境。
     2、每个实例的配置文件必须是单独的。
----------------my3307.ini参数文件内容
[mysqld]
port    = 3313
basedir=D:\Program_Files\MySQL\mysql-8.0.15-winx64
datadir=D:\Program_Files\MySQL\mysql-8.0.15-winx64\data
tasklist | findstr mysql

Linux下MySQL安装

tar -zxvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql57
mv mysql-5.7.19-linux-glibc2.12-x86_64 mysql5719
-- tar -Jxf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
-- mv mysql-8.0.16-linux-glibc2.12-x86_64 /usr/local/mysql80/mysql8016
groupadd mysql      ## 添加一个mysql组
useradd -r -g mysql mysql    ## 添加一个用户
useradd -r -s /sbin/nologin -g mysql mysql -d /usr/local/mysql #新建msyql用户禁止登录shell
chown -R mysql.mysql /usr/local/mysql57
--- 5.5、5.6  --MySQL 5.5、5.6的二进制安装默认密码为空
./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql55/mysql5557 --datadir=/usr/local/mysql55/mysql5557/data --defaults-file=/usr/local/mysql55/mysql5557/mysql55573310.cnf
./bin/mysqld_safe --defaults-file=/usr/local/mysql55/mysql5557/mysql55573310.cnf &
--MySQL 5.7.6之后的版本初始化数据库不再使用mysql_install_db
./bin/mysqld --defaults-file=/usr/local/mysql57/mysql5719/mysql57193308.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql57/mysql5719 --datadir=/usr/local/mysql57/mysql5719/data
./bin/mysqld_safe --defaults-file=/usr/local/mysql57/mysql5719/mysql57193308.cnf &
--同一个版本新建多个实例，和普通安装一样，重新设置配置文件和data文件、端口号
--- mysql -uroot -p -S/usr/local/mysql55/mysql5557/data/mysql55573310.sock
cp mysql57193308.cnf mysql57193309.cnf
./bin/mysqld --defaults-file=/usr/local/mysql57/mysql5719/mysql57193308.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql57/mysql5719 --datadir=/usr/local/mysql57/mysql5719/data57193309
./bin/mysqld_safe --defaults-file=/usr/local/mysql57/mysql5719/mysql57193309.cnf &
---5.5、5.6、5.7修改密码和允许远程登陆
set password=password("lhr"); 
grant all privileges on *.* to root@'%' identified by 'lhr';
flush privileges;
--8.0修改密码和允许远程登陆
update mysql.user set Host="%" where Host="localhost";
update mysql.user set authentication_string='' where user='root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lhr'; -- 多执行几次
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'lhr';
flush privileges;
select user,host from mysql.user;
--mysql 8.0远程连接，在参数文件的[mysqld]下添加：
default_authentication_plugin=mysql_native_password
----------------mysql57193308.cnf参数文件内容
[mysqld]
port=3308
basedir=/usr/local/mysql57/mysql5719
datadir=/usr/local/mysql57/mysql5719/data
socket=/usr/local/mysql57/mysql5719/data/mysql57193308.sock
server_id=57193308
log-bin
--配置开机启动，不适合多实例情况
cp ./support-files/mysql.server /etc/init.d/mysql57193308
chmod 755 /etc/init.d/mysql57193308
chkconfig --add mysql57193308
chkconfig mysql57193308 on
chkconfig --level 345 mysql57193308 on
service mysql57193308 start
---Linux启动MySQL
/usr/local/mysql55/mysql5557/bin/mysqld_safe --defaults-file=/usr/local/mysql55/mysql5557/mysql55573310.cnf &
/usr/local/mysql55/mysql5557/bin/mysqld_safe --defaults-file=/usr/local/mysql55/mysql5557/mysql55573311.cnf &
/usr/local/mysql56/mysql5637/bin/mysqld_safe --defaults-file=/usr/local/mysql56/mysql5637/mysql56373312.cnf &
/usr/local/mysql56/mysql5637/bin/mysqld_safe --defaults-file=/usr/local/mysql56/mysql5637/mysql56373313.cnf &
/usr/local/mysql57/mysql5719/bin/mysqld_safe --defaults-file=/usr/local/mysql57/mysql5719/mysql57193308.cnf &
/usr/local/mysql57/mysql5719/bin/mysqld_safe --defaults-file=/usr/local/mysql57/mysql5719/mysql57193309.cnf &
/usr/local/mysql80/mysql8016/bin/mysqld_safe --defaults-file=/usr/local/mysql80/mysql8016/mysql80163314.cnf &
/usr/local/mysql80/mysql8016/bin/mysqld_safe --defaults-file=/usr/local/mysql80/mysql8016/mysql80163315.cnf &
---Linux关闭MySQL
/usr/local/mysql55/mysql5557/bin/mysqladmin -u root -plhr -S/usr/local/mysql55/mysql5557/data/mysql55573310.sock shutdown
/usr/local/mysql55/mysql5557/bin/mysqladmin -u root -plhr -S/usr/local/mysql55/mysql5557/data55573311/mysql55573311.sock shutdown
/usr/local/mysql56/mysql5637/bin/mysqladmin -u root -plhr -S/usr/local/mysql56/mysql5637/data/mysql56373312.sock shutdown
/usr/local/mysql56/mysql5637/bin/mysqladmin -u root -plhr -S/usr/local/mysql56/mysql5637/data56373313/mysql56373313.sock shutdown
/usr/local/mysql57/mysql5719/bin/mysqladmin -u root -plhr -S/usr/local/mysql57/mysql5719/data/mysql57193308.sock shutdown
/usr/local/mysql57/mysql5719/bin/mysqladmin -u root -plhr -S/usr/local/mysql57/mysql5719/data57193309/mysql57193309.sock shutdown
/usr/local/mysql80/mysql8016/bin/mysqladmin -u root -plhr -S/usr/local/mysql80/mysql8016/data/mysql80163314.sock shutdown
/usr/local/mysql80/mysql8016/bin/mysqladmin -u root -plhr -S/usr/local/mysql80/mysql8016/data80163315/mysql80163315.sock shutdown
启动全部实例：mysqld_multi start
查看全部实例状态：mysqld_multi report
启动单个实例：mysqld_multi start 3306
停止单个实例：mysqld_multi stop 3306
查看单个实例状态：mysqld_multi report 3306
---------------------- /etc/my.cnf 管理多实例
[mysqld_multi]
mysqld=/usr/local/mysql80/mysql8016/bin/mysqld_safe
mysqladmin=/usr/local/mysql80/mysql8016/bin/mysqladmin
log=/usr/local/mysql80/log/mysqld_multi.log
user=root
password=lhr
[client]
port=3308
socket=/usr/local/mysql57/mysql5719/data/mysql57193308.sock
user=root
host=localhost
password=lhr
[mysql]
default-character-set=utf8
[mysqld56213306]
mysqld=/usr/local/mysql55/mysql5557/bin/mysqld_safe
mysqladmin=/usr/local/mysql55/mysql5557/bin/mysqladmin
log-bin
basedir=/usr
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
slow_query_log=1
slow_query_log_file=/var/lib/mysql/rhel6lhr-slow.log
[mysqld55573310]
mysqld=/usr/local/mysql55/mysql5557/bin/mysqld_safe
mysqladmin=/usr/local/mysql55/mysql5557/bin/mysqladmin
port=3310
basedir=/usr/local/mysql55/mysql5557
datadir=/usr/local/mysql55/mysql5557/data
socket=/usr/local/mysql55/mysql5557/data/mysql55573310.sock
server_id=55573310
log-bin
[mysqld55573311]
mysqld=/usr/local/mysql55/mysql5557/bin/mysqld_safe
mysqladmin=/usr/local/mysql55/mysql5557/bin/mysqladmin
port=3311
basedir=/usr/local/mysql55/mysql5557
datadir=/usr/local/mysql55/mysql5557/data55573311
socket=/usr/local/mysql55/mysql5557/data55573311/mysql55573311.sock
server_id=55573311
log-bin
[mysqld56373312]
mysqld=/usr/local/mysql56/mysql5637/bin/mysqld_safe
mysqladmin=/usr/local/mysql56/mysql5637/bin/mysqladmin
port=3312
basedir=/usr/local/mysql56/mysql5637
datadir=/usr/local/mysql56/mysql5637/data
socket=/usr/local/mysql56/mysql5637/data/mysql56373312.sock
server_id=56373312
log-bin
[mysqld56373313]
mysqld=/usr/local/mysql56/mysql5637/bin/mysqld_safe
mysqladmin=/usr/local/mysql56/mysql5637/bin/mysqladmin
port=3313
basedir=/usr/local/mysql56/mysql5637
datadir=/usr/local/mysql56/mysql5637/data56373313
socket=/usr/local/mysql56/mysql5637/data56373313/mysql56373313.sock
server_id=56373313
log-bin
[mysqld57193308]
mysqld=/usr/local/mysql57/mysql5719/bin/mysqld_safe
mysqladmin=/usr/local/mysql57/mysql5719/bin/mysqladmin
port=3308
basedir=/usr/local/mysql57/mysql5719
datadir=/usr/local/mysql57/mysql5719/data
socket=/usr/local/mysql57/mysql5719/data/mysql57193308.sock
server_id=57193308
log-bin
[mysqld57193309]
mysqld=/usr/local/mysql57/mysql5719/bin/mysqld_safe
mysqladmin=/usr/local/mysql57/mysql5719/bin/mysqladmin
port=3309
basedir=/usr/local/mysql57/mysql5719
datadir=/usr/local/mysql57/mysql5719/data57193309
socket=/usr/local/mysql57/mysql5719/data57193309/mysql57193309.sock
server_id=57193309
log-bin
[mysqld80163314]
mysqld=/usr/local/mysql80/mysql8016/bin/mysqld_safe
mysqladmin=/usr/local/mysql80/mysql8016/bin/mysqladmin
port=3314
basedir=/usr/local/mysql80/mysql8016
datadir=/usr/local/mysql80/mysql8016/data
socket=/usr/local/mysql80/mysql8016/data/mysql80163314.sock
default_authentication_plugin=mysql_native_password
server_id=80163314
log-bin
[mysqld80163315]
mysqld=/usr/local/mysql80/mysql8016/bin/mysqld_safe
mysqladmin=/usr/local/mysql80/mysql8016/bin/mysqladmin
port=3315
basedir=/usr/local/mysql80/mysql8016
datadir=/usr/local/mysql80/mysql8016/data80163315
socket=/usr/local/mysql80/mysql8016/data80163315/mysql80163315.sock
default_authentication_plugin=mysql_native_password
server_id=80163315
log-bin

Windows重新注册

首先切换到MySQL的bin目录下，执行如下命令：
mysqld remove
mysqld install
------Windows下卸载MySQL
①　关闭MySQL相关进程后并停止MySQL服务（services.msc）
②　命令行执行：sc delete mysql或mysqld remove
③　删除所有MySQL文件
④　regedit打开注册表，删除以下位置的3个文件夹：
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL

MySQL命令：

查看错误： perror 错误号
Oracle为oerr ora 01555
mysql -uroot -h192.168.59.130 -plhr
mysql -uroot -h192.168.59.130 -P3306 -plhr
mysql --html -t -f --silent  <  mysql_check_lhr_v1.3.0.sql
mysql -h192.168.1.63 -uroot -plhr -P3306 --html -t  -f --silent  <  mysql_check_lhr_v1.3.0.sql
---修改提示符
export MYSQL_PS1="(\u@\h) [\d]> "
prompt (\u@\h) [\d]> 
prompt (\u@\h) [\d]>\_
prompt (\U) [\d]>\_
prompt \u@\d>\_
prompt \R:\m:\s>\_
prompt (\u@\h) [\d] \R:\m:\s>\_
[oracle@edsir1p8-EMREP ~]$ more .bashrc 
# .bashrc
# User specific aliases and functions
# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi
export PS1="[\u@\h-\`echo \$ORACLE_SID\` \W]$ "
--杀死mysql
killall mysqld