projectz

Tech, Gadgets, Photography, Social Media and Poor Spelling

Setting up a MYSQL Database, Table and Granting Permissions from the command line.

This post is really a reminder for me.. It’s a few MYSQL commands which i was using today. I’m a lazy bugger most of the time and would normally do this using phpmyadmin. However I’ve had to get this working using the command line.

Logging into a MYSQL Database

mysql -u <username> -h <hostname> -p <password>

Creating an SQL database

create database <databasename>;

 Setup a user with the all privileges to manage the database

GRANT ALL PRIVILEGES ON <databasename>.* TO dbuser’@’networklocation IDENTIFIED BY ‘yournewpassword’ ;

FLUSH PRIVILEGES ;

To use the database you just created, otherwise you can’t easily setup tables in the new database:

use filedownloads;

Setup a new Table in your Database:

CREATE TABLE <database_table> ( download_id int unsigned not null auto_increment primary key, flag varchar(20), filedate varchar(20), filename varchar(30), filesize int, username varchar(60) );

Its worth explaining the above:

  • created a primary key column in the database called download_id which is number value, will auto increment on each new record and can’t be empty.
  • the varchar is the field type with the length of the field in brackets

I’ve broken the next command up into individual lines, this is useful for setting up users with specific rights to the database. In my example i have a database user on a different server attaching remotely and inserting records. And a local user setup to select and update records from PHP

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON databasename.*

TO ‘username’@’clientipaddress

IDENTIFIED BY ‘password’;

IF you need to add another column to this table:

alter table <tablename> add column <new column name> varchar (20) ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on November 19, 2011 by in regular and tagged , , , , , .
%d bloggers like this: