One of the most popular ways to view and work with a MySQL database is through a web app called phpMyAdmin. There are many other tools out there that you can use, but why use a tool when you’ve already got a command line to work with?
I used to use phpMyAdmin all the time. I eventually found out, through many frustratingly lost hours, that phpMyAdmin doesn’t handle sql dumps very well. So, the next best bet was to do a dump through the command line. Ever since then, I began playing around with the database with the command line, and now it is my preferred method of working with the database.
This post will eventually grow to hopefully be a good reference for myself, and hopefully for you as well. I encourage all of you to learn this stuff. It will be worth it. As for now, the list here will be short, but I hope to grow it. If you have any specific requests, or better ways to do anything I’ve written here, please post a comment.
Connecting to the Database
mysql -u USERNAME -p -h 100.100.100.100 --port 3306 DATABASE;
- -u: Tells mysql that username you want to use
- -p: Tells mysql to prompt you for your password
- -h: Tells mysql what host to use (can be an IP address, or a domain.com)
- ––port: Tells mysql what port to connect to
- DATABASE: This you would replace with what database you want to connect to
If you have connected to the server already via SSH, you do not need to supply the host. You usually don’t need to supply the port, however, there will be cases that you need to supply one (ie: if you are running MAMP or WAMP, the database port is 8889). If the database has no password, you do not need to supply the -p.
Listing the Databases and Tables
#Display the databases
#Select a database to use
#Display the tables of that database
More to come. If you get connected and select a database to use, play around with what you know. This is all just standard MySQL commands. Try starting out with “SELECT * FROM yourtable;” and see what happens. If you haven’t used the command line before, you’ll be impressed with how the tables are displayed. Have fun!