Create new mysql user with grant option


Login in mysql using:

$ mysql -u root -p

First create a database using:

mysql> create database dbname;
Query OK, 1 row affected (0.00 sec)

Then in mysql shell create new user using:


mysql> create user 'username'@'localhost' IDENTiFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on dbname.* to 'username'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

Or you can use ‘%’ wildcard if you want to connect mysql from any host:

mysql> create user 'username'@'%' IDENTiFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on dbname.* to 'username'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

For more details visit mysql manual.

Advertisements

Create and Import dumpfile or SQL datafile into mysql database


Here are few commands to easily create and import MySQL data.

To create dumpfile:
mysqldump -u USER -p PASSWORD DATABASE > filename.sql

To import dumpfile:
mysql -u USER -p PASSWORD DATABASE < filename.sql

Export MySQL database structure only:
mysqldump -u USER -p PASSWORD --no-data DATABASE > filename.sql

Export data of MySQL database only:
mysqldump -u USER -p PASSWORD --no-create-info DATABASE > filename.sql

To create dumpfile of all databases:
mysqldump -u USER -p PASSWORD --all-databases > filename.sql

To import dumpfile when either database name is not known or dump has database info:
mysql -u USER -p PASSWORD < filename.sql

USER : database username
PASSWORD : password
DATABASE : database name to which datafile is to be imported