笔记:MySQL部署指南
一、安装前
1、检查端口是否被占用
netstat -unltp | grep -w :3306
2、更新系统程序
yum -y update
3、检查是否安装mariadb
rpm -qa | grep mariadb-libs
3.1、卸载mariadb
rpm -e --nodeps mariadb-libs
4、检查是否安装MySQL
rpm -qa | grep mysql-server
4.1 卸载mysql-server
rpm -e --nodeps mysql-server
5、检查是否存在mysql用户
id mysql
5.1、创建mysql用户,设置禁止登录权限
useradd -r mysql -M -s /sbin/nologin
二、开始安装
1、下载压缩包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
1.1、解压压缩包
tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
1.2、修改解压后文件名
mv mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql
1.3、删除压缩包
rm -rf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
2、创建数据库存data文件及日志文件目录
mkdir -p /usr/local/mysql/{data,logs}
3、 给mysql安装位置文件设置用户和所属主
chown -R mysql:mysql /usr/local/mysql
4、创建mysql命令软连接
ln -s /usr/local/mysql/bin/mysql /usr/bin/
ln -s /usr/local/mysql/bin/mysqldump /usr/bin/
ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/
ln -s /usr/local/mysql/bin/mysql_config /usr/bin/
5、创建my.cnf配置文件内容
cat > /etc/my.cnf <<END
# MySQL服务端配置
[mysqld]
# MySQL服务端默认监听的TCP/IP端口
port=3306
# MySQL软件安装路径
basedir=/usr/local/mysql
# MySQL数据文件存放路径
datadir=/usr/local/mysql/data
# 错误日志存放路径
log-error=/usr/local/mysql/logs/mysql.log
# 用于本地连接的Unix套接字文件存放路径
socket=/tmp/mysql.sock
# 开启事件调度器event_scheduler
event_scheduler=ON
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志存放路径
slow_query_log_file = /usr/local/mysql/logs/mysql-slow.log
# 超过10秒的查询,记录到慢查询日志,默认值10
long_query_time = 1
# InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典的缓冲池。
innodb_buffer_pool_size = $(echo "$(($(free | sed -r -n '/Mem:/p' | awk '{print $2}')/1024))M")
# 默认的用户身份验证插件。MySQL 5.5.7之前的mysql客户端不支持
default-authentication-plugin=mysql_native_password
# 数据库服务器ID
server-id=10
# 二进制日志存放路径
log-bin=/usr/local/mysql/logs/mysql-bin.log
# 每个主机的连接请求异常中断的最大次数。对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。如需对该主机进行解禁,执行:FLUSH HOST。
max_connect_errors=1000
# 进程ID文件存放路径
pid-file=/usr/local/mysql/mysql.pid
END
6、创建mysqld.service配置文件
cat > /lib/systemd/system/mysqld.service <<END
[Unit]
Description=MySQL Server
Documentation=man:mysqld
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65536
LimitNPROC = 65536
END
7、初始化数据库
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
7.1、查看初始默认密码
tail -n 1 /usr/local/mysql/logs/mysql.log | awk -F "root@localhost:" '{print $NF}'
7.2、 检查当前MySQL版本
mysql --version | awk -F " " '{print $5}' | awk -F "," '{print $1}'
8、启动服务、开机自启、停止服务、禁止开机自启
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
systemctl stop mysqld
systemctl disable mysqld
8.1、放行端口
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --reload
三、安装后
1、使用以方法来修改root用户的密码
方法一:利用mysqladmin
命令
mysqladmin -u root -p 旧密码 password 新密码
方法二:以root登入mysql> 后,使用set password指令设置
[root@mysql1 mysql]
mysql> set password for root@'localhost' = password('a123456!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
方法三:以root登入mysql> 后,使用alter指令设置
```bash
[root@mysql1 mysql]
mysql> alter user 'root'@'localhost' identified by 'a123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
方法四:以root登入mysql> 后,使用UPDATE更新相应的表记录
skip-grant-tables
systemctl restart mysqld
[root@mysql1 mysql]
MySQL 5.7.5 and earlier:
mysql> update mysql.user set password=password("a123456!") where user="root" and host="localhost";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
[root@mysql1 mysql]
MySQL 5.7.6 and later:
mysql> update mysql.user set authentication_string=password('a123456!') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
skip-grant-tables
systemctl restart mysqld
2、使用GRANT授权工具设置服务器端远程访问
[root@mysql1 mysql]
mysql> grant all privileges on *.* to 'root'@'%' identified by 'a123456!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>