Databases

Смотрим размер баз данных mysql. Нашёл интересный запрос, который выводит результат в удобочитаемом виде. 1 2 3 4 SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "DB size in MB" FROM information_schema. TABLES GROUP BY table_schema; В результате получаем: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 +-----------------------+---------------------+ | database_name | Database size in MB | +-----------------------+---------------------+ | 3456models_wp | 6.75263977 | | 4563oems_wp | 1.42401886 | | 346_kinkdb | 5.32735062 | | 7657blog_wp | 546.99484158 | | information_schema | 0.00781250 | | 78_atm | 0.19333363 | | 87_345videos | 23.48179626 | | 87_bio35 | 0.64177418 | | 87_germanperverts | 0.63171864 | | 87_go456 | 2.92414284 | +-----------------------+---------------------+ Для того чтоб узнать размер таблиц конкретной базы данных: 1 2 3 4 SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.tables WHERE table_schema = "my_table"; Где my_table – имя интересующей базы данных. В результате получаем: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 +---------------------------+---------+ | table_name | Size MB | +---------------------------+---------+ | phpbb_privmsgs_rules | 0.00 | | phpbb_modules | 0.04 | | phpbb_boardrules | 0.06 | | phpbb_push_subscriptions | 0.20 | | phpbb_profile_fields_data | 0.67 | | phpbb_bookmarks | 0.00 | | phpbb_ads | 0.05 | | phpbb_ranks | 0.00 | | phpbb_acl_groups | 0.07 | | phpbb_user_notifications | 19.09 | | phpbb_topics_track | 3.86 | | phpbb_drafts | 0.03 | | phpbb_forums_access | 0.00 | | phpbb_banlist | 0.06 | | phpbb_confirm | 24.43 | | phpbb_privmsgs | 4.80 | | phpbb_moderator_cache | 0.00 | | phpbb_bbcodes | 0.01 | | phpbb_notifications | 15.55 | | phpbb_poll_options | 0.01 | | phpbb_lang | 0.00 | | phpbb_privmsgs_folder | 0.00 | | phpbb_icons | 0.00 | | phpbb_ad_locations | 0.03 | | phpbb_search_wordlist | 0.00 | | phpbb_register_log | 0.05 | | phpbb_bots | 0.01 | | phpbb_sessions | 5197.88 | | phpbb_acl_users | 0.00 | | phpbb_acl_roles | 0.01 | | phpbb_zebra | 0.01 | | phpbb_captcha_answers | 0.00 | | phpbb_topics_watch | 0.16 | | phpbb_ad_group | 0.02 | | phpbb_sitelist | 0.00 | | phpbb_ext | 0.02 | | phpbb_config_text | 0.02 | | phpbb_privmsgs_to | 0.23 | | phpbb_teampage | 0.02 | | phpbb_profile_lang | 0.00 | | phpbb_reports | 0.07 | | phpbb_sessions_keys | 0.80 | | phpbb_attachments | 2.20 | | phpbb_log | 6.47 | | phpbb_oauth_tokens | 0.05 | | phpbb_styles | 0.01 | | phpbb_extensions | 0.00 | | phpbb_config | 0.04 | | phpbb_users | 10.78 | | phpbb_search_wordmatch | 0.00 | | phpbb_sphinx | 0.02 | | phpbb_notification_types | 0.03 | | phpbb_forums_track | 0.23 | | phpbb_forums | 0.06 | | phpbb_login_attempts | 10.12 | | phpbb_thanks | 10.09 | | phpbb_words | 0.00 | | phpbb_oauth_accounts | 0.02 | | phpbb_oauth_states | 0.05 | | phpbb_captcha_questions | 0.00 | | phpbb_search_results | 0.00 | | phpbb_profile_fields_lang | 0.00 | | phpbb_forums_watch | 0.00 | | phpbb_posts | 155.20 | | phpbb_reports_reasons | 0.00 | | phpbb_acl_roles_data | 0.02 | | phpbb_topics | 0.45 | | phpbb_profile_fields | 0.01 | | phpbb_acl_options | 0.01 | | phpbb_groups | 0.01 | | phpbb_warnings | 0.00 | | phpbb_extension_groups | 0.00 | | phpbb_user_group | 0.93 | | phpbb_qa_confirm | 0.01 | | phpbb_poll_votes | 0.02 | | phpbb_topics_posted | 0.08 | | phpbb_migrations | 0.09 | | phpbb_smilies | 0.01 | | phpbb_disallow | 0.00 | | stk_reparse_bbcode_backup | 36.02 | +---------------------------+---------+ 80 rows in set (0.008 sec) Смотрим размер всех таблиц всех баз данных и сортируем их по размеру: ...

1 2 3 4 5 6 7 [mysqld] skip-external-locking bind-address = 127.0.0.1 skip-name-resolve = 1 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION query_cache_size = 0 query_cache_type = 0

В качестве памятки для себя… Если видим ошибку “The table ‘galleries’ is full”, увеличиваем значение heap_table_max_size Замена в таблице UPDATE articles SET text = REPLACE(text, 'http://', 'https://') WHERE text LIKE '%http://%';

Остановилась MySQL репликация на слейве (version 5.1.61). Slave_IO_Running было отмечено как Yes, но Slave_SQL_Running как No. Обычный перезапуск слейва не помог, значит требуется более детальный анализ проблемы. Похоже на то что повреждены relay логи слейва, т.к. тестирование с помощью “mysqlbinlog” показало ошибку. Следовательно, решением должно быть удаление текущих бинлогов слейва и указание слейву позиции с которой нужно продолжить репликацию из бинлогов мастера. Вот полный вывод команды “show slave status\G” на остановившемся слейв сервере: {loadposition ads} 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Slave_IO_State: Waiting for master to send event Master_Host: 10.1.79.48 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.002046 Read_Master_Log_Pos: 639600842 Relay_Log_File: triton-relay-bin.001957 Relay_Log_Pos: 243 Relay_Master_Log_File: mysql-bin.002045 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: pretinac_radio,web Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: web.logging_www,web.logging_raspored,web.web_korisnik Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1594 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 0 Exec_Master_Log_Pos: 103641119 Relay_Log_Space: 983411603 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1594 Last_SQL_Error: [the same error description as in Last_Error] Для исправления этой ошибки нужно сбросить текущий бинлог слейва и указать позицию, с которой нужно продолжить репликацию. До этого важно запомнить два параметра, которые мы видели выше в выводе статуса на слейве, это Relay_Master_Log_File и Exec_Master_Log_Pos: ...

Прогресс импорта, или экспорта mysql баз данных можно наблюдать с помощью консольного “Pipe Viewer”, или pv. Импорт pv /path/to/sqlfile.sql | mysql -uUSERNAME -pPASSWORD -D DATABASE_NAME Экспорт Для начала нам нужно узнать полный размер экспортируемых баз данных. Это можно посмотреть в таблице information_shema: 1 2 3 4 5 6 7 8 9 SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM ( SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema IN ('DATABASE_NAME') ) A; После этого можем использовать получившийся общий размер для мониторинга прогресса экспорта: 1 mysqldump -uUSERNAME -pPASSWORD DATABASE_NAME | pv -s 9999M > DATABASE_NAME.sql На этом всё. Оригинал взят на сайте dubbs.github.io

Как-то пришлось мне восстанавливать всего то несколько баз из полного бэкапа mysql, который содержал в себе все базы. Довольно долго сканировал интернет и кое что нашлось. Ниже будет приведён код скрипта, который мне в этом очень помог. Но, отработал он у меня корректно только на linux, на freebsd что-то у него не получалось. Cкрипт предназначен для разрезания MySQL-дампа с несколькими базами на отдельные базы. Файлы именуются по принципу: название_базы.номер_в_общем_дампе.sql Использование ./db_split.sh файл_дампа.sql каталог_куда_складывать_дампы Скрипт 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 #!/bin/bash # By Rain ;) # Создан 2008-04-01 # Изменен 2008-04-17: добавлено описание # Изменен 2008-04-18: добавлено удаление "USE DATABASE" в начале дампов # Изменен 2009-06-08: переписал с использованием awk # Проверка передачи переменных (да-да, примитивно, но надо ж ему хоть что-то проверять? :)) [ -z "$2" ] && echo "Error: directory is not set" && exit 1 [ ! -d "$2" ] && echo "Error: $2 not a directory" && exit 1 # Весь процесс awk '/^CREATE DATABASE/{nodb+=1; print "Processing DB "nodb"..."}; {print >> "'$2'/"nodb".sql"}' "${1}" echo -e "Renaming dumps..." ls -1 ${2}/*.sql | while read file do dumpname="$(head -n1 "${file}" | sed -re "s/^CREATE DATABASE|[\` ;]//g" -e "s@/\\*[\!a-zA-Z0-9_ ]*\*\/@@g")" sed -i -n '/^CREATE DATABASE/,/^USE/!p' "${file}" # Конечно, вряд ли окажутся 2 базы с одним именем, но кто знает... [ ! -e "${file%/*}/${dumpname}.sql" ] && mv "${file}" "${file%/*}/${dumpname}.sql" done echo "All done" Не забываем сделать скрипт исполняемым chmod +x ./db_split.sh ...

Допустим у нас есть mysql база данных site_db, а в ней таблица tablename. Нам нужно конвертировать таблицу tablename из myisam в innodb. По идее всё просто. 1 2 3 mysql -u root -p USE site_db; ALTER TABLE tablename ENGINE=InnoDB; Но в жизни мы можем получить следующий ответ ответ 1 ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes И вот здесь можно побороться. SHOW CREATE TABLE tablename; Нам покажут много строк, среди которых будет строчка с текстом FULLTEXT KEY key_name (column_list) или что-то около того. Важно чтоб в начале было FULLTEXT KEY. Запомнили этот ‘key_name’. Удаляем этот индекс ALTER TABLE tablename DROP INDEX key_name; в ответ получим что-то такое 1 2 Query OK, 6175950 rows affected (20 min 56.88 sec) Records: 6175950 Duplicates: 0 Warnings: 0 а затем снова пробуем конвертировать ALTER TABLE tablename ENGINE=InnoDB; В этот раз должно всё получиться. Не забывайте использовать screen, если работаете с большими базами данных.

В качестве заметки… При изменении innodb_log_file_size придется остановить сервер и удалить (или переименовать) старый лог файл, для чего могут потребоваться рутовые права. Если же старый файл не удалять, mysql просто откажется стартовать. Моя выжимка из конфига (не самая сильная dev-машина, 3G RAM, Dual CPU E2140 @ 1.60GHz) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 [innodb] innodb_buffer_pool_size = 1024M innodb_flush_method = O_DIRECT innodb_log_file_size = 256M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 [mysqld] log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes default-storage-engine = InnoDB collation_server=utf8_general_ci character_set_server=utf8 и вот ещё из другого источника innodb_buffer_pool_size – 70 – 80% оперативной памяти. Я ставлю это значение в 12G на системе с 16G RAM innodb_log_file_size – зависит от необходимого вам объема данных для восстановления, но 256МБ будут разумным компромиссом между производительностью и рамером лог-файла innodb_log_buffer_size=4M – 4 мегабайта – нормальное значение, если вы не используете подачу больших блоков данных в InnoDB через каналы (pipes). Если используете, это значение лучше увеличить. innodb_flush_logs_at_trx_commit=2 – если вас не особо заботит ACID, и вы можете себе позволить потерять транзакции за последние секунду или две, в случае полного краха ОС, то установите это значение. Но это может повлечь печальные эффекты при коротких записях транзакций. innodb_thread_concurrency=8 – даже при имеющихся InnoDB Scalability Fixes будет совсем не лишним иметь ограниченное количество потоков. Значение может быть больше или меньше в зависомости от ваших потребностей, но 8 будет оптимальным значением для начала. innodb_flush_method=O_DIRECT – избегайте двойной буферизации и уменьшите активность swap, в большинстве случаев это увеличивает производительность. Но будьте осторожны, если у вас нет RAID с возможностью сохранения данных, операции ввода-вывода могут проходить некорректно и данные могут быть повреждены. innodb_file_per_table– если у вас немного таблиц, используйте эту опцию и рост занимаемого таблицами места не будет бесконтрольным. Эта опция добавлена в MySQL 4.1 и сейчас достаточно стабильна для использования. ...

Пришлось на днях настроить ротацию логов mongodb. С logrotate не сложилось, там костыль на костыле и костылём погоняет. В общем используем встроенную функцию mongodb для ротации логов и немного ей помогаем. Создаём скриптик, а потом этот скриптик добавляем в крон. # vim /root/scripts/mongorotate.sh 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 #!/bin/bash ### log rotate /usr/bin/kill -USR1 $(cat /var/run/mongodb/mongodb.pid) ### compress newly rotated for f in /home/logs/mongodb/mongodb.log.????-??-??T??-??-??; do gzip "$f" done ### remove files older than x days #find /home/logs/mongodb/mongodb.log.????-??-??T??-??-??.gz -ctime +7 -delete ### remove more then X-1 last files ls -t /home/logs/mongodb/mongodb.log.????-??-??T??-??-??.gz|tail -n+5|xargs -d "\n" -i rm "{}" Обратите внимание что в скрипте можно оставлять архивы логов за несколько дней, а можно оставлять просто несколько последних архивов логов. Так же обратите внимание на пути к самим логам и к файлу .pid. Разрешим файлу выполняться # chmod +x /root/scripts/mongorotate.sh Добавляем задание в крон # crontab -e Rotate logs mongodb 0 2 * * * /bin/bash /root/scripts/mongorotate.sh > /dev/null 2> /dev/null ...