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)