2

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

Community
  • 1
firephil
  • 169
  • 1
  • 1
  • 8
  • 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. –  Mar 30 '15 at 19:47
  • As yoonix says, you need to specify a password with the -p option. Please post as answer, yoonix. – David Houde Mar 30 '15 at 19:50

2 Answers2

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

firephil
  • 169
  • 1
  • 1
  • 8
  • 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
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
megamorf
  • 161
  • 7