Файл ibdata1 является частью InnoDB, и хранит в себе данные таблиц, их индексы и другую служебную информацию.
Со временем – этот файл может увеличиться до совсем неприличных размеров.
Что бы упростить работу с этим файлов – можно разделить его, создав отдельный файл для каждой базы данных и таблицы, с помощью опции innodb_file_per_table.
В MySQL версии 5.6 и выше эта опция включена по-умолчанию, в более старых – необходимо выполнить некоторые дополнительные действия.
В данном случае имеется:
# yum list installed | grep mysql-server mysql-server.i686 5.5.38-1.el6.remi @remi
Создаём директорию для бекапов:
# mkdir -p /root/backup/mysql
# cd backup/mysql/
Создаём резервную копию всех имеющихся баз:
# mysqldump -u root -p -ERA --flush-privileges > alldbs.sql Enter password:
Проверяем её наличие:
# file alldbs.sql alldbs.sql: ASCII English text, with very long lines
Проверяем имеющиеся базы:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_1 | | db_2 | | db_3 | | db_4 | | mysql | | performance_schema | +--------------------+ 7 rows in set (0.00 sec)
Удаляем все базы, кроме баз mysql и information_schema:
# mysql -u root -p -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema | grep -v db_4 | grep -v OLD | gawk '{print "drop database " $1 "; select sleep(0.1);"}' | mysql -u root -pPASSWROD
Проверяем оставшиеся базы:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 3 rows in set (0.00 sec)
ОК.
Останавливаем MySQL сервер:
# service mysqld stop Stopping mysqld: [ OK ]
Удаляем старые файлы:
# rm /var/lib/mysql/ibdata1
# rm /var/lib/mysql/ib_logfile0
# rm /var/lib/mysql/ib_logfile1
Удалить надо все файлы логов – ib_logfil*.
Редактируем файл /etc/my.cnf, и в блоке [mysqld] добавляем строку:
innodb_file_per_table = 1 innodb_data_file_path=ibdata1:10M:autoextend:max:1G
Тут указывается, что InnoDB должен создавать отдельные файлы .ibd и .frm для хранения информации и индексов для каждой таблица, вместо того, что бы хранить их все в одном файле.
Подробнее – тут>>>.
Второй параметр – задаёт размещение файла ibdata1 (т.к. путь не указан – будет использоваться “хранилище” MySQL – /var/lib/mysql), с начальным размером в 10МБ, и автоматическим увеличением при необходимости до максимум 1ГБ.
При достижении лимита – его можно будет либо увеличить, либо добавим второй файл так:
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
Подробнее – тут>>>.
Запускаем MySQL:
# service mysqld start Starting mysqld: [ OK ]
Проверяем логи:
# tail /var/log/mysqld.log 140706 14:22:41 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.38' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi 140706 14:23:16 [Note] /usr/libexec/mysqld: Normal shutdown 140706 14:23:16 [Note] Event Scheduler: Purging the queue. 0 events 140706 14:23:16 InnoDB: Starting shutdown... 140706 14:23:17 InnoDB: Shutdown completed; log sequence number 1595675 140706 14:23:17 [Note] /usr/libexec/mysqld: Shutdown complete 140706 14:23:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# tail /var/log/mysqld-error.log 140706 15:18:10 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure 140706 15:18:10 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure 140706 15:18:10 [Note] Event Scheduler: Loaded 0 events 140706 15:18:10 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.38' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
На ошибку Native table ‘performance_schema’.’*’ has the wrong structure пока не обращаем внимания.
Восстанавливаем наши базы данных:
# mysql -u root -p < alldbs.sql Enter password:
Проверяем базы:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_1 | | db_2 | | db_3 | | db_4 | | mysql | +--------------------+ 6 rows in set (0.01 sec)
Запускаем mysql_upgrade, что бы сгенерировать новую базу performance_schema:
# mysql_upgrade -u root -p --force Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock' Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock' db_1.table_1 OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... OK
Готово.
Проверим файлы:
# ls -lh /var/lib/mysql/db_1/ total 112K -rw-rw---- 1 mysql mysql 65 Jul 6 15:20 db.opt -rw-rw---- 1 mysql mysql 8.5K Jul 6 15:20 table_1.frm -rw-rw---- 1 mysql mysql 96K Jul 6 15:21 table_1.ibd
Создадим новую таблицу в базе db_1:
mysql> use db_1; Database changed mysql> show tables; +----------------+ | Tables_in_db_1 | +----------------+ | table_1 | +----------------+ 1 row in set (0.00 sec)
mysql> create table table_2 (column_1 varchar(20), column_2 varchar(20)); Query OK, 0 rows affected (0.11 sec)
Проверяем:
# ls -lh /var/lib/mysql/db_1/ total 220K -rw-rw---- 1 mysql mysql 65 Jul 6 15:20 db.opt -rw-rw---- 1 mysql mysql 8.5K Jul 6 15:20 table_1.frm -rw-rw---- 1 mysql mysql 96K Jul 6 15:21 table_1.ibd -rw-rw---- 1 mysql mysql 8.5K Jul 6 15:26 table_2.frm -rw-rw---- 1 mysql mysql 96K Jul 6 15:26 table_2.ibd
Каждая таблица хранится в отдельном файле.
Теперь удалим базу:
mysql> drop database db_1; Query OK, 2 rows affected (0.03 sec)
И проверим файлы:
# ls -lh /var/lib/mysql/db_1 ls: cannot access /var/lib/mysql/db_1: No such file or directory
И полезные ссылки:
http://habrahabr.ru
http://rootedlabs.wordpress.com
http://dba.stackexchange.com
http://dba.stackexchange.com
https://rtcamp.com
