restore MySQL

User_EQXRP

Hello,

the task is to restore a backup into another existing database through a UI connected via ODBC with MySQL.

I tried to execute a stored procedure that is calling mysqlimport but the stored procedure is not allowed to run mysqlimport command.

Is there a way to resolve this?

Thanks in advance

Dave Stokes-MySQL Community Team-Oracle

Can you share some details like how the backup was made? Do you have proper permissions?

User_EQXRP

Hello Dave,

thank you for your fast response.

As it is a proof of concept we have MySQL Community 8.0.24 installed and we currently log in as root with all privileges. Later we have to take away all rights which are not necessarily needed.

We are open for logical or physical dump. Maybe "SELECT ... INTO OUTFILE" is a way which might work but then we have to create the database and the containing objects as well.

Dave Stokes-MySQL Community Team-Oracle

There are much better ways to perform backups. SELECT INTO OUTFILE is not really a usefully backup strategy .

At a minimum I would urge you to use mysqldump as it will save the structured and the data. My personal favorite is the dumpInstance utility in the MySQL Shell (see https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html) for completeness and performance.

And if you are using the Enterprise edition of MySQL then look at Enterprise Backup - https://dev.mysql.com/doc/mysql-enterprise-backup/8.0/en/

User_EQXRP

The current setup is a running MS Access application with Frontend and Backend.

The next step which is our proof of concept is to migrate the backend to MySQL and one requirement is initiating to restore an existing backup from MS Access frontend through starting a stored procedure on MySQL.

But the stored procedure doesn't have the rights to run the MYSQL Shell mysql or mysqlimport command for restoring the backup.

Do i have to set up appropriate rights for the user running the stored procedure?

Dave Stokes-MySQL Community Team-Oracle

Many sites turn off the ability to use LOAD DATA for security reasons. See https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html for some details.

You may want to use the conversion utility in MySQL Workbench to move your data into MySQL. Using a stored procedure to do the conversion is not a common thing.