How to create a database directory structure for MySQL?

Question

I was following this tutorial:

https://www.digitalocean.com/community/tutorials/how-to-use-mysql-with-your-ruby-on-rails-application-on-ubuntu-14-04

So far, I installed mysql-server mysql-client libmysqlclient-dev, and I was suppose to tell MySQL to create its database directory structure where it will store its information using sudo mysql_install_db.

$ sudo mysql_install_db

[WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
[ERROR]   The data directory needs to be specified.

I thought running mysqld --initialize would solve the issue:

$ mysqld --initialize 

mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[ERROR] Aborting

How do I tell MySQL to create its database directory structure where it will store its information?

Solution 1

I managed to solve the problem using a previously answered question, How to install mysql on Ubuntu 16.04, but I needed to modify the solution to work for me.

When I deleted/moved /var/lib/mysql directory, mysqld --initialize couldn't create the database directory I want in replacement for /var/lib/mysql , and it gave me Errcode: 13 - Permission denied even though I preceded it with sudo.

I deleted/moved the content of /var/lib/mysql/, but not the directory itself. I tried again to run sudo mysql --initialize, and it worked, and created new content within the /var/lib/mysql/ directory. The content produced by mysql --initialize is different than the previous content I deleted/moved.

I continued the rest of the steps in the tutorial, and worked perfectly.

P.S. I needed to use sudo -i to access /var/lib/mysql.

Solution 2

This is the entire solution for your problem.

According to fromdual, the suggestion is to just follow the advice of the error. Some functionality is being deprecated.

Our advice is to enable the variable explicit_defaults_for_timestamp now on your testing systems so you can see if your application behave well and then you are prepared for the next release when this feature will become the default.

In short term this warning is NOT dangerous. In the long term you have to be prepared for the deprecated functionality. You get rid of the warning my setting explicit_defaults_for_timestamp = 1 in your my.cnf [mysqld] section.

You can find the my.cnf file in the following locations, and in this order the values override each other:

• /etc/my.cnf
• /etc/mysql/my.cnf
• $MYSQL_HOME/my.cnf
• [datadir]/my.cnf
• ~/.my.cnf

You can also find your file by

find / -name my.cnf

I added the following in /etc/mysql/my.cnf

[mysqld]
explicit_defaults_for_timestamp = 1

Now try

mysqld --initialize

And I got a reduced error without Timestamp stuff

mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
2018-11-12T20:13:45.024116Z 0 [ERROR] Aborting

Is this good? Not sure so I look deeper. According to the aboveaccepted stack answer for the error you need to

sudo -i #log into root
cd /var/lib/mysql
rm -r *

su username  # get back to the original user

mysqld --initialize 

This, still gave me the exact same error. No change!

mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
2018-11-12T20:13:45.024116Z 0 [ERROR] Aborting

I am not sure the above accepted answer helped at all with the removing the /var/lib/mysql, but sudo helped:

sudo mysqld --initialize

worked... No error at all!

Comments

[thang]

first solution doesn't work for me, and this also doesn't work for me :(