安装之前
- 更新yum
sudo yum update
- 安装wget
yum install wget
安装
- 下载库并且更新
#下载
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
#更新
yum update
- 安装mysql-server服务,并且启动
#安装
sudo yum install mysql-server
#启动
sudo systemctl start mysqld
配置MySQL Server
- 运行mysql_secure_installation脚本解决安全问题
安装完成后.安装记录中存在默认的root密码,安装记录的位置默认:/var/log/mysql.log
sudo grep 'temporary password' /var/log/mysqld.log
# 重置root密码,删除匿名账户,删除测试表等安全设置
sudo mysql_secure_installation
创建新数据库和用户
#创建数据库
create database testdb;
#创建用户
create user 'testuser'@'localhost' identified by 'password';
#授权
grant all on testdb.* to 'testuser' identified by 'password';
#或者
# testdb 可以使用任何IP操作所有表(不推荐)
GRANT ALL PRIVILEGES ON *.* TO 'testdb'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
#刷新
flush privileges;
调试MySQL服务
- MySQL Tuner 是mysql的调试脚本.Mysql运行24小时候执行
#下载
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
#运行
perl ./mysqltuner.pl
>> MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.23
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(45K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 86 warning(s).
[!!] /var/log/mysqld.log contains 62 error(s).
[--] 4 start(s) detected in /var/log/mysqld.log
[--] 1) 2018-08-08T03:15:11.068296Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-08-08T02:38:22.849221Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-08-07T08:50:30.315177Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-08-07 16:36:36 4378 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2018-08-08T03:02:01.814508Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-08-07T08:58:56.299375Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-08-07 16:43:50 4378 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 19m 7s (46 q [0.040 qps], 12 conn, TX: 47K, RX: 3K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 338.9M
[--] Other process memory: 80.4M
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 170.1M (1.07% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (2.13% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/46)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 8.33% (1/12)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 11 selects)
[OK] Query cache prunes per day: 0
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 7% (1 on disk / 13 total)
[OK] Thread cache hit rate: 91% (1 created / 12 connections)
[OK] Table cache hit rate: 93% (111 open / 119 opened)
[OK] Open file limit used: 0% (23/5K)
[OK] Table locks acquired immediately: 100% (139 immediate / 139 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.4% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 75.6% (41 cached / 10 reads)
[OK] Write Key buffer hit rate: 100.0% (7 cached / 7 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 83.11% (1122 hits/ 1350 total)
[!!] InnoDB Write Log efficiency: 25% (5 hits/ 20 total)
[OK] InnoDB log waits: 0.00% (0 waits / 15 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: http://bit.ly/2wgkDvS
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
