30选5玩法|福彩30选5开奖结果321|
 

标签:mysql

mysqldump的权限说明

No Comments mysql_mariadb
mysqldump 所需要的权限说明:

1、对于table 来说mysqldump 最少要有select 权限。
2、对于view 来说mysqldump 要有show view 权限。
3、对于trrigger 来说mysqldump 要有trriger 权限。
4、如果要产生一份一致的备份 mysqldump 要有lock tables 权限。

相关用户创建与授权语句:
create user [email protected]'127.0.0.1';
grant select on tempdb.* to [email protected]'127.0.0.1';
grant show view on tempdb.* to [email protected]'127.0.0.1';
grant lock tables on tempdb.* to [email protected]'127.0.0.1';
grant trigger on tempdb.* to [email protected]'127.0.0.1';
远程 授权 lock tables 会报错。
Error Code : 1144 Illegal GRANT/REVOKE command; please consult themanual to see which privileges can be used

mysqldump的权限说明

No Comments mysql_mariadb
mysqldump 所需要的权限说明:

1、对于table 来说mysqldump 最少要有select 权限。
2、对于view 来说mysqldump 要有show view 权限。
3、对于trrigger 来说mysqldump 要有trriger 权限。
4、如果要产生一份一致的备份 mysqldump 要有lock tables 权限。

相关用户创建与授权语句:
create user [email protected]'127.0.0.1';
grant select on tempdb.* to [email protected]'127.0.0.1';
grant show view on tempdb.* to [email protected]'127.0.0.1';
grant lock tables on tempdb.* to [email protected]'127.0.0.1';
grant trigger on tempdb.* to [email protected]'127.0.0.1';

Mysql中查看每个IP的连接数

No Comments mysql_mariadb

 

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

一看必会系列:MYSQL5.7 官方YUM安装

No Comments mysql_mariadb

 

安装官方源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

开启5.7版本原
yum-config-manager –enable mysql57-community

关闭8.0版本源
yum-config-manager –disable mysql80-community

安装mysql-server

yum install mysql-community-server

启动
systemctl start mysqld
systemctl enable mysqld

查看密码

grep ‘temporary password’ /var/log/mysqld.log

 

进入密码 mysql –u root –p

 

修改密码

  • mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MPass122!';

一看必会系列:MYSQL5.7 官方YUM安装

No Comments mysql_mariadb

 

安装官方源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

开启5.7版本原
yum-config-manager –enable mysql57-community

关闭8.0版本源
yum-config-manager –disable mysql80-community

安装mysql-server

yum install mysql-community-server

启动
systemctl start mysqld
systemctl enable mysqld

查看密码

grep ‘temporary password’ /var/log/mysqld.log

 

进入密码 mysql –u root –p

一看必会系列:MYSQL5.7 官方YUM安装

No Comments mysql_mariadb

yum install mysql-server
yum list |grep mysql
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum list |grep mysql
yum install mysql-server
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

yum-config-manager –enable mysql57-community

yum-config-manager –disable mysql80-community

yum install mysql-community-server

systemctl start mysqld
netstat -ntlp
grep ‘temporary password’ /var/log/mysqld.log

MySQL添加用户、删除用户与授权

No Comments mysql_mariadb

1.新建用户

1.1 登录MYSQL:

@>mysql -u root -p

@>密码

1.2 创建用户:

mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));

这样就创建了一个名为:test 密码为:1234 的用户。

注意:此处的"localhost",是指该用户只能在本地登录,不能在另外一台机器上远程登录。如果想远程登录的话,将"localhost"改为"%",表示在任?#25105;?#21488;电脑上都可以登录。也可以指定?#31243;?#26426;器可以远程登录。

     新建加 授权 grant all privileges on *.* to [email protected]’%’ identified by ‘q23’;

1.3 然后登录一下:

mysql>exit;

@>mysql -u test -p

@>输入密码

mysql>登录成功

2.为用户授权

授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.1 登录MYSQL(有ROOT权限),这里以ROOT身份登录:

@>mysql -u root -p

@>密码

2.2 首先为用户创建一个数据库(testDB):

mysql>create database testDB;

2.3 授权test用户拥有testDB数据库的所有权限(某个数据库的所有权限):

mysql>grant all privileges on testDB.* to [email protected] identified by ‘1234’;

  mysql>flush privileges;//刷新系统权限表

格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.4 如果想指定部分权限给一用户,可以这样来写:

mysql>grant select,update on testDB.* to [email protected] identified by ‘1234’;

mysql>flush privileges; //刷新系统权限表

2.5 授权test用户拥有所有数据库的某些权限:  

mysql>grant select,delete,update,create,drop on *.* to [email protected]"%" identified by "1234";

     //test用户?#36816;?#26377;数据库都有select,delete,update,create,drop 权限。

//@"%" 表示?#36816;?#26377;非本地主机授权,不包括localhost。(localhost地址设为127.0.0.1,如果设为真?#26723;?#26412;地地址,不知道是否可以,没有验证。)

//对localhost授权:?#30001;?#19968;句

grant all privileges on testDB.* to [email protected]localhost identified by ‘1234’;

grant all privileges on testDB.* to test@127.0.0.1 identified by ‘1234’;

即可。

3. 删除用户

  @>mysql -u root -p

@>密码

  mysql>Delete FROM user Where User=’test’ and Host=’localhost’;

  mysql>flush privileges;

  mysql>drop database testDB; //删除用户的数据库

删除账户及权限:>drop user 用户名@’%’;

>drop user 用户名@ localhost;

4. 修改指定用户密码

  @>mysql -u root -p

  @>密码

   mysql>update mysql.user set password=password(‘新密码’) where User="test" and Host="localhost";

  mysql>flush privileges;

5. 列出所有数据库

mysql>show database;

6. 切换数据库

mysql>use ‘数据库名’;

7. 列出所有表

mysql>show tables;

8. 显示数据表结构

mysql>describe 表名;

9. 删除数据库和数据表

mysql>drop database 数据库名;

mysql>drop table 数据表名;

 

10.查询用户的库权限
mysql> show grants for [email protected]’%’;
+——————————————————————————————————–+
| Grants for [email protected]%                                                                                   |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘crmuser’@’%’ IDENTIFIED BY PASSWORD ‘*2EC189E935E11959ECDFFD8334RG93B08D17A46’ |
| GRANT ALL PRIVILEGES ON `tospur_sf`.* TO ‘crmuser’@’%’                                                 |
+——————————————————————————————————–+
2 rows in set (0.00 sec)

mysql>

 

11.查询格式化输出
mysql> select * from user where user = ‘crmuser’\G;
*************************** 1. row ***************************
                  Host: %
                  User: crmuser
              Password: *2EC189E935E11959ECDFFD8334RG93B08D17A46

使用mysqldbcompare 对比数据库间差异

No Comments mysql_mariadb

 

使用mysqldbcompare 对比数据库间差异

————-安装
rpm -ivh https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm
rpm -ivh https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.4-1.el6.noarch.rpm
————对比
mysqldbcompare –server1=root:[email protected] \
    –server2=root:[email protected] db1:db2 –changes-for=server1 -a \/
    –difftype=sql

mysqldbcompare –server1=root:[email protected] –server2=root:[email protected] db1:db2 –changes-for=server1 -a –difftype=sql
   
mysqldbcompare –server1=admin:[email protected]:3355 –server2=admin:[email protected]:3356  –changes-for=server1  -a –difftype=sql

使用-a 就不能指定数据库,是所有库的对比
mysqldbcompare –server1=admin:[email protected]:3355 –server2=admin:[email protected]:3356  –changes-for=server1  -a –difftype=sql

 
[[email protected] ~]# mysqldbcompare –server1=admin:[email protected]:3355 –server2=admin:[email protected]:3356 –changes-for=server1 budget:budget –difftype=sql
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 172.16.9.83: … connected.
# server2 on 172.16.9.83: … connected.
# Checking databases budget on server1 and budget on server2
#
#                                                   Defn    Row     Data  
# Type      Object Name                             Diff    Count   Check 
# ————————————————————————-
# PROCEDURE head_audit_procedure                    pass    -       -      
# TABLE     base_data_budget                        pass    pass    -      
#           – Compare table checksum                                pass   
# TABLE     budget_body                             pass    pass    -      
#           – Compare table checksum                                FAIL   
 
 
—————?#38382;?#35828;明相关文章
  http://www.ttlsa.com/mysql/mysqldbcompare-compare-two-databases/
  https://blog.csdn.net/zxb4221v/article/details/61782128
 
[[email protected] ~]# find / -name mysqldbcompare^C
[[email protected] ~]# mysqldbcompare –server1=admin:[email protected]:3355 –server2=admin:[email protected]:3356 –changes-for=server1 budget:budget
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 172.16.9.83: … connected.
# server2 on 172.16.9.83: … connected.
# Checking databases budget on server1 and budget on server2

下载
https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm

[[email protected] ~]# rpm -ivh https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm
Retrieving https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el6.noarch.rpm
warning: /var/tmp/rpm-tmp.2Ob7hD: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    mysql-connector-python >= 2.0.0 is needed by mysql-utilities-1.6.5-1.el6.noarch
[[email protected] ~]# yum update mysql-connector-python

yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm

 
v[[email protected] ~]# rpm -e  yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm^C
[[email protected] ~]# rpm -e mysql-connector-python-1.1.6-1.el6.noarch

 

[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]# rpm -ivh https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm
Retrieving https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el6.x86_64.rpm
warning: /var/tmp/rpm-tmp.CcFjfP: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                ########################################### [100%]
   1:mysql-connector-python ########################################### [100%]
[[email protected] ~]#

示例一:检查server1,server2上的某个表结?#25925;?#21542;一致
mysqldiff –server1=user:[email protected]:port –server2=user:[email protected]:port –changes-for=server2  dbname.tablename:dbname.tablename
示例二:检查server1,server2上的两个数据库中的表结?#25925;?#21542;一致
mysqldiff –server1=user:[email protected]:port –server2=user:[email protected]:port dbname:dbname –changes-for=server2

3、mysqldbcompare
mysqldiff该工具是官方mysql-utilities工具集的一个脚本,可以用来检查不同数据库之间的数据一致性,检查内容包括数据库?#22336;?#38598;、表结构、数据内容,只要有一个不一样,则检查不通过。
示例:
mysqldbcompare –server1=user:[email protected]:3306 –server2=user:[email protected]:3306 –changes-for=server1 dbname:dbname

mysqldbcompare –server1=admin:[email protected]:3355 –server2=admin:[email protected]:3356 –changes-for=server1 budget:budget

一看必会系统:使用rpm升级mysql 5.5到5.6,yum升级5.6到5.7

使用rpm及yum升级mysql 5.5到5.6,5.6到5.7

使用yum 安装指定版本mysql

升级版

1.停用应用

2.备份所有库

3.增加源

  curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
  wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

 

https://repo.mysql.com/

5.5.35 https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-server-5.5.35-2.el7.x86_64.rpm

5.6.34 https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-server-5.6.35-2.el7.x86_64.rpm

yum -y install gcc gcc-c++ make cmake automake autoconf libxml2 libxml2-devel zlib zlib-devel ncurses ncurses-devel
yum -y install libaio libaio-devel net-tools wget

———yum的升级方式  5.6升5.7

下载和安装官方源

[[email protected] ~]# rpm -ivh https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Retrieving https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
warning: /var/tmp/rpm-tmp.yAt0Yg: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql57-community-release-el7-11 ################################# [100%]

确认源是否安装完成

yum repolist all| grep mysql

mysql-cluster-7.5-community/x86_64                         MySQL Cluster 7.5 Community                                                     disabled
mysql-cluster-7.5-community-source                         MySQL Cluster 7.5 Community – Source                                            disabled
mysql-cluster-7.6-community/x86_64                         MySQL Cluster 7.6 Community                                                     disabled
mysql-cluster-7.6-community-source                         MySQL Cluster 7.6 Community – Source                                            disabled
mysql-connectors-community/x86_64                          MySQL Connectors Community                                                      enabled:     45
mysql-connectors-community-source                          MySQL Connectors Community – Source                                             disabled
mysql-tools-community/x86_64                               MySQL Tools Community                                                           enabled:     59
mysql-tools-community-source                               MySQL Tools Community – Source                                                  disabled
mysql-tools-preview/x86_64                                 MySQL Tools Preview                                                             disabled
mysql-tools-preview-source                                 MySQL Tools Preview – Source                                                    disabled
mysql55-community/x86_64                                   MySQL 5.5 Community Server                                                      disabled
mysql55-community-source                                   MySQL 5.5 Community Server – Source                                             disabled
mysql56-community/x86_64                                   MySQL 5.6 Community Server                                                      disabled
mysql56-community-source                                   MySQL 5.6 Community Server – Source                                             disabled
mysql57-community/x86_64                                   MySQL 5.7 Community Server                                                      enabled:    247
mysql57-community-source                                   MySQL 5.7 Community Server – Source                                             disabled
mysql80-community/x86_64                                   MySQL 8.0 Community Server                                                      disabled
mysql80-community-source                                   MySQL 8.0 Community Server – Source                                             disabled
updates/7/x86_64                                           CentOS-7 – Updates – mirrors.aliyun.com                                         enabled:  2,41
  
  
[[email protected] ~]# rpm -qa |grep mysql   ——-现有版本
mysql-community-libs-5.6.34-2.el7.x86_64
mysql-community-server-5.6.34-2.el7.x86_64
mysql-community-common-5.6.34-2.el7.x86_64
mysql-community-client-5.6.34-2.el7.x86_64
mysql57-community-release-el7-11.noarch

[[email protected] ~]# yum update mysql-community-server-5.7.11-1.el7.x86_64 -y

升级到指定版本要指定详细版本号.请注意

Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Updating   : mysql-community-common-5.7.11-1.el7.x86_64                                                                                             1/8
  Updating   : mysql-community-libs-5.7.11-1.el7.x86_64                                                                                               2/8
  Updating   : mysql-community-client-5.7.11-1.el7.x86_64                                                                                             3/8
  Updating   : mysql-community-server-5.7.11-1.el7.x86_64                                                                                             4/8
  Cleanup    : mysql-community-server-5.6.34-2.el7.x86_64                                                                                             5/8
  Cleanup    : mysql-community-client-5.6.34-2.el7.x86_64                                                                                             6/8
  Cleanup    : mysql-community-libs-5.6.34-2.el7.x86_64                                                                                               7/8
  Cleanup    : mysql-community-common-5.6.34-2.el7.x86_64                                                                                             8/8
  Verifying  : mysql-community-common-5.7.11-1.el7.x86_64                                                                                             1/8
  Verifying  : mysql-community-libs-5.7.11-1.el7.x86_64                                                                                               2/8
  Verifying  : mysql-community-client-5.7.11-1.el7.x86_64                                                                                             3/8
  Verifying  : mysql-community-server-5.7.11-1.el7.x86_64                                                                                             4/8
  Verifying  : mysql-community-server-5.6.34-2.el7.x86_64                                                                                             5/8
  Verifying  : mysql-community-client-5.6.34-2.el7.x86_64                                                                                             6/8
  Verifying  : mysql-community-libs-5.6.34-2.el7.x86_64                                                                                               7/8
  Verifying  : mysql-community-common-5.6.34-2.el7.x86_64                                                                                             8/8

Updated:
  mysql-community-server.x86_64 0:5.7.11-1.el7                                                                                                           

Dependency Updated:
  mysql-community-client.x86_64 0:5.7.11-1.el7       mysql-community-common.x86_64 0:5.7.11-1.el7       mysql-community-libs.x86_64 0:5.7.11-1.el7     

Complete!

[[email protected] ~]# !net
netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1767/sshd          
tcp6       0      0 :::3306                 :::*                    LISTEN      82400/mysqld       
tcp6       0      0 :::22                   :::*                    LISTEN      1767/sshd          

mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

 

———RPMR的升级方式 5.5到5.6

wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-common-5.5.35-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-libs-5.5.35-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-client-5.5.35-2.el7.x86_64.rpm

wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-common-5.6.34-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-libs-5.6.34-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-client-5.6.34-2.el7.x86_64.rpm
?#27492;?#24207;安装
mysql-community-common
mysql-community-libs
mysql-community-client
mysql-community-server

 

4.在线升级

5.备用方案。?#23545;?#21518;再安装,再导入数据

6.回退方案

——–正确的安装过程

[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
error: unpacking of archive failed on file /usr/bin/mysqlcheck;5a97f082: cpio: read
error: mysql-community-client-5.5.35-2.el7.x86_64: install failed
[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-server-5.5.35-2.e################################# [100%]
[[email protected] ~]# systemctl status mysql
Unit mysql.service could not be found.
[[email protected] ~]# systemctl status mysqld
● mysqld.service – MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[[email protected] ~]# systemctl restart mysqld

———–登陆测试

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>
mysql>

——————–5.5.45到5.6.34 升级过程

报错
[[email protected] ~]# rpm -ivh mysql-community-common-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-common-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
    file /usr/share/mysql/errmsg-utf8.txt from install of mysql-community-common-5.6.34-2.el7.x86_64 conflicts with file from package mysql-community-common-5.5.35-2.el7.x86_64

解决  先?#23545;?#160;  mysql-community-common-5.5.35-2.el7.x86_64
    [[email protected] ~]# rpm -qa |grep mysql
mysql-community-common-5.5.35-2.el7.x86_64
mysql-community-client-5.5.35-2.el7.x86_64
mysql-community-libs-5.5.35-2.el7.x86_64
mysql-community-server-5.5.35-2.el7.x86_64
[[email protected] ~]# yum remove mysql-community-common-5.5.35-2.el7.x86_64

Removed:
  mysql-community-common.x86_64 0:5.5.35-2.el7                                                                                                           

Dependency Removed:
  mysql-community-client.x86_64 0:5.5.35-2.el7       mysql-community-libs.x86_64 0:5.5.35-2.el7       mysql-community-server.x86_64 0:5.5.35-2.el7     

Complete!

开始安装
[[email protected] ~]# rpm -ivh mysql-community-common-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-common-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-common-5.6.34-2.e################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-client-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-client-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    mysql-community-libs(x86-64) >= 5.6.10 is needed by mysql-community-client-5.6.34-2.el7.x86_64
[[email protected] ~]# rpm -ivh mysql-community-libs-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-libs-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-libs-5.6.34-2.el7################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-client-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-client-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.6.34-2.e################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-server-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-server-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-server-5.6.34-2.e################################# [100%]
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
启动

systemctl restart  mysqld.service
[[email protected] ~]# !mysql
mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34 MySQL Community Server (GPL)  —————————验证版本正确,数据都在

Copyright (c) 2000, 2016, 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 databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| bass               |
| budget             |
| mysql              |
| performance_schema |
| test               |
+——————–+
6 rows in set (0.05 sec)

mysql>

 

 

——-安装失败
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm
error: Failed dependencies:
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
error: unpacking of archive failed on file /usr/bin/mysqlcheck;5a97f082: cpio: read
error: mysql-community-client-5.5.35-2.el7.x86_64: install failed    ——-安装包损坏,重新下载
[[email protected] ~]#

———–报错
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm 
error: Failed dependencies:
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    mysql-community-common(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    net-tools is needed by mysql-community-server-5.5.35-2.el7.x86_64
    perl(DBI) is needed by mysql-community-server-5.5.35-2.el7.x86_64
————解决
?#27492;?#24207;安装
mysql-community-common
mysql-community-libs
mysql-community-client
mysql-community-server

    net-tools is needed by mysql-community-server-5.5.35-2.el7.x86_64
    perl(DBI) is needed by mysql-community-server-5.5.35-2.el7.x86_64
    [[email protected] ~]# yum install -y net-tools  perl-DBI
———-解决
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    mysql-community-common(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64

——–如有冲突

删除原有mariadb
[[email protected] ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[[email protected] ~]# yum remove -ymariadb-libs-5.5.52-1.el7.x86_64

一看必会系列:mysql 5.5版本升级到mysql 5.6版本

 

1.停用应用

2.备用所有库

3.增加源

  curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
  wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

 

https://repo.mysql.com/

5.5.35 https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-server-5.5.35-2.el7.x86_64.rpm

5.6.34 https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-server-5.6.35-2.el7.x86_64.rpm

yum -y install gcc gcc-c++ make cmake automake autoconf libxml2 libxml2-devel zlib zlib-devel ncurses ncurses-devel
yum -y install libaio libaio-devel net-tools wget

 

wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-common-5.5.35-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-libs-5.5.35-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.5-community/el/7/x86_64/mysql-community-client-5.5.35-2.el7.x86_64.rpm

wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-common-5.6.34-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-libs-5.6.34-2.el7.x86_64.rpm
wget https://repo.mysql.com/yum/mysql-5.6-community/el/7/x86_64/mysql-community-client-5.6.34-2.el7.x86_64.rpm
?#27492;?#24207;安装
mysql-community-common
mysql-community-libs
mysql-community-client
mysql-community-server

 

4.在线升级

5.备用方案。?#23545;?#21518;再安装,再导入数据

6.回退方案

——–正确的安装过程

[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
error: unpacking of archive failed on file /usr/bin/mysqlcheck;5a97f082: cpio: read
error: mysql-community-client-5.5.35-2.el7.x86_64: install failed
[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-server-5.5.35-2.e################################# [100%]
[[email protected] ~]# systemctl status mysql
Unit mysql.service could not be found.
[[email protected] ~]# systemctl status mysqld
● mysqld.service – MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[[email protected] ~]# systemctl restart mysqld

———–登陆测试

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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>
mysql>

——————–5.5.45到5.6.34 升级过程

报错
[[email protected] ~]# rpm -ivh mysql-community-common-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-common-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
    file /usr/share/mysql/errmsg-utf8.txt from install of mysql-community-common-5.6.34-2.el7.x86_64 conflicts with file from package mysql-community-common-5.5.35-2.el7.x86_64

解决  先?#23545;?#160;  mysql-community-common-5.5.35-2.el7.x86_64
    [[email protected] ~]# rpm -qa |grep mysql
mysql-community-common-5.5.35-2.el7.x86_64
mysql-community-client-5.5.35-2.el7.x86_64
mysql-community-libs-5.5.35-2.el7.x86_64
mysql-community-server-5.5.35-2.el7.x86_64
[[email protected] ~]# yum remove mysql-community-common-5.5.35-2.el7.x86_64

Removed:
  mysql-community-common.x86_64 0:5.5.35-2.el7                                                                                                           

Dependency Removed:
  mysql-community-client.x86_64 0:5.5.35-2.el7       mysql-community-libs.x86_64 0:5.5.35-2.el7       mysql-community-server.x86_64 0:5.5.35-2.el7     

Complete!

开始安装
[[email protected] ~]# rpm -ivh mysql-community-common-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-common-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-common-5.6.34-2.e################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-client-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-client-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    mysql-community-libs(x86-64) >= 5.6.10 is needed by mysql-community-client-5.6.34-2.el7.x86_64
[[email protected] ~]# rpm -ivh mysql-community-libs-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-libs-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-libs-5.6.34-2.el7################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-client-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-client-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.6.34-2.e################################# [100%]
[[email protected] ~]# rpm -ivh mysql-community-server-5.6.34-2.el7.x86_64.rpm
warning: mysql-community-server-5.6.34-2.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-server-5.6.34-2.e################################# [100%]
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]#
启动

systemctl restart  mysqld.service
[[email protected] ~]# !mysql
mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34 MySQL Community Server (GPL)  —————————验证版本正确,数据都在

Copyright (c) 2000, 2016, 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 databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| bass               |
| budget             |
| mysql              |
| performance_schema |
| test               |
+——————–+
6 rows in set (0.05 sec)

mysql>

 

 

——-安装失败
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm
error: Failed dependencies:
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
[[email protected] ~]# rpm -ivh mysql-community-client-5.5.35-2.el7.x86_64.rpm
Preparing…                          ################################# [100%]
Updating / installing…
   1:mysql-community-client-5.5.35-2.e################################# [100%]
error: unpacking of archive failed on file /usr/bin/mysqlcheck;5a97f082: cpio: read
error: mysql-community-client-5.5.35-2.el7.x86_64: install failed    ——-安装包损坏,重新下载
[[email protected] ~]#

———–报错
[[email protected] ~]# rpm -ivh mysql-community-server-5.5.35-2.el7.x86_64.rpm 
error: Failed dependencies:
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    mysql-community-common(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    net-tools is needed by mysql-community-server-5.5.35-2.el7.x86_64
    perl(DBI) is needed by mysql-community-server-5.5.35-2.el7.x86_64
————解决
?#27492;?#24207;安装
mysql-community-common
mysql-community-libs
mysql-community-client
mysql-community-server

    net-tools is needed by mysql-community-server-5.5.35-2.el7.x86_64
    perl(DBI) is needed by mysql-community-server-5.5.35-2.el7.x86_64
    [[email protected] ~]# yum install -y net-tools  perl-DBI
———-解决
    mysql-community-client(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64
    mysql-community-common(x86-64) = 5.5.35-2.el7 is needed by mysql-community-server-5.5.35-2.el7.x86_64

——–如有冲突

删除原有mariadb
[[email protected] ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[[email protected] ~]# yum remove -ymariadb-libs-5.5.52-1.el7.x86_64

30选5玩法