Create an index to a mysql table. Say there is a product_id column:

CREATE UNIQUE INDEX product_id
on table_name (product_id);

Add to existing table

mysql> alter table tablename add index product_id on product_id;

Checking the log shows:

# [ERROR] /usr/libexec/mysqld: Sort aborted

Check the processlist on a plesk server:

# mysqladmin -u admin -p`cat /etc/psa/.psa.shadow` processlist

+---------+----------------+----------------------+----------------+---------+------+-------+------------------+
| Id      | User           | Host                 | db             | Command | Time | State | Info             |
+---------+----------------+----------------------+----------------+---------+------+-------+------------------+
| 1926335 | user           | 108.xx.xx.xxx:17599  | databasename   | Update  | 0    | Locked| select * from..' |
|         |                |                      |                |         | 0    |       |                  |
+---------+----------------+----------------------+----------------+---------+------+-------+------------------+

Kill the process:

# mysqladmin -u admin -p`cat /etc/psa/.psa.shadow` kill [thread_id]

Create an index on the table

Run

# mysql_upgrade

Plesk

# mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`

The purge binary command fails – no logs are deleted and the process hangs:

mysql> PURGE BINARY LOGS TO 'BINLOG.000100';

Checking the error log:

ERROR 3 (HY000): Error writing file './mysql-bin.~rec~' (Errcode: 28)

The PURGE BINARY LOGS command requires free space to be able to clear the logs, which is why this command was failing. To be able to run the command please to stop MySQL, zero the first binary log and also remove it from the mysql-bin.index file. Restart MySQL, run the command:

PURGE BINARY LOGS TO 'BINLOG.000xxx';

If you get this error

ERROR 1373 (HY000): Target log not found in binlog index

Check the date. Since the binary log was from September 4th, ran the following:

PURGE BINARY LOGS BEFORE '2014-09-04';

Now the log files up to that date will be purged. If you are not using replication, you can most likely disable the binary logging unless you have this enabled for easier data recovery.

This article shows how to create a MySQL user, remove a MySQL user and to show a list of MySQL users.

Grant all privileges on a database to a single user:

</p>
<p>mysql&gt; CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';</p>
<p>

<br>
mysql&gt; GRANT ALL ON my_db.* TO 'new_user'@'localhost';<br>

Or…

<br>
mysql&gt; GRANT ALL PRIVILEGES ON database_name.* to 'new_user'@'localhost' IDENTIFIED BY 'password';<br>

Remove a USer from Mysql

Review a List of MySQL Users

</p>
<p>mysql&gt; SELECT User,Host FROM mysql.user;</p>
<p>

Remove a MySQL User
To remove a user from MySQL, we again use the DROP command.

It only takes one simple command to delete a user in MySQL, but BEWARE; dropping a user can not be undone! The command is as follows:

</p>
<p>mysql&gt; DROP USER 'testuser'@'localhost';</p>
<p>

If a user of the name testuser does not exist, then you’ll receive this error:

</p>
<p>mysql&gt; ERROR 1396 (HY000): Operation DROP USER failed for 'testuser'@'localhost'</p>
<p>

To find the my.conf file location use the following command:

]# mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

There are 2 different limits for MYSQL. Max Connections and Max user connections. By default, the limit is 151 for max connections and 0 or unlimited for user connections.

MySQL server has a default limit of 151 simultaneous connections. Most connections to the SQL server run very quickly so even a large amount of queries should not cause a server to hit this limit. This problem usually occurs when a query takes too long to execute or locks a table preventing other queries from executing (and building up the number of simultaneous connections).

Once the server hits the limit of connections, it will refuse to accept new queries and return error 1203: Too many connections.

If the server is currently at the limit of connections, it will still accept one more connection from a MySQL account with super user privileges (like user root). You can use this connection to view the currently running queries or try to kill the queries that are running for too long.

Check for the settings. Log into the mysql server. Run the following:
Max Connections

MariaDB [(none)]> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

Max Use
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘max_use%’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_user_connections | 0 |
+———————-+——-+
1 row in set (0.00 sec)
How to update max_connections Value

Before increasing this value, make sure your server has enough resources to handle more queries. Now execute below query in mysql terminal to set this value temporarily. Remember that this value will reset on next mysql reboot.

MariaDB [(none)]> SET GLOBAL max_connections = 250;
Query OK, 0 rows affected (0.00 sec)

To set this value permanently, edit mysql configuration file on your server and set following variable. The configuration file location may change as per your operating system. By default you can find this at /etc/my.cnf on CentOS and RHEL based system and /etc/mysql/my.cnf on Debian based system. Under [mysqld]:

max_connections = 250

Now restart mysql service and check value again with above given command. This time you will see that value is set to 250.

MariaDB [(none)]> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 250   |
+-----------------+-------+
1 row in set (0.00 sec)

Another simple way to temporarily resolve this issue is to restart the MySQL server. On CentOS/RHEL:

 # systemctl restart mariadb.service

You can also enable the slow query log which will record queries that take longer than a specified amount of time (two seconds by default) for later review and optimization.

nano /etc/my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow-query.log

Restart

 # systemctl restart mariadb.service

You can use the “show processlist” command to view currently running queries. This is useful if you are looking to see if a query is locking a table, or if you have too many open queries, or for any other number of reasons.

On a Linux Plesk server the MySQL password is stored in /etc/psa/.psa.shadow. You would view the current query list with this command:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show processlist' 

The output will be truncated so that everything fits onto one line. If you need to view the entire query you need to run “show full processlist.”

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e 'show full processlist'

If you are not running Plesk, and have cPanel/WHM, by default there is no password set for user root:

# mysql -uroot -e 'show processlist'