Import sql file into mysql via command line

Question

I have a little weird problem. I would like to import sql file via command line. Command looks like

mysql -u root -p root pdweb-sandbox < C:\Apache24\htdocs\pdweb-sandbox\migrations/init.sql

But if there is password explicitly typed in command it does nothing. No error but also no action. Only if the password is empty it works as expected(but I need type it to the command line). But I would like to have password in the command. It is a part of a script which is triggered automatically. Password comes from phinx.yml config file. Is there a way to do it with password in command?

Yongjun Rong · Accepted Answer · 2017-05-25T20:59:49.247

1

There are many ways you can do this in a script:

Using mysqlimport. You can find the details here. https://www.toadworld.com/platforms/mysql/w/wiki/6152.mysql-importing-with-mysqlimport
Using mysql, the details about how to use it as below (Copied from https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_password)

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one.

Specifying a password on the command line should be considered insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”. You can use an option file to avoid giving the password on the command line.

edited May 25 '17 at 20:59

answered May 25 '17 at 20:35

Yongjun Rong

36
3

I think it works like mysql command. No error but also no action. – Čamo May 25 '17 at 20:59
When you use the -p, you need make sure it is like "-psecrect" or "--password=secrect" – Yongjun Rong May 25 '17 at 21:04
Yes the problem was in space after -p. Thank you. I will have to do it another way because of security. – Čamo May 26 '17 at 13:30

score 0 · Answer 2 · answered May 25 '17 at 21:02

The above two ways are not secure. You can put the password in another option file. Details is here: https://dev.mysql.com/doc/refman/5.7/en/password-security-user.html `> Store your password in an option file. For example, on Unix, you can list your password in the [client] section of the .my.cnf file in your home directory:

[client] password=your_pass To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:

shell> chmod 600 .my.cnf To name from the command line a specific option file containing the password, use the --defaults-file=file_name option, where file_name is the full path name to the file. For example:

shell> mysql --defaults-file=/home/francis/mysql-opts Section 4.2.6, “Using Option Files”, discusses option files in more detail.`
Using expect. "man expect". http://www.admin-magazine.com/Articles/Automating-with-Expect-Scripts

Is phinx.yml config file secure enough? I wrote about it in question. — Čamo, May 25 '17 at 21:34
It is difficult to say because it depends on how to you save the sphinx.yml file and what the permission for that file. But store the db password as a clear text in a file is always insecure. — Yongjun Rong, May 27 '17 at 02:28

Import sql file into mysql via command line

2 Answers2