MySQL - Versijų istorija

\dev\null 15:56, 16 balandžio 2022

2022-04-16T15:56:29Z

\dev\null: jau ištrintų lentelių failų metadata šalinimas iš MySQL

2020-10-21T11:21:50Z

jau ištrintų lentelių failų metadata šalinimas iš MySQL

\dev\null 09:57, 14 liepos 2020

2020-07-14T09:57:46Z

\dev\null 16:50, 5 kovo 2020

2020-03-05T16:50:45Z

185.104.185.10: [ERROR] Can't init tc log fix

2019-12-29T19:23:58Z

[ERROR] Can't init tc log fix

188.69.207.191 18:24, 3 rugpjūčio 2019

2019-08-03T18:24:09Z

\dev\null: FEDERATED ENGINE

2019-06-21T15:50:55Z

FEDERATED ENGINE

\dev\null 05:47, 11 rugsėjo 2018

2018-09-11T05:47:40Z

Naujas puslapis

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');

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

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

== mariadb.service failed because a timeout was exceeded. ==

=== Sprendimas ===
Idedam i serviso faila mysql.service.
[Service]
TimeoutSec=infinity

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

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

← Ankstesnė versija		15:56, 16 balandžio 2022 versija
6 eilutė:		6 eilutė:

	<nowiki>UPDATE mysql.user SET plugin = '' WHERE plugin = 'unix_socket';</nowiki> flush privileges;		<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ė ===		=== MySQL 5.7.6 ar vėlesnė ===

← Ankstesnė versija		11:21, 21 spalio 2020 versija
245 eilutė:		245 eilutė:
	cat your-dump.gz.sql \| sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql		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 =		= Optimizacijos =

@@ 239 eilutė: / 239 eilutė: @@
 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
 = Optimizacijos =

@@ 11 eilutė: / 11 eilutė: @@
 === MySQL 5.7.5 ar ankstesnė===
   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NaujasSlapt');
+=== MySQL 5.5.60 ir ankstesnė ===
 Tuomet uždarome esamą MySQL daemono procesą

@@ 183 eilutė: / 183 eilutė: @@
 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';
 == mariadb.service failed because a timeout was exceeded. ==

@@ 193 eilutė: / 193 eilutė: @@
   [Service]
   TimeoutSec=infinity
 == MySQL uzcrashines table ==

@@ 105 eilutė: / 105 eilutė: @@
 Suspausta db:
   pv dump.sql.gz | zcat | mysql -u user -ppasswd -D database
 = Problemų sprendimas =