MySQLDump with Ansible – How to Backup Database

mysqldump

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?

Best Answer

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.

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.

Related Solutions

MySQL – How to Detect a Running mysqldump

Your only hope in this scenario is to have the PROCESS privilege.

The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.

According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?

You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:

SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:

select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:

mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.

Give it a Try !!!

MySQL – How to Use mysqldump to Ignore Databases for Backup

Back on Dec 16, 2011, I answered the question How do you mysqldump specific table(s)?

I collected all tables not including a certain set of table names.

Using the same principles, you could have collect all the database names from the metadata table information_schema.schemata that you want mysqldump'd, create a query to return that list, then use that list of databases to formulate the mysqldump command.

DATABASES_TO_EXCLUDE="db1 db2 db3"
EXCLUSION_LIST="'information_schema','mysql'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

All you need to do is put the databases you do not want mysqldump'd into DATABASES_TO_EXCLUDE

Give it a Try !!!

Best Answer

Related Solutions

MySQL – How to Detect a Running mysqldump

MySQL – How to Use mysqldump to Ignore Databases for Backup

Related Question