Category:MySQL


 * Repairing MySQL
 * MySQL Commands
 * phpminiadmin
 * MySQL Master/Slave Replication

Access Control
Changing the root password for Mysql mysqladmin -u root password NEWPASSWORD This will give full access to the database "mythconverg" for the user "mythtv" from any computer. GRANT ALL ON mythconverg.* TO 'mythtv'@'%' IDENTIFIED BY 'mythtv'; or for just this network GRANT ALL ON mythconverg.* TO 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';

flush privileges; To Display all users: SELECT * from mysql.user;

Running a Query from the Command Prompt
echo "SELECT logdate, details FROM mythlog WHERE module='autoexpire' ORDER BY logdate DESC;" |mysql -t -h mythtv -u mythtv -pmythtv mythconverg

Mythtv Query's
delete from program where starttime < date_sub(curdate, interval 1 day) or starttime > date_add(curdate, interval 14 day);
 * This will remove any entries with dates more than 1 day in the past or more that 14 days in the future.

UPDATE Query's
UPDATE record SET autotranscode = 1, transcoder = 28 WHERE recgroup LIKE 'Matt'; UPDATE record SET inactive = 1 WHERE recgroup LIKE 'Kelsey';

echo "UPDATE record SET profile = 'Default', autotranscode = '1', transcoder = '28', autocommflag = '1', storagegroup = 'Transcoding' WHERE recgroup LIKE 'Kelsey';" |mysql -t -h mythtv -u mythtv -pmythtv mythconverg

DELETE Query's
DELETE FROM `ulog` WHERE datetime > date_add(curdate, interval 90 day);

DELETE Old Log Entry Query's
echo "DELETE FROM ulog WHERE local_time >= DATE_SUB(SYSDATE, INTERVAL 90 DAY);" |mysql -t -h localhost iptablelog

COUNT Query
select username, COUNT(username) AS addresses from awl GROUP BY username ORDER BY addresses DESC;

BLOB & LongText
echo "INSERT INTO play (ID, Data) VALUES ('test', '`/home/mythtv/bin/System/CheckStatus`');" |mysql -t test

Other Querys
for a in `echo 'show tables;' |mysql --skip-column-names mythconverg`; do echo "ALTER TABLE $a ENGINE = innodb;" |mysql -t mythconverg; done

Create a new Database
mysqladmin create DATABASENAME

Deleting a Database
mysqladmin delete DATABASENAME