4

I am a MySQL novice but need to schedule a task to automatically back up my databases.

I have managed to create a backup manually using the following command :-

mysqldump -u root -p --all-databases > "a:\mysql\all_databases.sql"

But I cannot work out how to run the command without having to manually enter my password. I have tried:--

mysqldump -u root -p'*my password*' --all-databases > "a:\mysql\all_databases.sql"

Which appears to execute, but the resultant file is only 1kB in size. Whereas the first command generates a file that is 2661kB.

Can anyone help me?

Marcos Nakamine
  • 1,286
  • 17
  • 29
Highly Sceptical
  • 41
  • 1
  • 1
  • 3
  • See how to store your password in a config file so that you're not prompted every time: http://stackoverflow.com/a/9293090/470749 – Ryan May 28 '14 at 22:03
  • mysqldump -u root -ppassword test1 >"C:\a.sql" I am using this command which is not working ,it is giving access denied – AnonymousDev Nov 10 '15 at 22:56

2 Answers2

6

Try the following command, replace "your_password" with the root password:

mysqldump --user=root --password=your_password --all-databases >"a:\mysql\all_databases.sql"
ethanh
  • 125
  • 1
  • 7
2

Even it's a old thread :) For FULL backups including users, access rights (like my case) performance_schema, functions, routines & triggers, you don't need mysqldump at all.

Download & install 7zip on your machine. Stop your mysql server. Open command prompt and use this:

"C:\Program Files\7-Zip\7z.exe" a -t7z -mx9 -mhe -p<my_secure_password> "W:\backup_all_mysql_databases.7z" "W:\wamp\bin\mysql\mysql5.6.17\data"

For uncompressed 186MB of /data folder you will get 170KB of 7z archive. You can create a BAT file for appending current datetime to file.

Restoring means to un-compress, delete old /data folder from mysql and replace with the one from backup.

There is a minor drawback... if you forget the root password (like me) you'll have a unusable restore of databases too.

That's all.

user1797147
  • 915
  • 3
  • 14
  • 33
  • Why unusable? It's possible to reset forgotten root password to MySQL, having root access to operating system. Or you mean system root password? – Vasiliy Nov 18 '16 at 13:30