Mysql安装配置(RPM)

最终版本：

mysql version:

Server version: 5.6.19-log MySQL Community Server (GPL)

Centos Version:

CentOS Linux release 7.6.1810 (Core)

卸载旧版本mysql
rpm -qa|grep mariadb
rpm -qa | grep -i mysql
mariadb-libs-5.5.65-1.1.al7.x86_64
rpm -e --nodeps mariadb-libs-5.5.65-1.1.al7.x86_64
yum list installed | grep mysql
yum list installed | grep mariadb
yum -y remove mysqlXXX

如果有，就使用上面卸载mariadb的命令进行卸载。
删除服务
systemctl list-unit-files --type=service | grep -i mysql
chkconfig --del mysql

删除mysql分散的文件夹
whereis mysql
mysql: /usr/lib64/mysql
rm -rf /usr/lib64/mysql

安装依赖
yum -y install perl autoconf net-tools

5、增加mysql用户组
检查mysql用户及组是否存在，如果没有执行下面命令
cat /etc/group |grep mysql
创建组
groupadd mysql
创建用户并把该用户加入到组mysql，这里的 -r是指该用户是内部用户，不允许外部登录
useradd -d /app/mysql -r -g mysql mysql
给用户mysql设置密码，需要输入2次密码
passwd mysql
New password: 
BAD PASSWORD: The password is a palindrome
Retype new password: 
passwd: all authentication tokens updated successfully.

6、安装

安装mysql
cd /opt/software/mysql
MySQL-client-5.6.19-1.rhel5.x86_64.rpm          mysql-community-devel-5.6.19-2.el5.x86_64.rpm  MySQL-server-5.6.19-1.rhel5.x86_64.rpm
mysql-community-common-5.6.19-2.el5.x86_64.rpm  MySQL-devel-5.6.19-1.rhel5.x86_64.rpm          MySQL-shared-compat-5.1.69-1.rhel5.x86_64.rpm

安装mysql
rpm -ivh http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm
rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm 
rpm -ivh MySQL-client-5.6.19-1.rhel5.x86_64.rpm 
rpm -ivh MySQL-devel-5.6.19-1.rhel5.x86_64.rpm
rpm -ivh MySQL-server-5.6.19-1.rhel5.x86_64.rpm
-----没有安装:libaio-0.3.107-10.el6.x86_64.rpm-----
error: Failed dependencies:
libaio.so.1()(64bit) is needed by MySQL-server-5.6.45-1.el7.x86_64
libaio.so.1(LIBAIO_0.1)(64bit) is needed by MySQL-server-5.6.45-1.el7.x86_64
libaio.so.1(LIBAIO_0.4)(64bit) is needed by MySQL-server-5.6.45-1.el7.x86_64

修改密码
查看mysql状态
service mysql status
MySQL is not running                                       [FAILED]
如果是开启服务状态，用service mysql status关闭服务

初始化mysql

setenforce 0  //关闭SELinux

mkdir -p /app/mysql/data/
touch /app/mysql/data/db-v3.err
touch /app/mysql/data/db-v3.pid
chmod 755 /app/mysql/data/db-v3.pid
chmod 755 /app/mysql/data/db-v3.err
chown -R mysql:mysql   /app/mysql
cp /opt/my.cnf /etc/

my.cnf 
server-id = 207


mysqld --initialize --user=mysql --console
/usr/bin/mysql_install_db --user=mysql --datadir=/app/mysql/data/
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 27545
[root@db-2 software]# Logging to '/var/lib/mysql/db-2.err'.
191009 17:07:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

暂时此窗口,然后

重点***新建***Xshell窗口登录
mysql -u root -p
要求输入密码，直接回车

切换数据库
use mysql;
select Host,User,Password from user; 
修改密码
UPDATE user SET password=password("密码") WHERE user='root'; 
flush privileges;
use mysql;

flush privileges;
退出
quit   
重启mysql服务
/etc/init.d/mysql restart
or
service mysql restart
-------------
Shutting down MySQL..                                      [  OK  ]
Starting MySQL.                                            [  OK  ]
----------

mysql -u root -p

修改root密码并赋予任何主机访问数据的权限（远程访问）
grant all privileges on *.* to 'root'@'%' identified by '密码字符串' with grant option; 
flush privileges; 
exit
show grants for root@'%';
退出
重启mysql
service mysql restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
开放端口
systemctl status firewalld
查看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服务
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服务自动开启命令
service mysql restart 
chkconfig mysql on

mysql集合重要目录
/app/mysql/data 数据库文件
/usr/share/mysql 命令及配置文件
/usr/bin mysqladmin、mysqldump等命令

问题1:Table 'mysql.plugin' doesn't exist
缺少了一步，就是
初始化mysql
cp /opt/my.cnf /etc/
mkdir -p /app/mysql/data/
mysqld --initialize --user=mysql --console
/usr/bin/mysql_install_db --user=mysql --datadir=/app/mysql/data/

/etc/init.d/mysql start

问题2:
Can't open the mysql.plugin table. Please run mysql_upgrade to create it


问题3:
Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
chown -R mysql:mysql  /app/mysql/

问题:
/usr/sbin/mysqld: File './mysql-bin.index' not found
chown -R mysql:mysql   /app/mysql

问题:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
my.cnf ->socket=/app/mysql/mysql.sock 
ln -s /app/mysql/mysql.sock /var/lib/mysql/mysql.sock

 检查mysql组和用户是否存在，如无则创建
cat /etc/group | grep mysql
cat /etc/passwd | grep mysql 

问题(演示环境):1067 - Invalid default value for 'XXXXXX'
去掉 sql_mode 中的 values: NO_ZERO_IN_DATE,NO_ZERO_DATE 即可：
show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+

mysql> set session sql_mode='STRICT_TRANS_TABLES,,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> flush privileges;



查询数据所有用户权限;
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查询某个具体用户的权限
select * from mysql.user where user='root' \G;

 添加远程访问权限
 use mysql;
 update user set host='%' where user='root';
 select host,user from user;


创建并授权所有相关数据库的用户名

grant all privileges on *.* to 'root'@'%' identified by '密码字符串' with grant option; 

create user 'yqy_v2'@'%' identified by '密码字符串';
-- grant all privileges  on yqy_v2.* to "数据库名称"@'%';
grant all privileges  on yqy_v2.* to "数据库名称"@'%' identified by '密码字符串' with grant option; 


用户只读权限：
grant select on *.* to "query"@'%'identified by '密码字符串';
FLUSH PRIVILEGES;

修改用户密码
UPDATE user SET password=password("密码字符串") WHERE user='yqy_v2'; 

flush privileges;

-- 只读用户
grant select on  *.* to 'query'@'%' identified by '密码字符串';
FLUSH PRIVILEGES;
-- 添加远程访问权限
 use mysql;
 update user set host='%' where user='query';
 select host,user from user;

删除权限：
revoke all privileges on query.* from 'query'@'%';