Różnice pomiędzy wersjami 5 i 9 (Spinanie 4 wersji)
⇤ ← Wersja 5 z 2016-02-22 12:40:59 →
Rozmiar: 6544
Edytor: wtps0n
Komentarz: + my.cnf; poprawki literówek
← Wersja 9 z 2016-02-29 01:17:59 → ⇥
Rozmiar: 7543
Edytor: wtps0n
Komentarz: + link do podziału na partycje, aktualizacja podziału
Usunięcia zostały oznaczone w ten sposób. Dodatki zostały oznaczone w ten sposób.
Linia 24: Linia 24:
Warto skorzystać z instrkcji opisanych przez [[http://www.unixmen.com/migrate-from-mysql-to-mariadb-in-freebsd/|unixmena|target="_blank"]] Warto skorzystać z instrukcji opisanych przez [[http://www.unixmen.com/migrate-from-mysql-to-mariadb-in-freebsd/|unixmena|target="_blank"]]
Linia 51: Linia 51:
zfs create -o compression=off -o primarycache=metadata -o recordsize=16K sys/data/v/db/mysql
zfs create sys/data/v/db/mysql/logs
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=lzma -o recordsize=128k -o primarycache=metadata sys/data/v/db/mysql/logs
zfs create sys/data/v/db/log/mysql
Linia 58: Linia 60:
chown mysql:mysql /var/db/mysql
su
-m mysql -c /usr/local/bin/mysql_install_db
chown -R mysql:mysql /var/db/mysql /var/log/mysql
/usr/local/bin/mysql_install_db --basedir=/usr/local/ --user=mysql --datadir=/var/db/mysql/
Linia 126: Linia 128:
innodb_data_home_dir = innodb/
Linia 183: Linia 186:
Więcej przykładów konfiguracji można znaleźć w katalogu `/usr/local/share/mysql/` - pliki `my-*.cnf`.
Linia 211: Linia 215:
 * [[http://www.justincarmony.com/blog/2011/04/06/restoring-large-mysql-dump-900-million-rows/||trget="_blank"]]
 * [[https://scriptingmysql.wordpress.com/2013/07/18/mysql-creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/||trget="_blank"]]
 * [[http://haydenjames.io/mysql-query-cache-size-performance/|mySQL tuning: query cache|trget="_blank"]]
 1. [[http://www.justincarmony.com/blog/2011/04/06/restoring-large-mysql-dump-900-million-rows/||target="_blank"]]
 1. [[https://scriptingmysql.wordpress.com/2013/07/18/mysql-creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/||target="_blank"]]
 1. [[http://www.sysadmin-cookbook.net/mysql/||target="_blank"]]
 1. [[http://vitobotta.com/painless-hot-backups-mysql-live-databases-percona-xtrabackup/||target="_blank"]]
 1. [[http://en.latindevelopers.com/ivancp/|blog poświęcony mySQLowi|target="_blank"]]
 1. [[http://haydenjames.io/mysql-query-cache-size-performance/|mySQL tuning: query cache|target="_blank"]]
 1. [[https://www.patpro.net/blog/index.php/2014/03/09/2617-mysql-on-zfs-on-freebsd/|ustawienia partycji ZFS dla mySQL|target="_blank"]]

<<Include(MysqlTipsTricks,, from="^----$", to="^----$", editlink)>>

##<<Include(pagename, heading, level, from="regex", to="regex", sort=ascending|descending, items=n, skipitems=n, titlesonly, editlink)>>

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=lzma -o recordsize=128k -o primarycache=metadata sys/data/v/db/mysql/logs
zfs create sys/data/v/db/log/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
/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.

Ustawienia periodic.conf

# --- backup MySQLa ----------------------------------------------
#
daily_mysql_backup_enable="YES"
daily_mysql_backupdir="/opt/backups/mysql"
daily_mysql_savedays="21"
daily_mysql_dump_subdirs="YES"
daily_mysql_dump_dflt_charset="DATABASE"
daily_mysql_dump_args_add=""
daily_mysql_dump_exclude="performance_schema"

Dodatkowo ustawienie dot. użytkownika oraz hasła do backupu:

daily_mysql_dump_user="uzytkownik"
daily_mysql_dump_pass="HasloDoBackupu"

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

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 = innodb/
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      = 8M
max_heap_table_size     = 128M
key_buffer_size         = 4M
read_buffer_size        = 1M
read_rnd_buffer_size    = 1M
sort_buffer_size        = 8M
join_buffer_size        = 2M
thread_stack            = 512K
thread_cache_size       = 8
thread_concurrency      = 16
query_cache_type        = 1
query_cache_size        = 30M
query_cache_limit       = 8M
tmp_table_size          = 128M
table_cache             = 32K
table_definition_cache  = 32K
read_rnd_buffer_size    = 1M
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.

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;

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 należy oddzielnie utworzyć backup ze strukturą danych, a oddzielnie z samymi danymi.

Pozostałe sposoby

Inne sposoby są podane w linkach poniżej.

Źródła w sieci internet

  1. http://www.justincarmony.com/blog/2011/04/06/restoring-large-mysql-dump-900-million-rows/

  2. https://scriptingmysql.wordpress.com/2013/07/18/mysql-creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/

  3. http://www.sysadmin-cookbook.net/mysql/

  4. http://vitobotta.com/painless-hot-backups-mysql-live-databases-percona-xtrabackup/

  5. blog poświęcony mySQLowi

  6. mySQL tuning: query cache

  7. ustawienia partycji ZFS dla mySQL

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.


{i}  (c) 2004 - 2026 BSD Serwis Krzysztof Stryjek. W przypadku błędów, poprawek proszę kontaktować się z administratorem wiki.

SerwerBazDanychMysql (ostatnio edytowane 2026-03-13 22:34:14 przez wtps0n)