mysql学习笔记一
mysql安装基础清单：
1、centos7.4
2、mysql5.7
3、关闭防火墙
4、关闭selinux
1、linux下源码安装mysql，进入官网https://dev.mysql.com/downloads/mysql/5.7.html，下载mysql：wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
2、从官网使用rpm安装mysql方式
#下载mysql的rpmyum安装源 [root@localhost ~]#wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm #安装rpm包 [root@localhost ~]#yum -y localinstall mysql57-community-release-el7-8.noarch.rpm #安装mysql [root@localhost ~]#yum -y install mysql-community-server #在mysql配置文件里面配置utf8默认格式 [root@localhost ~]# vim /etc/my.cnf
#设置utf8存储格式 character_set_server=utf8 init_connect='SET NAMES utf8' #centos7下启动mysql服务 [root@localhost ~]# systemctl start mysqld #开启启动mysql [root@localhost ~]# systemctl enable mysqld #重新加载服务 [root@localhost ~]# systemctl daemon-reload #查看mysql初始化密码 [root@localhost ~]# grep 'temporary password' /var/log/mysqld.log 2019-10-06T01:31:10.249482Z 1 [Note] A temporary password is generated for root@localhost: BIZVkJO&F277 #使用初始化账号：root 密码：BIZVkJO&F277登录mysql [root@localhost ~]# mysql -uroot -p #修改root密码为MyNewPass4! mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 或者使用下面的方式修改root密码 mysql> set password for 'root'@'localhost'=password('MyNewPass4!'); #添加admin用户设置远程连接和密码 mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'oqB2heQz!' WITH GRANT OPTION; #查看数据库默认编码 mysql> show variables like '%character%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ mysql默认配置文件路径： 配置文件：/etc/my.cnf 日志文件：/var/log//var/log/mysqld.log 服务启动脚本：/usr/lib/systemd/system/mysqld.service socket文件：/var/run/mysqld/mysqld.pid
#设置/etc/my.cnf
[mysqld]
#数据库存储目录
datadir=/var/lib/mysql
#数据库锁目录
socket=/var/lib/mysql/mysql.sock
#设置ip连接
bind-address = 0.0.0.0
#设置存储引擎默认的字符编码
character_set_server=utf8
init_connect='SET NAMES utf8'
设置二进制的日志目录
log-bin=/var/lib/mysql/binlog
#设置二进制日志存放的天数
expire_logs_days = 10
#设置每个二进制文件的大小
max_binlog_size = 100M
#查看mysqly运行时的参数
MariaDB [(none)]> show variables like '%buffer%';
1 MariaDB [(none)]> show variables like '%buffer%'; 2 +---------------------------------------+-----------+ 3 | Variable_name | Value | 4 +---------------------------------------+-----------+ 5 | aria_pagecache_buffer_size | 134217728 | 6 | aria_sort_buffer_size | 134217728 | 7 | bulk_insert_buffer_size | 8388608 | 8 | innodb_blocking_buffer_pool_restore | OFF | 9 | innodb_buffer_pool_instances | 1 | 10 | innodb_buffer_pool_populate | OFF | 11 | innodb_buffer_pool_restore_at_startup | 0 | 12 | innodb_buffer_pool_shm_checksum | ON | 13 | innodb_buffer_pool_shm_key | 0 | 14 | innodb_buffer_pool_size | 134217728 | 15 | innodb_change_buffering | all | 16 | innodb_log_buffer_size | 8388608 | 17 | join_buffer_size | 131072 | 18 | join_buffer_space_limit | 2097152 | 19 | key_buffer_size | 134217728 | 20 | mrr_buffer_size | 262144 | 21 | myisam_sort_buffer_size | 8388608 | 22 | net_buffer_length | 16384 | 23 | preload_buffer_size | 32768 | 24 | read_buffer_size | 131072 | 25 | read_rnd_buffer_size | 262144 | 26 | sort_buffer_size | 2097152 | 27 | sql_buffer_result | OFF | 28 +---------------------------------------+-----------+ 29 23 rows in set (0.00 sec)
安全加固
数据库
1/禁止以root账户运行MySQL实例
2/数据库账户分配最小权限
3/账户密码满足复杂性，并90天更换一次
4/根据需要只监听本地或内网地址
5/禁止root账户远程
6/权限申请流程设置规范，合理
操作系统
1/防火墙只允许可信任IP访问
2/关闭不必要的账户/服务和端口
3/ssh使用证书+密码认证
4/及时给系统软件打补丁
5/系统/程序日志收集
数据库备份
1/定期备份
2/备份多份存储在不同位置
3/定期检查备份可用性
[root@localhost ~]# cat mysql_status.sh #!/bin/bash mysql -h127.0.0.1 -uroot -pqazwsx -e "show variables; show global status" | awk ' { VAR[$1]=$2 } END { MAX_CONN = VAR["max_connections"] CURRENT_CONN = VAR["Threads_connected"] ACTIVE_CONN = VAR["Threads_running"] RECE = VAR["Bytes_received"] SENT = VAR["Bytes_sent"] QPS = VAR["Questions"] / VAR["Uptime"] VAR[$1]=$2 } END { MAX_CONN = VAR["max_connections"] CURRENT_CONN = VAR["Threads_connected"] ACTIVE_CONN = VAR["Threads_running"] RECE = VAR["Bytes_received"] SENT = VAR["Bytes_sent"] QPS = VAR["Questions"] / VAR["Uptime"] TPS = (VAR["Com_commit"] + VAR["Com_rollback"] / VAR["Uptime"] POOL_TOTAL_SIZE = VAR["innodb_buffer_pool_size"] POOL_USAGE_PERCENT = 100 - ((VAR["Innodb_buffer_pool_pages_free"] / VAR["Innodb_buffer_pool_pages_total"]) * 100) POOL_HIT_RATE = VAR["Innodb_buffer_pool_read_requests"] / (VAR["Innodb_buffer_pool_read_requests"] + VAR["Innodb_buffer_pool_reads"]) * 100 printf "+------------------------------+----------+ " printf "| 连接数 | " printf "+------------------------------+----------+ " printf "| %30s | %9d | ", "Max connections", MAX_CONN printf "| %30s | %9d | ", "Current connections", CURRENT_CONN printf "| %30s | %9d | ", "Active connections", ACTIVE_CONN printf "+------------------------------+----------+ " printf "| 网络流量 | " printf "+------------------------------+----------+ " printf "| %30s | %7.1f KB | ", "Receive", RECE / 1024 printf "| %30s | %7.1f KB | ", "Sent", SENT / 1024 printf "+------------------------------+----------+ " printf "| QPS/TPS | " printf "+------------------------------+----------+ " printf "| %30s | %9d | ", "QPS", QPS printf "| %30s | %9d | ", "TPS", TPS printf "+------------------------------+----------+ " printf "| InnoDB buffer poll | " printf "+------------------------------+----------+ " printf "| %30s | %7.1f MB | ", "Innodb_buffer_pool_size", POOL_TOTAL_SIZE / 1024 / 1024 printf "| %30s | %7.1f % | ", "Usage rate", POOL_USAGE_PERCENT printf "| %30s | %7.1f % | ", "Hit rate", POOL_HIT_RATE printf "+------------------------------+----------+ " } '