3

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

Improve this question
4
  • What access errors do you get? Commented Mar 30, 2015 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 Commented Mar 30, 2015 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. Commented Mar 30, 2015 at 19:47
  • As yoonix says, you need to specify a password with the -p option. Please post as answer, yoonix. Commented Mar 30, 2015 at 19:50

2 Answers 2

Reset to default
2

[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

Improve this answer
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. Commented Mar 31, 2015 at 11:04
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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.