How to Configure MySQL Server in Linux | MySQL | MariaDB | LinuxTopic

How to Configure MySQL Server in Linux | MySQL | MariaDB | LinuxTopic

mysql, mysql download, mysql tutorial, mysql interview questions, mysql queries, mysql commands, mysql versions, mysql download for windows, mysql dump, mysqldump, mysql dump database, mysql dump table, mysql restart, mysql restore, mysql server
MySQL Server
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 ?
mysql, mysql download, mysql tutorial, mysql interview questions, mysql queries, mysql commands, mysql versions, mysql download for windows, mysql dump, mysqldump, mysql dump database, mysql dump table, mysql restart, mysql restore, mysql server 


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
mysql, mysql download, mysql tutorial, mysql interview questions, mysql queries, mysql commands, mysql versions, mysql download for windows, mysql dump, mysqldump, mysql dump database, mysql dump table, mysql restart, mysql restore, mysql server
my.cnf
character-set-server=utf8

Step 3

To Start MySQL Service

service mysql restartchkconfig mysql on


Step 4

To Configuration MySQL Server, With the help of  mysql_secure_installation Command
mysql_secure_installation
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 to MySQL Server using Shell, Login into MySQL using cli or Terminal
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>





How to Show User and Host in MySQL / MariaDB / Display MySQL 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
  • mysql.user tells mysql database and the user table.
  • ; semicolon : The command ends with a semicolon
To Create a Database / How to Create Database in MySQL / MariaDB

mysql>CREATE DATABASE school;
Create MySQL Database



Show / Display database list

mysql> show databases;
mysql, mysql download, mysql tutorial, mysql interview questions, mysql queries, mysql commands, mysql versions, mysql download for windows, mysql dump, mysqldump, mysql dump database, mysql dump table, mysql restart, mysql restore, mysql server
show database
Add a User in database
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 [email protected];



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';



To Use Database

mysql> use <database name>
mysql>use school



To Create Table in database

mysql> CREATE TABLE student (id INT, name VARCHAR(20), email VARCHAR(20));
Create database tables
Display Tables / Show Database Tables

mysql> SHOW TABLES;



Insert Data in to the Table:

mysql> INSERT INTO student (id,name,email) VALUES(1,"jai","[email protected]");

mysql> INSERT INTO student (id,name,email) VALUES(2,"ram","[email protected]");

mysql> INSERT INTO student (id,name,email) VALUES(3,"shive","[email protected]");



Show / Display Table data:
myslq> SELECT * FROM student;



Updating data in Database Tables

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;
Delete data from database
Here We update name shyam to ram with id = 2

Deleting data From Database Tables

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

To Setup a  MySQL root  Password
mysqladmin -u root password NEWPASSWORD
To Change or Update a MySQL root  Password
mysqladmin -u <username> -p'<old Password>' password <new password>
mysqladmin -u root -p'server' password server32
BACKUP & RESTORE of DATABASE

How to Dump a MySQL / MariaDB database ?

Backup: back up a single database, you create the dump and send the output into a file,
mysqldump -u root -p <databasename> > <dump file name.sql>mysqldump -u root -p mail > mail.sqlEnter password: ********


Restore Database:
mysqldump -u root -p <database> < <dumpfile>.sqlmysqldump -u root -p mail < mail.sql
Enter password: ******



Multiple 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 »