MySQL5.7安装
快速部署使用yum在Linux下进行安装
YUM)
1. 下载MySQL源安装包
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
2. 安装
yum localinstall mysql57-community-release-el7-8.noarch.rpm
3.检查mysql源是否安装成功
yum repolist enabled | grep "mysql.*-community.*"
4.修改yum源
vim /etc/yum.repos.d/mysql-community.repo 改变默认安装的mysql版本。比如要安装5.6版本,将5.7源的enabled=1改成enabled=0。然后再将5.6源的enabled=0改成enabled=1即可。 备注:enabled=1表示即将要安装的mysql版本,这个文件也可以不修改,默认安装mysql最高版本
5. 安装MySQL
yum install mysql-community-server
常见问题:
1)Error: Unable to find a match
先执行:yum module disable mysql
再执行:yum -y install mysql-community-server
2)Public key for mysql-community-server-5.7.37-1.el7.x86_64.rpm is not installed
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
3)Error: Transaction test error:
#file /etc/my.cnf from install of mysql-community-server-5.7.44-1.el7.x86_64 conflicts with file from package mariadb-connector-c-config-3.1.11-2.oc8.1.noarch
yum remove mysql-common
yum remove mariadb-connector-c-config
6.启动MySQL并设置开机启动
$ systemctl start mysqld
$ systemctl enable mysqld
$ systemctl daemon-reload
7.端口开放
$ firewall-cmd --zone=public --add-port=3306/tcp --permanent $ firewall-cmd --reload
8.修改root本地登录密码
1)查看默认密码
grep 'temporary password' /var/log/mysqld.log
2)使用临时密码登录MySQL
$ mysql -uroot -p
3)修改密码(首先需要操作)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 或者: mysql> set password for 'root'@'localhost'=password('MyNewPass4!');
4)查看密码策略
mysql> show variables like '%password%';
9.添加远程登录用户
mysql> GRANT ALL PRIVILEGES ON *.* TO 'caoxiaobo'@'%' IDENTIFIED BY 'Caoxiaobo0917!' WITH GRANT OPTION;
10.使用客户端连接测试即可
源码安装)
下载源码包(https://dev.mysql.com/downloads/mysql/5.7.html#downloads), 注意 mysql5.7编译安装需要boost库, 可以在官网下载包含boost的源码包
1)安装依赖
yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison wget openssl-devel.x86_64
2)创建用户和组
groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql
2)安装
cd /usr/local/src
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.39.tar.gz
tar -zxvf mysql-boost-5.7.39.tar.gz
3)添加相关配置
#进入目录 cd mysql-5.7.29/ # 添加配置 cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=boost
DCMAKE_INSTALL_PREFIX #指定MySQL程序的安装目录
DMYSQL_DATADIR #数据文件目录
DMYSQL_UNIX_ADDR #socket文件路径
DDEFAULT_CHARSET #指定服务器默认字符集,默认latin1
DDEFAULT_COLLATION #指定服务器默认的校对规则,默认latin1_general_ci
4)编译安装
make -j 4 && make install -j: 编译时会占用大量的系统资源,可以通过-j参数指定多个编译命令进行并行编译来提高速度
5)创建数据目录和修改权限
mkdir -p /usr/local/mysql/data
6)添加配置文件
vim /etc/my.cnf 注意:5.7版本没有模板文件/usr/local/mysql/support-files/my-default.cnf 添加实例: port=3306 socket=/usr/local/mysql/mysql.sock user=mysql basedir=/usr/local/mysql datadir=/usr/local/mysql/data pid-file=/usr/local/mysql/data/mysql.pid sql_mode='ONLY_FULL_GROUP_BY' log_error=/usr/local/mysql/mysql-error.log explicit_defaults_for_timestamp=true !includedir /etc/my.cnf.d [client] port=3306 socket=/usr/local/mysql/mysql.sock
7)初始化数据库
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
另外 --initialize 选项默认以“安全”模式来初始化,则会为 root 用户生成一个密码并将该密码标记为过期,登陆后你需要设置一个新的密码。
8)生成服务启动脚本
echo 'export PATH=/user/local/mysql/bin:$PATH' >> /etc/profile source /etc/profile cp /user/local/mysql/support-files/mysql.server /etc/init.d/mysqld /etc/init.d/mysqld start chkconfig --add mysqld chkconfig mysqld on
9)初次登录之后, 需要重置密码
mysql -uroot -p mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # 密码修改 ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
常见问题:
1.出现datetime默认值为0000-00-00 00:00:00
原因:mysql5.7 datetime 默认值0000-00-00 00:00:00出错,是因为mysql5.7版本后,sql_mode参数被设置了NO_ZERO_IN_DATE,NO_ZERO_DATE
解决:
临时修改: mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ; mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ; 这里是去重:NO_ZERO_IN_DATE,NO_ZERO_DATE
永久修改:
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
或者 修改datetime的默认值
2.
mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by
默认关掉ONLY_FULL_GROUP_BY!