Архивы MySQL - Cтраница -

For all you Ubuntu/MySQL developers out there, have you ever seen the following?

neo@thematrix:~$ sudo /etc/init.d/mysql restart * Stopping MySQL database server mysqld [fail] * Starting MySQL database server mysqld [ OK ] /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

So, what is this “debian-sys-maint” user? Well, this MySQL user is created for the Ubuntu to be able to start/stop the database and to carry out other maintenance operations.

Sounds well enough, but then why do I keep running into the “access denied” problem for this user? Well, the issue is that with each update to MySQL, the user’s password in the database is overwritten. Ubuntu seems to go to the file/etc/mysql/debian.cnf in order to find this user’s password, but obviously the password is out of sync after the update has been applied.

As a result of this behaviour, I’ll run into the “access denied” problem every so often. Thankfully, the solution to this issue is fairly simple.

First, list the contents of the /etc/mysql/debian.cnf file:

neo@thematrix:~$ sudo cat /etc/mysql/debian.cnf

The contents of the file should look something like the following:

# Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = n4aSHUP04s1J32X5 socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] user = debian-sys-maint password = n4aSHUP04s1J32X5 socket = /var/run/mysqld/mysqld.sock basedir = /usr

See that password? That’s what we’re looking for!

Next, we want to issue a command to MySQL that tells it to grant the debian-sys-maint user all necessary privileges using the new password.

neo@thematrix:~$ mysql -u root -p <password>

Issue the GRANT command now to grant those permissions:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'n4aSHUP04s1J32X5';

Voila! If you restart MySQL, you’ll find that you should no longer be getting the “access denied” error message.

neo@thematrix:~$ sudo /etc/init.d/mysql restart * Stopping MySQL database server mysqld [ OK ] * Starting MySQL database server mysqld [ OK ] * Checking for corrupt, not cleanly closed and upgrade needing tables.

Bear in mind, because we just switched the password, and the change hasn’t been affected yet, you may need to kill the MySQL server processes in order to get MySQL to shut down at all.

Наверное, каждая таблица, содержит индексы. Часто, даже количество индексов может превышать количество столбцов в самой таблице.
При составлении индексов, можно создать те, которые будут практически дублировать друг-друга (встречается в составных индексах). А зачем нам тратить лишние ресурсы на обработку данных, которые нам не понадобятся?
Заинтересовал вопрос – а как можно узнать какие индексы незадействованные или мало задействованы в работе.

Выполнив такой запрос, можно узнать про “напрасные” индексы:

SELECT
  t.TABLE_SCHEMAAS `db`, t.TABLE_NAMEAS `table`, s.INDEX_NAMEAS `indexname`
 , s.COLUMN_NAMEAS `fieldname`, s.SEQ_IN_INDEX `seqin index`, s2.max_columnsAS `# cols`
 , s.CARDINALITYAS `card`, t.TABLE_ROWSAS `estrows`
 , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2)AS `sel %`
FROM INFORMATION_SCHEMA.STATISTICS s
 INNER JOIN INFORMATION_SCHEMA.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMAAND s.TABLE_NAME = t.TABLE_NAME
 INNER JOIN (
  SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,MAX(SEQ_IN_INDEX)ASmax_columns
  FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA !='mysql'
  GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
 )AS s2
 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMAAND
    s.TABLE_NAME = s2.TABLE_NAMEAND
    s.INDEX_NAME = s2.INDEX_NAME
WHERE t.TABLE_SCHEMA !='mysql'       /* Filterout the mysql system DB */
AND t.TABLE_ROWS > 10                 /*Only tableswith some rows */
AND s.CARDINALITYIS NOT NULL         /* Needat least one non-NULL valuein the field */
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /*unique indexes are perfect anyway */
ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /*DESC for best non-unique indexes */
LIMIT 10;

MySQL

Кэширование запросов в MYSQL

MySQL полезные команды

Percona Toolkit

MySQL — ошибка Checking for corrupt, not cleanly closed and upgrade needing tables

MySQL: “Access denied for user ‘debian-sys-maint’@’localhost'”

Как узнать самые ненужные индексы в MySQL