Mysql – How to connect to thesql without having to type an empty password

clientMySQLpasswordSecurity

I just installed MySQL, and by default the root user has no password. If I connect with this command:

mysql --port=3307 --host=127.0.0.1 -uroot -p

It asks me to enter a password, I just hit return, and it connects me. However, I want to make this part of a script, by piping a statement to it non-interactively, like this:

echo "$statement" | mysql --port=3307 --host=127.0.0.1 -uroot -p

But, when I actually do that, it still asks me for the password. How do I make it NOT ask for the password, and just not use one?

I tried removing the -p, and I get this message:

$ mysql --port=3307 --host=127.0.0.1 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I would expect there to be some sort of –nopassword argument, but I can't find one.

Best Answer

Turns out the answer is actually to use the -p -- the long version allows you to set it to empty, like so:

mysql --port=3307 --host=127.0.0.1 -uroot --password=

Related Solutions

Mysql – How export a database based in a primary key of a table

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ... 
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.

MySQL login after secure installation

You most likely have a .my.cnf file in your home directory, which is specifying a password.

The clue here is that when you did not specify a password (sudo -u root), the error message is still saying that you did.

Best Answer

Related Solutions

Mysql – How export a database based in a primary key of a table

MySQL login after secure installation

Related Question