Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Commands to create users, database, password and grant necessary privileges from the command line
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;

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];
@sajaycvrajan

This comment has been minimized.

Copy link

sajaycvrajan commented Feb 14, 2018

Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.