mysql学习笔记一

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%';

MariaDB [(none)]> show variables like '%buffer%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| aria_pagecache_buffer_size            | 134217728 |
| aria_sort_buffer_size                 | 134217728 |
| bulk_insert_buffer_size               | 8388608   |
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
| innodb_change_buffering               | all       |
| innodb_log_buffer_size                | 8388608   |
| join_buffer_size                      | 131072    |
| join_buffer_space_limit               | 2097152   |
| key_buffer_size                       | 134217728 |
| mrr_buffer_size                       | 262144    |
| myisam_sort_buffer_size               | 8388608   |
| net_buffer_length                     | 16384     |
| preload_buffer_size                   | 32768     |
| read_buffer_size                      | 131072    |
| read_rnd_buffer_size                  | 262144    |
| sort_buffer_size                      | 2097152   |
| sql_buffer_result                     | OFF       |
+---------------------------------------+-----------+
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 "+------------------------------+----------+\n"
printf "|                 连接数                  |\n"
printf "+------------------------------+----------+\n"
printf "| %30s | %9d  |\n", "Max connections", MAX_CONN
printf "| %30s | %9d  |\n", "Current connections", CURRENT_CONN
printf "| %30s | %9d  |\n", "Active connections", ACTIVE_CONN
printf "+------------------------------+----------+\n"
printf "|                 网络流量                |\n"
printf "+------------------------------+----------+\n"
printf "| %30s | %7.1f KB |\n", "Receive", RECE / 1024
printf "| %30s | %7.1f KB |\n", "Sent", SENT / 1024
printf "+------------------------------+----------+\n"
printf "|                 QPS/TPS                 |\n"
printf "+------------------------------+----------+\n"
printf "| %30s | %9d |\n", "QPS", QPS
printf "| %30s | %9d |\n", "TPS", TPS
printf "+------------------------------+----------+\n"
printf "|                 InnoDB buffer poll      |\n"
printf "+------------------------------+----------+\n"
printf "| %30s | %7.1f MB |\n", "Innodb_buffer_pool_size", POOL_TOTAL_SIZE / 1024 / 1024
printf "| %30s | %7.1f %  |\n", "Usage rate", POOL_USAGE_PERCENT
printf "| %30s | %7.1f %  |\n", "Hit rate", POOL_HIT_RATE
printf "+------------------------------+----------+\n"
} '