When there is a condition when it is not safe for MySQL or MariaDB to shutdown in a safe mode, it will just freeze. This hapens to me each time I run out of disk space.
The best way to solve this is to add more disk space, but if this is not possibe, I am stopping all applications, then kill the service. You will lose some pending transactions, but the database integrity should be ok, if you use XtraDB or InnoDB engine.
XtraDB is default for MariaDB. InnoDB is default for recent version of Oracle MySQL and Percona server.
To shutdown MariaDB or Mysql service you can use
mysqladmin shutdown. Or just run:
kill -SIGTERM pid-of-mysqld-process. See also: https://mariadb.com/kb/en/mariadb/shutdown/
If you restart MySQL durring a write operation (INSERT, UPDATE, DELETE, ALTER), the transaction will be rolled back by InnoDB engine. You can see the progress of the rollback with
show engine innodb status. See bellow.
Check which queries are slow. The following command returns all running queries that are were started 10 seconds or more ago.
mysql -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND<>'Sleep' and TIME > 10"
If you have a slow query that is using InnoDB engine, you can see what its progress with:
mysql -e "show engine innodb status \G"|less