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?

Answer 1

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

1. Using mysqlimport. You can find the details here. https://www.toadworld.com/platforms/mysql/w/wiki/6152.mysql-importing-with-mysqlimport

2. 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.

Answer 2

3. 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.`

4. Using expect. "man expect". http://www.admin-magazine.com/Articles/Automating-with-Expect-Scripts