Bug in MySQL backup script

[oguya]

backup_mysql.py fails to backup databases on MySQL 5.7 due to a warning raised by mysql cli tool.

• script's output

  $ python /opt/ilri/scripts/backup_mysql.py --password 'SOME_LONG_PASS' --notify yes --ignore-errors=no
  backing up 'mysql: [Warning] Using a password on the command line interface can be insecure.' db
  Failed to backup 'mysql: [Warning] Using a password on the command line interface can be insecure.' db.
  Output: mysqldump: [Warning] Using a password on the command line interface can be insecure.
  mysqldump: Got error: 1049: Unknown database 'mysql:' when selecting the database

• providing password using -p option in mysql cli tool also raises a similar warning

  # mysql -u root -pSOME_LONG_PASS
  mysql: [Warning] Using a password on the command line interface can be insecure.
  ...

[oguya]

OK, so the script passes the password as a option to mysql which is insecure! To fix this, we have to use either mysql's login-path or defaults-extra-file.

[oguya]

In backup_mysql.py script, we can write authentication info(user/pass) to a temp file & then pass it as an argument to mysql's & mysqldump's --default-extra-file option.

I think this will work for all versions of MySQL & MariaDB that we have in use.

[alanorth]

Wait, what about the ~/.my.cnf? Shouldn't that allow for passwordless mysql?

[oguya]

Yes it does! But, we can't assume that all hosts running MySQL/MariaDB will have that file present in root's home dir. That's why we explicitly require username/password in the backup script.

[oguya]

I'm patching up a quick fix for this :wink:

[alanorth]

Si mbaya. Nice

On Wed, Jun 29, 2016, 17:30 James Oguya notifications@github.com wrote:

Closed #36 https://github.com/ilri/rmg-ansible-public/issues/36 via f05d2a2 https://github.com/ilri/rmg-ansible-public/commit/f05d2a23bb7345be4ce56ffb0678d463ed7dbc13 .

— You are receiving this because you commented.

Reply to this email directly, view it on GitHub https://github.com/ilri/rmg-ansible-public/issues/36#event-707839384, or mute the thread https://github.com/notifications/unsubscribe/AALtCtsMuA7UUAZb8Qz2CtfM4p3YNdEZks5qQoF9gaJpZM4JA6LA .

Alan Orth alan.orth@gmail.com https://englishbulgaria.net https://alaninkenya.org https://mjanja.ch

[oguya]

On a side note, instead of providing user/pass info. to mysql* tools as shell args, we should use an options file with the --defaults-extra-file option—e.g. --defaults-extra-file=/root/.my.cnf—which will contain user/pass stuff

For example

• the old way:

  # mysqlcheck --all-databases --analyze --extended --auto-repair -u root -p'SOME_LONG_PASS'

• the new way:

  # mysqlcheck --defaults-extra-file=/root/.my.cnf --all-databases --analyze --extended --auto-repair

• contents of /root/.my.cnf

  # cat /root/.my.cnf
  [client]
  user=root
  password=SOME_LONG_PASS

[alanorth]

Well but if you're root and /root/.my.cnf exists it will use it by default. The case is rather how you solved it in the commit, by writing to a temporary config file and then using --defaults-extra-file.