mysql安全优化_MySQL安装与安全优化

Ubuntu 环境下 MySQL 安装与安全优化。

安装sudo apt-get update

sudo apt-get install mysql-server mysql-client # 设置root密码

# 设置数据库目录

sudo mysql_install_db

# 移除匿名帐户&＃xff0c;禁用root远程登录

sudo mysql_secure_installation # 回答n,y,y,y,y

设置默认字符集

中文环境下&＃xff0c;设置 utf8 为默认字符集&＃xff0c;防止出现乱码。$ sudo vi /etc/mysql/my.cnf

[mysqld]

collation-server &＃61; utf8_unicode_ci

init-connect &＃61; &＃39;SET NAMES utf8&＃39;

character-set-server &＃61; utf8

:wq保存配置&＃xff0c;重启MySQL

$ sudo service mysql restart

# 查看字符集设置

$ mysql -u root -p

show variables like &＃39;char%&＃39;;

show variables like &＃39;collation%&＃39;;

加强 MySQL 安全

迁移数据库目录

MySQL 数据库默认路径 /var/lib/mysql&＃xff0c;实际工作中&＃xff0c;常常需要定制数据库路径&＃xff0c;比如 /data/mysql&＃xff0c;或者 /opt/mysql&＃xff0c;可以是单独的数据盘或者分区&＃xff0c;这样有利于性能调优和保护数据安全&＃xff0c;同时也方便进行维护。

使用 mysql_install_db 重新初始化 datadir &＃xff1a;mkdir -p /data/mysql

chown -R mysql:mysql /data/mysql

mysql_install_db --user&＃61;mysql --basedir&＃61;/usr --datadir&＃61;/data/mysql

rm -rf /var/lib/mysql

禁用远程访问等$ sudo vi /etc/mysql/my.cnf

[mysqld]

datadir &＃61; /var/lib/mysql #数据库文件目录

bind-address &＃61; 127.0.0.1 #只允许本机访问&＃xff0c;或

skip-networking #禁用网络(但本机可以访问)

skip-show-database #禁用SHOW DATABASES

# 可增加&＃xff1a;

local-infile&＃61;0 #禁止加载本地文件&＃xff0c;防止类似&＃xff1a;SELECT load_file("/etc/passwd");

$ mysql -u root -p

use mysql

UPDATE user SET Host&＃61;&＃39;localhost&＃39; WHERE Host&＃61;"%";

用户名优化DROP USER ""; # 或

DELETE FROM user WHERE User&＃61;"";

RENAME USER root TO new_user; # 或

update user set user&＃61;"new_user" where user&＃61;"root"; # 或

rename user &＃39;root&＃39;&＃64;&＃39;localhost&＃39; to &＃39;newAdminUser&＃39;&＃64;&＃39;localhost&＃39;;

密码优化UPDATE user SET Password&＃61;PASSWORD(&＃39;newPassWord&＃39;) WHERE User&＃61;"user"; # or

SET PASSWORD FOR &＃39;username&＃39;&＃64;&＃39;%hostname&＃39; &＃61; PASSWORD(&＃39;newpass&＃39;);

select user,host,password from user;

FLUSH PRIVILEGES;

# 或

$ mysqladmin -u username -p password newpass

清空命令历史

客户端工具 mysql 会将执行的命令记录在当前用户目录下的 .mysql_history 文件中&＃xff0c;其中可能包含密码等敏感信息。cat /dev/null > ~/.mysql_history

使用日志

MySQL 日志包括错误日志、慢查询日志、一般日志和二进制日志&＃xff0c;默认生成错误日志。在产品环境下&＃xff0c;要合理使用日志&＃xff0c;避免给系统增加不必要的压力。

配置文件$ sudo vi /etc/mysql/my.cnf

log_error &＃61; /var/log/mysql/error.log

#general_log_file &＃61; /var/log/mysql/mysql.log

#log_slow_queries &＃61; /var/log/mysql/mysql-slow.log

#log_bin &＃61; /var/log/mysql/mysql-bin.log

#general_log &＃61; 1

#long_query_time &＃61; 2

#log-queries-not-using-indexes

/etc/mysql/conf.d/mysqld_safe_syslog.cnf

/etc/logrotate.d/mysql-server

查看配置sudo service mysql restart

mysql> SHOW VARIABLES LIKE &＃39;%log%&＃39;;

在代码中控制SET GLOBAL general_log &＃61; &＃39;ON&＃39;;

SET GLOBAL general_log &＃61; &＃39;OFF&＃39;;

SET GLOBAL slow_query_log &＃61; &＃39;ON&＃39;;

SET GLOBAL slow_query_log &＃61; &＃39;OFF&＃39;;

日志文件位置/var/lib/mysql/{host_name}.log

/var/lib/mysql/{host_name}.err

/var/lib/mysql/{host_name}-slow.log

/var/log/mysql.err - MySQL Error log file

/var/log/mysql.log - MySQL log file

sudo ls -l /var/log/mysql*

日志监控查看grep &＃39;something&＃39; /var/log/mysql.err

tail -f /var/log/mysql/mysql.log

tail -f /var/log/mysql.err

tail -f /var/log/syslog

less /var/log/mysql.errmysql> SHOW VARIABLES LIKE &＃39;%ssl%&＃39;;

mysql> \s

$ cat /etc/apparmor.d/usr.sbin.mysqld

...

/etc/mysql/*.pem r,

制作SSL证书sudo su -

cd /etc/mysql

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3600 \

-key ca-key.pem -out ca-cert.pem

openssl req -newkey rsa:2048 -days 3600 \

-nodes -keyout server-key.pem -out server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3600 \

-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

openssl req -newkey rsa:2048 -days 3600 \

-nodes -keyout client-key.pem -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3600 \

-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

配置服务端$ sudo vi /etc/mysql/my.cnf

[mysqld]

ssl-ca&＃61;/etc/mysql/ca-cert.pem

ssl-cert&＃61;/etc/mysql/server-cert.pem

ssl-key&＃61;/etc/mysql/server-key.pem

# 重启 MySQL

$ sudo service mysql restart

# 创建使用SSL帐号

GRANT ALL PRIVILEGES ON *.* TO &＃39;ssluser&＃39;&＃64;&＃39;%&＃39; IDENTIFIED BY &＃39;pass&＃39; REQUIRE SSL;

配置客户端$ sudo vi /etc/mysql/my.cnf

[client]

ssl-ca&＃61;/etc/mysql/ca-cert.pem

ssl-cert&＃61;/etc/mysql/client-cert.pem

ssl-key&＃61;/etc/mysql/client-key.pem

mysql -u ssluser -p -sss -e &＃39;\s&＃39; | grep SSL

MySQL Workbench 是 MySQL 官方提供的数据库管理工具&＃xff0c;免费跨平台&＃xff0c;支持数据库建模&＃xff0c;支持 MySQL 和 MariaDB&＃xff0c;支持通过 SSH 访问远程 MySQL&＃xff0c;即使将 MySQL 完全配置为本地访问也没有问题。当然&＃xff0c;可以继续使用 phpMyAdmin 管理数据库。