Category:MySQL

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

Access Control[edit | edit source]

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[edit | edit source]

echo "SELECT logdate, details FROM mythlog WHERE module='autoexpire' ORDER BY logdate DESC;" |mysql -t -h mythtv -u mythtv -pmythtv mythconverg

Mythtv Query's[edit | edit source]

• 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[edit | edit source]

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[edit | edit source]

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

DELETE Old Log Entry Query's[edit | edit source]

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

COUNT Query[edit | edit source]

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

BLOB & LongText[edit | edit source]

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

Other Querys[edit | edit source]

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[edit | edit source]

mysqladmin create DATABASENAME

Deleting a Database[edit | edit source]

mysqladmin delete DATABASENAME