Detailed background information will enable them to widen their MySQL horizon. Let’s take a look at some very basic MySQL administration to get started.

check how many MySQL connections are open?

mysql> SHOW STATUS WHERE `variable_name` = ‘Threads_connected’;
mysql> show global status like ‘%thread%’;

Views maximum allowed number of simultaneous connections limit,

mysql> show variables like “max_connections”;

update maximum connection limit directly,

Mysql> SET GLOBAL max_connections = 200;

Check connection limit,

mysql> select @@global.max_connections;
# vi my.cnf
max_connections = 250

MySQL : mysqladmin Commands for Database Administrator in Linux
Command will be shows server status information

mysqladmin status

Set MySQL root Password:

# mysqladmin -u root password ‘mypassword’

Change MySQL root Password:
old password : mypassword and new password : mypass

# mysqladmin -u root -pmypassword password ‘mypass’

Remove MySQL root password :

# mysqladmin -u root -pCURRENTPASSWORD password ”

Database current process list:

# mysqladmin processlist

All MySQL server Variables and Values:

# mysqladmin variables

Get Database Open Files Limit:
Displays information about innodb open files, how many number of open file limit on server

# mysqladmin variables |grep -i open

To Know Total Threads Connected and Running:

# mysqladmin extended-status | grep -wi ‘threads_connected|threads_running’ | awk ‘{ print $2,$4}’

MySQL Storage Engine Queries


To find out default Storage engine MySQL

mysql> SHOW GLOBAL VARIABLES LIKE ‘storage_engine’;

To find out which storage engine using the databases and count number of tables.


To show specific storage engine for database.

mysql> select table_name,engine from information_schema.tables where table_schema = ‘mysql_db’ and engine = ‘MEMORY’;

To view the storage engine for all the tables in your database,

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘mysql_db’;

To check MySQL storage engine type for a specific Table

mysql> SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`= ‘mysql_db’ AND TABLE_NAME = ‘posts’;

To change storage engine for particular tables

mysql > use mysql_db;
mysql> ALTER TABLE gal_modules ENGINE = MyISAM;
Once Altered table to view the changed storage engine
mysql> SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`= ‘mysql_db’ AND TABLE_NAME = ‘posts’;

How to Create MySQL Databases and Users with Permissions
Assume that, your going to create database named linuxdb and user named linuxuser.

mysql> CREATE DATABASE linuxdb;
mysql> CREATE USER linuxuser;

create mysql user with password then use this query.

mysql> CREATE USER ‘linuxuser’@’localhost’ IDENTIFIED BY ‘password’;

Permission Settings:

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘linuxuser@’localhost’ WITH GRANT OPTION; >> All databases
mysql> GRANT ALL PRIVILEGES ON linuxdb.* TO ‘linuxuser’@’localhost’ WITH GRANT OPTION; >> For DB linuxdb

Repair All Tables in All Databases in MySQL

# mysqlcheck –repair –databases database_name
# mysqlcheck –repair –all-databases


Leave a Reply

Your email address will not be published. Required fields are marked *