Migration of your data from one database to another

Before you consider migrating your data from MySQL to another database you have to know which objects have to be migrated.

With this query you will find the objects to consider:

SELECT TABLE_SCHEMA AS `SCHEMA`, IF(TABLE_TYPE = 'BASE TABLE', 'TABLE', TABLE_TYPE) AS OBJECT_TYPE, TABLE_NAME AS `OBJECT_NAME`
     , IFNULL(ENGINE, '') AS ENGINE, IFNULL(TABLE_ROWS, '') AS `ROWS`
     , IFNULL(DATA_LENGTH, '') AS DATA_SIZE, IFNULL(INDEX_LENGTH, '') AS INDEX_SIZE
  FROM information_schema.tables
 WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
UNION
SELECT '', IF(is_role = 'Y', 'ROLE', 'USER'), CONCAT("'", user, "'", '@', "'", host, "'") AS OBJECT_TYPE, '', '', '', ''
  FROM mysql.user
UNION
SELECT db, type, name, '', '', '', ''
  FROM mysql.proc
 WHERE db NOT IN ('sys', 'information_schema', 'performance_schema', 'mysql')
UNION
SELECT db, 'EVENT', name, '', '', '', ''
  FROM mysql.event
UNION
SELECT trigger_schema, 'TRIGGER', trigger_name, '', '', '', ''
  FROM information_schema.triggers
UNION
SELECT db, 'EVENT', name, '', '', '', ''
  FROM mysql.event
ORDER BY `SCHEMA` ASC, OBJECT_TYPE ASC, OBJECT_NAME ASC
;

+--------+-------------+---------------------------+--------+---------+-----------+------------+
| SCHEMA | OBJECT_TYPE | OBJECT_NAME               | ENGINE | ROWS    | DATA_SIZE | INDEX_SIZE |
+--------+-------------+---------------------------+--------+---------+-----------+------------+
|        | ROLE        | 'test_r'@''               |        |         |           |            |
|        | USER        | 'app'@'%'                 |        |         |           |            |
|        | USER        | 'app'@'127.0.0.1'         |        |         |           |            |
|        | USER        | 'focmm'@'127.0.0.1'       |        |         |           |            |
|        | USER        | 'test'@'localhost'        |        |         |           |            |
| sbtest | TABLE       | sbtest1                   | InnoDB | 9680    | 2637824   | 163840     |
| test   | EVENT       | myevent                   |        |         |           |            |
| test   | FUNCTION    | format_time               |        |         |           |            |
| test   | PROCEDURE   | diagnostics               |        |         |           |            |
| test   | TABLE       | dt                        | InnoDB | 6       | 16384     | 0          |
| test   | TABLE       | test                      | InnoDB | 1045044 | 63520768  | 0          |
| test   | TRIGGER     | test_trigger              |        |         |           |            |
| test   | VIEW        | test_v                    |        |         |           |            |
+--------+-------------+---------------------------+--------+---------+-----------+------------+

An easy way to dump all the object definitions (except users and roles) is the following command:

mysqldump --user=root --no-data --triggers --routines --events test > /tmp/test_structure_dump.sql

If you want to dump your data for importing them into another SQL database this command can help:

mysqldump --user=root --skip-extended-insert --skip-lock-tables --no-create-info \
--where='id = id '  --skip-add-locks --skip-comments --skip-quote-names test test \
 | grep -v '^/\*' | grep -v ^$  > /tmp/test_dump.sql