1.5. MySQL Plugin

1.5.1. validate_password

插件的卸载与安装

uninstall plugin validate_password;
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

查看变量设置

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql>

修改策略与密码长度

mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=4;
mysql> grant all privileges on test.* to 'test'@localhost  identified by 'chen';

1.5.2. MySQL Images manager

地址： https://github.com/netkiller/mysql-image-plugin

cd /usr/local/src/
git clone https://github.com/netkiller/mysql-image-plugin.git
cd mysql-image-plugin/
yum install cmake

cmake .
make && make install
		</screen>
		<screen>
Install

create function image_check returns string soname 'libimage.so';
create function image_remove returns string soname 'libimage.so';
create function image_rename returns string soname 'libimage.so';
create function image_crc32 returns string soname 'libimage.so';
create function image_move returns string soname 'libimage.so';
Uninstall

drop function image_check;
drop function image_remove;
drop function image_rename;
drop function image_crc32;
drop function image_move;
Example

select image_check('/path/filename.ext');
select image_remove('/path/filename.ext');
select image_rename('/path/oldfile.ext','/path/newfile.ext');
select image_crc32('/path/filename.ext');
select image_move('/path/filename.ext','/path/to/newfile.ext');

1.5.3. MySQL fifo

mysql-fifo-plugin

MySQL Pipes (FIFOs) Plugin

Build

cd /usr/local/src/
git clone https://github.com/netkiller/mysql-fifo-plugin.git
cd mysql-fifo-plugin/

cmake .
make
make install

or

gcc -O3  -g  -I/usr/include/mysql -I/usr/include  -fPIC -lm -lz -shared -o libfifo.so fifo.c
sudo mv libfifo.so /usr/lib/mysql/plugin/
Plugin Install and Uninstall

Install

create function fifo_create returns string soname 'libfifo.so';
create function fifo_remove returns string soname 'libfifo.so';
create function fifo_read returns string soname 'libfifo.so';
create function fifo_write returns string soname 'libfifo.so';
Uninstall

drop function fifo_create;
drop function fifo_remove;
drop function fifo_read;
drop function fifo_write;
Testing

创建管道

mysql> create function fifo_create returns string soname 'libfifo.so';
Query OK, 0 rows affected (0.02 sec)

mysql> select fifo_create('/tmp/myfifo');
+----------------------------+
| fifo_create('/tmp/myfifo') |
+----------------------------+
| ture                       |
+----------------------------+
1 row in set (0.00 sec)

查看管道是否创建

$ ls /tmp/myfifo 
/tmp/myfifo

覆盖测试，正确应该返回false

mysql> select fifo_create('/tmp/myfifo');
+----------------------------+
| fifo_create('/tmp/myfifo') |
+----------------------------+
| false                      |
+----------------------------+
1 row in set (0.00 sec)
删除管道

mysql> select fifo_remove('/tmp/myfifo');
+----------------------------+
| fifo_remove('/tmp/myfifo') |
+----------------------------+
| true                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select fifo_remove('/tmp/my');
+------------------------+
| fifo_remove('/tmp/my') |
+------------------------+
| false                  |
+------------------------+
1 row in set (0.00 sec)

删除不存在的管道会提示 false
读管道

在一个终端窗口中运行
mysql> select fifo_read('/tmp/myfifo');
+--------------------------+
| fifo_read('/tmp/myfifo') |
+--------------------------+
| Hello world              |
+--------------------------+
1 row in set (7.85 sec)

在另一个终端窗口中运行
mysql> select fifo_write('/tmp/myfifo','Hello world !!!');
+---------------------------------------------+
| fifo_write('/tmp/myfifo','Hello world !!!') |
+---------------------------------------------+
| true                                        |
+---------------------------------------------+
1 row in set (0.00 sec)	

或者

在命令行运行
$ echo "Hello world" > /tmp/myfifo

在SQL客户端中运行
mysql> select fifo_read('/tmp/myfifo');
+--------------------------+
| fifo_read('/tmp/myfifo') |
+--------------------------+
| Hello world
			 |
+--------------------------+
1 row in set (0.00 sec)
注意上面echo会自动增加换行符，-n参数可以避免
$ echo -n "Hello world" > /tmp/myfifo

mysql> select fifo_read('/tmp/myfifo');
+--------------------------+
| fifo_read('/tmp/myfifo') |
+--------------------------+
| Hello world              |
+--------------------------+
1 row in set (0.01 sec)
写管道

mysql> select fifo_write('/tmp/myfifo','Hello world !!!');
+---------------------------------------------+
| fifo_write('/tmp/myfifo','Hello world !!!') |
+---------------------------------------------+
| true                                        |
+---------------------------------------------+
1 row in set (0.00 sec)

$ cat /tmp/myfifo
Hello world !!!

管道 /tmp/nofifo 不存在会返回false
mysql> select fifo_write('/tmp/nofifo',concat(mobile,'\r\n')) from demo;
+-------------------------------------------------+
| fifo_write('/tmp/nofifo',concat(mobile,'\r\n')) |
+-------------------------------------------------+
| false                                           |
| false                                           |
| false                                           |
| false                                           |
| false                                           |
+-------------------------------------------------+
5 rows in set (0.01 sec)

1.5.4. 内容输出到文本插件

Plugin Install and Uninstall

# Install
create function out2file returns string soname 'liboutfile.so';

# Uninstall
drop function out2file;

操作演示

# 安装插件

mysql> create function out2file returns string soname 'liboutfile.so';
Query OK, 0 rows affected (0.00 sec)

# 调用插件

mysql> select out2file('/tmp/myoutfile',"Helloworld!!!");
+--------------------------------------------+
| out2file('/tmp/myoutfile',"Helloworld!!!") |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

# 查看文件

root@netkiller ~/mysql-outfile-plugin % cat /tmp/myoutfile
Helloworld!!!

触发器应用

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `sex` enum('Man','Woman') DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8		
		
DROP TRIGGER IF EXISTS `test`.`demo_AFTER_INSERT`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`%` TRIGGER `test`.`demo_AFTER_INSERT` AFTER INSERT ON `demo` FOR EACH ROW
BEGIN
	set @rev = "";
	SELECT 
    OUT2FILE('/tmp/demo.log',
            CONCAT_WS(',',
                    NEW.id,
                    NEW.name,
                    NEW.sex,
                    NEW.address))
	INTO @rev;
END$$
DELIMITER ;

原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。