MySQL

Apie MySQL klasterį aprašyta čia.

Slaptažodžio keitimas

Pirmiausia reiktų sukurti txt failą su SQL užklausa slaptažodžio pakeitimui

UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket'; flush privileges;

MySQL 5.7.37 ar vėlesnė

UPDATE user SET plugin='mysql_native_password' WHERE User='root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';
FLUSH PRIVILEGES;

MySQL 5.7.6 ar vėlesnė

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NaujasSlapt';

MySQL 5.7.5 ar ankstesnė

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NaujasSlapt');

MySQL 5.5.60 ir ankstesnė

UPDATE mysql.user SET password=PASSWORD('password') WHERE user ='root';

Tuomet uždarome esamą MySQL daemono procesą

/etc/init.d/mysql stop

arba

kill `cat /var/run/mysqld/mysqld.pid`

Inicijuojame mysql paleidimą su mūsų sukurtu failu:

mysqld_safe --init-file=/home/looseris/mysql-slapt-reset &

Pradžiamokslis

Duombazių parodymas:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.05 sec)

Duombazės sukūrimas:

mysql> create database testas;
Query OK, 1 row affected (0.08 sec)

Naujo vartotojo sukūrimas ir teisių priskyrimas prie duombazės "testas"

GRANT ALL ON testas.* TO testukas@localhost IDENTIFIED BY "slaptazodis"

Duombazės pakeitimas

mysql> USE testas;
Database changed

Duombazės lentelių parodymas

mysql> SHOW tables;
Empty set (0.00 sec)

Lentelės sukūrimas

mysql> create table lentele;
Query OK, 0 rows affected (0.00 sec)

Lentelės pašalinimas

mysql> drop table lentele;
Query OK, 0 rows affected (0.02 sec)

Visų lentelių šalinimas iš nurodytos duombazės:

mysql --silent --skip-column-names -e "SHOW TABLES" DUOMBAZĖ| xargs -L1 -I% echo 'DROP TABLE `%`;' | mysql -v DUOMBAZĖ

Naujo įrašo sukūrimas, jeigu jau egzistuoja, egzistuojančio atnaujinimas:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=VALUES(c);

Įrašų ištraukimas iš kelių vienodų lentelių (UNION):

 SELECT id, address, data, log_host, log_status from `lentele`
 where address like '%key%'
 UNION ALL
 SELECT id, address, data, log_host, log_status from `lentele2`
 where address like '%key%'
 Order by data desc

Jeigu norime, kad netrauktų duplikatinių įrašų vietoj UNION ALL naudojame, vieną UNION

Konvertavimas ir engine parinkimas

Konvertuojam i utf8

Pažiūrim kokia koduotė?

SELECT default_character_set_name FROM information_schema.SCHEMATA 
 WHERE schema_name = "DUOMBAZĖ";

Arba:

SHOW TABLE STATUS;

Pakeičiam į utf8:

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE  `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"

Rename database

mysql> create database naujas;
shell # mysqldump senas | mysql naujas
mysql> drop database senas;

Keiciam storage engine i InnoDB

mysql dbname -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}' | mysql dbname

Didelių lentelių struktūros keitimas

Pastarasis atvejis užtrunka gana ilgai kaip norim daryti ALTER table ant lentelės su keleta milijonų ar daugiau įrašų. Paprastesnis variantas būtų susikurti naują lentelę, pridėti reikiamą struktūrą ir iš senosios sukelti duomenis. Tai atrodytų maždaug taip:

mysql> use duombaze;
mysql> create table testdbi_new like testdbi;

Pridedame kokių stulpelių mums reikia lentelės struktūroje:

mysql> ALTER TABLE `testdbi_new` ADD `verified` INT NULL DEFAULT NULL AFTER `log_status`;

Keliame duomenis į naują duombazę

mysql> INSERT INTO testdbi_new (data,value,host,log_time,log_host,log_process,log_key,address,log_status) SELECT data,value,host,log_time,log_host,log_process,log_key,address,log_status FROM testdbi;

Pervadiname produkcinę lentelę į seną ir mūsų naujai sukurtą į produkcinę:

mysql> rename table testdbi to testdbi_old, testdbi_new to testdbi;

Importas / Exportas su progresu

Naudosim pv, kuris rodo progresą, duomenims einantiems per bet kokį pipe.

sudo apt-get install pv

Export, pirmiausia reikia gauti db dydį:

mysql > SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Tuomet tą dydį panaudoti su pv:

mysqldump -u <user> -h <host> -p<password> <database> | pv --size 100m > db_dump.sql

Import:

pv mysqldumpas.sql | mysql -u USER -p -D DUOMBAZĖ

Suspausta db:

pv dump.sql.gz | zcat | mysql -u user -ppasswd -D database

Federated tables

Norint pasiekti nutolusio mysql serverio tam tikrą duomenų bazės lentelę lokaliame serveryje, galima pasinaudoti FEDERATED engine. Taip galima bus patogiai iš vienos į kitą lentelę perkelti duomenis ar jais tiesiog manipuliuoti. Visų pirma pažiūrime ar yra FEDERATED palaikymas:

show engines;

Jeigu nėra galime jį įkrauti, pažiūrime kokius pluginus turime:

/usr/lib/mysql/plugin/ha_*

Jeigu matome ha_federated, galime jį įkrauti:

mysql>install plugin federated soname 'ha_federated.so';

Dabar pasirenkame duomenų bazę kurioje dirbsime:

use database;

Sukuriame naują lentele su nutolusios lentelės identiška struktūra, taip bus sujungta nauja sukurta lentelė su nutolusios lentelės duomenimis:

CREATE TABLE `testas` (
  `id` int(11) NOT NULL,
  `email` varchar(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reason` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `external` int(11) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `customer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX email (email),
  INDEX external (external)
) ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4
CONNECTION='mysql://useris:slaptazodis@hostas:portas/duomenu_baze/testas';

Taip pat įsitikinkite, kad nutolęs hostas jus prileidžia ir portas yra užbindintas ant 0.0.0.0 o ne 127.0.0.1 (localhost):

GRANT ALL PRIVILEGES ON *.* TO 'useris'@'%' IDENTIFIED BY 'slaptazodis'; flush privileges;

Problemų sprendimas

[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified

UPDATE mysql.user SET plugin =  WHERE plugin = 'unix_socket';
FLUSH PRIVILEGES;

[Warning] Too many connections

Koks dabar limitas ?

show variables like "max_connections";

Limito padidinimas:

set global max_connections = 200;

Statinis padidinimas my.cnf konfigūracijoje:

[mysqld_safe]
max_connections = 1000

max_allowed_packet didinimas

Kiek dabar?

show variables like "max_allowed_packet"; 

Padidinam

set global max_allowed_packet = 42043040;

Per sudetingo slaptazodzio reikalavimas

SET GLOBAL validate_password_policy=LOW;

arba i /etc/mysql/my.cnf

[mysqld]
validate_password_policy=LOW

MySQL crash ant innodb (reikia recovery)

Kai pasileidžia MySQL erroras:

InnoDB: End of page dump
2017-04-17 16:57:09 7fe6dc898720 InnoDB: uncompressed page, stored checksum in field1 2225910883, calculated checksums for field1: crc32 1252389629, innodb 2225910883, none  37359$
InnoDB: Page may be a transaction system page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK

Sprendimas

Įdedam į my.cnf konfigą

[mysqld]
innodb_force_recovery = 1

Restartuojam mysql, susitvarko. P.S jeigu mysql po upgreido dar reiktų išjungus šį parametą ir švariai paleidus mysql paleisti

mysql_upgrade

Kurios lentelės naudoja InnoDB engine ?

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

[ERROR] Can't init tc log

Sprendimas

Įdedam į my.cnf konfigą

[mysqld]
log_bin=ON

mariadb.service failed because a timeout was exceeded.

Sprendimas

Idedam i serviso faila mysql.service.

[Service]
TimeoutSec=infinity

Alter table monitoringas (innodb)

Sprendimas

select info stmt, id process, time run_time, state,
 trx_rows_locked, table_rows,
 round(trx_rows_locked/table_rows*100,2) pct_done,
 round(trx_rows_locked/time) rows_per_sec,
 round((table_rows-trx_rows_locked)/(trx_rows_locked/time)/60/60,2) hours_remaining,
 round((table_rows-trx_rows_locked)/(trx_rows_locked/time)/60,2) minutes_remaining
 from information_schema.tables t
 join information_schema.processlist p
 on p.info like concat(‘alter table ‘,t.table_name,’%’)
 and p.db = t.table_schema
 join information_schema.innodb_trx x
 on x.trx_mysql_thread_id = p.id;

MySQL uzcrashines table

Sprendimas

Pasirenkam duombaze suziurim kuris table yra uzcrasines ir parasom:

repair table <pavadinimas>

Arba is shell paleidziam:

mysqlcheck -A --auto-repair

Arba scriptukas:

mysql -p<password> -D<database> -B -e "SHOW TABLES LIKE 'User%'" \
 | awk 'NR != 1 {print "CHECK TABLE "$1";"}' \
 | mysql -p<password> -D<database>

Backup sprendimai

Nutolusiame serveryje turime labai didelę duomenų bazę su šimtais tūkstančių lentelių ir milijonais įrašų, serveris tuoj bus išjungtas, reikia kuo greičiau pasidaryti atsarginę duomenų kopiją ir perkelti ją šifruotu kanalu į lokalų serverį kuriame mažai vietos. Naudosime mysqldump ir pipe režimą, spausime duomenis ir viską atsiųsime per ssh:

ssh -C root@remote_serveris "mysqldump --single-transaction --opt --compress DUOMBAZĖ | gzip -9 -c" > DUOMBAZĖ.sql.gz

Sudėtingos struktūros greitas importas

service mysql restart --innodb-doublewrite=0
mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql

Lentelės atskyrimas

cat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql

InnoDB Lentelės pašalinimas rankiniu būdu

# rm /var/lib/mysql/<database>/<table_name>.ibd
# mysql
MySQL > SET FOREIGN_KEY_CHECKS=0;
MySQL > ALTER TABLE tableName DISCARD TABLESPACE;
MySQL > DROP TABLE tableName;

Pašalintos InnoDB lentelės failų metadata dropinimas

Atvejis kai pašalinote InnoDB lentelės failus .ibd ir .frm, ir negalite pašalinti pačios lentelės iš MySQL su klaidos kodu "Unknown table" arba "exists. Please DISCARD the tablespace before IMPORT.". Tuomet reikia sukurti dumb lentelę su fake struktūra, perkopijuoti jos .frm failą į reikiamos lentelės pavadinimą ir tuomet trinti.

MySQL > create table  innodb_table2(`id` int(10) unsigned NOT NULL);
# /etc/init.d/mysql stop
# cp -a /var/lib/mysql/<database>/innodb_table2.frm /var/lib/mysql/<database>/<table_name>.frm
# /etc/init.d/mysql start
MySQL > SET FOREIGN_KEY_CHECKS=0;
MySQL > drop table <table_name>

Optimizacijos

• Query Analyzer: A Tool for Analyzing MySQL Queries Without Overhead

Shrink MySQL ibdata1 Size using innodb_file_per_table

• https://www.thegeekstuff.com/2016/02/mysql-innodb-file-per-table/