环境：
systemctl stop firewalld
systemctl enable firewalld
vim /etc/sysconfig/selinux
需要的包：MySQL-5.6.44-1.el6.x86_64.rpm-bundle
链接: https://pan.baidu.com/s/1re0_XQZgYcg-JEGl95g9-g 密码: 8703
卸载MariaDB
rpm -qa | grep -i mariadb
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
卸载已有Mysql
卸载旧版本mysql
rpm -qa | grep -i mysql
如果有，就使用上面卸载mariadb的命令进行卸载、删除服务
systemctl list-unit-files --type=service | grep -i mysql
如果查找到mysql服务，使用下面的命令进行删除 chkconfig --del mysql
删除mysql分散的文件夹
[root@localhost ~]# whereis mysql mysql: /usr/lib64/mysql rm -rf /usr/lib64/mysql
安装依赖
yum install perl
yum -y install autoconf
下载mysql并上传到服务器中
增加mysql用户组
- 检查mysql用户及组是否存在，如果没有执行下面命令
cat /etc/group |grep mysql
- 创建组
groupadd mysql
- 创建用户并把该用户加入到组mysql，这里的 -r是指该用户是内部用户，不允许外部登录
useradd -r -g mysql mysql
- 给用户mysql设置密码，需要输入2次
passwd mysql
安装
- 安装mysql
解压
tar -xvf MySQL-5.6.44-1.el7.x86_64.rpm-bundle.tar
- 安装mysql
rpm -ivh MySQL-client-5.6.44-1.el7.x86_64.rpm
rpm -ivh MySQL-devel-5.6.44-1.el7.x86_64.rpm
rpm -ivh MySQL-server-5.6.44-1.el7.x86_64.rpm
- 如果出现如下错误
[root@localhost mysoft]# rpm -ivh MySQL-server-5.6.44-1.el7.x86_64.rpm
warning: MySQL-server-5.6.44-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY error: Failed dependencies: net-tools is needed by MySQL-server-5.6.44-1.el7.x86_64
- 需要安装net-tools
yum -y install net-tools
安装完，再次执行
rpm -ivh MySQL-server-5.6.44-1.el7.x86_64.rpm命令
- 修改密码，查看mysql状态
[root@localhost mysoft]# service mysql status ERROR! MySQL is not running
如果是开启服务状态，用service mysql status关闭服务
- 绕过密码登录
***_centos7_*** [root@localhost mysoft]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 1522 [root@localhost mysoft]# Logging to '/var/lib/mysql/localhost.localdomain.err'. 19052817:25:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
***_centos6_***
[root@localhost mysoft]# sudo mysqld_safe --skip-grant-tables &
- 登录
mysql -u root -p
要求输入密码，直接回车
- 切换数据库
use mysql
- 查询用户
select Host,User,Password from user;
- 修改密码
UPDATE user SET password=password("123") WHERE user='root'; mysql> flush privileges;
exit
- 重启mysql服务
[root@localhost mysoft]# service mysql restart
Shutting down MySQL..19052817:30:50 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.localdomain.pid ended SUCCESS! Starting MySQL. SUCCESS! [1]+ Done mysqld_safe --user=mysql --skip-grant-tables --skip-networking
- 第一次登录必须修改密码
mysql -u root -proot
mysql> set password = password('root');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 赋予任何主机访问数据的权限（远程访问）
mysql> grant all privileges on *.* to 'root'@'%' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)
#刷新权限后退出
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
exit
- 重启mysql
[root@localhost mysoft]# service mysql restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
开放端口
查看firewall状态（runing:运行，not runing:没有运行），如果没有运行，用systemctl start firewalld启动
[root@localhost mysoft]# firewall-cmd --state running
添加3306端口
[root@localhost mysoft]# firewall-cmd --permanent --zone=public --add-port=3306/tcp success
重新加载firewall
[root@localhost mysoft]# firewall-cmd --reload success
设置mysql开机启动
- 查看mysql服务
[root@localhost mysoft]# chkconfig --list mysql
Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
- 开启MySQL服务自动开启命令
chkconfig mysql on
- mysql集合重要目录
/var/lib/mysql 数据库文件 /usr/share/mysql 命令及配置文件 /usr/bin mysqladmin、mysqldump等命令
简单优化mysql配置
修改max_connections参数
--登录mysql数据库
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2965
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
--查看连接数
mysql> show processlist;
+------+------+---------------------+--------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+---------------------+--------------+---------+------+----------+------------------+
| 2821 | root | 192.168.1.124:58526 | testdb | Sleep | 7180 | | NULL |
| 2822 | root | 192.168.1.124:58527 | testdb | Sleep | 7180 | | NULL |
| 2840 | root | 192.168.1.124:58741 | testdb | Sleep | 5790 | | NULL |
| 2841 | root | 192.168.1.124:58742 | testdb | Sleep | 5820 | | NULL |
--查看最大连接数
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.01 sec)
--当processlist的数量大于查询到的max_connections时，就会出现too many connections的提示信息。
--修改最大连接数
set GLOBAL max_connections=1000;
--永久修改连接数
找到 my.cnf 修改配置文件
vim /etc/my.cnf
—在[mysqld]下面添加
max_connections=2000
然后重新启动mysql：systemctl restart mysqld
修改wait_timeout参数
--永久修改：
修改参数配置文件
vi /etc/my.cnf
[mysqld]
wait_timeout = 1800（单位秒)
#wait_timeout = 28800（默认是28800，8个小时)
interactive_timeout = 1800（单位秒）
#interactive_timeout = 28800（默认是28800，8个小时）
#增加以上两列即可，因为官方文档要求修改此参数必须同时修改interactive_timeout
查看数据库参数是否修改成功
Show variables like 'wait_timeout';
临时修改：
mysql -uroot -ptgram123
mysql> set global wait_timeout=1800;
Query OK,0 rows affected(0.00 sec)
mysql> set global interactive_timeout=1800;
mysql> show global variables like '%timeout%';
mysql> show variables like 'wait_timeout';
注意
必须要同时修改l级interactive_timeout和wait_timeout才能生效