Using Mysqladmin to Administer a MySQL Server : 15 practical examples October 13, 2012Posted by Tournas Dimitrios in Mysql.
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
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 ) .