mySQL

From MattWiki
(Redirected from MySQL)


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

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

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