Database Administration

Database Backup and Restore

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!

Calculate size of tables and indexes

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";

Check progress while populating guid in releases table

SELECT count(*) from releases where nzb_guid is null and nzbstatus = 1;

Request-ID lookups

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);

Fix predb dates (2016-01 to 2015-01)

update predb set predate = CONCAT('2015', substr(predate, 5)) WHERE predate like '2016-01%' ;

Clean up releases in Misc processing older than 4 weeks

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);

Reset tables to default values

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).

Fix missing procedures, etc.

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