mysqladmin is a client for performing administrative operations. You can use it to check the MySQL/MariaDB configuration and current status, to create and drop databases, and more. You can use mysqladmin to reload/refresh MySQL Privileges, set/change MySQL/MariaDB Root password, check MySQL/MariaDB Server status, show MySQL/MariaDB version, show all MySQL/MariaDB server Variables and Values, check all the running Process of MySQL/MariaDB server, connect remote mysql server, create/drop a Database, reload the grant tables, perform a flush-privileges operation, check mysqladmin options and usage, and shutdown MySQL/MariaDB safely. All this can be done by executing a mysqladmin commands. Invoke mysqladmin like this:
The following command shows MySQL version along with the current running status
1
mysqladmin -u root -p version
Or you can type in MySQL’s root password into the command if you already set it up
1 2 3 4 5 6 7 8 9 10 11 12 13 14
mysqladmin -u root -pPASSWORD version
mysqladmin Ver 8.42 Distrib 5.5.38, for Linux on x86_64 Copyright (c) 2000, 2014, 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.5.38 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 360 days 15 hours 41 min 13 sec
Threads: 2 Questions: 82271 Slow queries: 0 Opens: 386 Flush tables: 1 Open tables: 44 Queries per second avg: 0.260
2. To check MySQL Server is running
1 2 3
mysqladmin -u root -pPASSWORD ping
mysqld is alive
3. To find out current Status of MySQL server
This mysqladmin command will shows the status of uptime with running threads and queries.
1 2 3
mysqladmin -u root -pPASSWORD status
Uptime: 616037 Threads: 2 Questions: 82468 Slow queries: 0 Opens: 386 Flush tables: 1 Open tables: 44 Queries per second avg: 0.260
4. To set MySQL Root password
To set MySQL password for root user, use the following command.
1
mysqladmin -u root password NEWPASSWORD
5. To change MySQL Root password
For example, you want to change your old password 123old to the new password new321.
1
mysqladmin -u root -p123old password 'new321'
6. To check status of all MySQL Server Variables and values
8. To check all the running Process of MySQL server
1
mysqladmin -u root -pPASSWORD processlist
9. To create a Database in MySQL server
For example: to create a new database call “wordpress” in MySQL server, use the command as shown below
1
mysqladmin -u root -pPASSWORD create wordpress
To check if the database is created successfully, use the command as shown below to log in to your MySQL
1 2 3 4 5 6 7 8 9 10 11
mysql -u root -pPASSWORD
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3122 Server version: 5.5.38 MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2014, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
After you logged in to MySQL, use the command as shown below to check all databases
1 2 3 4 5 6 7 8 9 10 11
show databases;
+--------------------+ Database +--------------------+ information_schema wordpress mysql performance_schema +--------------------+ 4 rows in set (0.00 sec)
10. To drop a Database in MySQL server
For example: to drop a Database “wordpress” in MySQL server, use the following command. You will be asked to confirm press ‘y’.
1 2 3 4 5 6 7
mysqladmin -u root -p drop wordpress
Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed.
Do you really want to drop the 'wordpress' database [y/N] y Database "wordpress" dropped
11. To reload or refresh MySQL Privileges
The reload command tells the server to reload the grant tables.
1
mysqladmin -u root -pPASSWORD reload
The refresh command flushes all tables and reopens the log files.
1
mysqladmin -u root -pPASSWORD refresh
12. To flush hosts
Flush all host information from host cache.
1
mysqladmin -u root -pPASSWORD flush-hosts
13. To flush tables
Flush all tables.
1
mysqladmin -u root -pPASSWORD flush-tables
14. To flush threads
Flush all threads cache.
1
mysqladmin -u root -pPASSWORD flush-threads
15. To flush logs
Flush all information logs.
1
mysqladmin -u root -pPASSWORD flush-logs
16. To flush privileges
Reload the grant tables (same as reload).
1
mysqladmin -u root -pPASSWORD flush-privileges
17. To clear status variables
1
mysqladmin -u root -pPASSWORD flush-status
18. To shutdown MySQL server Safely
1
mysqladmin -u root -pPASSWORD shutdown
You can also use the follow command to stop or start the MySQL server To stop
1
/etc/init.d/mysqld stop
To Start
1
/etc/init.d/mysqld start
To restart
1
/etc/init.d/mysqld restart
19. To kill Sleeping MySQL Client Process
Use #8 command to identify sleeping MySQL client process.
1 2 3 4 5 6 7 8
mysqladmin -u root -pPASSWORD processlist
+----+------+-----------+----+---------+------+-------+------------------+ Id User Host db Command Time State Info +----+------+-----------+----+---------+------+-------+------------------+ 2 root localhost Sleep 11 9 root localhost Query 0 show processlist +----+------+-----------+----+---------+------+-------+------------------+
ID 2 is identified as sleeping process. run the following command with kill and process ID as shown below.
1 2 3 4 5 6 7
mysqladmin -u root -pPASSWORD kill 2
+----+------+-----------+----+---------+------+-------+------------------+ Id User Host db Command Time State Info +----+------+-----------+----+---------+------+-------+------------------+ 9 root localhost Query 0 show processlist +----+------+-----------+----+---------+------+-------+------------------+
To kill multiple IDs, pass the process ID’s with comma separated as shown below.
Comments