How to create a MySQL database with a shell account
Some hosts give you the ability to login with a shell account through SSH. Even if this option is available, and on many hosts it is not, most of your admin activities will be more difficult since you will be using a command line interface rather than a web control panel like cPanel or Plesk. On the other hand once you learn the command line methods of maintaining your server often you can take care of tasks much more quickly. For example, it is a very quick process to create MySQL databases through shell access.
Login to MySQL
When you first login to your shell account you will be presented with a screen much like the following. Type in your login name and your password when prompted.
For my example I am logging in as root which is a very bad idea, and probably not something you will be allowed to do on your server anyway.
After you’ve successfully entered your username and password you should see a screen that looks like the following.
Starting MySQL
At the prompt type mysql and press enter. If you are on a Linux server you must use all lower case. On a Windows server the case doesn’t matter.
If you need to enter a username and password that’s different than the login you used for your shell account you can use the -u and -p flags after mysql to enter your username and password respectively. Be sure that there isn’t a space between -u and your username or -p and your password.
Inside MySQL
If you were able to login to the MySQL server you should see a screen that looks like the following.
Notice that the prompt has changed to mysql> instead of [username@servername]#. Also notice that the version of the MySQL server is shown; in this case 5.0.24a-standard.
Creating the Database
Type the following command, replacing test with what you would like to call your new database.
Be sure to end the command with a semicolon. This is how MySQL knows that you’re finished with the command.
Assuming that the database was successfully created you should see the following status returned. If an error is returned check with your host.
Adding a User
Now you’ll need to add a user to access the database.
While you can technically use the same user to access your database as you are using to create the database, generally that is a bad idea as it can present a security risk.
Use the following GRANT command to add a user to your newly created database, replacing testuser with your new username and testpassword with your new password.
And you should receive the following status message.
And that’s it!
That’s it. You now have a new database on your MySQL server with a user set to be able to access it. You’ll use this username to connect to your database through whatever database based web application you’re using.
Question, Comments...
Do you have more questions. Please either leave a comment below or join us in our new forum.