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’;
or
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;
or
+++++++++++++++++++++++++++++++
# 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
========================

mysql> SHOW ENGINES;
 

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.
==============================================================

mysql> SELECT engine,GROUP_CONCAT(DISTINCT TABLE_SCHEMA)

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 *