MySQL: разделение и уменьшение файла ibdata1

Автор: | 07/07/2014

mysqllogoФайл 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