The Most Important MySQL Commands Print

  • 52

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 based hosting or Java 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


Was this answer helpful?

« Back