The Most Important MySQL Commands

How to setup MySQL a database, a user, and then import SQL data.

1- SSH into server

# mysql -u root -p

It will prompt for password for root mysql user.

This will change your prompt to mysql prompt.

Note: If you're using our PHP or Tomcat web hosting, use your SiteWorx user name and password instead of "root".

2- Create database

> CREATE DATABASE dbname CHARACTER SET utf8;
> CREATE USER 'username'@'localhost' IDENTIFIED BY 'some-pass';

note: where dbname is the database name you desire to use,username the user you choose, and some-pass is whatever password you wish to us.

3- Now you need to bind that user to the database:

> USE dbname; (dbname as in database name you set above)
> GRANT USAGE on *.* to username@localhost identified by 'some-pass';
> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';
> exit

Note: Substitute localhost with % if you wish to access database remotely. For example GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

4- Import SQL file to your database

# cd /path/to/sqlfile/
# mysql -u root -p dbname < filename.sql

You will be prompted for your password.

How to make backup or dump of your database

Suppose your database is myDatabase and you want to export all data to a file called MyExportedData.sql

# mysqldump -u root -p myDatabase > MyExportedData.sql

  • 52 Users Found This Useful
Was this answer helpful?

Related Articles

PostgreSQL

What Is PostgreSQL? PostgreSQL is pronounced Post-Gres-Q-L, also called just Postgres....

How to Use MySQL

Overview MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query...

Connecting to MySQL Remotely

You can connect to MySQL remotely by using host % instead of localhost. If you are security...

How to Install Oracle XE 11g on CentOs 5

 Note: # means ROOT prompt. # yum install libaio bc flex Download...

How to create database in Siteworx

How to create a database in siteworx With Sitworx UI you have full control over the creation of...