Skip to content
A branch of percona server bringing memcached record and sql-filter features
C++ C Java Perl TSQL Shell Other
Branch: percona-server…
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
BUILD
Docs
build-ps
client
cmake
cmd-line-utils/libedit
dbug
doc
extra
include
internal/mysql-test/suite
libevent
libmysql
libmysqld
libservices
man
mysql-test
mysys
mysys_ssl
packaging
plugin
policy
regex
scripts
sql-bench
sql-common
sql
storage
strings
support-files
tests
unittest
vio
win
zlib
.gitattributes
.gitignore
.gitmodules
.is_percona_server_bzr
.travis.yml
CMakeLists.txt
COPYING
COPYING.innodb-deadlock-count-patch
COPYING.show_temp_51
Doxyfile-perfschema
INSTALL
README.MySQL
README.md
VERSION
config.h.cmake
configure.cmake

README.md

Percona Server auto manager

Percona Server auto manager is a branch of percona-server-5.6.39-83.1 bringing memcached record and sql filter.

Documentation: percona-server-5.6.39-83.1

note: the memcached feature maybe usefull when you want login mysql server with one-time-one-password way, such as google totp. and the sql filter can be helpful when you want restrict the developers only execute allowed sql statement, you can disabled this with --skip-sql-filter option.

Dependency

The memcached option depended on the following package:

libmemcached-devel

How to build

you can compile the source with the same way in percona-source-install, the memcached record feature was off by default, you can enable this with the following option:

git clone --recursive https://github.com/arstercz/percona-server-auto-manager.git
cd percona-server-auto-manager

cmake . -DWITH_MEMCACHED_RECORD=ON -DWITH_EMBEDDED_SERVER=OFF -DBUILD_TESTING=OFF
or
cmake . -DWITH_EMBEDDED_SERVER=OFF -DBUILD_TESTING=OFF         # if you don't need memcached record

# just make client
cd client
make -j4

the sql filter feature was embedded into client/mysql with --sql-fiter option, default is on, you can disabled this by --skip-sql-filter when you connect mysql server.

note: the tokudb need the gcc version must greater than 4.7.

How does it work?

memcached option

We add the store_userpass_mem function to the source code src/client/mysql.cc and src/client/mysqldump.c so that we can send mysql username and password to memcached server, and you can user --help option to see the --memcached-server option when you compile the source code.

# /opt/percona5.6.39/bin/mysql --help|grep -P 'memcached|threshold|filter'
  --memcached-server=name 
  --table-threshold=# table size(MB) threshold for disabled alter syntax, default
  --sql-filter        whether enable sql filter, default is true(1)
                      (Defaults to on; use --skip-sql-filter to disable.)
memcached-server                  localhost:11211
table-threshold                   200
sql-filter                        TRUE

the username and password will be send to memcached server before the mysql or mysqldump really connect to the mysql server.

sql filter

We add the following rules before sending the actual sql queries to MySQL Server, return immediately if mathed these rules, otherwise send to MySQL Server:

1. select statement must have where/limit keywords;
2. update/delete statement must have where keyword;
3. disable 'update/delete ..where..(order by|limit)' syntax;
4. disable 'drop database/drop schema' syntax;
5. disable 'create index' syntax;
6. disable descreased ALTER syntax, this means you can 'add' column, but not 'drop|change|modify|rename' column;
7. disable 'grant all' syntax;
8. disable 'revoke' syntax;
9. disable 'load' syntax;
10. disabled descreased DDL syntax. this means you can not 'purge/truncate/drop' table;
11. disabled 'set ...' syntax, except 'set names ...';
12. disabled if table size is greater than --table-threshold value, default is 200(MB);

record sql

We add --record-file option to record all sql, default is /tmp/.mysql_record_all, every user have a $record-file.$USER file to record the sql, you can use --record-file="" to skip record the sql query. if you login with root user:

# less /tmp/.mysql_record_all.root 
[2018-10-08T12:50:55 login:root user:root shell:/bin/bash cwd:/home/mysql/percona-server-auto-manager/client db:(null)] show databases
[2018-10-08T12:50:59 login:root user:root shell:/bin/bash cwd:/home/mysql/percona-server-auto-manager/client db:test] show tables

How to use?

login mysql server with a right/wrong password:

# /opt/percona5.6.39/bin/mysql -h 10.0.21.5 -u arstercz -P 3305 -p --memcached-server "10.0.21.5:11211"
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.21.5' (113)

read from memcached:

# memcached-tool 10.0.21.5:11211 dump
Dumping memcache contents
  Number of buckets: 1
    Number of items  : 1
    Dumping bucket 1 - 1 total items
    add arstercz 0 1520839412 10
    xxxx123456

when you connect to mysql server, the sql filter was triggerd automaticly:

mysql arstercz@[10.0.21.5:3305 (none)] > alter table checksums add column sss varchar(50);                   

        [WARN] - Must 'use <database>' before alter table, current database is null.

mysql arstercz@[10.0.21.5:3305 (none)] > use percona
Database changed
mysql arstercz@[10.0.21.5:3305 percona] > alter table checksums drop column sss varchar(50);   

        [WARN]
         +-- alter table checksums drop column sss varchar(50)
         Caused by: disable descreased ALTER syntax.
this sql syntax was disabled by administrator

mysql arstercz@[10.0.21.5:3305 percona] > select * from checksums;

        [WARN]
         +-- select * from checksums
         Caused by: no where/limit for select clause
this sql syntax was disabled by administrator

mysql arstercz@[10.0.21.5:3305 percona] > delete from checksums;

        [WARN]
         +-- delete from checksums
         Caused by: no where for delete/update clause
this sql syntax was disabled by administrator

mysql arstercz@[10.0.21.5:3305 percona] > alter table test.user_info add column sss varchar(50);

        [WARN] - the test.user_info size is 4240MB, disallowed by administrator

extra feature

audit_log plugin

we add audit_log_timezone option to support LOCAL or UTC timezone in audit_log, default is UTC, you can change with dynamic way:

set global audit_log_timezone = LOCAL;

readonly prompt

you can add \i to mysql prompt option to know whether the mysql server is readonly or not, ro means read only, this maybe a slave host, rw means read write:

[mysql]
prompt = 'mysql \u@[\h:\p \d \i] > '

readonly check was embedded whether you enable WITH_MEMCACHED_RECORD:

mysql root@[localhost:s3301 (none) rw] > select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql root@[localhost:s3301 (none) rw] > set global read_only = 1;
Query OK, 0 rows affected (0.07 sec)

mysql root@[localhost:s3301 (none) ro] > set global read_only = 0;
Query OK, 0 rows affected (0.00 sec)

mysql root@[localhost:s3301 (none) rw] > 

disable password prompt

The warning message occurs when you connect MySQL server with -ppassword option by default. we disabled this behavior.

You can’t perform that action at this time.