If you’re setting up a MySQL database on a server without a GUI, then the following steps will hopefully help get you up and running quickly.
If you are in a graphical environment then you might be best off using MySQL Workbench (which is a replacement for previous MySQL Admin).
Let’s create the following
1) A database named ‘acmeproducts’
2) A user named ‘acmeuser’ with a password ‘acmepass’
Login to MySQL as the root user
mysql -u root -p
Then enter your root password.
Create a new database
mysql> create database acmeproducts;
Create a new user for your database
It’s generally good practice to have a separate user account for each database you are creating, so let’s create an account that only has access to our database.
Note that user names must be 16 characters or less.
mysql> create user 'acmeuser'@'localhost' identified by 'acmepass';
And allow the user to access your database.
mysql> grant all privileges on acmeproducts.* to 'acmeuser'@'localhost';
Quit out of the MySQL interpreter
mysql> quit;
Executing a MySQL script
You can execute a MySQL script from the command line:
mysql -u root -p < acmeproducts.sqlSome useful MySQL commands
How to select a MySQL database?
mysql> use acmeproducts;
How to show a list of MySQL databases?
mysql> show databases;
How to show a list of tables in a MySQL database?
mysql> use acmeproducts; mysql> show tables;
How to drop a MySQL database
mysql> drop database acmeproducts;
3 Comments
I’m sure it’s common knowledge, but still: remember that granting a user all privleges is very bad practice. (You could write an entire article just about privleges I guess.)
Being careful is usually a better way to think about critical steps like these. My motto: “being too careful is better than being lazy”.
Still a useful list of commands.
@Michiel, thanks for the note.
There is a easy reference that doesn’t get into too much detail here: http://www.ntchosting.com/mysql/grant.html
I imagine that a useful and more explicit privilege grant may be something like:
mysql> grant select, insert, update, delete on acmeproducts.* to acmeuser@’localhost’;
Indeed, that will be enough in most cases. Some advanced users might require Stored Procedures.
I sometimes fantasize about writing a simple form of AI, maintaining its own database and creating and altering tables as required. Unfortunately, that’s as far as I get usually. I’m yet to find out how to make something out of it.