mysqldump from azure

Category: azure database for mysql

Question

CN_Jesper on Sat, 04 Aug 2018 22:40:21


Hello. I am trying to dump Mysql Azure database using powershell script:

$path = 'C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin'
$dumpPath = [Environment]::GetFolderPath("Desktop")+"\\dump.sql"
cd $path
$hostAzure = "omsu-projects.mysql.database.azure.com" 

.\mysqldump.exe --result-file=$dumpPath --all-databases --user=<username> --host=$hostAzure --port=3306 --password=<password> --column-statistics=0



Throws:mysqldump: Got error: 9002: The connection string may not be right.

I want to create dump once and save database state. 
Then do some queries that will change database's state.
Look at result, and then restore it from dump.

Simplier I want to run tests on it, and be able to restore it before next one.
So test's will be independend from each other.

I am using Spring for Backend of my application. So also is there any way to restore dump from Java Code and if it is how should I done it with Azure MySql?

First time I tried to do so though idea, selecting database there on using 'dump with mysqldump' but got error: 

mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')
Searching for solution I realized that I should add '--column-statistics=0' to mysqldump params. And then I started tring to achieve this using mysqldump directly through powershell script.


May be it's too difficult way and there is way easier, so providing better solution to it can solve problem too. :)
Thanks in advance. 






Replies

Andrea Lam on Mon, 06 Aug 2018 18:26:23


Just want to make sure I understand what you're trying to do. 

  1. Dump from Azure DB for MySQL
  2. Restore the dump to a local MySQL server in a programmatic way using Spring/Java? 

Is that correct? 

Thanks, 

Andrea Lam

Program Manager, Azure DB for MySQL

CN_Jesper on Sat, 11 Aug 2018 11:45:10


Yes.

Andrea Lam on Tue, 14 Aug 2018 16:21:57


Would this be helpful? You can use the Runtime process to execute a command to perform the backup and restore from .sql file by passing in your user name, server name, and database name programmatically. 

Thanks,

Andrea Lam

Program Manager, Azure DB for MySQL