|
Rozmiar: 8270
Komentarz: * reorganizacja periodic.conf.local
|
Rozmiar: 7912
Komentarz: * aktualizacja katalogów zfs; my.cnf zgodnie z ostatnimi tuningami
|
| Usunięcia zostały oznaczone w ten sposób. | Dodatki zostały oznaczone w ten sposób. |
| Linia 53: | Linia 53: |
| zfs create -o compress=lzma -o recordsize=128k -o primarycache=metadata sys/data/v/db/mysql/logs | zfs create -o compress=lz4 -o recordsize=128k -o primarycache=metadata sys/data/v/db/mysql/logs |
| Linia 55: | Linia 55: |
# ustawienie partycji dla tabel tymczasowych # tabele tymczasowe zawsze s/a tabelami MyISAM, tak wi/e ustawienia jak dla podstawowego katalogu # zfs create -o compression=off -o recordsize=8K -o checksum=off sys/data/v/tmp/mysql |
|
| Linia 60: | Linia 65: |
| chown -R mysql:mysql /var/db/mysql /var/log/mysql | chown -R mysql:mysql /var/db/mysql /var/log/mysql /var/tmp/mysql |
| Linia 80: | Linia 85: |
| daily_mysql_savedays="21" | daily_mysql_savedays="35" |
| Linia 86: | Linia 91: |
| daily_mysql_dump_include="" | |
| Linia 88: | Linia 94: |
| daily_mysql_dump_tables="fkvectra" | |
| Linia 129: | Linia 136: |
| max_connections = 550 | max_connections = 390 |
| Linia 152: | Linia 159: |
| max_allowed_packet = 8M max_heap_table_size = 128M key_buffer_size = 4M read_buffer_size = 1M read_rnd_buffer_size = 1M |
max_allowed_packet = 2M max_heap_table_size = 1024M key_buffer_size = 512M read_buffer_size = 2M read_rnd_buffer_size = 2M |
| Linia 158: | Linia 165: |
| join_buffer_size = 2M | join_buffer_size = 16M myisam_sort_buffer_size = 64M |
| Linia 163: | Linia 171: |
| query_cache_size = 30M query_cache_limit = 8M tmp_table_size = 128M |
query_cache_size = 64M query_cache_limit = 64M tmp_table_size = 512M |
| Linia 168: | Linia 176: |
| read_rnd_buffer_size = 1M | read_rnd_buffer_size = 8M |
| Linia 198: | Linia 206: |
| = 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: {{{#!highlight sql numbers=off SET AUTOCOMMIT=0; SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; }}} Zaś na końcu wszystko przywrócić: {{{#!highlight sql numbers=off SET AUTOCOMMIT=1; SET UNIQUE_CHECKS=1; SET FOREIGN_KEY_CHECKS=1; COMMIT; }}} == Podział backupów: struktura oraz dane == Podczas tworzenia backupów należy oddzielnie utworzyć backup ze strukturą danych, a oddzielnie z samymi danymi. |
Spis Treści
- Baza danych mySQL
- MariaDB
- phpMyAdmin
- Ustawienie katalogu z bazami
- Backup baz danych
- Konfiguracja mySQL - my.cnf
- Źródła w sieci internet
- Sztuczki i kruczki związane z mySQL
- Tworzenie limitowanych backupów
- Odzyskiwanie backupów
- mysql-sys
Baza danych mySQL
Jeżeli to ma być mySQL (chociaż złośliwi mówią, że Oracle zabije mySQLa), to korzystamy z własnego repozytorium pakietów instalujemy serwer:
pkg install -y databases/mysql55-server
MariaDB
Jeżeli jednak chcemy się przesiąść na mariadb, to są istotne zmiany.
Modyfikacja /etc/make/make.local.conf
Komentujemy chęć skorzystania z mySQLa:
#WANT_MYSQL_VER= 55
#WITH_MYSQL_VER= 55
Trzeba też to zrobić w pliku /etc/make/make.ports-flags.conf.
Warto skorzystać z instrukcji opisanych przez unixmena
Jedna tylko modyfikacja dotycząca kodowania:
cd /usr/ports/databases/mariadb-client
make WITH_CHARSET=utf8 WITH_XCHARSET=all WITH_COLLATION=utf8_general_ci all install clean
cd /usr/ports/databases/mariadb-server
make WITH_CHARSET=utf8 WITH_XCHARSET=all WITH_COLLATION=utf8_general_ci all install clean
phpMyAdmin
Korzystając z własnego repozytorium pakietów instalujemy panel administracyjny:
pkg install -y databases/phpmyadmin
Konfiguracja PMA
W pliku konfiguracyjnym (domyślnie jest pusty) należy dopisać:
$cfg['blowfish_secret'] = 'losowy ciąg znakow wygenerowany z apg';
Ustawienie katalogu z bazami
Korzystając z podziału na partycje zfs trzeba utworzyć następujące partycje:
# jesli jest partycja zfs, to utworzenie oddzielnego dataset
zfs create -o compression=off -o recordsize=8K sys/data/v/db/mysql
zfs create -o compression=off -o recordsize=16K -o primarycache=metadata sys/data/v/db/mysql/ibdata
zfs create -o compress=lz4 -o recordsize=128k -o primarycache=metadata sys/data/v/db/mysql/logs
zfs create sys/data/v/db/log/mysql
# ustawienie partycji dla tabel tymczasowych
# tabele tymczasowe zawsze s/a tabelami MyISAM, tak wi/e ustawienia jak dla podstawowego katalogu
#
zfs create -o compression=off -o recordsize=8K -o checksum=off sys/data/v/tmp/mysql
# jesli na partycji zwyklej, to utworzenie katalogu
mkdir -p /var/db/mysql
# ustawienie uprawnien
chown -R mysql:mysql /var/db/mysql /var/log/mysql /var/tmp/mysql
/usr/local/bin/mysql_install_db --basedir=/usr/local/ --user=mysql --datadir=/var/db/mysql/
Backup baz danych
Kopie baz danych wykonuje skrypt periodic z repozytorium skryptów. Skrypt to 502.mysql. Ale zanim on zadziała, trzeba utworzyć specjalnego użytkownika w serwerze mySQL:
\u mysql
INSERT INTO user (host,user,password,Select_priv,Lock_tables_priv) VALUES ('localhost','__user__',password('__haslo__'),'Y','Y');
flush privileges;
Ustawienia periodic.conf
Poniższe ustawienia są na serwerze głównym. Konfiguracja wyśrubowna po kątem dużej bazy. Pominięcie kompresji z poziomu skryptu i zrzucenie tego na system plików (ZFS) znacznie przyspieszyło wykonywanie backupu.
# --- backup MySQLa ---------------------------------------------- # daily_mysql_backup_enable="YES" daily_mysql_backupdir="/opt/backups/mysql" daily_mysql_savedays="35" daily_mysql_dump_subdirs="YES" daily_mysql_dump_dflt_charset="DATABASE" daily_mysql_dump_args="-CcRq -E -K --add-locks --add-drop-table" daily_mysql_dump_args_add="-l --skip-extended-insert" daily_mysql_dump_exclude="RCS information_schema performance_schema logs ibdata" daily_mysql_dump_include="" daily_mysql_dump_compress="" daily_mysql_dump_compress_flags="-T4 -c" daily_mysql_dump_tables="fkvectra"
Konfiguracja periodic.conf.local
daily_mysql_dump_user="__user__"
daily_mysql_dump_pass="__haslo__"
Konfiguracja mySQL - my.cnf
Plik jest wczytywany z katalogu /var/db/mysql/
[client] port = 3306 socket = /tmp/mysql.sock [mysqld_safe] socket = /tmp/mysql.sock #nice = 5 # --- definicje dla mySQL daemon ----------------------------------- # [mysqld] user = mysql socket = /tmp/mysql.sock port = 3306 tmpdir = /var/tmp/mysql ###language = /usr/local/share/mysql/english lc-messages-dir = /usr/local/share/mysql back_log = 50 skip-external-locking skip-innodb_doublewrite ft_min_word_len = 3 character_set_filesystem = binary character_set_server = utf8 bind-address = 127.0.0.1 skip-name-resolve log-queries-not-using-indexes = 1 slow_query_log = 1 long_query_time = 3 slow_query_log_file = /var/log/mysql/slow-queries.log max_connections = 390 wait_timeout = 150 interactive_timeout = 300 innodb_file_per_table = 1 default-storage-engine = innodb innodb_data_file_path=ibdata1:512M;ibdata2:512M;ibdata3:256M:autoextend innodb_log_group_home_dir=logs/ innodb_data_home_dir = ibdata/ innodb_buffer_pool_size = 512M innodb_autoextend_increment = 128 innodb_thread_concurrency=16 innodb_log_buffer_size=8M innodb_additional_mem_pool_size=128M innodb_flush_log_at_trx_commit=1 innodb_autoinc_lock_mode=0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb-log-file-size = 512M innodb-flush-method = O_DIRECT innodb-doublewrite = FALSE max_allowed_packet = 2M max_heap_table_size = 1024M key_buffer_size = 512M read_buffer_size = 2M read_rnd_buffer_size = 2M sort_buffer_size = 8M join_buffer_size = 16M myisam_sort_buffer_size = 64M thread_stack = 512K thread_cache_size = 8 thread_concurrency = 16 query_cache_type = 1 query_cache_size = 64M query_cache_limit = 64M tmp_table_size = 512M table_cache = 32K table_definition_cache = 32K read_rnd_buffer_size = 8M query_cache_min_res_unit = 2K thread_concurrency=16 #read_buffer_size = 4M #innodb_log_file_size=1024M #key_buffer = 8M #key_buffer_size = 64M #sort_buffer_size = 128M #join_buffer_size = 128M #query_cache_wlock_invalidate = 1 #delay_key_write = ALL [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] no-auto-rehash [isamchk] key_buffer = 32M [myisamchk] ft_min_word_len=3
Więcej przykładów konfiguracji można znaleźć w katalogu /usr/local/share/mysql/ - pliki my-*.cnf.
Pozostałe sposoby
Inne sposoby są podane w linkach poniżej.
Źródła w sieci internet
http://www.justincarmony.com/blog/2011/04/06/restoring-large-mysql-dump-900-million-rows/
http://vitobotta.com/painless-hot-backups-mysql-live-databases-percona-xtrabackup/
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.
(c) 2004 - 2026 BSD Serwis Krzysztof Stryjek. W przypadku błędów, poprawek proszę kontaktować się z administratorem wiki.
