Introduction to MySQL Database Administration(Article -01)

Introduction to MySQL Database Administration.

What is MySQL?

MySQL is the most popular open source SQL database management system and it is developed, distributed and supported by Oracle Corporation.

Initially MySQL was introduced by MySQLAB organization and acquired by SUN MICRO SYSTEMS then MySQL acquired by ORACLE .

Overview of MySQL

MySQL is a relational database management system (RDBMS) released under the GNU General Public License (GPL). It is one of the most widely used open-source database systems, and is compatible with a multitude of website applications.

MySQL is available in two products

  • Enterprise edition – is available as subscription based edition.
  • Community – is available for free of cost.

OS supported by MySQL

MySQL supports most of the operating systems such as Linux, Windows, and MAC etc…

MySQL Tools

Workbench’s is one of the most popular GUI tool.

  1. Navicat for MySQL
  2. Sequel Pro
  3. HeidiSQL
  4. phpMyAdmin
  5. SQLyog
  6. DBTools Manager
  7. MyDB Studio

 

Installation of MYSQL

MySQL Enterprise DB installation on Linux.

You can download software from below URLs.

Community edition:

https://dev.mysql.com/downloads/

Enterprise Edition : 

https://support.oracle.com/epmos/faces/MosIndex.jspx?_afrLoop=406840439614708&_afrWindowMode=0&_adf.ctrl-state=aab0jfx7x_4

Login to server using putty and execute apt-get update   to check updated are properly installed or not.

To Install MySQL DB execute below command, and make sure you are installing as root user.

Press enter then you will get below screen asking for root password, it is not mandatory but recommended to set root password.

Reenter the password.

After reentering root password again it will resume installation.

Default locations on Linux are /usr/local/mysql/var

if you install MySQL from a source distribution  /usr/local/mysql/data

if you install from a binary distribution and /var/lib/mysql

if you install from an RPM file. Under Windows, the default data directory is C:\mysql\data.

To check MySQL process is running or not executes

How to login to MySQL database

In command prompt $ mysql –uroot –p and press enter, it will prompt to enter password as shown below.

By default MySQL will create 3 system databases, you can see the using command

mysql> show databases;

  • Information_schema database

INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains.

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them,

and you cannot set triggers on them. Also, there is no database directory with that name.

  • mysql System Database

The mysql database is the system database. It contains tables that store information required by the MySQL server as it runs.

  • Performance_schema

Performance Schema provides a way to inspect internal execution of the server at runtime. The Performance Schema focuses primarily on performance data. Performance Schema monitors server events.

 

How to open a database

How to check tables under information_schema  database.

To see the table structure

To change the database.

TO check the tables under mysql database.

If we want to grep what mysql daemons are running on the background.

Here mysqld is a server program

mysql is a client program.

By default server program is running in /usr/sbin/mysqld

Lets try to find out where mysql is running.

These are different programs which are installed at the time mysql installation.(default).

 

Thank you ……

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

Add Comment