title: Clone table from one MariaDB database to another, including defaults, indexes and triggers?
body: <p>I want to copy a table, including its indexes and triggers, from one database to another. This is not as straightforward as I had hoped. Here is a <a href="https://en.wikipedia.org/wiki/Minimal_Working_Example" rel="nofollow noreferrer">minimal working example (MWE)</a> to demonstrate. First, my MariaDB version:</p>
  
  <pre><code>$ mysql --version
  mysql  Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
  </code></pre>
  
  <p>Next, the first table:</p>
  
  <pre><code>CREATE DATABASE db1;
  USE db1;
  CREATE TABLE tb1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      word VARCHAR(50) NOT NULL);
  INSERT INTO tb1 (word) VALUES ('foo');
  DELETE FROM tb1 WHERE word='foo';
  DELIMITER $$
  CREATE TRIGGER before_word_insert BEFORE INSERT ON tb1 FOR EACH ROW
      BEGIN
          SET NEW.word=TRIM(NEW.word);
          IF NOT (NEW.word REGEXP '^[[:alpha:]]+$') THEN
              SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT = 'Invalid word';
          END IF;
      END$$
  DELIMITER ;
  INSERT INTO tb1 (word) VALUES ('foo');
  DESCRIBE tb1;
  SHOW TRIGGERS;
  SHOW INDEX FROM tb1;
  </code></pre>
  
  <p>These last three lines give:</p>
  
  <pre><code>+-------+-------------+------+-----+---------+----------------+
  | Field | Type        | Null | Key | Default | Extra          |
  +-------+-------------+------+-----+---------+----------------+
  | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
  | word  | varchar(50) | NO   |     | NULL    |                |
  +-------+-------------+------+-----+---------+----------------+
  2 rows in set (0.00 sec)
  
  +--------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
  | Trigger            | Event  | Table | Statement                                                                                                                                                | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
  +--------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
  | before_word_insert | INSERT | tb1   | BEGIN SET NEW.word=TRIM(NEW.word); IF NOT (NEW.word REGEXP '^[[:alpha:]]+$') THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid word'; END IF; END | BEFORE | NULL    |          | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
  +--------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
  1 row in set (0.04 sec)
  
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | tb1   |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>So far, so good. Now, to copy <code>tb1</code> to another database (on the same server):</p>
  
  <pre><code>CREATE DATABASE db2;
  USE db2;
  CREATE TABLE db2.tb1 AS SELECT * FROM db1.tb1;
  DESCRIBE tb1;
  SHOW TRIGGERS;
  SHOW INDEX FROM tb1;
  </code></pre>
  
  <p>I had hoped these last three lines would give identical output as they did for <code>db1</code>, but they don't:</p>
  
  <pre><code>+-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | int(11)     | NO   |     | 0       |       |
  | word  | varchar(50) | NO   |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  
  Empty set (0.00 sec)
  
  Empty set (0.00 sec)
  </code></pre>
  
  <p>In other words, <code>CREATE TABLE db2.tb1 AS SELECT * FROM db1.tb1;</code>:</p>
  
  <ul>
  <li>copied the table contents;</li>
  <li>copied the column types;</li>
  <li><strong>did not copy</strong> the "Key" property;</li>
  <li><strong>did not copy</strong> the "Default" property;</li>
  <li><strong>did not copy</strong> the "Extra" property;</li>
  <li><strong>did not copy</strong> the triggers; and</li>
  <li><strong>did not copy</strong> the indexes.</li>
  </ul>
  
  <p><strong>My question:</strong> what would be a concise equivalent to <code>CREATE TABLE db2.tb1 AS SELECT * FROM db1.tb1;</code> that <strong>would</strong> copy all of those missing items?</p>