{
  "$type": "com.whtwnd.blog.entry",
  "content": "# Time for mysqldump\n\nThe script is identical other than two things. First, we have to use a separate secrets file to the user and password to execute mysqldump. Secondly, the parameter order is different for mysqldump, and it is very picky. I have included some source post and documentation that helped me get around a few issues. \n\n\n## Storing the password \n\nLike Postgres, I wanted to store the password somewhere so I wouldn't need to manually enter for each dump (and avoid plain text exposure of my password when passing the command). This can be done by following the instructions here https://stackoverflow.com/questions/9293042/how-to-perform-a-mysqldump-without-a-password-prompt .\nThis post, referencing official mysql documentation, states to do the following in /.my.cnf file. \n\n```bash\n[mysqldump]\nuser=mysqluser\npassword=secret\n```\n\nBe sure to set the correct permissions to the file (CHMOD 600 for unix), but Windows should do the same thing as your pgpass file and assume it is secure. \n\n\n## mysqldump parameters\n\n```sql\n.\\mysqldump.exe --defaults-file=$mysqlpassword -u $user -h $server.hostname -B $database --set-gtid-purged=OFF --result-file=$dumpfile\n```\n\nYou will need to pass your '--defaults-file=' parameter first, as this should always be the first parameter passed if you are using it. \n\nhttps://stackoverflow.com/questions/3836214/problem-with-mysqldump-defaults-extra-file-option-is-not-working-as-expecte\n\nAdditionally, unless you are planning on using this dump for a replica instance (from a primary instance), I strongly suggest using '--set-gtid-purged=off'.\n\nhttps://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_set-gtid-purged\n\nYour last parameter is the '--result-file='. Yes, same situation here, where you cannot simply use '>'. \n\n# The script\n\nThat covers a quick two part post. Find the script below, or on my github\n\nhttps://github.com/randoneering/adhoc_mysqldumps\n\n```powershell\n<#\n\nBackground info on issues with parameters I ran into\n\nOfficial MySQL documentation on mysqldump: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html\n\nWhy --defaults-extra-file option needs to be first: https://stackoverflow.com/questions/3836214/problem-with-mysqldump-defaults-extra-file-option-is-not-working-as-expecte\n\nAvoid gtid errors when restoring to another database: https://superuser.com/questions/906843/import-mysql-data-failed-with-error-1839\n\nDescription:\nThe following script runs mysqldump with necessary parameters to generate a dump, gzip the file, and upload to an s3 bucket.\n#>\n\n#Credentials for backup user\n\n$user = \"svc_backup\"\n\n$mysqlpassword = \"path\\to\\.my.cnf\"\n\n\n\n# Setup alias for 7zip for easy gziping\n\n$7zipPath = \"$env:ProgramFiles\\7-Zip\\7z.exe\"\n\nSet-Alias Compress-7Zip $7ZipPath\n\n\n\n#Location of mysqldump and backup destination\n\n$mysqldumpLocation = \"\\path\\to\\mysqldump.exe\"\n\n$backupDest = \"path\\to\\dumps\\$($database)\"\n\n\n\n#Use for testing script\n\n$serverlist = @(\n\n@{\n\nhostname = \"\"\n\ndatabases = @(\n\n\"\"\n\n)\n\n}\n\n)\n\n\n#Script to run mysqldump and gzip for each database instance(and db) that are mentioned in the Array(s) above)\n\nforeach ($server in $serverlist) {\n\nforeach ($database in $server.databases){\n\n#write logging, in case we automate this in some task scheduler\n\nStart-Transcript \"path\\to\\logs\\mysqldump\\env\\$(\"env\"+$date+$server.hostname).log\" -Append\n\nWrite-Host \"Running backup for $database\"\n\n#Set dump location\n\n$dumpfile = \"path\\to\\dumps\\$($database).sql\"\n\n\n\n#Check if file is already there, if so delete.\n\nif(Test-Path $dumpfile){\n\nRemove-Item $dumpfile\n\nWrite-Host \"$dumpfile removed\"\n\n}else{\n\nWrite-Host \"$dumpfile does not exist. Proceeding\"\n\n}\n\n#Execute pg_dump with parameters\n\n.\\mysqldump.exe --defaults-file=$mysqlpassword -u $user -h $server.hostname -B $database --set-gtid-purged=OFF --result-file=$dumpfile\n\n\n#Variables for 7zip\n\n$Source = $dumpfile\n\n#Check if file is already there, if so delete\n\n$Destination = \"path\\to\\gzip\\$($database).gz\"\n\nif(Test-Path $Destination){\n\nRemove-Item $Destination\n\nWrite-Host \"$Destination removed\"\n\n}else{\n\nWrite-Host \"$Destination does not exist. Proceeding\"\n\n}\n\n#Compress to gzip at highest compression\n\nCompress-7zip a -mx=5 $Destination $Source\n\n\nStop-Transcript\n\n}\n\n}\n\n#Upload to s3 bucket\n\naws s3 cp \\path\\to\\dumps s3://s3bucket --recursive\n\n```",
  "createdAt": "2023-10-29",
  "title": "Automate your dumps with Powershell(part 2)",
  "visibility": "public"
}