-1

I am Looking for script that 1. backup all MYSQL database daily once with date and time 2. All database mysql database consistency check.

Tried this getting an error - Usage: mysqldump [OPTIONS] database [tables]

@echo off
set current=%date:~10,4%%date:~4,2%%date:~7,2%
set filename="G:\MYSQL_Backup\Paintrax-DBs-%current%.sql"
::set filename2="G:\MYSQL_Backup\Paintrax-DBs-%current%.zip"
echo %filename%
cd "G:\MYSQL_Backup"
:: mysqldump -u root -p password --all-databases > G:\MYSQL_Backup\all_databases_2022_Dec.sql
:: mysqldump -u root -p password -port 9119 --all-databases > G:\MYSQL_Backup\all_databases_20200424.sql
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --host=localhost --port=9119 -p password --result-file=%filename% --default-character-set=utf8 --user=root --protocol=tcp --routines --events --single-transaction=TRUE --add-drop-table --all-databases >logfile.txt
echo backup-finished
:: comment out
::if exist %filename% (
  ::  "C:\Program Files\7-Zip\7z.exe" a %filename2% %filename%
    ::echo zip-finished
::    del %filename%
::)
::if exist %filename2% (
  ::  copy %filename2% "\\x.x.x.x\MySQL Backups"
    ::echo copy-finished
::)

Version I am using: Windows server 2019 mysql Ver 8.0.31 for Win64 on x86_64 (MySQL Community Server - GPL)

Thank you.

Saran
  • 71
  • 7

1 Answers1

1

You have -p password with a space, but according to 4.5.4 mysqldump:

--password[=password], -p[password]

If given, there must be no space between --password= or -p and the password following it.

On the other hand, you should not use this parameter in your script, exposing the password, but store the password in the option file.

Specifying a password on the command line should be considered insecure. To avoid giving the password on the command line, use an option file. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

Even if the script file was only readable by the user running it, anyone having access to the server could read the password while the script is running, e.g., with

wmic process where caption="mysqldump.exe" get commandline

Esa Jokinen
  • 421
  • 5
  • 8
  • Thank you for the response. Let me try it out. – Saran Dec 10 '22 at 12:50
  • It is working fine after changing password parameter, but I can see the dump backing up mysql system database as well. How can I exclude that. Ex - CREATE DATABASE /!32312 IF NOT EXISTS/ mysql /!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION='N' /; – Saran Dec 10 '22 at 12:56
  • I think that's another question that should be asked separately, as this is a Q/A site; not a forum. – Esa Jokinen Dec 10 '22 at 14:58
  • I have 20 + dbs, if I mention each db name it is big list, if any new db created that is going to be missed unless we add manually inti a script that is the reason. – Saran Dec 12 '22 at 04:38