mysqlshow is another way to check the database details table and column information, let’s check some of the basic commands

Display column name along with column information for table in database

Here we can see some tips with mysqladmin tool

To check the status of mysqld

# mysqladmin -u root -pmysql ping

# service mysqld status

# mysqladmin -u root -pmysql status

To find mysql version

# mysqladmin -u root -pmysql version

To view all the MySQL Server status variable and it s current value.

# mysqladmin -u root -pmysql extended-status

To display all MySQL server system variables and the values

# mysqladmin -u root -pmysql variables

# mysqladmin -u root -pmysql processlist

To create a DB

# mysqladmin -pmysql create DBtest

To Drop a DB

# mysqladmin -pmysql drop DBtest

To reload mysqld demon

# mysqladmin -pmysql reload

# mysqladmin -pmysql refrsh

To stop mysql daemon

# mysqladmin -pmysql shutdow

Note: You can also use /etc/rc.d/init.d/mysqld stop to shutdown the server. To start the server, execute /etc/rc.d/init.d/mysql start

Mysql flush

# mysqladmin -pmysql flush-hosts

# mysqladmin -pmysql flush-logs

# mysqladmin -pmysql flush-privileges

# mysqladmin -pmysql flush-status

# mysqladmin -pmysql flush-tables

# mysqladmin -pmysql flush-threads

flush-privileges: Reload the grant tables (same as reload).

flush-status: Clear status variables.

flush-threads: Flush the thread cache.

To start and stop MySQL replication on a slave server

# mysqladmin -u root -pmysql stop-slave

# mysqladmin -u root -pmysql start-slave

Repairing corrupted mysql tables using myisamchk / mysqlcheck / repair

 myisam is the default storage engine for mysql database. There is a chance of myisam tables gets corrupted easily. myisamchk helps to identify and fix corrupted tables in myisam.

when table created in mysql it will create different files under mysql some of them are *.MYD (mydata ) file to store data, *.MYI (myindex) to store the index and *.frm to store table format.

you have to shutdown mysqld before performing the repair

Checking all corrupted tables using myisamchk

# myisamchk -c /var/lib/mysql/*.MYI >> /tmp/log.txt

redirect the output to a file then it will display only corrupted table names on the screen. log.txt contains info about all tables including good ones.

Repair the corrupted table using myisamchk

# myisamchk -r /var/lib/mysql/*.MYI

It is a client performs table maintenance: It checks, repairs, optimizes, or analyzes tables etc. It checks all tables and repair them if necessary.

both mysqlcheck and myisamchk are similar, but works differently. The main difference is that mysqlcheck must be used when the mysqld server is running, whereas myisamchk should be used when it is not. The benefit of usingmysqlcheck is that you do not have to stop the server to perform table maintenance.

Checking tables using mysqlcheck

mysql> mysqlcheck [options] db_name tbl_name 
mysql> mysqlcheck [options] --databases  db_name 
mysql> mysqlcheck [options] --all-databases

some options include  --auto-repair,--check -c

Repair tables with mysqlcheck

# mysqlcheck -uroot -p -r tablename;

3.REPAIR 

# repair table tablename;

MySQL Performance tuning [my.cnf file parameters + description]

[mysqld]# Maximum allowed number of connections to the MySQL Server
max_connections = 1500

# The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys.
key_buffer = 1024M

#The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add #indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.
join_buffer_size = 100M#Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to #131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB
read_buffer_size = 1M

#Each session that needs to do a sort allocates a buffer of this size
sort_buffer_size = 2M

#This is the old name of table_open_cache. The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
table_cache = 1800

# Number of threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there.
thread_cache_size = 384

# The number of seconds the server waits for activity on a noninteractive connection before closing it.
wait_timeout = 300

# The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
connect_timeout = 10# The maximum size of internal in-memory temporary tables.
tmp_table_size = 64M

# This variable sets the maximum size to which MEMORY tables are allowed to grow
max_heap_table_size = 64M

# The maximum size of one packet or any generated/intermediate string.
max_allowed_packet = 64M

# If there are more than this number of interrupted connections from a host, that host is blocked from further connections
max_connect_errors = 1000

# When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
read_rnd_buffer_size = 524288# Don’t cache results that are larger than this number of bytes.Default value is 1mb
query_cache_limit = 4M

#The size of the persistent buffer used for statement parsing and execution. This buffer is not freed between statements. If you are running complex queries, a larger query_prealloc_size value might be #helpful in improving performance, because it can reduce the need for the server to perform memory allocation during query execution operations.
query_prealloc_size = 65536

# The allocation size of memory blocks that are allocated for objects created during statement parsing and execution.
query_alloc_block_size = 131072

# The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.
query_cache_size = 1024M;

#Set the query cache type
# 0 or OFF : Don’t cache results in or retrieve results from the query cache.
# 1 or ON   : Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
# 2 or DEMAND : Cache results only for cacheable queries that begin with SELECT SQL_CACHE
query_cache_type = 1

# This variable is set from the value of the –skip-name-resolve option. If it is ON, mysqld resolves host names when checking client connections. If OFF, mysqld uses only IP numbers and all Host # column values in the grant tables must be IP numbers or localhost.
skip-name-resolve

[mysqld_safe]#The number of files that the operating system allows mysqld to open
open_files_limit = 8192

[mysqldump]# The maximum size of one packet or any generated/intermediate string.
max_allowed_packet = 16M

MyISAM and InnoDB are the two most-common database engines available.  While installing the DB most of them don’t even take the time to select a storage-engine and just accept the database default.

The storage-engine is what which store, handle, and retrieve information for a particular table.

1. MyISAM

 It is the default engine with MySQL. Data in MyISAM tables is split between three different files on the disk.One for the table format, another for the data, and lastly a third for the indexes.

2. InnoDB

 InnoDB is transaction-safe means data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, means when one query is busy updating or inserting a row, another query can update a different row at the same time.

InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid.

MyISAM v/s InnoDB

   MyISAM in most cases will be faster than InnoDB. It is the default engine chosen by the MySQL development team.

In  InnoDB features like row-level locking, transaction-safe queries, and relational table design are all very fast.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.