• mariadb版本降级


    起因:

    在工作中可能遇到这样一种情况:升级时发现备份的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.

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

    降级步骤:

    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)
  • 相关阅读:
    Executors 构建线程池
    结构型模式——Bridge(未完成)
    结构型模式——Adapter
    创建型模式——Builder
    创建型模式——Abstract Factory
    Java与线程
    Java内存模型
    类加载
    Class类文件的结构
    垃圾收集器
  • 原文地址:https://www.cnblogs.com/yanwuliu/p/10968632.html
Copyright © 2020-2023  润新知