Make copies of MySql database from an ASP .Net Core 3.0 Web API


I've got a .Net Core 3.0 Web Api hosted in "Azure App Service". I also have a MySql 5.7 database server hosted on "Azure Database for MySql".

In my Web API, I need to create a service which creates copies of one of the databases in MySql Server. These copies will reside on the same server as the original, but will obviously have different names.

I created an Sql script which, when run in Workbench for example, creates the database (including views, functions and stored procedures). However, when I try run this from my Web API like this (I've created an endpoint I can call for now - eventually will be a service):

public class SchemasController : ControllerBase
    private readonly DbContext _context;

    public SchemasController(DbContext context)
        _context = context;

    public async Task<ActionResult> PostSchema(SchemaDto schemaDto)
        string script = System.IO.File.ReadAllText("CreateSchema.sql");
        script = script.Replace("[SCHEMA_NAME]", schemaDto.SchemaName);

        using (System.Data.Common.DbCommand command = _context.Database.GetDbConnection().CreateCommand())
            command.CommandText = script;
            await _context.Database.OpenConnectionAsync();
            int rowsAffected = await command.ExecuteNonQueryAsync();
            await _context.Database.CloseConnectionAsync();

        return Ok();

It gives this error:

MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ USE testcopy$$ CREATE DEFINER=propworx@% PROCEDURE `customers' at line 5

I've had this issue before when trying to execute sql queries in .Net with DELIMETER $$ in it. I could probably delete all the DELIMETER related stuff and it might work. But I was thinking perhaps a better solution would be to instead create a dump of the original, and then restore it as a copy. The advantage of doing it this way would be that if the master database changes, I don't need to recreate a new SQL script file.

The problem is that I'm just not sure how to go about doing this considering my API is in "Azure App Service" and MySql Server is in "Azure Database for MySql Server". Whereas on a local Windows PC I would do something like this from the Command Prompt:

cd C:\Program Files\MySQL\MySQL Server 5.7\bin
mysqldump.exe –e –u[username] -p[password] -h[hostname] [databasename] > C:\backup.sql
mysql –u[username] -p[password] -h[hostname] [databasename] < C:\backup.sql

Would I be able to do something along those lines in Azure?


0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0