Mysql password file linux

How to pass password from file to mysql command?

I have a shell script which calls the mysql command with one parameter from external file, it looks like this (also I saw this example in other resources):

mysql --user=root --password=`cat /root/.mysql` 

I tried different quotes without success. How to pass it? UPDATE 1: Found that I can pass password without space symbol. The problem in this, my root pass contains spaces.

6 Answers 6

Store your password in a protected mysql cnf file:

install -m 700 -d /srv/secrets/ install -m 600 /dev/null /srv/secrets/root@localhost.cnf editor /srv/secrets/root@localhost.cnf 

Store the password in the client.password ini property

Include this file as the first argument in your mysql command:

mysql \ --defaults-extra-file=/srv/secrets/root@localhost.cnf \ --user=root \ --host=localhost \ --no-auto-rehash 

This works. One can also pass host and username in the same file bypassing the need to pass them separately in the command.

Use mysql_config_editor which is installed with the mysql client

mysql_config_editor set --login-path=dev --user=dbuser --host=localhost -p 

Enter the password and then you can log in like this

You should use the mysql_config_editor for this.

$ mysql_config_editor set \ --login-path=name_of_connection \ --host=server.example.com \ --user=login_as_user \ --password 

this will prompt for a password, and save to a .mylogin.cnf file in your homedir.

mysql —login-path=name_of_connection dbname will connect to dbname on server.example.com as login_as_user

If —login-path is used with other options, ex. —silent , —login-path has to be the first argument/option

Источник

Where is the default root password for MySQL on Ubuntu?

After a fresh new install of MySQL 5.7 on Ubuntu 19, when I attempt to log in for first time I cannot because I don’t have the credentials. But the install did not prompt for credentials. I tried doing a grep for ‘temporary password’ in the following directories but none have a temp password. Where is the default password located? Thank you.

3 Answers 3

Restart MySQL in passwordless mode, reset the password, restart the MySQL service.

# 1 : Stop mysql service /etc/init.d/mysql stop # 2: Start to MySQL server w/o password: mysqld_safe --skip-grant-tables & # Step # 3: Connect to mysql server using mysql client: mysql -u root 

-- 4: Setup new MySQL root user password use mysql; update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; flush privileges; quit 

# 5: Stop MySQL Server: /etc/init.d/mysql stop # 6: Start MySQL server and test it /etc/init.d/mysql start mysql -u root -p 

After doing step 2, attempting step 3 doesn’t work. I get ‘Can’t connect to local MySQL server through socket’ error.

I also get the following when attempting step 2. ‘ mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists.’

Can’t connect to local MySQL server through socket Follow these step to resolve this

Find your socket file by running.

create a link to this file in tmp directory.

sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

Well, none of the above well-intentioned answers worked for me, on Ubuntu 20.04-6. Turns out, mysqld’s default socket file path doesn’t exist when mysqld isn’t running, and mysqld_safe doesn’t create it. So the answer is to create the socket directory, and also set ownership to Unix user mysql-

$ sudo systemctl stop mysql.service $ sudo mkdir /var/run/mysqld $ sudo chown mysql.mysql /var/run/mysqld $ sudo mysqld_safe --skip-grant-tables --socket=/var/run/mysqld/mysqld.sock & $ mysql -u root > *proceed with mysql password change* > ^D $ mysqladmin shutdown $ sudo systemctl start mysql.service 

This worked for me. Now you should be able to go about your mysql business as usual, as I was.

UPDATE- if you’ve just installed MySQL 8.0+, there’s now a straightforward way to set the root password: Securing the Initial MySQL Account

Источник

How to create a credential file for MySQL

This tutorial will show you how to create a credential file for MySQL. The credential file allows us to store our sensitive username and password to allow automated access to MySQL and its tools.

Having to enter your username and password every time you access MySQL’s console or use one of its utilities can be a pain, especially if you follow password best practices, where you use a 16 character, random character password.

Create a Credential File

The credential file syntax is similar to MySQL’s configuration file, and that’s because it is a configuration file. Any of the parameters we place in this file could also be added to my.cnf.

To add credentials we first tell MySQL where they will be applied. For example, you could set it to [mysql] to set credentials for the MySQL client. In our example, we are assigning them to [mysqldump] so that they will be used by that tool.

[mysqldump] user=backup_operator password=Fa$2av;$a3q5g

chmod 0400 ~/.mysql/mysqldump.cnf

Using the Credentials File

Now that we have created our credentials file we no longer need to type our username or password into the command-line. This is even more beneficial when using scripts to automate our works, as we no longer need to place sensitive information in them.

To use your credentials file you use the –defaults-extra-file flag in your MySQL command. For example, to access the MySQL console with your credential file you would do the following:

mysqldump --defaults-extra-file=~/.mysql/mysqldump.cnf

Encrypting your Credentials

Limiting access to your credentials is a good step to protect your information. Unfortunately, there is a lot of risks just leaving your information in clear text files. If an attacker was able to use a vulnerability to grant themselves higher privileges, they will be able to view your information.

To further safeguard our credentials, MySQL 5.6 introduced a method to encrypt them in a stored profile. To use your stored credentials you just need to tell MySQL which profile to use.

Creating the Profile

The command to set your credentials and store them under a profile is mysql_config_editor.

Execute the following command to set a username and password under a profile called backups.

mysql_config_editor set --login-path=backups --host=localhost --user=backupops --password

Using the Profile

To use your secured credentials with MySQL, mysqldump, or other mysql tools you just need to specify the profile. In our example, we are using the credentials stored under our backups profile.

Источник

How to change the mysql root password

I have installed MySQL server 5 on redhat linux. I can’t login as root so I can’t change the root password.

mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

mysqladmin -u root password 'newpass' 

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' 

/sbin/service mysqld start --skip-grant-tables 

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass') -> WHERE User='root'; ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user' 

I even uninstalled mysql-server (using yum) and then reinstalled it but that did not help. How do I force reset the root password?

10 Answers 10

One option is to save UPDATE mysql.user SET Password=PASSWORD(‘newpass’) WHERE User=’root’; into a file and then manually start mysqld with —init-file=FILENAME . Once the server starts, it should reset your password, and then you should be able to log in. After this, you should shut down the server and start it normally.

Good lord, why would someone give a -1 to the only solution I found to be working. An init sql file was the only way I could overwrite the mysql root user password and be able to use it. It is a shame you need to fallback to such operations on a freshly installed software, yet it is good to have a reliable workaround.

A little late to the game, but I had the same issue on a raspberry pi install and found out that it needs elevation. Adding a sudo to the front of the password change allowed it to work.

sudo mysqladmin -u root password 'newpass' 

followed by an elevated sql access

If either are not run as sudo, it will fail.

The root user password is an empty string by default.

And (using password: NO) says that there is no password.

Do you try to login from another system? I imagine you can only login as root user locally.

I am not sure, but I don’t think so. Unfortunately I never used redhat. EDIT: But why don’t you just try it?

Note that if you want to try logging in with no password, you should just avoid specifying -p on the command line altogether.

Note: The root password may not by blank by default, now (just found this out). I installed MySQL 5.7.16 from a yum repository and found that the installer had created a temporary password that was stored in a plain text file 😛 (for yum this was /var/log/mysqld.log)

I removed the MySQL installation and deleted the data files, and then reinstalled it.

Then I was able to set the root password. Once you set the root password to something. mysqladmin won’t let you reset it if you don’t know it.

To reset it, you’ve got to have ownership over how mysqld is executed, and feed it an init file to change the root password: https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

This helped me on Windows with MySQL Server 5.6. Make sure you change the mysqld path to point to where you have installed MySql Server, for me it was «C:\Program Files\mysql\MySQL Server 5.6\bin\mysqld.exe» :

1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop.
3. Create a text file containing the following statements. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; 

C:\> C:\mysql\bin\mysqld --init-file=C:\\mysql-init.txt 

If you installed MySQL to a location other than C:\mysql, adjust the command accordingly. The server executes the contents of the file named by the —init-file option at startup, changing each root account password. You can also add the —console option to the command if you want server output to appear in the console window rather than in a log file. If you installed MySQL using the MySQL Installation Wizard, you may need to specify a —defaults-file option:

C:\> "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\\mysql-init.txt 

Источник

How to pass password to mysql command line

The proper answer is don’t put your passwords on the command line where anyone with access to /proc can trivially read them as long as the program is running. That’s what a ~/.my.cnf is for, properly chmod’ed to 0600

6 Answers 6

The mysql client utility can take a password on the command line with either the -p or —password= options.

If you use -p , there must not be any blank space after the option letter:

I prefer the long options in scripts as they are self-documenting:

mysql --password=mypassword --user=me --host=etc 

This is insecure because any user could view the password — either directly via /proc/$pid/cmdline or via the ps command. It’s true that mysql overwrites the password in argv during startup but there is always a time window where another user could observe the password. Also, on some systems the argv overwriting might not work.

Of course this is insecure, but most people are doing something like this in a build process that will be deleted shortly afterwards.

List of running processes might be captured by machine monitoring tools and appear then in log files. A short running process is a bad excuse.

You have to be very careful how you pass passwords to command lines as, if you’re not careful, you’ll end up leaving it open to sniffing using tools such as ps .

The safest way to do this would be to create a new config file and pass it to mysql using either the —defaults-file= or —defaults-extra-file= command line option.

The difference between the two is that the latter is read in addition to the default config files whereas with the former, only the one file passed as the argument is used.

Your additional configuration file should contain something similar to:

[client] user=foo password=P@55w0rd 

Make sure that you secure this file.

mysql --defaults-extra-file= [all my other options] 

Источник