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 管理数据库。
京公网安备 11010802041100号