jump to navigation

Using Mysqladmin to Administer a MySQL Server : 15 practical examples October 13, 2012

Posted by Tournas Dimitrios in Mysql.
trackback

MySQL is a powerful relational database system and probably the most used (especially on shared hosting) . It is also a pre-installed service on all web-development “bundled” local servers like :WAMP , XAMP , MAMP . MySQL’s mysqladmin is a command-line (terminal)  interface for administrators to perform server administration tasks .  It can be used to replace the root user default password with your own , check the server’s configuration and current status , create and drop databases , check if the MySQL database service is running , shutdown the MySQL database service and bring it up running again and more …. Let’s see 15 practical examples .

  • Changing the root password for Mysql :
    mysqladmin -u root -p   ‘newpassword’
  • Checking whether the MySQL server is running
    mysqladmin -u root -p ping
  • Exploring the status of the MySQL server
    mysqladmin -u root -p status
  • what version of MySQL is running
    mysqladmin -u root -p version
  • viewing MySQL’s status variables and their current values
    mysqladmin -u root -p extended-status
  • display all running processes / queries in the database
    mysqladmin -u root -p processlist
  • killing a “hung” client process
    mysqladmin -u root -p processlist
    mysqladmin -u root -p kill 7
    First , discover the hanging process using the command processlist . It will return a list of all running processes with their accompanied process_id’s . Then use the kill command and the desired process_id . To kill several processes at once , separate process id’s with commas .
  • Create a database
    mysqladmin -u root -p create testdb
  • delete an existing database
    mysqladmin -u root -p drop testdb
  • safely stop the server
    mysqladmin -u root -p shutdown
  • mysqladmin’s flush commands
    mysqladmin -u root -p xxxyyyzzz
    Replace xxxyyyzzz with :
    flush-hosts , flush-logs , flush-status , flush-privileges , flush-tables , flush-threads
  • combining several commands together
    mysqladmin  -u root -p status version process
  • re-load all the privileges and rights
    mysqladmin -u root -p reload
  • start or stop the replication of MySQL on the slave-server
    mysqladmin  -u root -p stop-slave
    mysqladmin  -u root -p start-slave
  • display all MySQL’s system variables and their values
    mysqladmin  -u root -p variables 
  • get a list of all mysqladmin’s options
    mysqladmin  -help 

Note : It’s assumed that the commands were executed on a local server or first a remote SSH-connections were established (for Windows machines , PUTTY is an extremely simple ssh-client) . We could of course define remote IP address on the commands like :
mysqladmin -u root -p  -h xxx.xxx.xxx.xxx create testdb
Most likely , Mysql has remote access disabled and we have to create a new user that has remote access privileges :

/* Of course the user/IP should be edited */
CREATE USER 'toor'@'192.168.1.51' IDENTIFIED BY '***' ;
GRANT ALL PRIVILEGES ON * . * TO 'toor'@'192.168.1.51'  IDENTIFIED BY '565491d704013245'

If previous commands return an “Access denied” error use the following code :

/* Of course the user/IP should be edited */
CREATE USER 'toor'@'192.168.1.51' IDENTIFIED BY '***' ;
GRANT SELECT ,
INSERT ,
UPDATE ,
DELETE ,
CREATE ,
DROP ,
RELOAD ,
PROCESS ,
FILE ,
REFERENCES ,
INDEX ,
ALTER ,
SHOW DATABASES ,
SUPER ,
CREATE TEMPORARY TABLES ,
LOCK TABLES ,
REPLICATION SLAVE ,
REPLICATION CLIENT,
CREATE VIEW ,
SHOW VIEW ,
CREATE ROUTINE,
ALTER ROUTINE,
CREATE USER,
EXECUTE ON * . * TO 'toor'@'192.168.1.51' IDENTIFIED BY '565491d704013245' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

Directly connecting to the remote server is considered a “bad security practice” , as everything is transmitted in plain text (notably username:password) .

A good “security habit ” is never to pass your password directly on the line of the command . As all commands on current terminal session are stored (temporarily) in memory . During a Terminal session , you may need to use the same command or a slightly edited version of the same command . For instance , changing YES to NO and that type of minor edit . If you’re in the same session , you can simply use the Up key to navigate to your previous commands (on Windows operating systems)  . On UNIX-based operating systems (Linux , MacOs) Terminal command history is stored on a hidden file (dot-file) . Previous commands (even from a previously closed terminal session) can be recalled with the arrow keys . So , NEVER pass your password directly on the command line . Just pass an empty “-p” character into the command line and press enter ,  you will be asked to enter your password (which isn’t stored in memory or hidden file )  .

Comments»

1. samehramzylabib - October 14, 2012

Reblogged this on Sam's PHP and commented:
When I start BusCoX hosting on a VPS I’ll need to know this.

tournasdimitrios1 - October 14, 2012

@samehramzylabib
Welcome and thanks for commenting .
I had forgotten to mention an alternative (GUI) method . Workbench can be tunneled via SSH .
The following link might be a refresher of how to tunnel Mysql’s Workbench through SSH :

2. Courtney - October 17, 2012

Hello I am so thrilled I found your blog page, I really found you by accident, while
I was looking on Yahoo for something else, Nonetheless I am here now
and would just like to say thanks a lot for
a incredible post and a all round thrilling blog (I also
love the theme/design), I don’t have time to read through it all at the minute
but I have saved it and also included your RSS feeds, so when I
have time I will be back to read much more, Please do keep up
the awesome jo.

3. Sok - October 19, 2012

very nice post. good stuff.

4. Jack - October 26, 2012

These are truly impressive ideas in regarding blogging.

You have touched some nice points here. Any way keep up wrinting.

5. psn - November 12, 2012

You really make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand.

It seems too complicated and extremely broad for me. I’m looking forward for your next post, I will try to get the hang of it!

tournasdimitrios1 - November 12, 2012

@psn
Post your questions , I’ll try to help you .

6. coorgi - November 26, 2012

is there anyway we can stop start the single database in my sql (NOT THE MYSQL SERVER).

tournasdimitrios1 - November 26, 2012

@coorgi
Mysql server is the engine, databases are just files on the file-system . To prevent access to a specific database , just set the permissions to the absolute minimum .


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s