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