Jovani
NoDeny
Постоялец
Карма: -9
 Offline
Сообщений: 234
|
 |
« Ответ #101 : 04 Марта 2020, 12:16:04 » |
|
Онлайн сейчас 4к Функция get_ip такая Процедуры DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
DECLARE real_login VARCHAR(64) DEFAULT NULL;
SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd,'3654') AS Value,':='
FROM users WHERE name=real_login;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
DECLARE usr_id INT;
DECLARE ip_index INT ;
DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
DECLARE real_login VARCHAR(64) DEFAULT NULL;
SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
IF login REGEXP '[=2B][0-9]+$' THEN
SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
ELSE SET ip_index=0;END IF;
SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;
SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN `login` VARCHAR(64), IN `ip` VARCHAR(16), IN `properties` VARCHAR(255))
BEGIN
DECLARE usr_id INT;
DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
DECLARE real_login VARCHAR(64) DEFAULT NULL;
DECLARE ip_index INT ;
SELECT SUBSTRING_INDEX(login, '=2b', 1) INTO real_login;
IF login REGEXP '[=2B][0-9]+$' THEN
SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
ELSE SET ip_index=0;END IF;
SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;
CALL set_auth(ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
DECLARE usr_id INT;
DECLARE ip_index INT ;
DECLARE real_login VARCHAR(64) DEFAULT NULL;
SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
IF login REGEXP '[=2B][0-9]+$' THEN
SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
ELSE SET ip_index=0;END IF;
SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
DELETE FROM auth_now WHERE ip = get_ip_by_num(usr_id, ip_index) LIMIT 1;
END$$
DELIMITER ;
DROP FUNCTION IF EXISTS `get_ip_by_num`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_num` ( user_id INTEGER UNSIGNED, num INTEGER )
RETURNS VARCHAR(15) NO SQL
BEGIN
DECLARE user_ip VARCHAR(15);
DECLARE real_ip VARCHAR(15);
IF( num IS NULL ) THEN SET num=0; END IF;
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id AND type='static' ORDER BY ip LIMIT num, 1;
IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id AND type='static' ORDER BY ip LIMIT 1;
IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;
SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
WHERE (uid = 0 OR uid = user_id)
AND type = 'dynamic'
AND realip = IF(real_ip>0,1,0)
ORDER BY uid DESC, id ASC LIMIT 1;
SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
WHERE uid = user_id LIMIT 1;
RETURN user_ip;
END$$
DELIMITER ;
###################### | InnoDB | |
=====================================
2020-03-04 12:11:12 0x86943bb00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14058 srv_active, 0 srv_shutdown, 284 srv_idle
srv_master_thread log flush and writes: 14342
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1947495
OS WAIT ARRAY INFO: signal count 1993120
RW-shared spins 0, rounds 983441, OS waits 271438
RW-excl spins 0, rounds 42995796, OS waits 539013
RW-sx spins 581754, rounds 14993381, OS waits 315719
Spin rounds per wait: 983441.00 RW-shared, 42995796.00 RW-excl, 25.77 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3337318567
Purge done for trx's n:o < 3337318096 undo n:o < 0 state: running but idle
History list length 150
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281509854459160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854453736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854458256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854450120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854456448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854454640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854451928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854455544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854457352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854452832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854451024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854449216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509854448312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
31920 OS file reads, 1290320 OS file writes, 1256365 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 88.09 writes/s, 88.09 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9779, seg size 9781, 824 merges
merged operations:
insert 1664, delete mark 69, delete 1
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 209 buffer(s)
Hash table size 276707, node heap has 34 buffer(s)
Hash table size 276707, node heap has 66 buffer(s)
Hash table size 276707, node heap has 1631 buffer(s)
Hash table size 276707, node heap has 2 buffer(s)
Hash table size 276707, node heap has 561 buffer(s)
Hash table size 276707, node heap has 13 buffer(s)
Hash table size 276707, node heap has 18 buffer(s)
398065.59 hash searches/s, 4249.98 non-hash searches/s
---
LOG
---
Log sequence number 1044016110674
Log flushed up to 1044016110311
Pages flushed up to 1043973315355
Last checkpoint at 1043973315355
0 pending log flushes, 0 pending chkp writes
1248104 log i/o's done, 88.10 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1099431936
Dictionary memory allocated 583256
Buffer pool size 65536
Free buffers 30959
Database pages 32043
Old database pages 11985
Modified db pages 556
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 31, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31840, created 203, written 38488
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 32043, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8192
Free buffers 3830
Database pages 4049
Old database pages 1514
Modified db pages 121
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4013, created 36, written 6017
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4049, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8192
Free buffers 4256
Database pages 3612
Old database pages 1353
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3593, created 19, written 522
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3612, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8192
Free buffers 3854
Database pages 4014
Old database pages 1501
Modified db pages 44
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3995, created 19, written 4054
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4014, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8192
Free buffers 3997
Database pages 3882
Old database pages 1453
Modified db pages 66
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3882, created 0, written 6569
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3882, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8192
Free buffers 3647
Database pages 4237
Old database pages 1584
Modified db pages 45
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4233, created 4, written 4123
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4237, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8192
Free buffers 3795
Database pages 4072
Old database pages 1523
Modified db pages 84
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4045, created 27, written 4700
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4072, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8192
Free buffers 3702
Database pages 4188
Old database pages 1565
Modified db pages 121
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 17, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4121, created 67, written 8047
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4188, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8192
Free buffers 3878
Database pages 3989
Old database pages 1492
Modified db pages 73
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3958, created 31, written 4456
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3989, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=1898, Main thread ID=36116434944, state: sleeping
Number of rows inserted 8301170, updated 1259977, deleted 4343, read 6342986786
0.00 inserts/s, 96.29 updates/s, 0.00 deletes/s, 399286.27 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
