How to restore a MYSQL compressed backup via mysqldump

Показать описание

How to restore a/all database/s from compressed backup in MySQL Server via mysqldump
************************************************************************************

1) Create a mysqlbackup user
2) Create a password less connection for mysqlbackup user
3) Create a script to backup all database
4) restore all database on same server

Step 1) Create a Bcakup user

mysql -u root -p

drop user 'mysqlbackup'@'localhost';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES ON *.* TO 'mysqlbackup'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'mysqlbackup'@'localhost';

Step 2) Create a password less connection

mysql_config_editor print --all
mysql_config_editor remove --login-path=emultiskills_MYSQL_mysqlbackup

mysql_config_editor set --login-path=emultiskills_MYSQL_mysqlbackup --host=localhost --user=mysqlbackup --password

-- It prompts for the password.

mysql --login-path=emultiskills_MYSQL_mysqlbackup

Step 3 ) Backup database password exposed

Backup database password masked

show databases;

drop database testdb;

Step 4) restore all databases on same server

syntax

SELECT
table_schema "Database Name",
ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Database Size in MB"
FROM
information_schema.TABLES
GROUP BY table_schema ;