MySQL : How to restore single database from mysqldump backup

preview_player
Показать описание
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

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

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

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

Step 4) restore one databases on same server

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
Рекомендации по теме
Комментарии
Автор

How to speed up mysqldump data import, we have a table with size 60GB and its taking 2 hours to restore, can you give any suggestion to restore it faster, the innodb_buffer_pool_size was set 50G and the server is having 8 CPU's and 65GB RAM

nagamalleswari