1

Running a docker mysql container in the following command

docker run -it --name mysql  -e MYSQL_ROOT_PASSWORD=123456 mysql

then docker creates a running mysql container with logs as

2020-07-23 09:39:19+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.21-1debian10 started.
2020-07-23 09:39:20+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-07-23 09:39:20+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.21-1debian10 started.
2020-07-23 09:39:20+00:00 [Note] [Entrypoint]: Initializing database files
2020-07-23T09:39:20.408751Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-07-23T09:39:20.408909Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.21) initializing of server in progress as process 42
2020-07-23T09:39:20.424771Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-23T09:39:22.342488Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-23T09:39:26.473394Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-07-23 09:39:35+00:00 [Note] [Entrypoint]: Database files initialized
2020-07-23 09:39:35+00:00 [Note] [Entrypoint]: Starting temporary server
mysqld will log errors to /var/lib/mysql/da5f3f1ae045.err
mysqld is running as pid 91
2020-07-23 09:39:37+00:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

2020-07-23 09:39:56+00:00 [Note] [Entrypoint]: Stopping temporary server
2020-07-23 09:39:59+00:00 [Note] [Entrypoint]: Temporary server stopped

2020-07-23 09:39:59+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

2020-07-23T09:39:59.908211Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-07-23T09:39:59.910343Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 1
2020-07-23T09:39:59.945124Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-23T09:40:00.579927Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-23T09:40:00.969050Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-07-23T09:40:01.137873Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-07-23T09:40:01.138424Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-07-23T09:40:01.145079Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-07-23T09:40:01.233430Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

Trying to access the container with

mysql -h localhost -u root -p

it asks for the password, using 123456 as the password fails; using empty password also fails.

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
user824624
  • 7,077
  • 27
  • 106
  • 183
  • did pass the correct password? `mysql -h localhost -u root -p123456`? – Adiii Jul 23 '20 at 10:00
  • yes, I passed the right password. I also use tableplus to access mysql with root and password 123456, it fails as well – user824624 Jul 23 '20 at 10:02
  • From where did you execute the `mysql -h localhost -u root -p` ? – CLNRMN Jul 23 '20 at 10:03
  • did you run the command inside container? or you tried to connect form host? – Adiii Jul 23 '20 at 10:03
  • by using " docker exec -it mysql mysql -uroot -p", then a prompt is out to input the password with 123456, then It can successfully get access to the mysql command line. however why I can't use tableplus with root@localhost and 123456 as the password. – user824624 Jul 23 '20 at 10:09
  • Tableplus is running on your Host? Did you export the mysql-port (3306) from the container? – CLNRMN Jul 23 '20 at 10:11

3 Answers3

3

docker exec -it mysql mysql -uroot -p, then a prompt is out to input the password with 123456, then It can successfully get access to the mysql command line. however why I can't use tableplus

From the comment above it's verified that you are able to access the mysql server inside the container, so the issue with tableplus not the container root password.

Just publish the port and it should work,

docker run -it -p 3306:3306 --name mysql  -e MYSQL_ROOT_PASSWORD=123456 mysql

Also, it seems like tableplus connecting with host MySQL instead of container one as container did not publish the port in your case so it trying to connect somewhere else.

Adiii
  • 54,482
  • 7
  • 145
  • 148
  • I tried docker run -it -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 mysql, it tells me docker: Error response from daemon: Ports are not available: listen tcp 0.0.0.0:3306: bind: address already in use. then I used lsof -n -i:3306, found no process with port 3306 running, any ideas? – user824624 Jul 23 '20 at 10:49
  • seems like ports is occupied, you should run with sudo i think `sudo lsof -n -i:3306` might be user issue. or just expose another port for testing `-p 3305:3306 ` and test connection – Adiii Jul 23 '20 at 10:50
2

It might be that this MySQL installation uses authentication mechanisms of the operating system. Instead of the root passowrd.

To test if that's the case, execute the following command:

sudo mysql -u root

It will ask you for your operating system privileged password, not the MySQL root password (which might be unset).

After you get in, you can add new users, as usual and as described in MySQL manual and tutorials.

Also, you might want try the instructions mentioned here:

https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

See section "B.4.3.2.3 Resetting the Root Password: Generic Instructions".

Quoting it below (and cleaned up a bit), in case if the website is not reachable:

Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option (see How to start MySQL with --skip-grant-tables? for some ways how to do it)

Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:

shell> mysql

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

mysql> FLUSH PRIVILEGES;

Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a root account with a different host name part, modify the instructions to use that host name.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally (without the --skip-grant-tables option).

JustAMartin
  • 13,165
  • 18
  • 99
  • 183
  • after input the OS password, it directly shows out " Access denied for user 'root'@'localhost' (using password: NO)" – user824624 Jul 23 '20 at 10:05
  • @user824624 So, it seems the root password somehow got broken in some strange way. I added a link to my answer to the official instructions for fixing broken root account. – JustAMartin Jul 23 '20 at 10:10
  • @user824624 Updated the answer with simpler instructions and an additional helpful link. – JustAMartin Jul 23 '20 at 10:17
0

I had the same issue and i fixed it replacing localhost with the private ip:

mysql -u root -p -h 192.168.10.4
Esteban López
  • 612
  • 6
  • 6