Wednesday, July 16, 2014

mysql mysqldump read password login information from file for crontab

mysql mysqldump read password login information from file for crontab

# vim ~/.my.cnf

[client]
host = hostname
port = 3306
user = root
password = mypassword
database = dbname

[mysqldump]
host = hostname
port = 3306
user = root
password = mypassword
database = dbname

Note:
The [client] option group is read by all client programs (including mysqldump, but not by mysqld).
The [mysqldump] option group is for "mysqldump" command only.

Make sure no other people can read .my.cnf file:
# chmod 400 ~/.my.cnf

Following two commands work:
# mysql --defaults-file=/root/.my.cnf
# mysqldump --defaults-file=/root/.my.cnf db_name > db_name.sql

or simply:
# mysql
# mysqldump db_name > db_name.sql

Multiple selection
# vim ~/.my.cnf
[client_conn1]
host = hostname1
port = 3306
user = root
password = mypassword
database = dbname1

[client_conn2]
host = hostname2
port = 3306
user = root
password = mypassword
database = dbname2

# mysql --defaults-file=/root/.my.cnf --defaults-group-suffix=_conn1

Note: group has to be preceded by 'client' to be read by mysql.
Note: it has to go after any [client] groups, otherwise it will be overridden.

Or set it as a alias command:
# vim ~/.cshrc
alias d1 'mysql --defaults-file=/root/.my.cnf --defaults-group-suffix=_conn1'

No comments: