0

I have installed mysql 5.7.19 using rpm package in RHEL, my requirement is to initialize the data directory using mysqld, the MySQL server with using some other user 'SAM' rather than mysql user. i have created my.cnf file and created a symbolic link ln -s etc/my.cnf .my.cnf, Following are the contents of my.cnf
[client] port=3307 socket=/tmp/mysql.sock host=localhost default-character-set=utf8

[mysqld]
port=3307
socket=/tmp/mysql.sock
datadir=/home/data/db
log-error=/var/opt/mysql/mysql-error.log
sql-mode=""
default-storage-engine=MyISAM
long_query_time=5
max_allowed_packet=16M
# Accept 4.0.x style passwords
# old-passwords=ON
character_set_server=utf8

[mysqldump]
quick

I'm getting following logs in file(mysql-error.log) while running command and mysql server is not getting up mysqld --defaults-file=/home/etc/my.cnf --basedir=/usr/sbin/.. --pid-file=/var/opt/mysql/mysql.pid

18-06-06T05:02:31.933483Z 0 [Note] InnoDB: Foreign key constraint system tables created
2018-06-06T05:02:31.933518Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2018-06-06T05:02:31.935391Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2018-06-06T05:02:31.935417Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2018-06-06T05:02:31.936921Z 0 [Note] InnoDB: sys_virtual table created
2018-06-06T05:02:31.937905Z 0 [Note] InnoDB: 5.7.19 started; log sequence number 0
2018-06-06T05:02:31.938825Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2018-06-06T05:02:31.938932Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-06-06T05:02:31.939424Z 0 [Note] Salting uuid generator variables, current_pid: 12598, server_start_time: 1528261351, bytes_sent: 0,
2018-06-06T05:02:31.939459Z 0 [Note] Generated uuid: 'd1916718-6946-11e8-9f90-000c29429204', server_start_time: 3546021758129118237, bytes_sent: 50645584
2018-06-06T05:02:31.939475Z 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: d1916718-6946-11e8-9f90-000c29429204.
2018-06-06T05:02:31.941093Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-06-06T05:02:31.941184Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2018-06-06T05:02:31.941191Z 0 [Note] Server hostname (bind-address): '*'; port: 3307
2018-06-06T05:02:31.941207Z 0 [Note] IPv6 is available.
2018-06-06T05:02:31.941211Z 0 [Note]   - '::' resolves to '::';
2018-06-06T05:02:31.941258Z 0 [Note] Server socket created on IP: '::'.
2018-06-06T05:02:31.942764Z 0 [Warning] Failed to open optimizer cost constant tables

2018-06-06T05:02:31.942851Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-06-06T05:02:31.942892Z 0 [ERROR] Aborting

can someone please answer is it possible to initialize of mysql server using other users rather than mysql

Note: This is a fresh install, not an upgrade. (This fact copied from a now-deleted Answer.)

1

From the website here, you can just modify this line

shell> bin/mysqld --initialize --user=mysql

and change it to:

shell> bin/mysqld --initialize --user=other_username

This user (who is NOT a user in the database server sense, but rather in the Operating System (OS) sense) only determines permissions on the underlying file system - i.e. who owns the data-dir for example, it has nothing to do with MySQL database users. For clarity, I would use different names for OS users and MySQL users.

With compilation of the source, you get even more fine-grained control over your system, but if all you want is to change the owner of the files on the underlying OS file system, then do it this way!

You can use --initialize-insecure and then login as root and change the password straight away, logging in as root with no password:

shell >mysql --user=root

and then

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

Logout and then login with

shell >mysql -u root -p

After -p type nothing else and press Return. You should then be prompted for the password (MyNewPass) that you have just entered.

  • Thanks, one more question here, can I ran the command like 'mysqld --initialize --datadir=/home/rm/testdata/db --log-error=/var/opt/mysql/mysql-error. – developer Jun 8 '18 at 7:06
  • after i ran the command mysqld --initialize --datadir=/home/rm/testdata/db --log-error=/var/opt/mysql/mysql-error. but mysqld still picks log-error file from /etc/my.cnf file, any suggestion would be much appreciated – developer Jun 8 '18 at 7:14
  • Use --defaults-file=/path/to/my/dir/my.cnf as an extra parameter to your initialize! – Vérace Jun 8 '18 at 13:28
  • For a belt-and-braces approach, use --basedir also shell> bin/mysqld --initialize --user=mysql --basedir=/opt/mysql/mysql --datadir=/opt/mysql/mysql/data - from here. – Vérace Jun 8 '18 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.