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/
没有评论