MySQL is database that used worldwide for various applications through different companies. Best thing is that still it’s an Opensource product.  Here in this blog we try to figure out some of MySQL commands usages on Linux systems. Here I am using RHEL5.

[root@srv30 ~]# yum install mysql*

    .....

Installed: mysql.i386 0:5.0.45-7.el5 mysql-bench.i386 0:5.0.45-7.el5 mysql-connector-odbc.i386 0:3.51.12-2.2 mysql-devel.i386 0:5.0.45-7.el5 mysql-server.i386 0:5.0.45-7.el5 mysql-test.i386 0:5.0.45-7.el5
Dependency Installed: e2fsprogs-devel.i386 0:1.39-20.el5 keyutils-libs-devel.i386 0:1.2-1.el5 krb5-devel.i386 0:1.6.1-31.el5 libselinux-devel.i386 0:1.33.4-5.1.el5 libsepol-devel.i386 0:1.15.2-1.el5 libtool-ltdl.i386 0:1.5.22-6.1 openssl-devel.i386 0:0.9.8e-7.el5 perl-DBD-MySQL.i386 0:3.0007-2.el5 perl-DBI.i386 0:1.52-2.el5 unixODBC.i386 0:2.2.11-7.1 zlib-devel.i386 0:1.2.3-3
Complete!

The Start the MySQL service.

[root@srv30 ~]# service mysqld restart
Stopping MySQL: [FAILED]
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OKTo start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h srv30.linuxphobia.com password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]

For Security point of view, assign a password for the root user.

mysqladmin –u root password new-password
[root@srv30 ~]# mysqladmin -u root password passw0rd

Now For access the MySQL, you need to use password like this.

[root@srv30 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.0.45 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

Let’s go through some basic commands of MySQL.

How can we see various databases in MySQL

 How to create Database

 

Create tables in MySQL.

Grant permission on database for Users

mysql> grant all privileges  on linuxphobia.* to  linuxphobia@'localhost' identified by 'linuxphobia';
Query OK, 0 rows affected (0.00 sec)

This command grant full permission on linuxphobia database to linuxphobia User (only mysql User) with the password linuxphobia
Noticeà This User is nothing relates to system, this is only exist in MySQL databases. You can check it as well
For checking it, go to mysql database. Then check the table named user.

mysql> use mysql
Database changed
mysql> select *  from user where user='linuxphobia';

It is obvious that you should have rights as non-root user for particular databases, so this will your checking for this command.
Take a backup of MySQL.

[root@srv30 ~]# mysqldump -u root -p linuxphobia > linuxphobia.sql
Enter password:
[root@srv30 ~]# ls
anaconda-ks.cfg  install.log  install.log.syslog  linuxphobia.sql

This backup is for content of the database. Means during the restore database should be in MySQL.
Backup all the Databases in MySQL.

[root@srv30 ~]# mysqldump -u root -p --all-databases > mysql.sql
Enter password:
[root@srv30 ~]# ls
anaconda-ks.cfg  install.log  install.log.syslog  linuxphobia.sql  mysql.sql

Restore of database.

[root@srv30 ~]# mysql -u root -p linuxphobia < linuxphobia.sql
Enter password: