Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Sunday, January 20, 2019

How to Check MySQL Database & Tables Size on linux ?

MySQL is a Relational Database Management System, widely used as a database system for Linux systems. This article will help you to calculate the size of tables and database in MySQL or MariaDB servers though SQL queries. MySQL stored all the information related to tables in a database in the information_schema database. We will use the information_schema table to find tables and databases size.

How to find each data base size ?

Check ALL Databases Size in MySQL

Using mysql query

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema;

Sample output:

mysql> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema
   -> ;
| Database           | Size (MB) |
| information_schema | 0.00878906 |
| mylabdb            | 0.00111008 |
| mysql              | 0.68704987 |
| performance_schema | 0.00000000 |

mysql> SELECT
   ->   table_schema 'Database Name',
   ->   SUM(data_length + index_length) 'Size in Bytes',
   ->   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
   -> FROM information_schema.tables
   -> GROUP BY table_schema;

Check Single Table Size in MySQL Database

To find out the size of a single MySQL database called mylabdb (which displays the size of all tables in it) use the
following mysql query.

mysql> SELECT table_name AS "Table Name",ROUND(((data_length + index_length) / 1024 / 1024),
2) AS "Size in (MB)" FROM  information_schema.TABLES WHERE table_schema = "mylabdb"
BY (data_length + index_length) DESC;

Finally, to find out the actual size of all MySQL database files on the disk (filesystem), run the
du command below.

sudo du -h /var/lib/mysql

Friday, June 30, 2017

how to find physical location of mysql database under linux?

If you are using MySQL, and want to know the actual physical path of MySQL, where MySQL stores the db, and its information files and the amount of data for MySQL data and MySQL index files i.e. table_name.MYD and table_name.MYI respectively, below is the easy solution.

Default MySQL Physical Directory Path is
/var/lib/mysql/ or /var/db/mysql

You can open it in Terminal  (using sudo or root access). Once you get into the folder, you can easily find the folders which are actually your database names, and in the database name folders, you can find the table names, having MYD and MYI extensions, which are actually your data and index files.

You can use the following command to locate MySQL datadir:

Method 1:>>grep datadir /etc/mysql/my.cnf

Sample output
datadir                        = /var/lib/mysql

Method 2:>>ps -eo cmd,args | grep mysql

Sample output:

/bin/sh /usr/bin/mysqld_saf /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/ --user=mysql
/usr/libexec/mysqld --based /usr/libexec/mysqld --basedir=/usr
--datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/ --skip-external-locking --socket=/var/lib/mysql/mysql.sock

Method 3:

mysql -u root -p

mysql> select @@datadir;
| @@datadir       |
| /var/lib/mysql/ |
1 row in set (0.00 sec)

This output confirms that MySQL is configured to use the default data directory, /var/lib/mysql/.

Monday, June 19, 2017

How create Mysql User, Database and set privileges to user on linux?

How create Mysql User, Database and set privileges to user

[]$ mysql –u root –p

mysql> create user 'ravi'@'localhost' identified by '123456';

mysql> create database if not exists `ravidb`;

mysql> grant all on ravidb.* to identified by "123456" with grant option;

[]$ mysql ravidb -u ravi -p

mysql> show databases;

mysql> use ravidb;

Thursday, November 3, 2016

How to check which port number mysql server is running on linux ?

Method 1:

To find a listener on a port, do this:$netstat -tln | grep 3306
tcp        0      0*               LISTEN     

Or$netstat -tlpn | grep mysql
tcp        0      0*               LISTEN      870/mysqld

Method 2:$grep port /etc/mysql/my.cnf
# One can use all long options that the program supports.
# It has been reported that passwords should be enclosed with ticks/quotes
port                 = 3306
port                 = 3306

Method 3:

Using Mysql Query$  mysql –u username –p password

| Variable_name | Value |
| port          | 3306  |


how to check mysql server uptime in linux ?

Method 1:
Using command line$mysqladmin  version | grep Uptime

Uptime:                                   30 sec

Method 2:$mysqladmin  status
Uptime: 121  Threads: 1  Questions: 116  Slow queries: 0  Opens: 46  Flush tables: 1  Open tables: 41  Queries per second avg: 0.958

Method 3:

Show MySQL server uptime using a query$ mysql –u username –p password
| Variable_name | Value |
| Uptime        | 223   |

(The uptime is given in seconds)

how to check if mysql server is running or not in linux ?

Method 1:$ service mysql status
Or$ /etc/init.d/mysql status
mysql start/running, process 13749

Method 2:$ps aux | grep mysql
mysql    13749  0.2  1.3 492660 52336 ?        Ssl  13:05   0:38 /usr/sbin/mysqld

Method 3:$netstat -vulntp |grep -i mysql
tcp        0      0*               LISTEN      13749/mysqld    
Method 4:$lsof -i :3306
mysqld  13749 mysql   10u  IPv4 5037327      0t0  TCP localhost:mysql (LISTEN)

Method 5:$mysqladmin ping

mysqld is alive

Saturday, October 1, 2016

How to Check the Connection to MySQL DB Server in linux?

I would share the way to check the connection to MySQL database server. We can use the telnet or nc command to connect to port 3306. This would be very useful when we do the troubleshooting or to test the connectivity between the application server and the database server. Sample outputs will show succeeded or connected as below :

Host A =
Host B =

1. Test connectivity using nc command :

[root@linuxforfreshers~]# nc -z -w1 3306
Connection to 3306 port [tcp/mysql] succeeded!
2. Test connectivity using telnet command :
syntax: telnet hostname portnumber

[root@linuxforfreshers~]#  telnet 3306
Telnet escape character is 'X'.
Connected to
Escape character is 'X'.

telnet> Connection closed.

Tuesday, March 8, 2016

What is DDL, DML ,DCL and TCL in linux ?

 Data Definition Language (DDL) - Data definition language (DDL) commands enable you to perform the following tasks:

Ø  Create, alter, and drop schema objects

Ø  Grant and revoke privileges and roles

Ø  Add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the object being acted upon. For example, an ALTER TABLE command fails if another user has an open transaction on the specified table.


Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.


Transaction Control Language (TCL) -  Transaction control commands manage changes made by DML commands. These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.


Data Control Language (DCL) -  It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Thursday, January 21, 2016

how to change mysql root password in rhel6 ?

I recently had to reset the MySQL root password due to the fact that initializing it the way I assumed it should did not work. The following procedure will work in CentOS/RHEL/Scientific Linux and Fedora.

After installing MySQL using

# yum install mysql-server
I can run the command

# mysqladmin -u root password 'new-password'
Trying to log in with the following failed

# mysql -u root -p
with the following error

Access denied for user 'root'@'localhost'
Decided to not spend more time as it’s a fresh MySQL installation. And did the following to reset the root password for MySQL.

Resetting the root password

1) Stopped the MySQL service.

# service mysqld stop
2) Started MySQL in safe mode.

# mysqld_safe --skip-grant-tables &
3) Logged in using root.

# mysql -u root
4) Reset the password.

> use mysql;
> update user set password=PASSWORD("mynewpassword") where User='root';
> flush privileges;
> quit
5) Stop MySQL in safe mode.

# service mysqld stop
6) Start MySQL.

# service mysqld start
7) Log in using the new password.

# mysql -u root -p

Saturday, April 25, 2015

An Introduction To MySQL Database

This tutorial will explains how to install MySQL, create a sample database, create a table, insert records into the table, and select records from the table.


You can install mysql using the following command:

On Ubuntu:
sudo apt-get install mysql-server

On Centos:
 sudo yum install mysql-server
Follows the steps below to stop and start MySQL

service mysql start
Starting MySQL.                                            [  OK  ]

service mysql status
MySQL running (12588) 

service mysql stop
Shutting down MySQL.                                       [  OK  ]

Verifying Installation
You can check the MySQL installed version by performing mysql -V as shown below:
[local-host]# mysql -V
mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Access the MySQL shell
Once you have MySQL installed on your droplet, you can access the MySQL shell by typing the following command into terminal:

mysql -u root -p

After entering the root MySQL password into the prompt, you will be able to start building your MySQL database.

mysql -u root -p

Enter password: password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


Creating Databases
After connecting as MySQL root user, you can use this command to create database.
In this example, we will create unixmen database.

mysql> create database;

You can check what databases are available by typing this command:
Your screen should look something like this:


| Database           |
| information_schema |
| mysql              |
| performance_schema |
| test               |
| unixmen            | 
|                    |
5 rows in set (0.01 sec)

Creating Tables
Before you create a mysql table, you need to choose the database that you want to use:
USE unixmen;
Database changed
The following example creates a article table.
create table article (
name varchar(20),
number varchar(10),
page int(10)
writing_date DATE);
The command show tables to view all the tables available in the database.

| Tables_in_unixmen |
| article         |
1 row in set (0.01 sec)
To view the table description, do the following command
 mysql>DESCRIBE article;
| Field       | Type        | Null | Key | Default | Extra          |
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | YES  |     | NULL    |                |
| number      |  int(11)    | YES  |     | NULL    |                |
| page        | char(1)     | YES  |     | NULL    |                |
| writing_date| date        | YES  |     | NULL    |                |
5 rows in set (0.01 sec)

Add Information to Tables

Use the following sample insert commands to insert some records to the article table.
insert into article values(1,'article1','4','a','2012-04-13');
insert into article values(2,'article2','5','b','2012-04-14');
insert into article values(3,'article3','6','C','2012-04-15');
insert into article values(4,'article4','7','d','2012-04-16');
You can take a look at your table using this command

mysql> SELECT * FROM article
+----+------- +----------------+-----------+-------------+
| id | name   | number         | page      |wrinting_date|
+----+------- +----------------+-----------+-------------+
|  1 |article1| 1              | a         | 2012-04-13  |
|  2 |article2| 2              | b         | 2012-04-14  |
|  3 |article3| 3              | c         | 2012-04-15  |
|  4 |article4| 4              | d         | 2012-04-16  |
4 rows in set (0.00 sec)

Update Information in the Table
You can update a stored information in the table with this command:
UPDATE `article`
`number` = '6'
WHERE `article`.`name` ='article4

Delete a Row, a Column and a Table

You can  delete rows from the table with the following command:
DELETE from  where [column name]=[field text];
mysql> DELETE from article  where name='article2';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM article
+----+------- +----------------+-----------+-------------+
| id | name   | number         | page      |wrinting_date|
+----+------- +----------------+-----------+-------------+
| 1 |article1 | 1              | a          | 2012-04-13 |
| 3 |article3 | 3              | c          | 2012-04-15 |
| 4 |article4 | 4              | d          | 2012-04-16 |
3 rows in set (0.00 sec)

You can also delete a column using this command
ALTER TABLE  [column name];

And type this command if you want to delete all table