-
Notifications
You must be signed in to change notification settings - Fork 182
Database Administration
nZEDb uses the UTF-8 character set and stored procedures. These two features require special handling in order to correctly dump and restore the database.
To dump the database:
mysqldump --routines -u nzedb -p nzedb -r /PATH/nzedb-$(date +"%Y-%m-%d").sql
The --routines parameter saves the stored procedures. The -r option is used instead of > as it is UTF-8 safe. The optional $(date +"%Y-%m-%d") string appends today's date to the filename.
To restore the database:
mysql -u nzedb -p --default-character-set=utf8 nzedb
mysql> SET names 'utf8'
mysql> SOURCE /PATH/nzedb-2017-04-26.sql
The < operator is not used as it is not UTF-8 safe!
found here: http://blog.elijaa.org/index.php?post/2013/08/22/Calculate-Optimize-MySQL-Database-Size
SELECT TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows",
CONCAT((FORMAT((DATA_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Data Size",
CONCAT((FORMAT((INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Index Size",
CONCAT((FORMAT((DATA_LENGTH+ INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Total Size",
TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "nzedb"
or
DELIMITER $
DROP FUNCTION IF EXISTS byteResize$
CREATE FUNCTION byteResize(bytes FLOAT(9))
RETURNS VARCHAR(50)
BEGIN
# Unit list
DECLARE unit INTEGER UNSIGNED DEFAULT 1;
# Resizing
WHILE bytes > 1024 DO
SET bytes = bytes / 1024;
SET unit = unit + 1;
END WHILE;
RETURN CONCAT(ROUND(bytes, 2), ' ', ELT(unit, '', 'K', 'M', 'G', 'T'), 'b');
END$
DELIMITER ;
SELECT TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows",
byteResize(DATA_LENGTH) AS "Data Size",
byteResize(INDEX_LENGTH) AS "Index Size",
byteResize(DATA_LENGTH+ INDEX_LENGTH) AS "Total Size",
TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "nzedb";
SELECT count(*) from releases where nzb_guid is null and nzbstatus = 1;
Count
SELECT count(*) FROM releases r left join category c on c.ID = r.categoryID where (r.passwordstatus between -6 and -1) and (r.haspreview = -1 and c.disablepreview = 0);
View
SELECT r.ID,r.passwordstatus,name FROM releases r left join category c on c.ID = r.categoryID where (r.passwordstatus between -6 and -1) and (r.haspreview = -1 and c.disablepreview = 0);
update predb set predate = CONCAT('2015', substr(predate, 5)) WHERE predate like '2016-01%' ;
UPDATE releases r LEFT JOIN categories c ON c.ID = r.categories_id SET passwordstatus = 10 WHERE (r.passwordstatus BETWEEN -6 and -1) AND (r.haspreview = -1 AND c.disablepreview = 0) AND (r.adddate < NOW() - INTERVAL 672 HOUR);
If you find the need to reset a particular table's content (for example your release_naming_regexes have gotten out of wack):
DROP TABLE IF EXISTS <table-name>;
LOAD DATA LOCAL INFILE <path-to-nzedb-directory>/resources/db/schema/data/10-<table-name>.tsv
IGNORE INTO TABLE `<table-name>`
FIELDS TERMINATED BY "\t"
LINES TERMINATED BY "\r\n"
IGNORE 1 LINES (<table-fields>);
Replace the placeholders (<path-to-nzedb-directory>, <table-name>, <table-fields>) with the appropriate values. The <table-fields> can be found by opening the file you will be loading from, and copying the first line (be sure not to change/save the file accidentally).
If you find the need to fix/replace a routine or procedure you can do so as follows:
-
Find the patch file (.../resources/db/patches/mysql/) that added the procedure you need to (re)create.
-
Use the commands for restoring the database (top of this page) to run the patch file. Replace the
SOURCE /PATH/nzedb-2017-04-26.sql
with
SOURCE /<path-to>/<patch>.sql