Mysql + How to Create a Database & Add DB Users (All Diggin’ in the Dirt, Manual-Like)

Note:  If you’re working in a WHM / Cpanel environment, do NOT do it this way, instead, use the Cpanel tools…
(oh, and yea, anything enclosed in “<” and “>” below, are not intended literally, but rather as a placeholder for more reasonable inputs)

Login to the mysql command line as root.

mysql -uroot -p

Create the database:

CREATE DATABASE <DATABASE_NAME>;

Create the user, and grant permissions on said database:

GRANT ALL PRIVILEGES ON  <DATABASE_NAME>.* TO '<USERNAME>'@'localhost' IDENTIFIED BY '<DB_USER_PASSWORD>';

• Crispin

  Hey Lawrence, can you help me understand something?

  Most of our client sites have cPanel… so when you say "If you’re working in a WHM / Cpanel environment, do NOT do it this way, instead, use the Cpanel tools"

  Does that mean I cannot use these command lines after ssh-ing into the client’s server? And I should just setup db’s within cPanel GUI?

  Thanks for the clue!!

• Lawrence

  It’s a good question Crispin.  Well, you can use these commands on a cpanel system, but it’s generally a bad idea.  The reason it’s a bad idea is that you would then circumvent the cpanel system from knowing about the database.  So if you ever used the cpanel backup or migration tools, they wouldn’t know about your manually created database, and the database would be missing from the dump file.  Creating a database manually like this also prevents you from using the cpanel tools, like resetting a database user password.

  On a related note:  you can also manually setup cron tasks in a cpanel system.  But this is also generally a bad idea, as then cpanel isn’t aware of your cron settings.  And then if you go back and do use the cpanel tools to administer the crontab, cpanel will overwrite your manually input settings, and you’ll lose them.

  Good times!