1

I am trying to backup remote database using ansible and mysqldump. I have installed ansible in my local server.Generated the ssh keys. I also sent the public key to the remote server. Everything is fine in ansible part but can't backup using ansible and mysqldump.

I tried this:

ansible -m shell -a 'mysqldump -h 192.168.10.155 -u root -proot database_name  > database_name.sql' remote_server

But the backup is being created in home directory of remote_server. My requirement is that I would like to take the backup of remote_server into my local_server.

edit:

Now i using playbook:


  • hosts: remote_server become: yes tasks:

    • name: do a mysqldump ignore_errors: yes shell: mysqldump -h 192.168.10.155 -u root -proot database_name > dbname.sql
  • hosts: remote_server tasks:

    • name: Transfer file from remote to local

      synchronize:

      src: /home/user/test.txt dest: /home/user/backup delegate_to: local_server

Backup is happening but how can i get the backup in the server i am running ansible from?

I studied that this can be done using playbook as well.

How can I achieve it with playbook?

  • Your problem is not ansible, but mysqldump and command line interpretation- mysqldump allows to create backups from a mysql server to a client. Either install the client on the target (remote) server and run it there, or the "sending" to a remote backup has to done in another way (e.g. ssh). – jynus Jul 9 '18 at 13:01
  • MySQLDump writes backup file to the drive which is local to it. So you must to execute MySQLDump on your local server, which is backup file destination, and attach MySQLDump to the remote server (via command line options), which is data source. – Akina Jul 9 '18 at 13:06
  • I would like to automate mysqldump along with ansible. Ansbile Playbook mode would help automate better. So i am looking for playbook if someone has accomplished that way. – rackwadi Jul 10 '18 at 4:27
0

I am really sorry to post this one. AS i later realized, this was more of a ansible solutions i was looking for rather than mysqldump.

I tried many alternatives and finally this one worked for me.

enter image description here What this playbook does is, it on the first part takes the database backup using mysqldump and creates the backup in remote_server.

In the later part, it copies the backup from the remote_server to local server(the one hosting the ansible) with fetch command to local directory.

Once it is in the local directory, this can be copied to which ever server we want.

Hope this one helps to someone else as well.

Thank you everyone for the help.

| improve this answer | |
  • I think, finally you can zipped the db dump to reduce the network traffic – István Döbrentei Dec 14 '19 at 15:41
  • and you can use ansible mysql_db module to dump database – István Döbrentei Dec 14 '19 at 16:00

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.