0

No solution I found on stackoverflow works because, as noted below, I cannot even log into the MySQL container to update privileges.

I have a Laravel project in docker with a mysql database on an Ubuntu VPS. My docker.compose.yml file is set up as follows for mysql. With this setup I can access the mysql directly in Navicat app on Windows. However, this data does not persist once I "docker-compose down" the containers.

  mysql:
    image: mysql:8.0.33
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: dbasename
      MYSQL_USER: dbaseuser
      MYSQL_PASSWORD: dbasepwd
      MYSQL_ROOT_PASSWORD: rootpwd
    command: mysqld --sql_mode="ALLOW_INVALID_DATES,NO_ZERO_DATE"
    networks:
      - laravel

To attempt persistent data, when I add the following to the docker-compose.yml under mysql, I get "1130 host [my VPS ip address] not allowed to connect to this MySQL server" when trying Navicat remotely.

    volumes:
      - ./mysql:/var/lib/mysql

There is no firewall block (VPS port 3306 is set to allow any IP address).

I read about changing user privileges via

GRANT ALL PRIVILEGES ON database_name.* TO 'your_user'@'your_host' IDENTIFIED BY 'your_password';

However, I cannot even log into mySQL via CLI on my VPS - neither the username nor root with passwords as set in my docker-compose file are accepted. I have no way in!

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Please help.

randosev
  • 5
  • 4
  • try replace volumes value to `- ./sqls:/docker-entrypoint-initdb.d`, maybe work – frank_lee Jul 21 '23 at 01:12
  • 1
    if you do `docker-compose down`, you are not deleting the volumes, just the container, so do `docker-compose down -v` – matiaslauriti Jul 21 '23 at 01:50
  • It occurred to me that maybe something became corrupted, due to being unable to apply the widely published solution to my issue. So I took a backup of the vps MySQL folder contents, and proceeded to delete the folder. I then respun up the containers AND IT NOW WORKS. – randosev Jul 21 '23 at 02:07
  • With the proposed `volumes:` line, if there's MySQL content in the host `./mysql` directory, that will get reused (which is what you want) but correspondingly the settings in the Compose file won't get applied to it. If you do actually want to start from scratch, `rm -rf mysql` on the host with the container stopped. – David Maze Jul 21 '23 at 10:24

0 Answers0