MySQL : How to restore single database from MySQL database dump backup


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



Step 1) Create a backup user ( mysqlbackup)

mysql -u root -p
Mysql@1234

drop user 'mysqlbackup'@'localhost';
drop user 'mysqlbackup'@'%';

Select host, user from mysql.user;
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'Password@123';
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';
Select host, user from mysql.user;

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

Password@123
-- It prompts for the password. 

-- the user/pass are saved encrypted in your /home/mysql/.mylogin.cnf

mysql --login-path=emultiskills_MYSQL_mysqlbackup


Step 3 ) Backup database password exposed

mysqldump --user=username --password=password --all-databases "angled bracket"/FS/BackupFileName.sql


Backup database password masked

mysqldump --login-path=emultiskills_MYSQL_mysqlbackup --all-databases "angled bracket" /mysql/backup/alldb29042020.sql


ls -lrt /mysql/backup/alldb29042020.sql

mysql --login-path=emultiskills_MYSQL_mysqlbackup
show databases;
drop database testdb;
show databases;
create database testdb;



Step 4) restore one databases on same server


mysql --login-path=emultiskills_MYSQL_mysqlbackup testdb "inwards angled bracket" /mysql/backup/alldb29042020.sql



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 ;








Cleanup
*******
drop user 'mysqlbackup'@'localhost';
drop user 'mysqlbackup'@'%';
mysql_config_editor remove --login-path=emultiskills_MYSQL_mysqlbackup
rm /mysql/backup/alldb29042020.sql

свернуть