前往顾页
以后地位: 主页 > 精通Office > Ubuntu教程 >

Ubuntu 环境下 MySQL 装置与宁静优化

时候:2018-10-30 14:07来源:知行网www.zhixing123.cn 编辑:麦田守望者

 装置

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

# 设置数据库目次
sudo mysql_install_db

# 移除匿名帐户,禁用root长途登录
sudo mysql_secure_installation  # 答复n,y,y,y,y  

设置默许字符集
中文环境下,设置 utf8 为默许字符集,避免呈现乱码。

$ sudo vi /etc/mysql/my.cnf
[mysqld]
collation-server = utf8_unicode_ci  
init-connect = 'SET NAMES utf8'  
character-set-server = utf8

:wq保存建设,重启MySQL
$ sudo service mysql restart

# 检察字符集设置
$ mysql -u root -p
show variables like 'char%';  
show variables like 'collation%';  
加强 MySQL 宁静

迁徙数据库目次 MySQL 数据库默许途径 /var/lib/mysql,实际事情中,常常需求定制数据库途径,比如 /data/mysql,或 /opt/mysql,可所以伶仃的数据盘或分区,如许无益于机能调优和庇护数据宁静,同时也便利进行保护。
利用 mysql_install_db 从头初始化 datadir :

mkdir -p /data/mysql  
chown -R mysql:mysql /data/mysql  
mysql_install_db --user=mysql --basedir=/usr --datadir=/data/mysql  
rm -rf /var/lib/mysql  

禁用长途拜候等

$ sudo vi /etc/mysql/my.cnf
[mysqld]
datadir = /var/lib/mysql  #数据库文件目次  
bind-address = 127.0.0.1  #只许可本机拜候,或  
skip-networking  #禁用收集(但本机可以拜候)  
skip-show-database  #禁用SHOW DATABASES  
# 可增加:
local-infile=0  #避免加载本地文件,避免近似:SELECT load_file("/etc/passwd");

$ mysql -u root -p
use mysql  
UPDATE user SET Host='localhost' WHERE Host="%";  

用户名优化

DROP USER "";  # 或  
DELETE FROM user WHERE User="";  
RENAME USER root TO new_user;  # 或  
update user set user="new_user" where user="root";  # 或  
rename user 'root'@'localhost' to 'newAdminUser'@'localhost';  

暗码优化

UPDATE user SET Password=PASSWORD('newPassWord') WHERE User="user";  # or  
SET PASSWORD FOR 'username'@'%hostname' = PASSWORD('newpass');  
select user,host,password from user;  
FLUSH PRIVILEGES;  
# 或
$ mysqladmin -u username -p password newpass

清空号令汗青 客户端东西 mysql 会将履行的号令记其实以后用户目次下的 .mysql_history 文件中,此中可能包含暗码等敏感信息。

cat /dev/null > ~/.mysql_history  

利用日记 MySQL 日记包含错误日记、慢查询日记、一般日记和二进制日记,默许天生错误日记。在产品环境下,要公道利用日记,避免给体系增加不需求的压力。
建设文件

$ sudo vi /etc/mysql/my.cnf
log_error = /var/log/mysql/error.log  
#general_log_file = /var/log/mysql/mysql.log
#log_slow_queries = /var/log/mysql/mysql-slow.log
#log_bin          = /var/log/mysql/mysql-bin.log
#general_log      = 1
#long_query_time  = 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 '%log%';  

在代码中节制

SET GLOBAL general_log = 'ON';  
SET GLOBAL general_log = 'OFF';  
SET GLOBAL slow_query_log = 'ON';  
SET GLOBAL slow_query_log = 'OFF';  

日记文件地位

/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 'something' /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.err  

参考:http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs
利用SSL连接
检察SSL信息

mysql> SHOW VARIABLES LIKE '%ssl%';  
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=/etc/mysql/ca-cert.pem  
ssl-cert=/etc/mysql/server-cert.pem  
ssl-key=/etc/mysql/server-key.pem

# 重启 MySQL

$ sudo service mysql restart

# 建立利用SSL帐号

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'pass' REQUIRE SSL;  

建设客户端

$ sudo vi /etc/mysql/my.cnf
[client]
ssl-ca=/etc/mysql/ca-cert.pem  
ssl-cert=/etc/mysql/client-cert.pem  
ssl-key=/etc/mysql/client-key.pem

mysql -u ssluser -p -sss -e '\s' | grep SSL  

参考:http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html
利用SSH长途拜候 MySQL Workbench 是 MySQL 官方供应的数据库办理东西,免费跨平台,支撑数据库建模,支撑 MySQL 和 MariaDB,支撑经由过程 SSH 拜候长途 MySQL,即便将 MySQL 完整建设为本地拜候也没有问题。当然,可以继续利用 phpMyAdmin 办理数据库。

------分开线----------------------------
标签(Tag):Ubuntu MySQL装置与宁静优化
------分开线----------------------------
保举内容
猜你感兴趣