MySQL Commands to create users, database, password and grant necessary privileges from the command line

access_from_web.md

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' WITH GRANT OPTION;

mysql_commands.md

Bash commands

starting the server

$ mysql.server start

stoping the server

$ mysql.server stop

connecting to the server

$ mysql [-h hostname/ip] -u usernaem -p [database]

Backups

Create backup

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Restore backup

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

After install

Installing MySQL system tables

# mysql_install_db

Creating a new user

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from MySQL prompt.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Querys

Create a database

mysql> create database [database_name];

List all databases

mysql> show databases;

Switch to a database

mysql> use database_name;

List all the tables in the db.

mysql> show tables;

List database's field formats.

mysql> describe [table name];

Delete a database

mysql> drop database [database name];

Delete a table.

mysql> drop table [table name];

List columns and informations of a table

mysql> show columns from [table name];

Give user privilages for a db

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

Update database permissions/privilages

mysql> flush privileges;

Delete a column

mysql> alter table [table name] drop column [column name];

Add a new column to db

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique index column

mysql> alter table [table name] add unique ([column name]);

Modify collumn

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete index

mysql> alter table [table name] drop index [colmn name];