Конспект злого адміна: mysql

Показ дописів із міткою mysql. Показати всі дописи

Clearing zabbix history via SQL

DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));
DELETE FROM history WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));
DELETE FROM trends_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));
DELETE FROM trends WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));
DELETE FROM events WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));
DELETE FROM alerts WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY));

Вирішення помилки в mysql "Row Size Too Large" з InnoDB

[mysqld]

innodb_log_file_size = 512M
innodb_strict_mode = 0

mysql 8 зміна паролю з плагіном caching_sha2_password

SET GLOBAL validate_password.LENGTH = 4;

SET GLOBAL validate_password.policy = 0;

SET GLOBAL validate_password.mixed_case_count = 0;

SET GLOBAL validate_password.number_count = 0;

SET GLOBAL validate_password.special_char_count = 0;

SET GLOBAL validate_password.check_user_name = 0;

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

FLUSH PRIVILEGES;

mysql заміна кодування в таблицях і полях на прикладі апгрейда zabbix

Таблиці:

SELECT CONCAT('ALTER TABLE ',
              table_schema,
              '.',
              table_name,
              ' COLLATE=utf8_bin;') AS cmd 
FROM   (SELECT table_schema, 
               table_name, 
               table_collation 
        FROM   information_schema.tables t 
        WHERE  table_schema = 'zabbix' 
               AND table_collation != 'utf8_bin') AS tables 
ORDER  BY cmd;

Поля:

SELECT CONCAT('ALTER TABLE ',
              table_schema,
              '.',
              table_name,
              ' MODIFY COLUMN ',
              column_name,
              ' ',
              column_type,
              ' CHARACTER SET utf8 COLLATE utf8_bin',
              defaultval,
              nullable,
              ';') AS cmd
FROM   (SELECT table_schema, 
               table_name, 
               column_name, 
               collation_name, 
               character_set_name, 
               column_type, 
               column_default, 
               CASE 
                 WHEN column_default IS NULL THEN '' 
                 ELSE CONCAT(' DEFAULT ''', column_default, ''' ') 
               END AS defaultval, 
               CASE 
                 WHEN is_nullable = 'NO' THEN ' NOT NULL ' 
                 ELSE '' 
               END AS nullable 
        FROM   information_schema.columns 
        WHERE  table_schema = 'zabbix' 
               AND ( collation_name != 'utf8_bin' 
                      OR character_set_name != 'utf8' )) AS cols 
ORDER  BY cmd;

mysql: видалити усі записи з таблиці, окрім останніх N

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 10 -- keep this many records
  ) foo
);

глюк phpmyadmin: - count(): Parameter must be an array or an object that implements Countable

файл /usr/share/phpmyadmin/libraries/sql.lib.php 613 лінія

|| (count($analyzed_sql_results['select_expr'] == 1)

заміна на

|| ((count($analyzed_sql_results['select_expr']) == 1)

mysql-server виправлення кодування сервера в utf8

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

reset mysql root password - скидання root паролю в mysql

service mysql stop
mysqld_safe --skip-grant-tables &
mysql -u root

mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

# service mysql stop
# service mysql start
$ mysql -u root -p

Upgrading MySQL 5.5 to MySQL 5.6 on Ubuntu 14.04 LTS

sudo apt-get remove mysql-server
sudo apt-get autoremove
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6

Ставимо кастомний mysql на Ubuntu

sudo apt-get update && sudo apt-get upgrade
sudo apt-get install build-essential checkinstall libncurses5-dev curl
curl --o mysql-4.1.24.tar.gz http://mirror.provenscaling.com/mysql/community/source/4.1/mysql-4.1.24.tar.gz
sudo tar -xvzf mysql-4.1.24.tar.gz --directory=/usr/local/src
cd /usr/local/src/mysql-4.1.24/
sudo groupadd mysql
sudo useradd -g mysql mysql
sudo apt-get install
sudo ./configure --prefix=/usr/local/mysql
sudo make
sudo mkdir /usr/local/mysql
sudo mkdir /usr/local/mysql/include
sudo mkdir /usr/local/mysql/include/mysql
sudo mkdir /usr/local/mysql/lib
sudo mkdir /usr/local/mysql/lib/mysql
sudo mkdir /usr/local/mysql/share
sudo mkdir /usr/local/mysql/man
sudo mkdir /usr/local/mysql/mysql-test
sudo checkinstall
    y
    mysql-server 4.1.24
dpkg -l | grep -i mysql
sudo cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql/
pwd
bin/mysql_install_db --user=mysql
sudo chown -R root .
sudo chown -R mysql var
sudo chgrp -R mysql .
bin/mysql --version
bin/mysql_install_db --user=mysql
sudo bin/mysqld_safe user=mysql &

Конвертація mysql таблиць і полів

mysql --database=dbname -B -N -e "SHOW TABLES" \ 
| awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' \
| mysql --database=dbname

Виключаємо профіль mysqld в apparmor

sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

ремонтуємо mySQL

mysqlcheck -Aaco --auto-repair -u root -p