Centos7 源码安装 MySQL5.7

下载安装

# 具体版本可在 https://dev.mysql.com/downloads/mysql/5.7.html 页面查看
cd /root
wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

# 解压
tar xvfz mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

# 移动
mv mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/mysql
cd /usr/local/mysql

# 补全一些目录
mkdir /usr/local/mysql/data
mkdir /var/log/mysql

# 创建用户
useradd mysql

# 修改权限
chown -R mysql.mysql /usr/local/mysql

# 安装依赖,否则报 libaio.so.1 找不到
yum -y install libaio*

# 安装
/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --initialize

配置 /etc/my.cnf

cat < /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
# 取消密码验证
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
EOF

 快捷方式和自动重启

# 添加快捷方式
ln -s /usr/local/mysql/bin/mysql /usr/bin
ln -s /usr/local/mysql/bin/mysqldump /usr/bin

# 将mysql加入服务
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

# 开机自启
chkconfig mysql on

# 开启
systemctl start mysql

# 进入MySQL，设置密码
/usr/local/mysql/bin/mysql -u root -p

重设密码

开始MySQL操作

use mysql;
update user set authentication_string=password('root') where user='root';
flush privileges;
exit;

调整配置文件 /etc/my.cnf

将/etc/my.cnf中的skip-grant-tables删除

vi /etc/my.cnf

:%s/skip-grant-tables/# skip-grant-tables/g
:wq

/usr/local/mysql/bin/mysql -u root -proot

登录再次设置一次

登录再次设置一次，不执行这一步好像有登录不了的问题

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
exit;

允许root用户在一个特定的IP进行远程登录，并具有所有库任何操作权限，具体操作如下：

在本机先使用root用户登录mysql：

mysql -u root -p"youpassword"

进行授权操作：

GRANT ALL PRIVILEGES ON *.* TO root@"172.16.16.152" IDENTIFIED BY "youpassword" WITH GRANT OPTION;

重载授权表：

FLUSH PRIVILEGES;

退出mysql数据库：

exit

允许root用户在任何IP进行远程登录，并具有所有库任何操作权限，具体操作如下：

在本机先使用root用户登录mysql：

mysql -u root -p"youpassword"

进行授权操作：

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "youpassword" WITH GRANT OPTION;

重载授权表：

FLUSH PRIVILEGES;

退出mysql数据库：

exit

重启下服务
systemctl restart mysql
创建一个非 root 账号，允许远程连接
/usr/local/mysql/bin/mysql -u root -proot
-- 创建业务数据库
CREATE DATABASE ${db_name} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建一个远程查询的的账号
CREATE USER ${user_name} IDENTIFIED BY '${user_password}';
-- 授权所有 IP 可以访问
GRANT SELECT ON ${db_name}.* TO '${user_name}'@'%';

-- 创建一个局域网访问的账号
CREATE USER '${local_user_name}'@'192.168.0.%' IDENTIFIED BY '${local_user_password}';

GRANT ALL ON ${db_name}.* TO '${local_user_name}'@'192.168.0.%';
-- 刷新用户权限
flush privileges;

mysql备份

yum -y install holland-mysqldump.noarch    #下载备份插件

​​​​​​​

mysqldump -h 127.0.0.1 -uroot -p"Gd*(53#SALE" -R -E --all-databases  >back.sql