How to clone MySQL database using SQL within same server?

Here is how to clone MySQL/MariaDB within same server.

Core principle: use `Create TABLE <new_db>.<table_name> as select * from <old_db>.<table_name>`, and do it for all tables.

If you have too many tables, you use the following SQL to generate a list of SQL.

Steps:

1. Use `CONCAT` to create SQL by selecting all tables from DB
2. Get a list of `Create Table` and then run it all.

SELECT CONCAT('CREATE TABLE my_database.20240510.', table_name, ' AS SELECT * FROM my_database.', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'my_database';

CREATE TABLE `my_database.20240510`.change_password_history AS SELECT * FROM my_database.change_password_history;
CREATE TABLE `my_database.20240510`.children AS SELECT * FROM my_database.children;
CREATE TABLE `my_database.20240510`.failed_jobs AS SELECT * FROM my_database.failed_jobs;
CREATE TABLE `my_database.20240510`.interests AS SELECT * FROM my_database.interests;
CREATE TABLE `my_database.20240510`.jobs AS SELECT * FROM my_database.jobs;
...

Hope it helps someone.