Mysql root user is not initialized #216

Hello there !

I am using docker-toolbox with Windows (quite sad about it but due to VPN and company group strategies it is quite hard to setup a Unix environment here) and I think Windows permissions are the problem.

I copy here my files :

:
    : 
    : 
    :
        - 
        - 
        - 
        - 
        - 
        - 
        - 
    :
        
        - 
    :
        
        - 

FROM mysql:5.7
MAINTAINER Helpless Dev <helpless.dev@wonderful_company.com>

COPY conf.d/*.cnf /etc/mysql/conf.d/
COPY docker-entrypoint-initdb.d/databases.sh /docker-entrypoint-initdb.d/databases.sh
COPY docker-entrypoint-initdb.d/config.sh /docker-entrypoint-initdb.d/config.sh

RUN chmod a+x /docker-entrypoint-initdb.d/config.sh && chown root:root /docker-entrypoint-initdb.d/config.sh
RUN chmod a+x /docker-entrypoint-initdb.d/databases.sh && chown root:root /docker-entrypoint-initdb.d/databases.sh

[mysqld]
default-storage-engine=INNODB
init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
bind-address        = 0.0.0.0
skip-external-locking
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
query_cache_limit   = 1M
query_cache_size        = 16M
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16M

chmod 644 /etc/mysql/conf.d/custom.cnf

 

 

mysql=( mysql --protocol=socket -uroot )

 [    ] 
    mysql+=( -p )


IFS=  -a dbs  

 [   0 ]
       
    
         [  ] 
             create database 
             CREATE DATABASE IF NOT EXISTS  ;  
        

    

     [  ] 
        mysql+=(  )
    


 [    ] 
     [   0 ] 
           
        
             [  ] 
                 GRANT ALL ON .* TO '  
            
        
    

     FLUSH PRIVILEGES ;  

2016-09-26T11:40:30.936825Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-09-26T11:40:30.939713Z 0 [Note] mysqld (mysqld 5.7.15) starting as process 1 ...
2016-09-26T11:40:30.954868Z 0 [Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
2016-09-26T11:40:30.956901Z 0 [Note] InnoDB: PUNCH HOLE support available
2016-09-26T11:40:30.959072Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-09-26T11:40:30.959141Z 0 [Note] InnoDB: Uses event mutexes
2016-09-26T11:40:30.959160Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-09-26T11:40:30.959348Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-09-26T11:40:30.959368Z 0 [Note] InnoDB: Using Linux native AIO
2016-09-26T11:40:30.959587Z 0 [Note] InnoDB: Number of pools: 1
2016-09-26T11:40:30.960271Z 0 [Note] InnoDB: Using CPU crc32 instructions
2016-09-26T11:40:30.966106Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-09-26T11:40:30.979996Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-09-26T11:40:30.981340Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-09-26T11:40:31.009740Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-09-26T11:40:31.081507Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-09-26T11:40:31.082573Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-09-26T11:40:31.188290Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-09-26T11:40:31.189949Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-09-26T11:40:31.190054Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-09-26T11:40:31.190480Z 0 [Note] InnoDB: Waiting for purge to start
2016-09-26T11:40:31.240774Z 0 [Note] InnoDB: 5.7.15 started; log sequence number 12135621
2016-09-26T11:40:31.241113Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-09-26T11:40:31.242288Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool



2016-09-26T11:40:31.248585Z 0 [Warning] World-writable config file './auto.cnf' is ignored.
2016-09-26T11:40:31.249364Z 0 [Warning] World-writable config file './auto.cnf' has been removed.





2016-09-26T11:40:31.249913Z 0 [Note] Salting uuid generator variables, current_pid: 1, server_start_time: 1474890030, bytes_sent: 0,
2016-09-26T11:40:31.250064Z 0 [Note] Generated uuid: '0775897e-83de-11e6-8440-0242ac110002', server_start_time: 281476451600687, bytes_sent: 68492832
2016-09-26T11:40:31.250115Z 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: 0775897e-83de-11e6-8440-0242ac110002.
2016-09-26T11:40:31.259733Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-09-26T11:40:31.260256Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2016-09-26T11:40:31.260418Z 0 [Note] IPv6 is available.
2016-09-26T11:40:31.260458Z 0 [Note]   - '::' resolves to '::';
2016-09-26T11:40:31.260500Z 0 [Note] Server socket created on IP: '::'.
2016-09-26T11:40:31.274069Z 0 [Note] InnoDB: Buffer pool(s) load completed at 160926 11:40:31
2016-09-26T11:40:31.289454Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2016-09-26T11:40:31.289812Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2016-09-26T11:40:31.360977Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2016-09-26T11:40:31.487421Z 0 [Note] Event Scheduler: Loaded 0 events
2016-09-26T11:40:31.487685Z 0 [Note] mysqld: ready for connections.
Version: '5.7.15'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

I put in evidence the file permissions problems.

In the Dockerfile, I tried to run chmod 644, but after investigation, it is impossible with docker toolbox to modify the «o» permissions. I can do chmod u=rw,g=r but not u=rw,go=r or even a=r. I tried several combinations before concluding that it is impossible with docker toolbox on windows to change the «o» permissions during the building. I do not know if I have to do an issue in Docker Machine repository or Toolbox.

I would be glad if you can help me, that would avoid a Vagrant box on my Windows, which would be sad for me and my team 😀 !

Thanks a lot, and have a nice day !

It’s some issue with your .env file and the variable substitutions in the docker-compose.yml, removing them and just putting the variable in normally works fine

  ### MYSQL ################################################

$ docker-compose up -d

Step 1/8 : FROM mysql:latest

Step 2/8 : ARG TZ=UTC
 ---> Running in 40cd51c89a59
Removing intermediate container 40cd51c89a59


 ---> Running in 4dd62bfe4448
Removing intermediate container 4dd62bfe4448


n -R mysql:root /var/lib/mysql/
 ---> Running in dedef095e3fa
Removing intermediate container dedef095e3fa

Step 5/8 : COPY ./conf.d/my.cnf /etc/mysql/conf.d/my.cnf

Step 6/8 : RUN chmod 0444 /etc/mysql/conf.d/my.cnf
 ---> Running in 770e2d5d1761
Removing intermediate container 770e2d5d1761


 ---> Running in 5b525ac58ac5
Removing intermediate container 5b525ac58ac5

Step 8/8 : EXPOSE 3306
 ---> Running in 00f17f22afc7
Removing intermediate container 00f17f22afc7


Successfully built dcf76a4ad169
Successfully tagged root_mysql:latest
WARNING: Image for service mysql was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`.

$ docker logs mysql

create_db.sql was ran

$ 


$ docker  -it mysql bash
# 


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 MySQL Community Server - GPL



Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;






2 rows in set (0.00 sec)

lupa18 opened this issue

Jul 6, 2017

· 6 comments

Error creating root user

lupa18 opened this issue

Jul 6, 2017

· 6 comments

Comments

db:
  image: mysql:latest
  restart: unless-stopped
  environment:
    MYSQL_ROOT_PASSWORD: mypassword
  volumes:
    - db:/var/lib/mysql

docker-library

locked as resolved and limited conversation to collaborators

Sep 8, 2020

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> grant usage on *.* to 'test'@'%' identified by 'test';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> show grants for root;
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*hash_is_here' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select user();
+-----------------+
| user()          |
+-----------------+
| root@172.17.0.4 |
+-----------------+
1 row in set (0.01 sec)

So as you can see, I can log in as root, root has all privileges on .. Any ideas how could this happen? I haven’t upgraded MySQL while keeping the data (I know that this could be caused by upgrading from 5 to 5.5). Thanks.

Note: Empty/blank password means there is no password; hence anyone can log in as long as MySQL privileges have been granted.

To log in as a root user in Linux:

1. Select start menu > Log Out on the top right corner. This allows you to log out of the user account that you are using at the moment.
2. You will now see a login window, and here is where you log in using the username “root” and the password you set up for the root user. If there are several users in the login window, select others and log in.

If the password is not set up or you forgot or need to change your MySQL password, below is a guide on how to do set up/recover the password.

How to set up, change, and recover a MySQL root password

Please note: there have been many attacks across the IT landscape, thus necessitating very strong passwords, which I highly recommend for your databases. You can try out an unsystematic password generator, then save the password via the aid of a password manager.

Let’s carry on.

Setting the password for the first time

Typically, during the installation of MySQL, you are required to set an initial password. However, if this was not done while installing and setting up MySQL, you will first have to set up a password. Here is how to do that:

1. First, open up a terminal window.
2. After launching the terminal, paste and execute this command:

mysqladmin -u root password Fosslinux

Where Fosslinux is the password that you will be using from now on, this means that every time you log into MySQL using the command mysql -u root -p, you will have to key in the password we have just configured.

mysql_secure_installation

MySQL secure installation

After setting up the MySQL root password for the first time, it is time to learn how to change it.

Changing the MySQL root user password

• First, create a new file using the command below:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Fosslinux$11';

create new password

Where Fosslinux$11 is the new password that will be used.  Remember to satisfy the current password policy by combining uppercase, lowercase, numeric and special characters.

Save the file as ~/mysql-pwd

• Next, stop the MySQL daemon using the following command:

sudo systemctl stop mysql

stop MySQL service

• Now that the daemon is stopped, issue and execute the following command on your terminal:

sudo mysqld -init-file=~/mysql-pwd

save MySQL password

• As soon as the command prompt completes executing the above command, proceed and restart the MySQL daemon using the following command:

sudo systemctl start mysql

start MySQL service

• At this point, you should be able to log into the MySQL command prompt using the newly set admin passcode by executing the command below:

mysql -u root -p

Now that we have fruitfully learned how to change the newly created MySQL root password, it is time to learn how to recover a forgotten or lost MySQL root password.

Recover your MySQL password

1. Stop the MySQL server process using the command:

sudo service mysql stop

service stop MySQL

sudo mysqld_safe --skip-grant-tables --skip-networking &

mysql -u root

mysql> use mysql;

mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';

mysql> flush privileges;

mysql> quit

Once you are done running all the commands mentioned above, proceed and restart the MySQL daemon using this command:

sudo service mysql restart

restart MySQL service

By now, you should be able to log into MySQL using the newly set password.

That’s all. We believe you are now in a position to set, reset, and recover your MySQL root password.

How To Solve The MySQL Error: Access denied for user root@localhost

mysql -u root -p

sudo mysql

Then, input your passcode at the prompt. A MySQL shell will load.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'insert_password';

For the sake of the tutorial, I will change the password to ‘Fosslinux as shown in the command line and output below:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Fosslinux';

open MySQL shell

After running the commands listed above, exit the MySQL shell by clicking CTRL + D on your keyboard or key in “exit” on the MySQL shell and click enter. You do not have to restart the MySQL service for you to log in using the command below:

sudo service mysql restart 

restart MySQL service

At this point, you can now attempt to access MySQL with root again. In the terminal, key in:

mysql -u root -p

login to MySQL as root

Conclusion

The article further breaks down the steps to set up, change and recover a MySQL root password and set up the root password for the first time. Ensure to stick to all the processes provided in this article to avoid security breaches by using strong password combinations. We hope the article will be helpful. If yes, give a thumbs up via the comments section.

MySQL root user cannot create users

mysql > grant all on *.*  to root@'%' identified by '123123';

mysql> grant all on wordpress1.* to wordpress@'192.168.0.%' identified by '123123';
ERROR 1044 (42000): Access denied for user 'root'@'192.168.0.%' to database 'wordpress1'

Permission denied! ! !

After searching for answers on the Internet, it took a long time and finally solved

mysql> select user,host,Grant_priv from mysql.user;
+-----------+-------------+------------+
| user      | host        | Grant_priv |
+-----------+-------------+------------+
| root      | 192.168.0.% | N          |
+-----------+-------------+------------+
1 rows in set (0.00 sec)

mysql> update mysql.user set Grant_priv='Y' where user='root';

mysql> select user,host,Grant_priv from mysql.user;
+-----------+-------------+------------+
| user      | host        | Grant_priv |
+-----------+-------------+------------+
| root      | 192.168.0.% | Y          |
+-----------+-------------+------------+
1 rows in set (0.00 sec)

then,It is also a necessary step:

mysql> flush privileges;

Remember, I just didn’t write! Hahaha

The result of viewing is this:

mysql> show grants for root@'192.168.0.%'\G
*************************** 1. row ***************************
Grants for [email protected].%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' WITH GRANT OPTION
1 row in set (0.00 sec)

Appear laterWITH GRANT OPTION That’s it!

mysql> grant all on wordpress1.* to wordpress@'192.168.0.%' identified by '123123';
Query OK, 0 rows affected (0.00 sec)