安装MySQL与基本配置 - Iawen's Blog - 我喜欢这样自由的随手涂鸦, 因为我喜欢风......

首页 > 数据库安装MySQL与基本配置 2022-11-21 14:40:25

1. MySql 的安装

1.1 直接使用安装包安装

#yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm # 5.7
yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm # 8+
yum install mysql-community-server
systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld
systemctl daemon-reload

# MySQL GPG密钥过期问题
# 可以在运行安装程序之前导入密钥: 
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

2.1 编译安装mysql

2.1.1 源码下载

2.1.2 编译安装

从MySQL5.7.5开始Boost库是必需的, 下载Boost库到/usr/local/src下解压后复制到/usr/local/boost目录下

cmake \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DMYSQL_DATADIR=/usr/local/mysql/data \
    -DSYSCONFDIR=/etc \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_MEMORY_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
    -DMYSQL_TCP_PORT=3306 \
    -DENABLED_LOCAL_INFILE=1 \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DEXTRA_CHARSETS=all \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DWITH_BOOST=/usr/local/boost/boost

# 出错后重新运行配置, 需要删除CMakeCache.txt文件

2.1.3 配置MySQL

进一步修改my.cnf文件为自己的

mv /etc/my.cnf my.cnf.bak
cd /usr/local/mysql/support-files/
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

系统服务及路径变量设置

/usr/local/mysql/bin/mysqld_safe --user=mysql&
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
chmod 755 /etc/init.d/mysql 
chkconfig --add mysql
chkconfig --level 345 mysql on

vi /etc/profile
PATH=/usr/local/mysql/bin:$PATH
export PATH
source /etc/profile

2.1.4 Ubuntu 下需要注意的

vim /etc/apparmor.d/usr.sbin.mysqld

# Allow data dir access
  /data/mysql/ r,
  /data/mysql/** rwk,

service apparmor restart

2. MySQL的配置

2.1 配置文件 my.cnf

配置文件: /etc/my.cnf
日志文件: /var/log/mysqld.log
服务启动脚本: /usr/lib/systemd/system/mysqld.service
socket文件: /var/lib/mysql/mysql.sock

也可以进入MySQL, 使用命令查看

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.27-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 22 min 11 sec

Threads: 1  Questions: 102  Slow queries: 0  Opens: 122  Flush tables: 1  Open tables: 16  Queries per second avg: 0.076
--------------

然后配置PHP里对应的项: mysqli.default_socket = /var/lib/mysql/mysql.sock

注: 在启动MySQL服务时, 会按照一定次序搜索my.cnf, 先在/etc目录下找, 找不到则会搜索$basedir/my.cnf, 在本例中就是/usr/local/mysql/my.cnf, 这是新版MySQL的配置文件的默认位置！在使用"yum update"更新系统后, 需要检查下/etc目录下是否会多出一个my.cnf, 如果多出, 将它重命名成别的。否则, MySQL将使用这个配置文件启动, 可能造成无法正常启动等问题。

2.2 权限设置

2.2.1 系统帐号

使用下面的命令查看是否有mysql用户及用户组

cat /etc/passwd 查看用户列表
cat /etc/group 查看用户组列表

如果没有就创建

groupadd mysql
useradd -g mysql mysql

修改/usr/local/mysql及MySQL数据目录权限

chown -R mysql:mysql /usr/local/mysql
chown -R mysql. /home/mysqldata

2.2.2 数据库初始化和临时密码

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysqldata

# 保存随机密码或者手动查询: 
grep 'temporary password' /var/log/mysqld.log

2.2.3 MySQL自身的配置

用随机密码登录, 首先修改一下密码:

ALTER USER 'root'@'localhost' IDENTIFIED BY '---password---'; 

# caching_sha2_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456Aa=';

# 或者修改 my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password 

# MySQL 5.6及一下
# set password for 'root'@'localhost'=password('---password---'); 
# update mysql.user set authentication_string=password('---password---') where user='root' and host='localhost'; 

# 若要设置root用户可以远程访问, 或创建新用户与上同, 执行
CREATE USER 'root'@'%' IDENTIFIED BY '---password---';
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "---password---";

flush privileges;

2.2.4 忘记 root 密码

5.6版的更改密码用: 首先用mysql_safe启动数据库: mysql_safe --skip-grant-tables 5.7版, 修改/etc/my.conf, 添加参数skip-grant-tables

然后重启 MySQL 后使用 ALTER USER 命令修改密码。

2.3 MySQL 慢查询设置及检查

在/etc/my.cnf中的[mysqld]中加入如下代码:

slow_query_log=ON
slow_query_log_file=/data/mysql/log/mysql_slow_query.log
long_query_time=2
log_queries_not_using_indexes = ON

第一句是开启慢查询第二句是用来定义慢查询日志的路径第三句是用来定义查过多少秒的查询算是慢查询, 我这里定义的是2秒第四句就是记录下没有使用索引的query

mysql> show variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF                           |
| log_slow_slave_statements | OFF                           |
| slow_launch_time          | 2                             |
| slow_query_log            | ON                            |
| slow_query_log_file       | /data/logs/mysql/slow_log.log |
+---------------------------+-------------------------------+
5 rows in set (0.01 sec)

对于前面的慢查询日志我们使用mysqldumpslow来提取如下:

mysqldumpslow -s at,al /var/lib/mysql/xxxxxxx-slow.log

#以下是按照最大耗用时间排最后, 只显示2条的方式格式化日志文件 
mysqldumpslow -r -t 2 /var/lib/mysql/xxxxxxx-slow.log

2.4 MySQL 的主从设置

2.4.1 主服务器(必须开启二进制日志功能/etc/my.cnf):

server-id = 1
expire_logs_days = 7
log-bin = /home/mysqldata/iawen-binlog

FLUSH TABLES; 
SHOW MASTER STATUS;

查看并记录下当前的日志文件及文件位置

2.4.2 从服务器:

server-id=2#需要与主服务器不同

CHANGE MASTER TO MASTER_HOST='--MASTER IP--',MASTER_USER='root',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='iawen-binlog.000001', MASTER_LOG_POS=294;
 
START SLAVE;
SHOW SLAVE STATUS;

将从 MySQL设置为只读状态的命令(MySQL进行数据库迁移时, 限定master主库不能有任何数据变化, 就可以通过这种方式来设定。):

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";

2.5 锁表检查:

mysql> show status like 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 3791133 |
| Table_locks_waited | 0 |
+-----------------------+---------+

Table_locks_immediate 指的是能够立即获得表级锁的次数 Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数

查看正在被锁定的的表

mysql> show open tables where in_use>0;
Empty set (0.00 sec)

2.6 其他配置设定与说明

[mysqld]
server-id = 1
default-character-set = utf8
datadir = /data/mysql
socket = /tmp/mysql.sock
log_error = /data/logs/mysql/error.log
pid-file = /var/run/mysqld/mysqld.pid

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

expire_logs_days = 7
log-bin = isa-binlog
binlog_format = mixed
# skip_grant_tables

slow_query_log = TRUE
slow_query_log_file = /data/logs/mysql/slow_log.log
long_query_time = 2

# 关于内存的设定, 注意, 内存的简单计算方式为: 
# key_buffer + (sort_buffer + read_buffer ) * max_connection
# 且总量不可高于实际的物理内存量！
# 128 + (2+2)*150 = 728MB
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer_size = 2M
max_connections = 150
max_connect_errors = 10

max_allowed_packet = 4M
#table_cache = 1024
myisam_sort_buffer_size = 32M
query_cache_size = 16M
table_open_cache = 64
net_buffer_length = 2M
read_rnd_buffer_size = 2M

skip-name-resolve

log_timestamps = SYSTEM
bind_address = 0.0.0.0

3. MySQL缓存

3.1 Query cache

Query cache 作用于整个MySQL实例, 主要用于缓存MySQL中的ResultSet, 也就是一条SQL语句执行的结果集, 所以仅仅只能针对select 语句。 Query cache的使用需要多多个参数配合, 其中最为关键的是 :

query_cache_size: 用于设置缓存ResultSet 的内存大小
query_cache_type: 设置在何种场景下使用Query Cache
- 0: OFF
- 1: ON

3.2 DEMAND 表示只有显式要求才使用

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.07 sec)

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16760152 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.01 sec)

通过调节以下几个参数可以知道query_cache_size 设置得是否合理:

Qcache inserts: 表示多少次未命中然后插入
Qcache hits: 值大则表明缓冲使用非常频繁
Qcache lowmem prumes: 值非常大, 则表示经常出现缓冲不够的情况
Qcache free blocks: 值大则表明缓存区中的碎片很多, 需要寻找合适的机会进行整理
Query Cache命中率 = Qcache_hits / (Qcache_hits + Qcache_inserts)

当使用InnoDB存储引擎的时候, innodb_buffer_pool_size 参数可能是影响性能的最为关键的一个参数。(类似Oracle数据库的顾db_cache_size) 如果有足够的内存, 尽可能将该参数设置到足够大, 将尽可能多的InnoDB的索引及数据都放入到该缓冲区域中, 直至全部。可以通过下面公式来计算缓存命中率:

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

table_cache是一个非常重要的MySQL性能参数, 主要用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表, 因此该参数与max_connections有关。

4. MySql最左前缀原则

4.1 B-Tree 索引和 Hash 索引的对比

对于 B-tree 和 hash 数据结构的理解能够有助于预测不同存储引擎下使用不同索引的查询性能的差异, 尤其是那些允许你选择 B-tree 或者 hash 索引的内存存储引擎。

4.1.1 B-Tree 索引的特点

B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。有时, 即使有索引可以使用, MySQL 也不使用任何索引。发生这种情况的场景之一就是优化器估算出使用该索引将要求 MySql 去访问这张表的绝大部分记录。这种情况下, 一个表扫描可能更快, 因为它要求更少量的查询。但是, 如果这样的一个查询使用了 LIMIT 来检索只是少量的记录时, MySql 还是会使用索引, 因为它能够更快地找到这点记录并将其返回。

4.1.2 Hash 索引的特点

Hash 索引有着与刚才所讨论特点的相比截然不同的特点:

Hash 索引只能够用于使用 = 或者 <=> 运算符的相等比较(但是速度更快)。Hash 索引不能够用于诸如 < 等用于查找一个范围值的比较运算符。依赖于这种单值查找的系统被称为 “键-值存储”; 对于这种系统, 尽可能地使用 hash 索引。
优化器不能够使用 hash 索引来加速 ORDER BY 操作。这种类型的索引不能够用于按照顺序查找下一个条目。
MySql 无法使用 hash 索引估计两个值之间有多少行(这种情况由范围优化器来决定使用哪个索引)。如果你将一张 MyISAM 或 InnoDB 表转换成一个 hash 索引的内存表时, 一些查询可能会受此影响
查找某行记录必须进行全键匹配。而 B-tree 索引, 任何该键的左前缀都可用以查找记录。

4.2 最左前缀原则

通过实例理解单列索引、多列索引以及最左前缀原则实例: 现在我们想查出满足以下条件的用户id:

mysql>SELECT `uid` FROM people WHERE `lname`='Liu' AND `fname`='Zhiqun' AND `age`=26;

因为我们不想扫描整表, 故考虑用索引。

4.2.1 单列索引:

ALTER TABLE people ADD INDEX lname (lname);

将lname列建索引, 这样就把范围限制在lname='Liu'的结果集1上, 之后扫描结果集1, 产生满足fname='Zhiqun'的结果集2, 再扫描结果集2, 找到 age=26的结果集3, 即最终结果。由于建立了lname列的索引, 与执行表的完全扫描相比, 效率提高了很多, 但我们要求扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除lname列上的索引, 再创建fname或者age 列的索引, 但是, 不论在哪个列上创建索引搜索效率仍旧相似。

4.2.2 多列索引:

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

为了提高搜索效率, 我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存, 所以我们不用扫描任何记录, 即可得到最终结果。注: 在mysql中执行查询时, 只能使用一个索引, 如果我们在lname,fname,age上分别建索引,执行查询时, 只能使用一个索引, mysql会选择一个最严格(获得结果集记录数最少)的索引。

4.2.3 最左前缀:

顾名思义, 就是最左优先, 上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引, (lname,fname)组合索引以及(lname,fname,age)组合索引。注: 在创建多列索引时, 要根据业务需求, where子句中使用最频繁的一列放在最左边。拓展: 在网上看到一个关于最左前缀原则提出这么一个例子。多列字段做索引, state/city/zipCode, 想要索引生效的话, 只能使用如下的组合

state/city/zipCode
state/city
state

其他方式(如city, city/zipCode), 则索引不会生效这种现象是怎么导致的？和索引的存储方式有关吗？本人页参考了下其他网友的观点, 个人认为, 所谓最左前缀原则就是先要看第一列, 在第一列满足的条件下再看左边第二列, 以此类推。 有位网友描述得很形象: 你可以认为联合索引是闯关游戏的设计例如你这个联合索引是state/city/zipCode 那么state就是第一关 city是第二关, zipCode就是第三关你必须匹配了第一关, 才能匹配第二关, 匹配了第一关和第二关, 才能匹配第三关你不能直接到第二关的索引的格式就是第一层是state, 第二层才是city 索引是因为B+树结构所以查找快如果单看第三列是非排序的。多列索引是先按照第一列进行排序, 然后在第一列排好序的基础上再对第二列排序, 如果没有第一列的话, 直接访问第二列, 那第二列肯定是无序的, 直接访问后面的列就用不到索引了。所以如果不是在前面列的基础上而是但看后面某一列, 索引是失效的。大家有不同的观点可以提出, 这是个人理解的观点。

Iawen's Blog

我喜欢这样自由的随手涂鸦, 因为我喜欢风......

笔记导航

最新笔记