how to configure MySQL server in Linux step by step

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 
Previous
Next Post »