How to check Database size in Linux

We can check all the sizes of databases through execute a simple query in mysql server

Step 1: Login with mysql server

Login to mysql server by putting the mysql server credentials

# mysql -u username -p
Enter Password: 

Step 2: Execute the Query

Execute the mysql query in mysql server.

mysql> SELECT table_schema "Data Base Name",
    -> sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    -> sum( data_free )/ 1024 / 1024 "Free Space in MB"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema ;

 +---------------------------------+----------------------+------------------+
 | Data Base Name | Data Base Size in MB | Free Space in MB |
 +---------------------------------+----------------------+------------------+ 
 | about_demo_conf | 0.11203384 | 0.02596664 |
 | appstore | 0.02434349 | 0.00234127 |
 | cacti | 0.95234585 | 0.00552368 |
 | growingfeet | 0.10937500 | 11765.00000000 |
 | information_schema | 0.00781250 | 0.00000000 |
 | lotus_to_mysql | 0.47713852 | 0.00000000 |
 | moderapp_log | 0.00230408 | 0.00000000 |
 | moedeogeventmessen | 0.13060379 | 0.00000000 |
 | mysql | 0.66061878 | 0.00000000 |
 | vsftpd | 0.00310898 | 0.00000000 |
 | what2do | 0.03068542 | 0.00035095 | 
+---------------------------------+----------------------+------------------+

How to Configure MySQL Master-Master Replication

Step 1: Install MySQL Server

Install MySQL Server on both machines.

# apt-get update
# apt-get install mysql-server mysql-client

Step 2: Configure MySQL Servers

Edit the /etc/mysql/my.cnf file on both machines. Add or modify the following values:

Server 1:

# vi /etc/mysql/my.cnf
        server_id           = 1
        log_bin             = /var/log/mysql/mysql-bin.log
        log_bin_index       = /var/log/mysql/mysql-bin.log.index
        relay_log           = /var/log/mysql/mysql-relay-bin
        relay_log_index     = /var/log/mysql/mysql-relay-bin.index
        expire_logs_days    = 10
        max_binlog_size     = 100M
        log_slave_updates   = 1
        auto-increment-increment = 2
        auto-increment-offset = 1

Server 2:

# vi /etc/mysql/my.cnf
        server_id           = 2
        log_bin             = /var/log/mysql/mysql-bin.log
        log_bin_index       = /var/log/mysql/mysql-bin.log.index
        relay_log           = /var/log/mysql/mysql-relay-bin
        relay_log_index     = /var/log/mysql/mysql-relay-bin.index
        expire_logs_days    = 10
        max_binlog_size     = 100M
        log_slave_updates   = 1
        auto-increment-increment = 2
        auto-increment-offset = 2

Step 3: Modify my.cnf file with bind-address

Edit or add bind-address in my.cnf

# vi /etc/mysql/my.cnf    	
        bind-address    = x.x.x.x

Step 4: Restart MySQL Server

After inserting the above entries restart mysql server.

# service mysqld restart

Step 5: Create Replication Users

Connect with mysql server as root

# mysql -u root -p

Configure the replication users on each Linode. Replace x.x.x.x with the private IP address of the opposing Linode, and password with a strong password:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'x.x.x.x' IDENTIFIED BY 'password';

Run the following command to test the configuration. Use the private IP address of the opposing Linode:

 mysql -ureplication -p -h x.x.x.x -P 3306

Step 6: Configure Database Replication

Server 1:

While logged into MySQL on Server 1, query the master status:

mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      120 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

Server 2:

On Server 2 at the MySQL prompt, set up the slave functionality for that database. Replace x.x.x.x with the private IP from the first server. Also replace the value for master_log_file with the file value from the previous step, and the value for master_log_pos with the position value.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=106;
mysql> SLAVE START;

Step 7: Configure Database Replication Vice Versa

Server 2:

On Server 2, query the master status. Again note the file and position values.

mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      160 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

Server 1:

Set the slave database status on Server 1, replacing the same values swapped in step 2 with those from the Server 2.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=160;
mysql> SLAVE START;

Step 8: Create Database

Test by creating a database and inserting a row:

Server 1:

mysql> create database test;
mysql> create table test.books (`id` varchar(10));

Server 2:

mysql> show tables in test;

MySQL Commands

SQL Server Commands

How to Delete Database in mysql through Linux Shell

There are 2 methods in order to delete the database in mysql through Linux Shell.

Method 1:

Delete through mysqladmin Command

You can delete the database without logging in mysql database;

# mysqladmin -u[username] -p[password] drop [database]

Method 2:

Step 1: Login in mysql Database

First we’ll login to the MySQL server from the command line with the following command:

# mysql -u root -p
mysql>

Step 2: Delete Database

mysql> DROP DATABASE test_database;

How to Enable mysql slow query logs in Debian/Ubuntu

Step 1: Modify /etc/mysql/my.cnf File

To enable mysql slow query logs add the lines in my.cnf file.

#vi /etc/mysql/my.cnf

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Step 2: Restart mysql service

After inserting the above entries restart mysql server.

#service mysqld restart

How to Uninstall MySQL Server in Debian/Ubuntu

Step 1: Remove mysql Server

We can use apt-get command in order to remove both MySQL server and client in Debian / Ubuntu:

# apt-get --purge remove mysql-client mysql-server mysql-common
# apt-get autoremove
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
linux-headers-3.2.0-31-virtual linux-headers-3.2.0-31
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
libdbd-mysql-perl* libmysqlclient18* mysql-client* mysql-client-5.5* mysql-common* mysql-server*
mysql-server-5.5*
0 upgraded, 0 newly installed, 7 to remove and 0 not upgraded.
After this operation, 67.5 MB disk space will be freed.
Do you want to continue [Y/n]? y
(Reading database ... 105097 files and directories currently installed.)
Removing mysql-server ...
Removing mysql-server-5.5 ...
mysql stop/waiting
Purging configuration files for mysql-server-5.5 ...
Removing mysql-client ...
Removing mysql-client-5.5 ...
Removing libdbd-mysql-perl ...
Removing libmysqlclient18 ...
Purging configuration files for libmysqlclient18 ...
Removing mysql-common ...
Purging configuration files for mysql-common ...
dpkg: warning: while removing mysql-common, directory '/etc/mysql' not empty so not removed.
Processing triggers for ureadahead ...
Processing triggers for man-db ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place

Step 2: Delete mysql Directory

Also delete the mysql directory

rm -rf /etc/mysql/

How to Export & Import MySQL database in Linux

Export MySQL database

To export mysql database, use the mysqldump command.

# mysqldump -u root -p database-name > backup.sql
e.g
# mysqldump -u test -p abc > abc_backup.sql

Import MySQL database

To import mysql database, use the mysql command.

# mysql -u root -p database-name < backup.sql
e.g
# mysql -u test -p abc < abc_backup.sql