filmov
tv
MySQL : How to restore single database from mysqldump backup

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