I use the mysql command-line client a lot. It acts like a mini-shell that makes it easy to send commands or queries to a MySQL server and view the results. However, its navigational facilities aren't terribly shell-like.
To change to a particular database, you must use the "USE" command:
mysql> use test Database changed
To get a list of databases, you must use the "SHOW DATABASES" command:
mysql> show databases; +------------+ | Database | +------------+ | Tribute911 | | mysql | | test | +------------+ 3 rows in set (0.07 sec)
To get a list of tables within a database, you'd typically use the "SHOW TABLES" command after having USEd that database:
mysql> use test Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | BODY_SIDE | | Filer | | aatest | | blah | | http_auth | | passwd | | t1 | | t2 | | test | +----------------+ 9 rows in set (0.07 sec)
Often times I'll forget that I'm at a "mysql>" command prompt, type "ls" or "cd", and just expect it to know what I want.
Today I fixed that bug. As of now, my copy of the client understands the "cd" and "ls" commands. The patch is available (against the latest 4.0 BitKeeper tree) and has been sent to the developers for possible inclusion.
Here it is in action:
mysql> ls +------------+ | Database | +------------+ | Tribute911 | | mysql | | test | +------------+ 3 rows in set (0.07 sec) mysql> cd mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ls +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.07 sec)
I like it. :-)
Update: In the comments, Russell asks about setting the prompt to contain the current database name. Of course you can do that!
mysql> prompt mysql (\d)> PROMPT set to 'mysql (\d)> ' mysql ((none))> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql (mysql)>
That was added quite a while ago.
Posted by jzawodn at July 15, 2004 01:11 PM
Nice! Now if you could just get the prompt to tell you which DB you're in (like a Unix command prompt), that would *rock*.
-Russ
Cool!
But don't stop there.
mkdir == create database
rmdir == drop database
ls -l table == describe table
And so on. Sort of the reverse of the shell which aped the game of Adventure.
I've actually given some thought to incorporating mysql commands into a regular shell.. extend the filesystem to treat mysql as a set of directories and files, etc..
The things that complicate the situation are: 1) authentication to get into mysql databases - since mysql doesn't use /etc/passwd users. 2) issuing mysql statements would be a pain because you'd have to escape sertain characters on the command line, even if you had SELECT, DELETE, UPDATE shell binaries that just passed stuff to the mysql client.
Your mods are definitely a step in the right direction. I find that the mysql command-line client is by far the most convenient interface to a database server I've used, and there's no harm in making it even more convenient. In fact, there's room to maybe add a .mysqlrc file to user directories and read a bunch of alias commands from there. :)
Ha. You aren't the only one who repeatedly types ls accidentally while using mysql. I like the addition, though I don't think I agree with making other shell commands do things. I'd rather see rmdir give an error than drop a database. But maybe thats just me. ls, on the other hand, is harmlessly helpful.
I don't know how many times I've done the same thing trying to CD to a database or LS the tables in a database. I sure hope they accept this for inclusion.
Another nice feature for the MySQL Client would be to provide additional info for the SHOW DATABASES (such as number of tables in each database) and SHOW TABLES (such as number of columns and rows for each) commands. But maybe that's asking too much.
Otherwise, I really don't have any gripes with the client's performance.
"Of course you can do that! ... That was added quite a while ago."
Nice again! That should really be the default.
Had to laugh because of course I've done that. Even worse, typed 'exit' and/or 'ls -l' etc into IM windows not realizing the terminal wasn't in focus.
Was thinking it would be cool to have something like 'alias' inside the MySQL shell to create shortcuts for common commands like "show status like 'Qcache%'". Maybe that's overkill, but it did prompt me to make a shell alais: alias qq="mysql -e \"show status like 'Qcache%'\""
I'm partial to the "alias" syntax that Mike Kruckenberg mentioned above. It would be nice to be able to setup a .mysqlrc file with aliases for commonly used commands.
Nice to notice that people are trying to make MySQL easier to use. Indeed some kind of support for aliases could be nice thing also so that would allow do more things in less time. Still that if there are some errors in aliases that could propably destroy much of the content of the database if not used properly (but that would be possible anyway if user don't know what he/she is doing).
Another thing that would be nice is wildcards. I would love to be able to enter:
use bigdatabase;
show tables;
describe %; or describe 'user%';
Anything to be able to describe all my tables in a database.
Seth: The funny thing is that 'rmdir' and 'mkdir' are actually the only bloody thing that mySQL -does- on a DROP DATABASE/CREATE DATABASE ;)
Ivan: if you ever get around to your plans, you may want to look at my dteq tool (http://www.dataloss.nl/software/dteq/), it allows for easy formatting of mysql query results. It's mostly used for generating configuration files from mySQL but it can be useful in a plain shell environment too. I've been trying to come up with a couple of aliases (like SELECT, UPDATE) but it's not trivial - and I've been getting on without aliases for years now :)
Please don't have them add hardcoded commands! create and Alias keyword. So that everyone can create their own little hacks.
It's a neat idea.. none the less. As I'm sure many people type ls all over the place.. like IRC :)
Why not make a mysql file system module for linux/BSD. That way one could just mount a database and cd to it.
Philip, you mean like the project at http://no.spam.ee/~tonu/modules.php?name=News&new_topic=2
?
That's great - the best ideas are always the simplest.
However I wouldn't take it any further and go down the mkdir/rmdir road as otherwise you'll end up rm'ing the wrong things and ls'ing the wrong things - which will just give you the same problem in reverse ;-)
That's great - the best ideas are always the simplest.
However I wouldn't take it any further and go down the mkdir/rmdir road as otherwise you'll end up rm'ing the wrong things and ls'ing the wrong things - which will just give you the same problem in reverse ;-)
Cool. I also like the idea of the filesystem access to MySQL. I sometimes try to do a "select * from foo..." at the command prompt.
Also, my excitment about customising the MySQL client's prompt was promptly dashed (pun intended), as that was only added in version 4.0.2. I really must get around to upgrading from 3.23...
I imagine that most of us have been doing this secretly and each time yelling something along the lines of "Newman!" at MySQL when it comes back with a parse error.
Bravo, Jeremy!
Actually, what I really want is:
show processlist order by State, Info; or
select * from 'show processlist' where info is not NULL.
Of course, neither of these things make it more unix like, just would make my life easier at times.
This is a tremendous bit of business; nicely done. I suspect it'll be difficult to roll between installations that have/don't have these capabilities. :)
hey i have problem with mysql databasse
radiomar 17057 0.0 0.9 10408 4096 ? S 18:15 0:00 /usr/bin/php radio_stats.php
radiomar 17068 0.0 0.9 10412 4100 ? S 18:16 0:00 /usr/bin/php radio_stats.php
radiomar 17089 0.0 0.9 10412 4096 ? S 18:16 0:00 /usr/bin/php radio_stats.php
radiomar 17092 0.0 0.9 10412 4100 ? S 18:16 0:00 /usr/bin/php radio_stats.php
now shows to me this and ussign my server load slow and i cant open my website how i can stop this
