I’m trying to setup up MySQL on mac os 10.6 using Homebrew by
brew install mysql 5.1.52.
Everything goes well and I am also successful with the
However when I try to connect to the server using:
/usr/local/Cellar/mysql/5.1.52/bin/mysqladmin -u root password 'mypass'
/usr/local/Cellar/mysql/5.1.52/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'
I’ve tried to access
mysqladmin or mysql using -u root -proot as well,
but it doesn’t work with or without password.
This is a brand new installation on a brand new machine and as far as I know the new installation must be accessible without a root password. I also tried:
but I also get
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus:
cleanup commands, unloaded the
launchctl script, then deleted the mysql directory in
/usr/local/var, deleted my existing
/etc/my.cnf (leave that one up to you, should it apply) and launchctl plist
Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing.
brew remove mysql brew cleanup launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist sudo rm -rf /usr/local/var/mysql
I then started from scratch:
- installed mysql with
brew install mysql
ran the commands brew suggested: (see note: below)
unset TMPDIR mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp
Start mysql with
mysql.server startcommand, to be able to log on it
Used the alternate security script:
launchctlsection from the brew package script output such as,
#start launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist #stop launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
--force bit on
brew cleanup will also cleanup outdated kegs, think it’s a new-ish homebrew feature.
Note the second: a commenter says step 2 is not required. I don’t want to test it, so YMMV!
Here are detailed instructions combining getting rid of all MySQL from your Mac then installing it The Brew Way as Sedorner wrote above:
Remove MySQL completely per The Tech Lab
ps -ax | grep mysql
- stop and
killany MySQL processes
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/var/mysql
sudo rm -rf /usr/local/mysql*
sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
/etc/hostconfigand remove the line
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm -rf /tmp/mysql*
- try to run
mysql, it shouldn’t work
Brew install MySQL per user Sedorner from this StackOverflow answer
brew doctorand fix any errors
brew remove mysql
brew install mysql
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp # whoami is executed inline
run the commands Brew suggests, add MySQL to
launchctlso it automatically launches at startup
mysql should now work and be running all the time as expected
Had the same problem. Seems like there is something wrong with the set up instructions or the initial tables that are being created. This is how I got mysqld running on my machine.
If the mysqld server is already running on your Mac, stop it first with:
launchctl unload -w ~/Library/LaunchAgents/com.mysql.mysqld.plist
Start the mysqld server with the following command which lets anyone log in with full permissions.
mysql -u root which should now let you log in successfully without a password. The following command should reset all the root passwords.
UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;
Now if you kill the running copy of mysqld_safe and start it up again without the skip-grant-tables option, you should be able to log in with
mysql -u root -p and the new password you just set.
If brew installed MySQL 5.7, the process is a bit different than for previous versions.
In order to reset the root password, proceed as follows:
sudo rm -rf /usr/local/var/mysql mysqld --initialize
A temporary password will be printed to the console and it can only be used for updating the root password:
mysql.server start echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-new-password';" | mysql -uroot --password=TEMPORARY_PASSWORD
Okay I had the same issue and solved it. For some reason the mysql_secure_installation script doesn’t work out of the box when using Homebrew to install mysql, so I did it manually. On the CLI enter:
mysql -u root
That should get you into mysql. Now do the following (taken from mysql_secure_installation):
UPDATE mysql.user SET Password=PASSWORD('your_new_pass') WHERE User='root'; DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1'); DELETE FROM mysql.user WHERE User=''; DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%' DROP DATABASE test; FLUSH PRIVILEGES;
Now exit and get back into mysql with:
mysql -u root -p
I had the same problem just now. If you
brew info mysql and follow the steps it looks like the root password should be
new-password if I remember correctly. I was seeing the same thing you are seeing. This article helped me the most.
It turned out I didn’t have any accounts created for me. When I logged in after running
mysqld_safe and did
select * from user; no rows were returned. I opened the MySQLWorkbench with the
mysqld_safe running and added a
root account with all the privs I expected. This are working well for me now.
If mysql is already installed
Stop mysql completely.
mysql.server stop<– may need editing based on your version
ps -ef | grep mysql<– lists processes with mysql in their name
kill [PID]<– kill the processes by PID
Remove files. Instructions above are good. I’ll add:
sudo find /. -name "*mysql*"
- Using your judgement,
rm -rfthese files. Note that many programs have drivers for mysql which you do not want to remove. For example, don’t delete stuff in a PHP install’s directory. Do remove stuff in its own mysql directory.
Hopefully you have homebrew. If not, download it.
I like to run brew as root, but I don’t think you have to. Edit 2018: you can’t run brew as root anymore
sudo brew update
sudo brew install cmake<– dependency for mysql, useful
sudo brew install openssl<– dependency for mysql, useful
sudo brew info mysql<– skim through this… it gives you some idea of what’s coming next
sudo brew install mysql --with-embedded; say done<– Installs mysql with the embedded server. Tells you when it finishes (my install took 10 minutes)
sudo chown -R mysql /usr/local/var/mysql/<– mysql wouldn’t work for me until I ran this command
sudo mysql.server start<– once again, the exact syntax may vary
- Create users in mysql (http://dev.mysql.com/doc/refman/5.7/en/create-user.html). Remember to add a password for the root user.
- First, make sure you have homebrew installed
brew doctorand address anything homebrew wants you to fix
brew install mysql
brew services restart mysql
brew info mysql
mysql: stable 5.6.12 (bottled) http://dev.mysql.com/doc/refman/5.6/en/ Conflicts with: mariadb, mysql-cluster, percona-server /usr/local/Cellar/mysql/5.6.12 (9363 files, 353M) * Poured from bottle From: https://github.com/mxcl/homebrew/commits/master/Library/Formula/mysql.rb ==> Dependencies Build: cmake ==> Options --enable-debug Build with debug support --enable-local-infile Build with local infile loading support --enable-memcached Enable innodb-memcached support --universal Build a universal binary --with-archive-storage-engine Compile with the ARCHIVE storage engine enabled --with-blackhole-storage-engine Compile with the BLACKHOLE storage engine enabled --with-embedded Build the embedded server --with-libedit Compile with editline wrapper instead of readline --with-tests Build with unit tests ==> Caveats A "/etc/my.cnf" from another install may interfere with a Homebrew-built server starting up correctly. To connect: mysql -uroot To reload mysql after an upgrade: launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
. ERROR! The server quit without updating PID file (/var/run/mysqld/mysqld.pid).
or mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
I’m looking for a solution for some time but I can not solve my problem. I tried several solutions in stackoverflow.com but no this helping me.
MySQL server might not be running after installation with Brew. Try
brew services start mysql or just
mysql.server start if you don’t want MySQL to run as a background service.
I just installed MySQL (stable) 5.7.17 on a new MacBook Pro running Sierra and also got an error when running
Securing the MySQL server deployment. Enter password for user root: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
According to the installation info from Brew,
mysql_secure_installation should prompt me to… secure the installation. I figured the MySQL server might not be running and rightly so. Running
brew services start mysql and then
mysql_secure_installation worked like a charm.
Here is an update for MySQL 5.7
bash --version GNU bash, version 4.4.12(1)-release (x86_64-apple-darwin17.0.0) Copyright (C) 2016 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later This is free software; you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. #======================================== brew --version Homebrew 1.7.6 Homebrew/homebrew-core (git revision eeb08; last commit 2018-09-27) Homebrew/homebrew-cask (git revision c9f62; last commit 2018-09-27) #======================================== mysql --version mysql Ver 14.14 Distrib 5.7.23, for osx10.13 (x86_64) using EditLine wrapper #======================================== system_profiler SPSoftwareDataType Software: System Software Overview: System Version: macOS 10.13.3 (17D47) Kernel Version: Darwin 17.4.0 Boot Volume: Macintosh HD Boot Mode: Normal Computer Name: EdisonMacHomeBj User Name: Edison (edison) Secure Virtual Memory: Enabled System Integrity Protection: Disabled Time since boot: 6 days 23:13
brew remove [email protected] brew cleanup mv /usr/local/var/mysql /usr/local/var/mysql.bak brew install [email protected] rm -rf /usr/local/var/mysql #======================================== mysqld --initialize 2018-09-28T04:54:06.526061Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-09-28T04:54:06.542625Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive 2018-09-28T04:54:07.096637Z 0 [Warning] InnoDB: New log files created, LSN=45790 2018-09-28T04:54:07.132950Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-09-28T04:54:07.196824Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 87cf2f10-c2da-11e8-ac2d-ba163df10130. 2018-09-28T04:54:07.224871Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2018-09-28T04:54:07.366688Z 0 [Warning] CA certificate ca.pem is self signed. 2018-09-28T04:54:07.457954Z 1 [Note] A temporary password is generated for [email protected]: kq3K=JR8;GqZ #======================================== mysql_secure_installation -uroot -p"kq3K=JR8;GqZ" mysql_secure_installation: [Warning] Using a password on the command line interface can be insecure. Securing the MySQL server deployment. The existing password for the user account root has expired. Please set a new password. New password: Re-enter new password: VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: n Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : y New password: Re-enter new password: By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n ... skipping. By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n ... skipping. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done!
Just to add something to previous answers – When upgrading from MySql 5.6 to MySql 8.0, I followed the steps provided here to make a clean uninstall, yet I got following errors
2019-11-05T07:57:31.359304Z 0 [ERROR] [MY-000077] [Server] /usr/local/Cellar/mysql/8.0.18/bin/mysqld: Error while setting value 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'. 2019-11-05T07:57:31.359330Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/var/mysql is unusable. You can remove all files that the server added to it. 2019-11-05T07:57:31.359413Z 0 [ERROR] [MY-010119] [Server] Aborting 2019-11-05T07:57:31.359514Z 0 [Note] [MY-010120] [Server] Binlog end
Took me some time to figure it out. Found a clue here:
So, the key to my problem was removing /usr/local/etc/my.cnf file after uninstall.
After that one last step, MySql finally started working.
Try by giving Grant permission Command of mysql
I had the same issue after I tried to restart mysql.
I use the following two aliases in my .profile for convenience
alias mysql-stop='launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist' alias mysql-start='launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist'
After stoping mysql and then trying to restart I experienced the issue you were having. I looked into the launchctl load and it was reporting a “nothing found to load” error.
After a quick search I found this..
So I updated me
mysql-start alias as follows
alias mysql-start='launchctl load -w -F ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist'
This solved my issue which may be useful for you.
None of the above answers (or any of the dozens of answers I saw elsewhere) worked for me when using brew with the most recent version of mysql and yosemite. I ended up installing a different mysql version via brew.
Specifying an older version by saying (for example)
brew install mysql56
Worked for me. Hope this helps someone. This was a frustrating problem that I felt like I was stuck on forever.
The “Base-Path” for Mysql is stored in
/etc/my.cnf which is not updated when you do brew upgrade. Just open it and change the basedir value
For example, change this:
to point to the new version:
Restart mysql with:
Try solution I provided for MariaDB, high change that it works with MySQL also:
In short, try to login with your username! not root.
Try same name as your MacOS account username, e.g. johnsmit.
To login as root, issue:
mysql -u johnsmit