MySQL Basic

Every Linux Administration sometimes have to deal with MySQL database. Following command may help us to remember the basic command which we can use while dealing with MySQL Database.

Once you have installed MySQL on linux machine first thing you need to start MySQL.
Starting MySQL

/etc/init.d/mysql restart

MySQL configuration file
my.cnf file is the main MySQL configuration file. In Fedora you will get it in /etc/my.cnf and in Ubuntu you will find it in /etc/mysql/my.cnf

Assigning password to root

mysqladmin -u root password "techienote"

Login to MySQL
To login to your MySQL database you need to specify the username, “-p” will ask you the password

mysql -u username -p

Once you login into MySQL you will get following prompt

mysql>

Creating Database

mysql> create database database_name;

Deleting Database

mysql> drop database database_name;

List all Databases

mysql> show databases;

Using Database

mysql> use database_name;

List all Tables in the Database

mysql> show tables;

List all data in Table

mysql> select * from table_name;

Delete Table

mysql> drop table table_name;

Creating a user for database and assigning a password for it

mysql> grant all on database_name.* to database_user@servername identified by 'database_passwd';

Taking MySQL Databse backup

mysqldump -u database_username -p database_name > backup_file

It will generate file with sql extension.

Restoring MySQL Database backup

mysql -u database_username -p database_name < backup_file

For more information, please refer to the MySQL Reference Manual, which is available online at http://dev.mysql.com/doc/