MySQL Admin Tips

Hi friends,

This article gives you the quick start admin guide for MySQL.

# change root password
$ mysql -u root
set password for root@localhost = password('abcd');
set password for root@localhost = '';
flush privileges;
quit;

# create new database
$ mysql -p -u root
CREATE DATABASE amarok;
USE mysql;
GRANT ALL ON amarok.* TO amarok@localhost IDENTIFIED BY 'PASSWORD_CHANGE_ME';
FLUSH PRIVILEGES;

# get the config variables
mysqladmin -u root variables
for example, to find the datadir
mysqladmin -u root variables | grep 'datadir'

# rename a DB
mysqladmin -u root create boss
mysqldump -u root --opt boss_dev >db.sql
mysql -u root boss mysql> revoke all on boss_dev.* from 'old_db_user'@'localhost';

# give access from remote host
GRANT ALL ON *.* TO 'root'@'192.168.1.%';

# user management
select host, db, user from db;
revoke all on noss2_prd.* from 'boss2'@'localhost';
select host, user, password from user;
delete from user where user like 'dep%';

# procedures
select routine_schema, specific_name from INFORMATION_SCHEMA.ROUTINES;

# views
select table_schema, table_name from INFORMATION_SCHEMA.VIEWS;

# tables
SELECT table_schema, table_name, table_type, engine, data_length
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'boss' AND table_name like 'audit%';

SELECT table_name, data_length FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'boss' AND table_name like 'audit%';

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'boss' AND table_name = 'audit'\G

DROP TABLE audit_20060427102929;
DROP TABLE boss_2006apr27094201;

Thanks
Logu
logu_microsoft@hotmail.com
91-98414-99143