MySQL TABLESPACEs: Mover tabelas de volta de um TABLESPACE

Para consultar quais tabelas estão em TABLESPACEs do tipo “General”, que são as que podem ser compartilhadas entre Tabelas, verifique a INNODB_TABLES.

mysql> SELECT * FROM information_schema.INNODB_TABLES WHERE SPACE_TYPE = 'General';
+----------+---------------------------------+------+--------+------------+------------+---------------+------------+--------------+
| TABLE_ID | NAME                            | FLAG | N_COLS | SPACE      | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+---------------------------------+------+--------+------------+------------+---------------+------------+--------------+
|     3293 | mysql/columns_priv              |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3294 | mysql/component                 |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3295 | mysql/db                        |  161 |     25 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3296 | mysql/default_roles             |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3297 | mysql/engine_cost               |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3298 | mysql/func                      |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3299 | mysql/global_grants             |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3300 | mysql/gtid_executed             |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3301 | mysql/help_category             |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3302 | mysql/help_keyword              |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3303 | mysql/help_relation             |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3304 | mysql/help_topic                |  161 |      9 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3305 | mysql/ndb_binlog_index          |  161 |     15 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3306 | mysql/password_history          |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3307 | mysql/plugin                    |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3308 | mysql/procs_priv                |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3309 | mysql/proxies_priv              |  161 |     10 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3310 | mysql/role_edges                |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3311 | mysql/server_cost               |  161 |      7 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3312 | mysql/servers                   |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3313 | mysql/slave_master_info         |  161 |     31 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3314 | mysql/slave_relay_log_info      |  161 |     12 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3315 | mysql/slave_worker_info         |  161 |     16 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3316 | mysql/tables_priv               |  161 |     11 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3317 | mysql/time_zone                 |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3318 | mysql/time_zone_leap_second     |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3319 | mysql/time_zone_name            |  161 |      5 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3320 | mysql/time_zone_transition      |  161 |      6 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3321 | mysql/time_zone_transition_type |  161 |      8 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3322 | mysql/user                      |  161 |     54 | 4294967294 | Dynamic    |             0 | General    |            0 |
|     3546 | MonitORA/Tramites_Chamados      |  161 |      9 |       2110 | Dynamic    |             0 | General    |            0 |
|     3547 | MonitORA/Chamados               |  161 |     15 |       2111 | Dynamic    |             0 | General    |            0 |
|     3553 | zabbix/history_text             |  161 |      7 |       2117 | Dynamic    |             0 | General    |            0 |
+----------+---------------------------------+------+--------+------------+------------+---------------+------------+--------------+
33 rows in set (0.07 sec)

mysql>

E para mover a tabela de volta para o “local original”.

mysql> ALTER TABLE zabbix.history_text TABLESPACE innodb_file_per_table;
Query OK, 0 rows affected (41 min 28.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.