# MySQL installed - but without config example files - why?



## nonTechnical (Jan 24, 2016)

I've just installed databases/mysql57-server on a new server, but can't see the example config files in /usr/local/share/mysql or anywhere to copy to run the server.

I've used:

`$ sudo find / -name "mysql*" 2>/dev/null`

and /usr/local/share/mysql/ has:

my-default.cnf
mysql-log-rotate
mysql.server
mysql_security_commands.sql
mysql_sys_schema.sql
mysql_system_tables.sql
mysql_system_tables_data.sql
mysql_test_data_timezone.sql
mysql/mysqld_multi.server


```
$ cat my-default.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
```




```
$ ps -ax | grep mysql
617  -  Is  0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/freebsd-1gb-sfo1-01b-dev-db.pid
  715  -  I  0:41.51 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --plugin-dir=/usr/local/lib/mysql/plugin --log-error=/var/db/mysql/freebsd-1gb-sfo1-01b-dev-db.err --pid-file=/var/db/mysql/freebsd-1gb-sfo1-01b-dev-db.pid
```


```
$ sudo service mysql-server start
/etc/rc.conf: ---------------------------------------------: not found
/etc/rc.conf: ---------------------------------------------: not found
Starting mysql.
```

But in my /etc/rc.conf I have:


```
mysql_enable="YES"
```

Trying to complete the installation gives an error:


```
$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL server using password in '/root/.mysql_secret'
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
```

The above `find` shows as the first result:

/tmp/mysql.sock.lock

I can't find anything in the setup that would have caused the lack of config files. Here's what I used:

`$ sudo make config`

[x] Archive
[x] Blackhole
[ ]  Example     Compile "Example Storage" statically in server
[x] Federated
[x] Innobase
[x] Partition
[x] Perfschema
( ) Bundled_SSL
(x ) OpenSSL
( ) Without_SSL

I'm not using a /etc/make.conf, so I guess that rules out a possible way the install could have excluded the example files.

Any ideas?

(Thanks)


----------



## SirDice (Jan 24, 2016)

Please post the whole /etc/rc.conf.


----------



## nonTechnical (Jan 24, 2016)

SirDice said:


> Please post the whole /etc/rc.conf.



`$ sysrc -a`

```
/etc/rc.conf: ---------------------------------------------: not found
dumpdev: NO
hostname: dbserver01
mysql_enable: YES
ntpd_enable: NO
openntpd_enable: YES
openntpd_flags: -sv
sendmail_enable: NO
sendmail_msp_queue_enable: NO
sendmail_outbound_enable: NO
sendmail_submit_enable: NO
sshd_enable: YES
syslogd_flags: -ss
update_motd: NO
```

`$ cat /etc/rc.conf`

```
hostname="dbserver01"


#Disable logging from remote hosts and close that port,
#but still allow logging of localhost.

syslogd_flags="-ss"


update_motd="NO"


#pf_enable="YES"
#pf_rules="/etc/pf.conf"
#pflog_enable="YES"
#pflog_logfile="/var/log/pf.log"


# Get date and time
# -------------------------------------------
# Syncs at boot and incrementally over time.
# Disable ntpd to use openntpd as more compatible for jails.
ntpd_enable="NO"

# Use openntp
# -------------------------------------------
openntpd_enable="YES"
openntpd_flags="-sv"


# Set dumpdev to "AUTO" to enable crash dumps, "NO" to disable
# -------------------------------------------
dumpdev="NO"


#Enable Connections
# -------------------------------------------
sshd_enable="YES"


# Restrict Sendmail to Localhost Only
# -------------------------------------------
sendmail_enable="NO"
sendmail_submit_enable="NO"
sendmail_outbound_enable="NO"
sendmail_msp_queue_enable="NO"


# Start Servers
# -------------------------------------------
#postgresql_enable="YES"
mysql_enable="YES"


# Enable EzJail
# -------------------------------------------
#ezjail_enable="YES"


# Enable FTP-Proxy for Jails
---------------------------------------------
#ftpproxy_enable="YES"
#ftpproxy_flags=""
```


This is a DigitalOcean VPS, and will be a dedicated DB server, so I'm using the assigned network configuration that it comes with by default, so no interface stuff in the /etc/rc.conf.

I'm seeing:


```
/etc/rc.conf: ---------------------------------------------: not found
```

Show up during boot. Is that because of a DigitalOcean mod to the version of FreeBSD? This is my first time using a VPS, and also the first time I've seen such a message, so just wondering about that too, thanks.


----------



## tobik@ (Jan 24, 2016)

nonTechnical said:


> I'm seeing:
> 
> 
> ```
> ...


You're seeing that message because you forgot to comment out the dashes here:


nonTechnical said:


> ```
> # Enable FTP-Proxy for Jails
> ---------------------------------------------
> ```


----------



## nonTechnical (Jan 24, 2016)

Lol, thanks.

Is there anything like `$ rc.conf -t`, like testing nginx config, `$ nginx -t`?

And, any thoughts on why the port didn't include any example config files for me to copy to make my own my.cnf?

I'm going to try to install the pkg now and see if it has them.

After starting MySQL, no error is shown, but I'm still getting the same error when I actually try to use it:
`$ mysql -u root`

```
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
```

I guess because a my.cnf file is missing and the socket isn't set and is locked? (I have no idea about the locked part, just a guess.)

`/tmp]$ ls`

```
600 -rw-------  1 mysql  wheel  4B Jan 24 02:35 mysql.sock.lock
```


----------



## talsamon (Jan 24, 2016)

`ls -al /tmp/mysql.sock`

```
srwxrwxrwx  1 mysql  wheel  0 18 Jan 22:35 /tmp/mysql.sock
```


----------



## kpa (Jan 24, 2016)

As far as I know MySQL is supposed to work even without a configuration file, that's probably the reason why a sample file is not included. Check that the /tmp directory has the proper permissions, it should look like this in `ls -ld` output (this is from OS X but the permissions are the same there):


```
$ ls -ld /private/tmp
drwxrwxrwt  14 root  wheel  476 24 Tam 12:29 /private/tmp
```

If the permissions don't match, do:

`# chmod 1777 /tmp`


----------



## nonTechnical (Jan 25, 2016)

Thanks, but the permissions for /tmp are the defaults.

I think it's more likely that something isn't configured right in MySQL, because I can't find anything other than the very sparse /usr/local/share/mysql/my-default.cnf that is meant to be copied by the install somewhere (according to it - see my earlier post with a `cat` of it).

That would normally be fine - because to my knowledge (I haven't used MySQL 'lately') it normally doesn't create a my.cnf on freebsdFreeBSD installs. Instead, normally, example config files are created during the installation, so as to allow choosing one to copy one eg the larger/production oriented config, to become my.cnf for MySQL to use as the default.

Is this something new in MySQL installs since a particular version, that is - not including them, or am I having another really strange problem trying to install a pretty standard port on a new Digital Ocean VPS?

Chances are, I've broken something and forgotten about it - but I haven't created an /etc/make.conf and can't think of where I may changed something that could have been read by make(1) to not install those files?

If anyone could clear this up, please do, otherwise I'm going to revisit the MySQL docs and see if I can use /usr/local/share/mysql/my-default.cnf to begin with before customizing it later.

If only I could use PostgreSQL for this project, sigh.


----------



## junovitch@ (Jan 25, 2016)

The only thing that I see different with databases/mysql57-server compared to earlier versions I've used is there is a /root/.mysql_secret file made by default for the password.   If you don't have any real data in MySQL yet it should be safe to `service mysql-server stop; rm -r /var/db/mysql; rm /root/.mysql_secret; service mysql-server start` to get a completely clean slate and rule out any configuration aspect.


----------



## nonTechnical (Jan 29, 2016)

Thanks, I tried your suggestion and after starting mysql, got the error (which I didn't have before):


```
WARNING: failed precmd routine for mysql
```

It seems pretty obvious to me, at least, that mysql isn't being configured on install, and because there are no examples installed as I have found in the past, e.g. for a large/production db, mysql is failing to run properly/at all.

I've just found this link that ties this bug to another, that says as much.

http://comments.gmane.org/gmane.os.freebsd.devel.ports.bugs/320934




> bugzilla-noreply | 27 Dec 03:15 2015
> *[Bug 205465] databases/mysql57-server: "failed precmd routine" warning on service start*
> https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=205465
> Bug 205465 depends on bug 205433, which changed state.
> ...



If that's relevant, then it sure isn't fixed for me.


----------



## usdmatt (Jan 30, 2016)

I've installed MySQL hundreds of times and pretty much just done the following:


```
# pkg install mysql56-server
***add to /etc/rc.conf***
# service mysql-server start
```
It has built-in defaults and has never needed any configuration after install unless they've just made massive changes in 5.7 (I haven't actually used 5.7 yet). I would be surprise if this is an issue with 'MySQL configuring itself' as it should run fine without configuration.

Having said that, it does annoy me that it does not seem to install configuration files by default. When I do want to make changes, I end up scouring around for a sample config and creating it from scratch. I really wish the port would install default config samples (which are in the source tarball) into /usr/local/etc/mysql, and update the rc.d script to point MySQL at /usr/local/etc/mysql/my.cnf. The package is just a pre-compiled port so the package will always just do what the port does.

Since trying to remove all the default databases you're now getting a different error, which suggests it is failing to re-create them in /var/db/mysql. Make sure the /var/db/mysql directory is definitely gone. If it still fails the precmd I would manually try and see why it's failing. In the script, the precmd routine just runs mysql_create_auth_tables. This function creates the databases, then chowns them to the mysql user. If you temporarily change this function as follows, then run `service mysql-server start` it will output the command rather than run it.


```
-     eval $mysql_install_db $mysql_install_db_args >/dev/null 2>/dev/null
+     echo "$mysql_install_db $mysql_install_db_args >/dev/null 2>/dev/null"
```
You can then run this manually (without the /dev/null redirects on the end) and see what happens. If that succeeds then you can also try the other command it runs, `chown -R mysql:mysql /var/db/mysql` to make sure there's no problem with the mysql user. If both those commands work without error then there's no reason the precmd routine should be failing.


----------



## nonTechnical (Jan 31, 2016)

Thanks, I've rolled back the server and tried installing the 5.6 package (I tried the 5.7 port and package as per above posts), so that I can follow your advice.

This time, starting mysql56-server immediately gave the precmd error:


```
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
```

Making the change you suggested to /usr/local/etc/rc.d/mysql-server, and running it without sudo(8), confirms your comments:


```
$ service mysql-server start
/usr/local/bin/mysql_install_db --basedir=/usr/local --defaults-extra-file=/var/db/mysql/my.cnf --datadir=/var/db/mysql --force >/dev/null 2>/dev/null
chown: /var/db/mysql/mysql: Permission denied
chown: /var/db/mysql/test: Permission denied
chown: /var/db/mysql/performance_schema: Permission denied
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql
```

After returning /usr/local/etc/rc.d/mysql-server to normal, `$ sudo service mysql-server start` gives:


```
Starting mysql.
```

but `$ sudo service mysql-server status` gives:


```
mysql is not running.
```

The .pid is being created in /var/db/mysql.

The error log /var/db/mysql/freebsd-1gb-sfo1-01b-dev-db.err shows:


```
160130 18:02:37 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2016-01-30 18:02:37 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details
).
2016-01-30 18:02:37 0 [Note] /usr/local/libexec/mysqld (mysqld 5.6.27) starting as process 4056 ...
2016-01-30 18:02:37 4056 [Note] Plugin 'FEDERATED' is disabled.
2016-01-30 18:02:37 4056 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-30 18:02:37 4056 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-30 18:02:37 4056 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-30 18:02:37 4056 [Note] InnoDB: Memory barrier is not used
2016-01-30 18:02:37 4056 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-01-30 18:02:37 4056 [Note] InnoDB: Using CPU crc32 instructions
2016-01-30 18:02:37 4056 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-01-30 18:02:37 4056 [Note] InnoDB: Completed initialization of buffer pool
2016-01-30 18:02:37 4056 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 35
2016-01-30 17:56:30 3886 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
```

The last two error messages are repeated after those initial ones.

I've just noticed that when I `$ sudo service mysql-server stop`, the stdout is:
mysql not running? (check /var/db/mysql/webserver01.pid).

*If there's nothing you can see that's out of the ordinary before this, then my thinking is that mysql isn't running properly because of Digital Ocean's setup - for whatever reason the .pid is being named not after the hostname but the DO droplet/server name freebsd-1gb-sfo1-01b-dev-db.pid*

(BTWs, I agree - it's stupid that the sample config files aren't installed, and I couldn't even `find` them in the uncompressed dist source file after you mentioned them being in it. It made it a lot easier in the past to simply be able to copy the most relevant file and tweak it.)


----------



## usdmatt (Jan 31, 2016)

OK, turn off mysql in /etc/rc.conf so it doesn't try and start, then reboot the machine.
Make sure /var/db/mysql is gone completely.

Run the following command to recreate /var/db/mysql and the default databases and confirm that it completes without error.

```
/usr/local/bin/mysql_install_db --basedir=/usr/local --defaults-extra-file=/var/db/mysql/my.cnf --datadir=/var/db/mysql --force
```
 Make sure that you now have a /var/db/mysql directory containing the default database directories.

Then run the chown command to make sure the entire directory tree is owned by mysql. Make sure this completes without error as well.

```
chown -R mysql:mysql /var/db/mysql
```
 If you look at /var/db/mysql now with `ls -l` you should see the owner and group as mysql on /var/db/mysql itself, and everything under that directory.

If all still good, try running mysql, you'll need to use the `onestart` command as it's currently disabled in rc.conf.

```
service mysql-server onestart
```
 See if it's running with either the `service mysql-server status` command or just simply looking for mysql in `ps ax` output. If it appears in ps list, but the status command says not running, then there's a problem with the pid file.

If it doesn't run at all, show what it put into the log. Assuming mysql owns the /var/db/mysql directory, and mysql isn't running in the first place, there's no reason it should have a problem locking ibdata1.


----------



## nonTechnical (Jan 31, 2016)

Um...

It turned out the problem was with DO's config file, which I assumed was somewhere but I had forgotten about, and it wasn't in their docs.

It had to have the hostname updated in it, as it was set to the droplet name - it appears - after the droplet creation. As soon as the file was re-read and the hostname updated, /usr/local/etc/rc.d/mysql-server used the true hostname (the OS one), and mysql ran properly, with `$ sudo service mysql-server status`showing mysql's pid.

Yip f--ing hooray!

A DO rep said they're going to add docs about their freebsdFreeBSD install, droplet.conf, and more. They've flicked me credits to make up for the waste of time, but I'm going to have to spend a few nights making this up now.

Thanks to all for their help, in particular usdmatt.

Yup, I feel like such a nub and am in so many ways. So much to do - servers, dev, and more.

I still stand by what I wrote in another thread - mysql should be blamed for all noob and 'expert' mistakes.


----------



## tingo (Jan 31, 2016)

PEBCAK, user mistakes - most people learn from them and move on. Blaming is not a good way to learn.


----------



## kpa (Jan 31, 2016)

TBH, correctly set hostname is way way important for many services such as sendmail, it's just not emphasized well enough anywhere in the existing documentation. It is mentioned casually in the handbook but consequences of leaving it misconfigured are not covered anywhere.


----------

