how to configure MySQL server in Linux step by step

Question : how to configure MySQL server in Linux ?

Question : step by step MySQL  configuration in centos 6 ?

Question : what is database and how to configure ?



IP  - 10.20.2.33

Hostname - khandwa.lokesh.com

OS  - CentOS 6

PORT  - 3306

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use.

Required Packages :

mysql-server : The MySQL server and related files

mysql : MySQL client programs and shared libraries
            Additionally, you may need to install the following packages to access mysql using various                 programming languages:

php-mysql : A module for PHP applications that use MySQL databases.

perl-DBD-MySQL : A MySQL interface for perl.

MySQL-python :  An interface to MySQL.

step 1

Install MySQL Server & Client

# yum install mysql*  -y

For Using * after mysql , take all package related to mysql


Import Mysql server files and ports

Mysql server config file: /etc/my.cnf

Mysql server log file: /var/log/mysqld.log

Mysql database storage directory: /var/lib/mysql/

step 2

Open my.cnf file and add line

# vi /etc/my.cnf

character-set-server=utf8

step 3 

Start MySql Service

# service mysql restart

# chkconfig mysql on

step 4 

Configuration mysql Server

# mysql_secure_installation

Note:Press enter to give password for root when that program asks for it by default root password is null

step 5

Login in Myslq Server( Myslq shell )

# mysql -u <username> -p  <enter>

Enter password:********

mysql>

OPTION

-u  : MySQL Username

-h : MySQL server name (default is localhost)

-p : Prompt for password

Example :

# myslq -u root -p

Enter password:********

mysql>

Display user list

mysql> select user,host,password from mysql.user; 

SELECT command tells MySQL that you are asking for data

User, Host, Password is MySQL  fields

Myslq.user tells mysql database and the user table.

; semicolon : The command ends with a semicolon

Create a Database

mysql>CREATE DATABASE school;

Display database list

mysql> show databases; 

Add a database user:

mysql>CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';

mysql>CREATE USER 'lokesh'@'localhost' IDENTIFIED BY 'server32';

Grant database user permissions

mysql>GRANT ALL PRIVILEGES ON <database>.* to <username>@localhost;

mysql>GRANT ALL PRIVILEGES ON school.* to lokesh@localhost;

Flush the privileges to make the change take effect.

mysql>FLUSH PRIVILEGES;

verify that the privileges

myslq>SHOW GRANTS FOR '<username>'@'localhost';

mysql>SHOW GRANTS FOR 'lokesh'@'localhost';

Use Database

mysql> use <database name>

mysql>use school

Create Table in database

mysql> CREATE TABLE student (id INT, name VARCHAR(20), email VARCHAR(20));

Display Tables

mysql> SHOW TABLES;

Insert Data in to the Table:

mysql> INSERT INTO student (id,name,email) VALUES(1,"jai","jai@abc.com");

          mysql> INSERT INTO student (id,name,email) VALUES(2,"ram","ram@abc.com");

mysql> INSERT INTO student (id,name,email) VALUES(3,"shive","shive@abc.com");

Display Table data:

myslq> SELECT * FROM student;

Updating data

The UPDATE statement is used to change the value of columns in selected rows of a table.

mysql> SELECT * FROM student;

mysql> UPDATE student SET name='shyam' WHERE Id=2;
i update name shyam to ram with id = 2

Deleting data

mysql> DELETE FROM student WHERE Id=1;We delete a row with Id=1.

Delete all data in the table

mysql> DELETE FROM student;

mysql> TRUNCATE student;

Delete a Database in MySQL

mysql>DROP DATABASE <database name>;

mysql>DROP DATABASE student;

step 6

Setup a root user password

first time :

#mysqladmin -u root password NEWPASSWO

Change or Update a root user password

# mysqladmin -u <username> -p'<old Password>' password <new password>

# mysqladmin -u root -p'server' password server32

BACKUP & RESTORE of DATABASE

Backup: back up a single database, you create the dump and send the output into a file,

[root@khandwa ~]# mysqldump -u root -p <databasename>  > <dump file name.sql>

[root@khandwa ~]# mysqldump -u root -p mail > mail.sqlEnter password: ********

          [root@khandwa ~]#ls

Restore Database:

[root@khandwa ~]# mysqldump -u root -p <database>  <   <dumpfile>.sql

[root@khandwa ~]# mysqldump -u root -p mail < mail.sql

Enter password: ******

Multipal Database Backup

mysqldump -u root -p [database name 1] [database name 2] [database n]  > [dump file]

#mysqldump -u root -p mail lokesh lokesh1  >  databasebackup.sql

Backup all databases in MySQL.

mysqldump -u[user name] -p[password] --all-databases > [dump file]

# myslqdump -u root -p -all-databases > alldatabasebackup.sql

Backup a specific table in MySQL.

# mysqldump -u root -p [database name] [table name] \
> /tmp/sugarcrm_accounts_contacts.sql

phpMyAdmin : Click Here.. For WEB-BASED GUI Management for Mysql User php