Saturday, 19 January 2013

Practical Usages of Mysqladmin Commands

Here are some command that should know to manage the mysql environment:


To check whether MySQL Server is up and running:

root@server [~]# mysqladmin ping
mysqld is alive
root@server [~]#

To find out what version of MySQL is running?

root@server [~]# mysqladmin version

mysqladmin  Ver 8.41 Distrib 5.0.96, for unknown-linux-gnu on x86_64
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.0.96-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 6 hours 1 min 59 sec

Threads: 12  Questions: 17822637  Slow queries: 2497  Opens: 126271  Flush tables: 1  Open tables: 1024  Queries per second avg: 820.601
root@server [~]#

To know the current status of MySQL server?

root@server [~]# mysqladmin status
Uptime: 21801  Threads: 15  Questions: 17882068  Slow queries: 2497  Opens: 126424  Flush tables: 1  Open tables: 1024  Queries per second avg: 820.241
root@server [~]#

To display all MySQL server system variables and the values?

root@server [~]# mysqladmin variables

To know about mysql processlist:

root@server [~]# mysqladmin pr

or

root@server [~]# mysqladmin processlist

If you would like to monitor or debug any performance issue and identify the query that is causing problems then please use the below command,

root@server [~]# mysqladmin processlist -i1

-i, --sleep=#       Execute commands again and again with a sleep between.

The above command will execute the command every second.

Before performing the command without "mysqadmin -u root -p" option, please make sure the mysql root password already save in server.

Setting password for mysql user in .my.cnf

root@server [~]# /usr/bin/mysqladmin -u root password 'new-password'

root@server [~]# cd /root
root@server [~]# touch .my.cnf
root@server [~]# chmod 640 .my.cnf

And put in it:


[mysqladmin]

user="root"
password="mysql-root-password"


No comments:

Post a Comment