The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Stored Procedure for remote backup

I am trying to create a stored procedure that can be triggered via laravel 10 query but I am struggling to see what is wrong in the SQL i have gotten so far. Any clues as to where I am goinbg wrong would be much appreciated-

`CREATE PROCEDURE backup_database ( IN database_name VARCHAR(512), IN remote_location VARCHAR(512) ) BEGIN DECLARE backup_file_name VARCHAR(512);

SET backup_file_name = CONCAT(database_name, '-', NOW(), '.gz');

SET @command = CONCAT('mysqldump -u root -p --compatible=mariadb ', database_name, ' | gzip -c > ', remote_location, '/', backup_file_name); PREPARE statement FROM @command; EXECUTE statement; DEALLOCATE PREPARE statement;

SET @command = CONCAT('rm ', remote_location, '/', backup_file_name); PREPARE statement FROM @command; EXECUTE statement; DEALLOCATE PREPARE statement; END;`

Answer Answered by Daniel Black in this comment.

Prepared statements are for executing SQL, and not for executing system commands.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.