1

Is it possible to dump all databases without supplying the username and password for each databe ? The server has 10 databases with different usernames and passwords.

I will be logged in as root (centOS)

I used the following but i get access errors

mysqldump --all-databases > gzip > bak-sql.zip

[fixed]

mysqldump -u admin --password='password' --all-databases | gzip -7 > bak-sql.zip

[Related Thread]

https://stackoverflow.com/questions/8444108/how-to-use-mysql-dump

4
  • What access errors do you get?
    – user9517
    Mar 30 '15 at 19:40
  • mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect I guess i have to create a master user in the MySQLServer
    – firephil
    Mar 30 '15 at 19:42
  • 3
    Yes, you need a user with access to be able to do it. This goes for everything in MySQL, not just backups. Also, if what you put as your command is what you really used, you'll end up with your backups in an uncompressed file named gzip.
    – user143703
    Mar 30 '15 at 19:47
  • As yoonix says, you need to specify a password with the -p option. Please post as answer, yoonix. Mar 30 '15 at 19:50
2

Try this (from the mysql documentation):

first create a config file for mysql and make sure that only your account has access to it:

touch ~/.my.cnf
chmod 600 ~/.my.cnf

add the username password combinations in the config file:

[mysqldump]
user=databseuser12
password=otFRD?17*<8X0G

You can then run the mysqldump command without a password:

mysqldump --user=databseuser12 --host=mysql.example.com forumsalpha | gzip -9 > /home/myuser/backups/dbs/mysql-forumsalpha.sql.gz
1

[edit]

ok i used :

mysqldump -u admin --password='password' database-name | gzip -7 > bak-sql.zip

My assumption is correct as pointed out by @wurtel (linux associates the linux user with the MySQL user).

I logged in to MySql with phpMyAdmin and the admin user has all privileges but is not associated to any database ! (plesk panel) so i have to use the admin1 username1 databae1 format for each database and perform the task for each database. The best approarch will be to write a script with a for loop to write each database to a different file.

Also | gzip -7 > file is correct instead of > gzip > file which is wrong.

Because my password had special characters i got access errors, so i had to use the flag

--password='password' instead of -ppassword

1
  • 1
    If you're logged in as admin then mysql will use the user admin. If you're logged in as root mysql will use root; exactly as you write you expected. I see also that the admin user also needs a password, which is explicitly not what the question was about. Finally: have you checked that the dump produced is as expected? See yoonix's comment above about an uncompressed file named gzip.
    – wurtel
    Mar 31 '15 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.