MySQL: initialize the Data Directory (–data-dir) for Operating System user other than thesql

linuxMySQL

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.)

Best Answer

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.

Related Solutions

Mysql – How to identify InnoDB table corruption

Morgan gives a hint in his comment that InnoDB is constantly checking for corrupted pages by doing checksums on the pages it reads. If InnoDB finds a checksum mismatch, it will ~~crash~~ stop the server.

If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use innochecksum:

Because checksum mismatches will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

An interesting caveat:

innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

So yes, for an online table CHECK TABLE is probably the tool (or as pointed out in another answer mysqlcheck if you want to do more than a single database at a time.)

If you can shut down your database you can force it the checksums using innochecksum

Anecdotal: On a innodb tablespace of 29GB (with innodb_file_per_table=1), this script took about 2 Minutes

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

As a bonus, though, since you are running Percona, they implemented a new method for fast innodb checksum. I've never used it, but it might speed up the process.

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Best Answer

Related Solutions

Mysql – How to identify InnoDB table corruption

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Related Question