Taisomas MySQL

Apie MySQL klasterį [[PerconaDB|aprašyta čia]].


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

 <nowiki>UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';</nowiki> 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):
<syntaxhighlight lang="mysql">
 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
</syntaxhighlight>
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:
<syntaxhighlight lang="sql">
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';
</syntaxhighlight>
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 ===

<syntaxhighlight lang="mysql">
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;
</syntaxhighlight>

== 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


= Optimizacijos =
* [https://engineering.linkedin.com/blog/2017/09/query-analyzer--a-tool-for-analyzing-mysql-queries-without-overh 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/

[[Category:Programavimas]]
[[Category:Duombazės]]
{{Template:Distributions}}
{{Template:BSD}}
@@ 6 eilutė: / 6 eilutė: @@
   <nowiki>UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';</nowiki> 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ė ===
@@ 250 eilutė: / 245 eilutė: @@
   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 =