mariadb版本降级

迷南。 2021-11-01 07:06 393阅读 0赞

## 起因： ##

在工作中可能遇到这样一种情况：升级时发现备份的sql表列太宽，以至于出现以下错误：

Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

![1462981-20190603162955548-188088520.png][]

这时候就要进行版本回退，将宽表修改后重新备份，然后再升级。

## 降级步骤： ##

0、查看已有yum源：

[root@decoder ~]# yum repolist all
    已加载插件：fastestmirror, refresh-packagekit
    Loading mirror speeds from cached hostfile
    仓库标识                      仓库名称                      状态
    cdrom                          cdrom                       启用: 6,575
    repolist: 6,575

1、将yum源移除：

[root@localhost mysql]# yum list mariadb
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    Available Packages
    mariadb.x86_64         1:5.5.60-1.el7_5          local
    [root@localhost mysql]# cd /etc/yum.repos.d/
    [root@localhost yum.repos.d]# ls
    backup  local.repo  MariaDB.repo  nginx.repo
    [root@localhost yum.repos.d]# mv local.repo backup/
    [root@localhost yum.repos.d]# mv nginx.repo backup/

2、清除yum源缓存后生成cache：

[root@localhost yum.repos.d]# yum clean all
    Loaded plugins: fastestmirror
    Cleaning repos: mariadb
    Cleaning up list of fastest mirrors
    Other repos take up 101 M of disk space (use --verbose for details)
    [root@localhost yum.repos.d]# yum makecache
    Loaded plugins: fastestmirror
    Determining fastest mirrors
    mariadb                                             | 2.9 kB  00:00:00
    (1/3): mariadb/primary_db                  |  54 kB  00:00:01
    (2/3): mariadb/other_db                      | 9.2 kB  00:00:00
    (3/3): mariadb/filelists_db                    | 281 kB  00:00:13
    Metadata Cache Created

3、查看已安装的mariadb：

[root@localhost yum.repos.d]# yum list mariadb*
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    Installed Packages
    MariaDB-client.x86_64               10.3.15-1.el7.centos                      @mariadb
    MariaDB-common.x86_64      10.3.15-1.el7.centos                      @mariadb
    MariaDB-compat.x86_64            10.3.15-1.el7.centos                      @mariadb
    MariaDB-server.x86_64            10.3.15-1.el7.centos                      @mariadb
    Available Packages
    MariaDB-backup.x86_64          10.3.15-1.el7.centos                      mariadb
    MariaDB-backup-debuginfo.x86_64        10.3.15-1.el7.centos         mariadb
    MariaDB-cassandra-engine.x86_64     10.3.15-1.el7.centos      mariadb
    MariaDB-cassandra-engine-debuginfo.x86_64     10.3.15-1.el7.centos                      mariadb
    MariaDB-client-debuginfo.x86_64      10.3.15-1.el7.centos     mariadb
    MariaDB-common-debuginfo.x86_64      10.3.15-1.el7.centos     mariadb
    MariaDB-connect-engine.x86_64           10.3.15-1.el7.centos       mariadb
    MariaDB-connect-engine-debuginfo.x86_64     10.3.15-1.el7.centos     mariadb
    MariaDB-cracklib-password-check.x86_64     10.3.15-1.el7.centos      mariadb
    MariaDB-cracklib-password-check-debuginfo.x86_64   10.3.15-1.el7.centos                      mariadb
    MariaDB-devel.x86_64        10.3.15-1.el7.centos                      mariadb
    MariaDB-devel-debuginfo.x86_64     10.3.15-1.el7.centos    mariadb
    MariaDB-gssapi-server.x86_64      10.3.15-1.el7.centos           mariadb
    MariaDB-gssapi-server-debuginfo.x86_64    10.3.15-1.el7.centos        mariadb
    MariaDB-oqgraph-engine.x86_64       10.3.15-1.el7.centos      mariadb
    MariaDB-oqgraph-engine-debuginfo.x86_64     10.3.15-1.el7.centos   mariadb
    MariaDB-rocksdb-engine.x86_64       10.3.15-1.el7.centos    mariadb
    MariaDB-rocksdb-engine-debuginfo.x86_64    10.3.15-1.el7.centos   mariadb
    MariaDB-server-debuginfo.x86_64       10.3.15-1.el7.centos   mariadb
    MariaDB-shared.x86_64           10.3.15-1.el7.centos                      mariadb
    MariaDB-shared-debuginfo.x86_64       10.3.15-1.el7.centos    mariadb
    MariaDB-test.x86_64               10.3.15-1.el7.centos                      mariadb
    MariaDB-test-debuginfo.x86_64       10.3.15-1.el7.centos       mariadb
    MariaDB-tokudb-engine.x86_64         10.3.15-1.el7.centos           mariadb
    MariaDB-tokudb-engine-debuginfo.x86_64      10.3.15-1.el7.centos   mariadb

4、卸载已安装的mariadb：

[root@localhost yum.repos.d]# yum remove MariaDB-client MariaDB-common.x86_64 MariaDB-compat.x86_64 MariaDB-server.x86_64 -y

5、查看是否卸载完全：

[root@localhost yum.repos.d]# systemctl status mariadb
    Unit mariadb.service could not be found.
    [root@localhost yum.repos.d]# systemctl status mysql
    Unit mysql.service could not be found.
    [root@localhost yum.repos.d]# systemctl status mysqld
    Unit mysqld.service could not be found.

6、安装低版本的yum源：

[root@localhost yum.repos.d]# mv MariaDB.repo backup/
    [root@localhost yum.repos.d]# mv backup/local.repo  .

7、更新yum源：

[root@localhost yum.repos.d]# yum clean all
    [root@localhost yum.repos.d]# yum makecache

8、安装低版本mariadb：

[root@localhost yum.repos.d]# yum install -y mariadb mariadb-server

9、新建用户：

MariaDB [(none)]> create user ajie@localhost identified by 'ajie123';  #创建一个数据库用户ajie，密码为ajie123
    Query OK, 0 rows affected (0.00 sec)

10、普通用户提权：

MariaDB [(none)]> grant select,update,delete,insert ON mysql.* to ajie@localhost;  #授予ajie用户查询、更新、删除、插入权限。
    # mysql.*代表授予ajie的权限对数据库mysql下的所有表单有效
    Query OK, 0 rows affected (0.00 sec)

11、取消普通用户授权：

MariaDB [(none)]> revoke select,update,delete,insert on mysql.* from ajie@localhost;   #移除刚才授予ajie的权限
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show grants for ajie@localhost;  #查看用户ajie的权限，已经没有了之前授予的权限
    +-------------------------------------------------------------------------------------------------------------+
    | Grants for ajie@localhost                                                                                   |
    +-------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'ajie'@'localhost' IDENTIFIED BY PASSWORD '*A65FBC245EF7CC4346000B7CFA058E0D5A234219' |
    +-------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

转载于:https://www.cnblogs.com/yanwuliu/p/10968632.html

[1462981-20190603162955548-188088520.png]: /images/20211101/bc489a5d843d41c8b102cbd68fff9b78.png