Spis Treści

Sztuczki i kruczki związane z mySQL
Tworzenie limitowanych backupów
Odzyskiwanie backupów
1. Wyłączenie kontroli na czas odzyskiwania backupu
2. Podział backupów: struktura oraz dane
mysql-sys
Źródła w sieci internet

Sztuczki i kruczki związane z mySQL

Utworzenie tabeli mysql.innodb_index_stats

W procesie upgrade i/lub nowej instalacji skrypt do wykonywania backupów może zgłaszać brak tabeli. Zatem należy ją utworzyć:

USE mysql
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

Co zrobić, kiedy tabeli nie da się utworzyć

Próba utworzenia w/w tabel (lub tylko wybranych) może powodować błędy typu "tabela już istnieje", ale backup zgłasza błąd, że tabeli brak. Jednocześnie polecenie DROP TABLE slave_worker_info zgłasza błąd, że tabeli nie ma.

Rozwiązaniem jest wykonanie następujących czynności (na przykładzie slave_worker_info):

# skasowanie pliku .idb z tabelą
rm /var/db/mysql/mysql/slave_worker_info.*

# usunięcie tabeli
echo "DROP TABLE slave_worker_info" | mysql -u root mysql

# utworzenie tabeli (wklejenie w/w CREATE TABLE)

# restart serwera mysql (być może nadmiarowe, ale tak podają źródła w sieci)
service mysql_server restart

Ponowne uruchomienie backupu powinno wykonać się już poprawnie.

Sprawdzenie listy procesów

Pokazanie listy procesów w wersji skróconej (początki poleceń SQL):

SHOW FULL PROCESSLIST

Pokazanie listy procesów z pełnymi poleceniami SQL (czasami z bardzo długimi zapytaniami):

SHOW FULL PROCESSLIST

Ustawianie zmiennych

SET [SESSION|GLOBAL] variable=wartość

Naprawianie tabel

Zgodnie z dokumentacją mySQL należy wykonać:

SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX tbl_name IN repair_cache;
LOAD INDEX INTO CACHE tbl_name;
REPAIR TABLE tbl_name ;
-- ALTER TABLE tbl_name ADD INDEX column_name (column_name);
-- OPTIMIZE TABLE tbl_name
SET GLOBAL repair_cache.key_buffer_size = 0;

Tabele MyISAM oraz indeksy

Dotyczy szczególnie bardzo dużych tabel. Dla operacji INSERT oraz UPDATE warto zdjąć indeksy z takich tabel:

ALTER TABLE tbl_name DISABLE KEYS;

Niemniej trzeba pamiętać, że wykonywanie SELECT-ów z taką ułomną tabelą powoduje bardzo długie zapytania. Można to przywrócić (ale to trwa):

ALTER TABLE tbl_name ENABLE KEYS;

EXPLAIN czyli sprawdzenie co zrobi zapytanie

Aby sprawdzić, jak optymalne jest zapytanie można użyć polecenia EXPLAIN:

EXPLAIN SELECT * FROM `ibf_core_attachments_map` AS `core_attachments_map` WHERE location_key='forums_forums' AND id1=56489 AND id2=1260029 AND id3 IS NULL;

Pokazanie indeksów związanych z tabelą

SHOW INDEXES FROM tbl_name;

Struktura tabeli

Domyślnie pokazanie samych kolumn:

-- describe jest aliase dla show columns
DESCRIBE tbl_name

Albo w wersji jak tabela została utworzona (wraz z indeksami, kodowaniem znaków, etc.):

SHOW CREATE TABLE tbl_name

Klonowanie tabeli na podstawie istniejącej

CREATE TABLE tbl1 LIKE tbl2;
INSERT INTO tbl1 SELECT * FROM tbl2 [WHERE ...];

Tworzenie limitowanych backupów

W ramach tworzenia backupów jest możliwość jego ograniczenia np. do liczby wierszy z każdego backupu. Można to uzyskać poprzez dodanie opcji do mysqldump

mysqldump [...] --opt --where='1=1 ORDEBY BY id DESC LIMIT 10' database table

W powyższym przykładzie wybieramy ostatnie 10 wierszy z tabeli

Odzyskiwanie backupów

Tutaj jest największy problem. Chodzi głównie o fakt budowania indeksów, sprawdzania odwołań ("foreign keys") itd. Można to nieco przyspieszyć.

Wyłączenie kontroli na czas odzyskiwania backupu

Należy podać takie polecenia:

SET AUTOCOMMIT=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;

Zaś na końcu wszystko przywrócić:

SET AUTOCOMMIT=1;
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

COMMIT;

Podział backupów: struktura oraz dane

Podczas tworzenia backupów warto oddzielnie utworzyć backup ze strukturą danych, a oddzielnie z samymi danymi.

mysql-sys

Można pobrać z [ 4 ] zestaw widoków, funckji pomocnych dla administratora bazy danych. Instalacja tego "schematu" jest również pomocna dla narzędzia mysqltuner. Wszystkie kroki instalacyjne należy wykonać zgodnie z dokumentacją zawartą na stronie mysql-sys.

MysqlTipsTricks