0%

MySQL5.6 rpm安装

发表于 更新于 分类于
本文字数： 4.5k 阅读时长 ≈ 4 分钟
MySQL5.6 rpm安装

2016-09-05-mysql-5.6

安装

https://blog.csdn.net/leshami/article/details/23826753

https://dev.mysql.com/doc/refman/5.6/en/linux-installation-rpm.html

下载 rpm 包

https://dev.mysql.com/downloads/mysql/5.6.html#downloads
https://mirrors.sohu.com/mysql/MySQL-5.6/
https://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.6/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
tar -xvf MySQL-*
rpm -qa|grep mysql
yum remove mysql-libs-5.1.73-3.el6_5.x86_64 -y
yum install MySQL-server-5.6.26-1.linux_glibc2.5.x86_64.rpm -y
yum install -y MySQL-client-5.6.26-1.linux_glibc2.5.x86_64.rpm
cp /usr/my.cnf /etc/my.cnf

mkdir -p /var/log/mysql/
chmod 777 -R /var/log/mysql

vi /etc/my.cnf

cat /root/.mysql_secret
mysql_secure_installation
mysql -uroot -p
1
2
3
4
5
use mysql;
select host,user,password from user;
update user set host='%' where user='root' and host='127.0.0.1';
exit;
service mysql restart

配置/etc/my.cnf

所有配置

https://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

优化配置

https://blog.csdn.net/renfufei/article/details/29215297

mysql 四个配置

https://jeremy.zawodny.com/blog/archives/011421.html

1
2
3
4
max_connect_errors=1844674407370954751
connect_timeout=15
skip-name-resolve
slave_net_timeout=30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# #default-character-set = utf8

#character_set_server = utf8
character-set-server = utf8mb4
default-storage-engine=INNODB

general_log=OFF
general_log_file=/var/log/mysql/mysql-query.log
federated
#lower_case_table_names = 1
long_query_time = 10
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow-query.log
#log-slow-queries = /var/log/mysql/slowquery.log
log_queries_not_using_indexes=1
skip-host-cache
skip-name-resolve
max_connect_errors=1844674407370954751
connect_timeout=15
skip-name-resolve
slave_net_timeout=30

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
bulk_insert_buffer_size = 2G
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set = utf8

[mysql.server]
default-character-set = utf8

[client]
default-character-set = utf8

日志

日志详细解析

https://pangge.blog.51cto.com/6013757/1319304

开启慢查询

https://blog.chinaunix.net/uid-20769015-id-3469275.html

死锁处理

https://blog.csdn.net/dc_726/article/details/8576151

https://my.oschina.net/phacks/blog/699284

登录

1
mysql -h 192.168.88.21 -u root -p 指定host

忘记 root 密码

1
2
3
vi /etc/my.cnf
[mysqld]
skip-grant-tables
1
service mysql restart

在[mysqld]下添加skip-grant-tables参数，重启 mysql

1
2
3
4
5
mysql
use mysql;
update user set password=password("123456") where user="root";
flush privileges;
\q

客户端连接慢

1
2
3
4
5
6
7
8
9
第一种：在 MySQL 所在服务器的 hosts 上加入过程连入的服务器的IP <=> 域名的记录。

192.168.0.11   webserver
第二种：修改 MySQL 的配置，在 [mysqld] 段加入 skip-name-resolve。

[mysqld]
...
skip-name-resolve
重启 MySQL 使之生效。

https://infong.me/remote-mysql-connection-slow/

慢查询日志分析

命令行下，进入 mysql/bin 目录，输入 mysqldumpslow ?help 或 –help 可以看到这个工具的参数

1
2
3
4
5
6
7
8
9
10
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的 20 个 sql 语句和返回记录集最多的 20 个 sql
mysqldumpslow -t 10 -s t -g "left join" host-slow.log
这个是按照时间返回前 10 条里面含有左连接的 sql 语句。
mysqldumpslow -s c -t 10 /database/mysql/slow-log
这会输出记录次数最多的10条SQL语句，其中：
-s order，是表示按照何种方式排序，order值有：c、t、l、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
-t num，即为返回前面多少条的数据；
-g pattern，pattern可以写一个正则匹配模式，大小写不敏感的；