 |
| 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
 |
| 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;
 |
| 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 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';
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","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");
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
