Mysql – Unable to create an user using thesql_embedded

mariadbmariadb-10.3MySQLusers

I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries.

mysql_embedded -e "CREATE DATABASE IF NOT EXISTS test ;"

mysql_embedded -e "CREATE USER admin@localhost IDENTIFIED BY 'password';"

mysql_embedded -e "select user, host, password, plugin from mysql.user;"

The above query successfully create a database and display user information. But it didn't create the user i.e the middle query didn't get executed. Even it didn't show any error. Can't I create an user using mysql_embedded ?

What is the alternative to mysql_embedded. Is it mysqld –bootstrap ?

Here is a blogpost on mysql_embedded https://mariadb.com/resources/blog/using-mysql_embedded-and-mysqld-bootstrap-to-tinker-with-privilege-tables/

Best Answer

As you are using the docker mariadb image the best way is to set the environment variables when running the image:

MYSQL_DATABASE
MYSQL_USER
MYSQL_PASSWORD

ref: https://hub.docker.com/_/mariadb/

These will case the database and user to be created with grants (ref)

TECHNIQUE #1 : Use an init file

INITFILE=/var/lib/mysql/init-file.sql
echo "SET @newpass = PASSWORD('mypass');" > ${INITFILE}
echo "CREATE USER 'root'@'localhost' IDENTIFIED BY @newpass;" >> ${INITFILE}
chown mysql:mysql ${INITFILE}
service mysql stop
service mysql start --init-file=${INITFILE}
rm -f ${INITFILE}

TECHNIQUE #2 : Inject User into mysql.user

With --skip-grant-tables enabled, run this:

REPLACE INTO mysql.user SET
                  Host = 'root',
                  User = 'localhost',
              Password = PASSWORD('whateverpasswordiwant'),
             Select_priv = 'Y',
             Insert_priv = 'Y',
             Update_priv = 'Y',
             Delete_priv = 'Y',
             Create_priv = 'Y',
               Drop_priv = 'Y',
             Reload_priv = 'Y',
           Shutdown_priv = 'Y',
            Process_priv = 'Y',
               File_priv = 'Y',
              Grant_priv = 'Y',
         References_priv = 'Y',
              Index_priv = 'Y',
              Alter_priv = 'Y',
            Show_db_priv = 'Y',
              Super_priv = 'Y',
   Create_tmp_table_priv = 'Y',
        Lock_tables_priv = 'Y',
            Execute_priv = 'Y',
         Repl_slave_priv = 'Y',
        Repl_client_priv = 'Y',
        Create_view_priv = 'Y',
          Show_view_priv = 'Y',
     Create_routine_priv = 'Y',
      Alter_routine_priv = 'Y',
        Create_user_priv = 'Y',
              Event_priv = 'Y',
            Trigger_priv = 'Y',
  Create_tablespace_priv = 'Y',
                ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 0,
    max_user_connections = 0
;

Then, restart mysql as usual.

See my post In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

Mysql Server Login and Permission Deny Error

Besides not running mysql_upgrade after upgrading MySQL from major subversion to major subversion (E.g 5.1 to 5.5), having the wrong permissions/ownership on the performance_schema folder and files within the data directory can also cause this error.

For example, a fresh install of MySQL 5.5's RPM will set the ownership of that folder and files within it to root:root. To fix this, you will need to change the ownership to mysql:mysql.

E.g.

chown -R mysql:mysql /var/lib/mysql/performance_schema

ls -al /var/lib/mysql

drwx------. 2 mysql mysql     4096 Jun 10 10:29 performance_schema

Best Answer

Related Solutions

Mysql – How to create root user after running thesql_install_db

TECHNIQUE #1 : Use an init file

TECHNIQUE #2 : Inject User into mysql.user

Mysql Server Login and Permission Deny Error

Related Question