mysql触发器实现指定表某行数据禁止更新

场景:
对外提供的演示系统,提供了一个测试用户test,要禁止test用户修改密码,防止别人无法登陆。
1、在代码中屏蔽掉密码修改逻辑(推荐)。
2、数据库中禁止test用户这条数据的修改。

注意:这里要实现禁止某行(1条)的数据更新,而不是限制整个表的数据更新。

# 表结构
CREATE TABLE `aaa` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(100) DEFAULT NULL,
   `password` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8


# 触发器实现禁止密码字段修改
# 方法就是在更新前检查新密码是否和老密码一致,如果不一致,则设置新密码字段值为旧值,通过new或者old获取新旧值。
# 通过判断username来精确到某一条数据

DELIMITER $$

USE `test`$$

DROP TRIGGER /*!50032 IF EXISTS */ `deny_test_password`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `deny_test_password` BEFORE UPDATE ON `aaa` 
    FOR EACH ROW BEGIN
    
    IF old.username = 'test' THEN
	IF new.password != '111' THEN
		SET new.password = '111';
	END IF;
    
    END IF;
    END;
$$

DELIMITER ;

InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different size 0 134217728 bytes

200622 15:20:32 [Note] Plugin 'FEDERATED' is disabled.
200622 15:20:32 InnoDB: The InnoDB memory heap is disabled
200622 15:20:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200622 15:20:32 InnoDB: Compressed tables use zlib 1.2.11
200622 15:20:32 InnoDB: Using Linux native AIO
200622 15:20:32 InnoDB: Initializing buffer pool, size = 256.0M
200622 15:20:32 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different size 0 134217728 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
200622 15:20:32 [ERROR] Plugin 'InnoDB' init function returned error.
200622 15:20:32 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
200622 15:20:32 [ERROR] Unknown/unsupported storage engine: InnoDB
200622 15:20:32 [ERROR] Aborting

提示说ib_logfile0文件的大小与 .cnf配置文件中的配置 67108864 大小不一致,导致启动失败。

解决方法:
1、可以修改mysql配置文件中 innodb_log_file_size 的值为 134217728/1024/1024 = 128 Mb,再测试启动mysql,如果不行继续下面方法2。
2、删除或备份mysql数据目录下的 ib_logfile 开头的文件,再启动mysql。

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
7 rows in set (0.00 sec)

# innodb_log_files_in_group 参数控制redo log文件个数,ib_logfile[0-1]
# innodb_log_file_size 控制redo log文件大小

# 解释
ib_logfile用于事务日志,也叫redo log,有了它,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失。
还有一个日志叫undo log。
它有两个作用:
1、提供事务回滚
2、多个行版本控制(MVCC)(事务隔离级别里有这个概念)
在数据更新时,不仅写redo log,也写对应的undo log,如果因为某些原因导致事务失败,可以通过undo log回滚。

注:
为了满足事务的原子性,在操作任何数据之前,首先将数据备份到Undo Log,然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句,系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。与redo log不同的是,磁盘上不存在单独的undo log文件,它存放在数据库内部的一个特殊段(segment)中,这称为undo段(undo segment),undo段位于共享表空间内。

mysql导入报@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty错误

从阿里云mysqldump的sql文件导入本地库报错

[root@localhost ~]# mysql -uroot -p$(cat mysql_password.txt) xxx < /home/backup/xxx/xxx2020-05-07-04-00-01.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

原因是阿里云rds自带主从复制,开启了gtid全局事务id用于保证主从复制一致,备份的时候没有忽略gtid,导致导入本地时报错。

方法1、

# 查看当前的gtid
mysql> select @@global.gtid_executed\G;

# 清空本地的gtid
mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

方法2、删掉sql文件中的 SET @@GLOBAL.GTID_PURGED 2行

-- MySQL dump 10.13  Distrib 5.7.17, for linux-glibc2.5 (x86_64)
--
-- Host: rm-xxx.mysql.rds.aliyuncs.com    Database: xxx
-- ------------------------------------------------------
-- Server version       5.7.25-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='6822560b-3659-11e8-afba-7cd30adaf22a:1-33310212,
79abd425-3659-11e8-8eeb-7cd30adaf28e:1-36989543';

方法3、

/usr/local/mysql/bin/mysqldump --set-gtid-purged=OFF
加上--set-gtid-purged=OFF参数再备份,这样就忽略。

mysql数据库中用户表host字段localhost、127.0.0.1、%区别

忘记mysql密码后,通过添加my.cnf中[mysqld]段skip_grant_tables参数跳过认证后修改密码。
改完后重启发现还是无法登陆,排查后发现是mysql.user表中host字段值导致的。

mysql5.5 默认用户信息

mysql> select host,user,password,authentication_string,plugin from user where user = 'root'\G;
*************************** 1. row ***************************
                 host: localhost
                 user: root
             password: *1AF59BA2193249964488569CA898EFB2675BD9AB
authentication_string: 
               plugin: 
*************************** 2. row ***************************
                 host: bt
                 user: root
             password: 
authentication_string: 
               plugin: 
*************************** 3. row ***************************
                 host: 127.0.0.1
                 user: root
             password: 
authentication_string: 
               plugin: 
*************************** 4. row ***************************
                 host: ::1
                 user: root
             password: 
authentication_string: 
               plugin: 
4 rows in set (0.00 sec)

当我们把host = localhost , user = root 的记录信息host改为 % 百分号后再看下。

mysql> select host,user,password,authentication_string,plugin from user where user = 'root'\G;
*************************** 1. row ***************************
                 host: %
                 user: root
             password: *1AF59BA2193249964488569CA898EFB2675BD9AB
authentication_string: 
               plugin: 
*************************** 2. row ***************************
                 host: bt
                 user: root
             password: 
authentication_string: 
               plugin: 
*************************** 3. row ***************************
                 host: 127.0.0.1
                 user: root
             password: 
authentication_string: 
               plugin: 
*************************** 4. row ***************************
                 host: ::1
                 user: root
             password: 
authentication_string: 
               plugin: 
4 rows in set (0.00 sec)

再用mysql -uroot -p 则无法登陆了,会提示下面错误。

[root@bt /]# mysql -uroot -p
Enter password: // 输入密码
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

此时用 mysql -uroot -h 127.0.0.1 -p 和 mysql -uroot -h bt -p 时,则无需密码即可登陆(也能查看库和表)。
因为host = bt & user = root或者 host = 127.0.0.1 & user = root 的password密码字段为空,所以可以空密码登陆。

那么为什么mysql -uroot -p 输入密码后会无法连接?
注意 % 百分号表示是任何主机,是不匹配 localhost 的,它只是针对于通过TCP/IP连接过来的主机。
当不带-h参数时,默认为localhost,会去表里匹配host=localhost的记录,没有找到对应记录,所以就拒绝连接了。

这个规则同样适用于代码中连接数据库。

参考mysql文档:https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html

localhost is a synonym for your local host name, and is also the default host to which clients try to connect if you specify no host explicitly.

这句话意思是 localhost 是你本地主机的别名,同样他是客户端(mysql -uxxx -pxxx)连接数据库,但未指定主机(-h)时的默认值。(通过socket连接)

You can use a --host=127.0.0.1 option to name the server host explicitly. 
This will make a TCP/IP connection to the local mysqld server. 

上面两句意思是说用127.0.0.1做为主机地址连接,这是通过tcp/ip连接实现的。

You can also use TCP/IP by specifying a --host option that uses the actual host name of the local host. 

这句意思是通过tcp/ip协议来连接主机的实际名称,比如 server-db ,连接时,会把主机名解析为ip地址。

In this case, the host name must be specified in a user table row on the server host, even though you are running the client program on the same host as the server.

意思是即使你客户端程序跟数据库在同一台机器,但是主机名也必须在mysql.user表中有记录。

在以上3中情况下(localhost/127.0.0.1/hostname)必须在mysql.user中能匹配到用户信息,否则就拒绝连接。
更多资料可参考mysql官方文档中关于 Access Control and Account Management 部分。

mysql5.6无法启动,通过设置启动时不进行前滚恢复启动

InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 595198 K bytes of memory

开始以为是key_buffer_size、read_buffer_size、sort_buffer_size、max_threads这几个参数的问题,修改以后也没效果。
日志里也说遇到个bug,也不知道到底是不是bug。
查到一篇类似文章:https://blog.51cto.com/legehappy/2119514

通过设置innodb_force_recovery参数不进行回滚才启动数据库。

以下仅供参考!

具体方法:
1、先备份mysql的数据目录(生产中非常重要)!
2、修改my.cnf添加。

[mysqld]
innodb_force_recovery = 1

重启数据库。

这里有6个值可用:
作为一个安全措施,InnoDB 在innodb_force_recovery大于0时阻止INSERT,UPDATE或DELETE操作。对于MySQL5.6.15,将innodb_force_recovery设为4或更高会让InnoDB处于只读模式。

1 (SRV_FORCE_IGNORE_CORRUPT)
即使服务器检测到损坏的页仍让它运行。试图使SELECT* FROM tbl_name跳过损坏的索引记录和页,这样有助于转储表。

2 (SRV_FORCE_NO_BACKGROUND)
阻止主线程和任何清除线程的运行。如果崩溃会在清除操作中发生,该恢复值会阻止它。

3 (SRV_FORCE_NO_TRX_UNDO)
不要在崩溃恢复后运行事务回滚。

4 (SRV_FORCE_NO_IBUF_MERGE)
阻止插入缓冲合并操作。如果它们会导致崩溃,不要做这些。不计算表统计。这个值可以永久损坏数据文件。使用这个值后,准备号删除并重建所有辅助索引。在MySQL5.6.15中,设置InnoDB为只读。

5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
在启动数据库时不查看撤消日志:InnoDB将即使未完成的事务也作为已提交。这个值可以永久损坏数据文件。在MySQL5.6.15中,设置InnoDB为只读。

6 (SRV_FORCE_NO_LOG_REDO)
不要通过恢复对重做日志进行前滚。这个值可能永久损坏数据文件。数据库页被留在一个陈旧的状态,这反过来又可能带给B-trees和其它数据库结构更多的损坏。在MySQL5.6.15中,设置InnoDB为只读。 

更多信息参考原文章,我这里通过设置为3后(从1-3挨个试的),数据库才启动,然后mysqldump备份数据,重装了mysql再导回去。

PS:
这台数据库是有定时备份的。只是为了研究一下怎么能恢复就没重装还原备份。