Introduction:
This topic plagues me to death every time I need to do some administrative function MySQL simply because I don’t do it every day. I have 3 servers that I manage entirely via the command line now and all 3 require me to know at least some MySQL. Unfortunately, I always end up going to several sources to get all the information I need. So instead of doing that in the future, I’m writing this blog as a centralized reference for everything I need. Hopefully you can use this blog as much as I will!
Note: You will need to click the “show code” icon in the top right corner to view the code entirely for some of the blocks that are longer than the code box.
POST UPDATED 6/5/2011 (deleting a user / listing users)
A few side notes:
- Hosts: Mysql allows access to certain hosts. % means allow the user to connect from any computer on the internet that can reach the database server, localhost / 127.0.0.1 both will allow the user to connect only from the local database server.
- means you need need to put in the appropriate information
Basic User Commands:
mysql -u <username> -p
- Login may require -h
- This is done through SSH / the console, not within the MySQL interpreter!
update mysql.user set password=PASSWORD("<new password>") where User='<username>';
- You don’t want to use % or other mysql special characters in the password as they will cause you grief when using other commands.
- This should be done within the MySQL Interpreter
Backup / Restore Commands:
These should be done through SSH / the console! NOT within MySQL’s interpreter!!!
mysqldump -u root -p'<mysql root's password>' --all-databases > <filename>.sql
mysql -u root -p<Root's Password> < <filename to restore backup with .sql extension>
mysqldump --user=<username> --password=<password> --databases <database> --opt --quote-names --allow-keywords --complete-insert > <filename>.sql
mysql -u <username> -p<User's Password> < <filename>.sql
General Administration Commands:
NOTE: All of these commands should be done within the MySQL interpreter!
create database <database name>;
drop database <database name>;
create user '<username>'@'<allowed hosts>' identified by '<user's password>';
grant all on <database name>.* to '<username>'@'<allowed hosts>';
select host, user, password from mysql.user;
delete from mysql.user where user='<username>'; delete from mysql.db where user='<username>';
flush privileges;
exit;
update mysql.user set host=’<allowed hosts>’ where user=’<username>’;
update mysql.db set Host='<allowed host>' where Db='<database name>';
update mysql.db set Host='<allowed host>' where Db='<database name>';
use <database name>;
show databases;
show tables;
describe <table name>;
Disclaimer:
I may have got a few of these commands incorrect (typo). If I did, please let me know so I can fix them asap. If I missed a command that you’d like, please comment and I will add it.
More From FamousPhil.com
Tags: administration, cheat, command line, interpreter, mysql
Posted in Hosting / Server Administration, Programming
Hey, I just hopped over to your site via StumbleUpon. Not somthing I would normally read, but I liked your thoughts none the less. Thanks for making something worth reading.
Sound good. Its also my favorite topic.That’s great andthanks for the fine sharring.
Terrific, that’s definitely what I was shooting for! You just saved me alot of work
Fantastic blog article.Really looking forward to read more. Cool.