如何部署MySQL二进制安装包

MySQL & MariaDB

1 简介

MySQL常见的安装方式有三种,
– rpm包安装
– 二进制包安装
– 源码包安装
注:本章将完成二进制包安装

2 系统环境

IP Address = 10.168.0.90
OS = CentOS 6.8 x86_64
Host Name = mysql_bin.cmdschool.org

3 安装前的准备

3.1 安装基础包

yum install -y libaio

3.2 下载软件包

cd ~
wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

3.3 解压软件包

tar -xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz

4 部署软件包

4.1 部署软件包

mv mysql-5.6.40-linux-glibc2.12-x86_64 /usr/local/
ln -s /usr/local/mysql-5.6.40-linux-glibc2.12-x86_64/ /usr/local/mysql

4.2 配置并测试环境变量

echo 'export MYSQL_HOME=/usr/local/mysql' > /etc/profile.d/mysql.sh
echo 'export PATH=${MYSQL_HOME}/bin:$PATH' >> /etc/profile.d/mysql.sh
echo 'export PATH=${MYSQL_HOME}/scripts:$PATH' >> /etc/profile.d/mysql.sh
source /etc/profile

测试环境变量:

mysql -V

4.3 创建运行用户

groupadd  -g 27 mysql
useradd -u 27 -g 27 -d /var/lib/mysql -c 'MySQL Server' -s /bin/false mysql

4.5 测试部署

4.5.1 初始化数据库

cd /usr/local/mysql
scripts/mysql_install_db -user=mysql

可见如下错误提示:

Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

4.5.2 解决依赖关系并重试

yum install -y numactl
scripts/mysql_install_db -user=mysql

将会看到如下提示:

[...]
2018-06-02 17:30:31 1502 [Note] InnoDB: Starting shutdown...
2018-06-02 17:30:33 1502 [Note] InnoDB: Shutdown completed; log sequence number 1626007
OK
[...]

4.6 尝试启动

4.6.1 执行启动脚本

cd /usr/local/mysql
bin/mysqld_safe --user=mysql &

可见如下提示:

180602 20:11:22 mysqld_safe Logging to '/var/log/mysqld.log'.
180602 20:11:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180602 20:11:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

4.6.2 启动日志检查

tail /var/log/mysqld.log

可见如下错误,

2018-06-02 20:05:07 2087 [ERROR] Can't start server: can't check PID filepath: No such file or directory

4.6.3 解决故障并重试

mkdir -p /var/run/mysqld/
chown mysql:mysql /var/run/mysqld/
bin/mysqld_safe --user=mysql &

4.7 检查运行状态

4.7.1 检查服务进程

ps -ef | grep mysql | grep -v grep

显示如下:

root       2685   1614  0 20:16 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql      2842   2685  0 20:16 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

4.7.2 检查服务端口

netstat -antp | grep mysqld

显示如下:

tcp        0      0 :::3306                     :::*                        LISTEN      2842/mysqld

4.8 部署服务控制

4.8.1 部署启动脚本

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

4.8.2 修改脚本参数

vim /etc/init.d/mysqld

修改如下配置:

basedir=/usr/local/mysql
datadir=/var/lib/mysql
mysqld_pid_file_path=/var/run/mysqld/mysqld.pid

4.8.3 测试控制脚本

/etc/init.d/mysqld stop
/etc/init.d/mysqld start
/etc/init.d/mysqld restart
/etc/init.d/mysqld status

4.8.4 配置开机自启动

chkconfig mysqld on

5 优化配置

5.1 定义客户端的sock

5.1.1 修改配置文件

vim /etc/my.cnf

加入如下配置:

[client]
port=3306
socket=/var/lib/mysql/mysql.sock

5.1.2 重启使配置生效

/etc/init.d/mysqld restart

5.1.3 测试登录

mysql -uroot -p

注:请直接按回车登录,另外如果你没有做以上优化之前,请使用以下方式登录,

mysql -uroot --socket=/var/lib/mysql/mysql.sock

5.2 修改管理员密码

5.2.1 登录数据库

mysql -uroot -p

5.2.2 执行修改命令

set password for 'root'@'localhost' = password('mypassword');
set password for 'root'@'localhost.localdomain' = password('mypassword');
set password for 'root'@'127.0.0.1' = password('mypassword');
set password for 'root'@'::1' = password('mypassword');
set password for ''@'localhost' = password('mypassword');
set password for ''@'localhost.localdomain' = password('mypassword');
flush privileges;

或者执行:

update mysql.user set password = password('mypassword') where user = 'root';
update mysql.user set password = password('mypassword') where user = '';
flush privileges;

5.2.3 检查修改结果

select user,host,password from mysql.user;

显示如下:

+------+-----------------------+-------------------------------------------+
| user | host                  | password                                  |
+------+-----------------------+-------------------------------------------+
| root | localhost             | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
| root | localhost.localdomain | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
| root | 127.0.0.1             | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
| root | ::1                   | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
|      | localhost             | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
|      | localhost.localdomain | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
+------+-----------------------+-------------------------------------------+
6 rows in set (0.00 sec)

5.3 删除anonymous用户

5.3.1 登录数据库

mysql -uroot -p

5.3.2 执行修改命令

drop user ''@'localhost';
drop user ''@'localhost.localdomain';

5.3.3 检查修改结果

select user,host from mysql.user;

显示如下:

+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| root | ::1                   |
| root | localhost             |
| root | localhost.localdomain |
+------+-----------------------+
4 rows in set (0.00 sec)

5.4 删除测试库访问权限

5.4.1 登录数据库

mysql -uroot -p

5.4.2 执行修改命令

delete from mysql.db where Db like 'test%';
flush privileges;

5.5 删除测试库

5.5.1 登录数据库

mysql -uroot -p

5.5.2 执行修改命令

drop database test;

5.6 禁用历史记录

5.6.1 删除历史记录文件

rm $HOME/.mysql_history

5.6.2 将历史记录指向空设备

ln -s /dev/null $HOME/.mysql_history

5.7 禁用远程登录

5.7.1 修改配置文件

vim /etc/my.cnf

加入如下配置:

[mysqld]
skip-networking

5.7.2 重启使配置生效

/etc/init.d/mysqld restart

5.8 修改root用户名

5.8.1 登录数据库

mysql -uroot -p

5.8.2 执行修改命令

update mysql.user set user="admin" where user="root";
flush privileges;

5.8.3 检查修改结果

select user,host from mysql.user;

显示如下:

+-------+-----------------------+
| user  | host                  |
+-------+-----------------------+
| admin | 127.0.0.1             |
| admin | ::1                   |
| admin | localhost             |
| admin | localhost.localdomain |
+-------+-----------------------+
4 rows in set (0.00 sec)

参阅文档
===========

软件下载:
————
https://dev.mysql.com/downloads/mysql/

二进制安装:
———–
https://dev.mysql.com/doc/refman/5.6/en/binary-installation.html

MySQL服务启动脚本:
————-
https://dev.mysql.com/doc/refman/5.6/en/mysqld-safe.html

MySQL手动安全配置:
————–
http://howtolamp.com/lamp/mysql/5.6/securing/

没有评论

发表评论

MySQL & MariaDB
如何开启Mariadb或MySQL Binlog?

1 前言 一个问题,一篇文章,一出故事。 笔者需要备份Mariadb的binlog,于是整理此文。 …

MySQL & MariaDB
如何迁移MariaDB或MySQL数据目录?

1 前言 一个问题,一篇文章,一出故事。 笔者之前的数据库使用默认部署在根目录下,现在想迁移至专用的 …

MySQL & MariaDB
如何基于CentOS 8.x安装MariaDB?

1 基础知识 – MariaDB数据库管理系统是MySQL的一个分支 – M …