# What creates MySQL's DB Schema Tables?



## rtwingfield (Feb 16, 2016)

I have twice attempted to install the /usr/ports/databases/mysql57-server with `make install`.  All seems to progress as advertised; however, the db schema tables are not created in /var/db/mysql/.  What am I missing here?  I expected the complement of tables to be created, but no.  So . . .what creates them?  Is there a shell script hidden somewhere that needs to be run?    (Never had this problem before.  Running FreeBSD 10.2-RELEASE.  Can't search this forum, Google search, ad nauseam to find an answer.)


----------



## rtwingfield (Feb 16, 2016)

. . .ok   . . .on a whim, I decided to try `service mysql-server start` and voilà, there are these files.   Like my late friend, Pat Henry used to say, "This is going to make perfect sense . . .once I understand it."

So, apparently, simply starting the service _automagically_ created the support files, and the db schema tables in /var/db/mysql/mysql.  Geez . . .let's see . . .what can we do to make this more obtuse   


FYI:


```
/var/db/mysql # ls -ls
total 123072
  4 -rw-r-----  1 mysql  mysql  56 Feb 15 21:36 auto.cnf
  4 -rw-r-----  1 mysql  mysql  3294 Feb 15 21:37 bravo.foobar.net.err
  4 -rw-r-----  1 mysql  mysql  6 Feb 15 21:36 bravo.foobar.net.pid
  4 -rw-------  1 mysql  mysql  1675 Feb 15 21:36 ca-key.pem
  4 -rw-r--r--  1 mysql  mysql  1074 Feb 15 21:36 ca.pem
  4 -rw-r--r--  1 mysql  mysql  1078 Feb 15 21:36 client-cert.pem
  4 -rw-------  1 mysql  mysql  1679 Feb 15 21:36 client-key.pem
  4 -rw-r-----  1 mysql  mysql  408 Feb 15 21:36 ib_buffer_pool
49184 -rw-r-----  1 mysql  mysql  50331648 Feb 15 21:36 ib_logfile0
49184 -rw-r-----  1 mysql  mysql  50331648 Feb 15 21:36 ib_logfile1
12320 -rw-r-----  1 mysql  mysql  12582912 Feb 15 21:36 ibdata1
12320 -rw-r-----  1 mysql  mysql  12582912 Feb 15 21:36 ibtmp1
  4 drwxr-x---  2 mysql  mysql  2048 Feb 15 21:36 mysql
  0 -rw-rw----  1 mysql  mysql  0 Feb 15 21:36 mysqld_safe.pid
  4 drwxr-x---  2 mysql  mysql  4096 Feb 15 21:36 performance_schema
  4 -rw-------  1 mysql  mysql  1679 Feb 15 21:36 private_key.pem
  4 -rw-r--r--  1 mysql  mysql  451 Feb 15 21:36 public_key.pem
  4 -rw-r--r--  1 mysql  mysql  1078 Feb 15 21:36 server-cert.pem
  4 -rw-------  1 mysql  mysql  1675 Feb 15 21:36 server-key.pem
  8 drwxr-x---  2 mysql  mysql  4608 Feb 15 21:36 sys
```

. . .and


```
/var/db/mysql/mysql # ls -ls
total 4804
  2 -rw-r-----  1 mysql  mysql  858 Jan 15 19:58 columns_priv.MYD
  8 -rw-r-----  1 mysql  mysql  8192 Feb  1 17:31 columns_priv.MYI
  10 -rw-r-----  1 mysql  mysql  8820 Jan  6 13:14 columns_priv.frm
  6 -rw-r-----  1 mysql  mysql  4880 Jan 15 19:58 db.MYD
  6 -rw-r-----  1 mysql  mysql  5120 Feb  1 17:31 db.MYI
  10 -rw-r-----  1 mysql  mysql  9582 Jan  6 13:14 db.frm
  10 -rw-r-----  1 mysql  mysql  8780 Jan  6 13:14 engine_cost.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 engine_cost.ibd
  0 -rw-r-----  1 mysql  mysql  0 Jan  6 13:14 event.MYD
  2 -rw-r-----  1 mysql  mysql  2048 Jan  6 13:14 event.MYI
  10 -rw-r-----  1 mysql  mysql  10223 Jan  6 13:14 event.frm
  0 -rw-r-----  1 mysql  mysql  0 Jan  6 13:14 func.MYD
  2 -rw-r-----  1 mysql  mysql  1024 Jan  6 13:14 func.MYI
  10 -rw-r-----  1 mysql  mysql  8665 Jan  6 13:14 func.frm
  2 -rw-r-----  1 mysql  mysql  35 Jan  6 13:14 general_log.CSM
  0 -rw-r-----  1 mysql  mysql  0 Jan  6 13:14 general_log.CSV
  10 -rw-r-----  1 mysql  mysql  8776 Jan  6 13:14 general_log.frm
  10 -rw-r-----  1 mysql  mysql  8784 Jan  6 13:14 gtid_executed.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 gtid_executed.ibd
  10 -rw-r-----  1 mysql  mysql  8700 Jan  6 13:14 help_category.frm
112 -rw-r-----  1 mysql  mysql  114688 Jan  6 13:14 help_category.ibd
  10 -rw-r-----  1 mysql  mysql  8612 Jan  6 13:14 help_keyword.frm
224 -rw-r-----  1 mysql  mysql  212992 Jan  6 13:14 help_keyword.ibd
  10 -rw-r-----  1 mysql  mysql  8630 Jan  6 13:14 help_relation.frm
160 -rw-r-----  1 mysql  mysql  163840 Jan  6 13:14 help_relation.ibd
  10 -rw-r-----  1 mysql  mysql  8770 Jan  6 13:14 help_topic.frm
2064 -rw-r-----  1 mysql  mysql  2097152 Jan  6 13:14 help_topic.ibd
  0 -rw-rw----  1 mysql  mysql  0 Jul 15  2011 host.MYD
  2 -rw-rw----  1 mysql  mysql  2048 Jul 15  2011 host.MYI
  10 -rw-rw----  1 mysql  mysql  9510 Jul 15  2011 host.frm
  14 -rw-r-----  1 mysql  mysql  12982 Jan  6 13:14 innodb_index_stats.frm
192 -rw-rw----  1 mysql  mysql  196608 Jan 15 09:21 innodb_index_stats.ibd
  10 -rw-r-----  1 mysql  mysql  8830 Jan  6 13:14 innodb_table_stats.frm
  96 -rw-rw----  1 mysql  mysql  98304 Jan 15 09:21 innodb_table_stats.ibd
  0 -rw-r-----  1 mysql  mysql  0 Jan  6 13:14 ndb_binlog_index.MYD
  2 -rw-r-----  1 mysql  mysql  1024 Jan  6 13:14 ndb_binlog_index.MYI
  10 -rw-r-----  1 mysql  mysql  8986 Jan  6 13:14 ndb_binlog_index.frm
  10 -rw-r-----  1 mysql  mysql  8586 Jan  6 13:14 plugin.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 plugin.ibd
304 -rw-r-----  1 mysql  mysql  293896 Jan  6 13:14 proc.MYD
  4 -rw-r-----  1 mysql  mysql  4096 Jan  6 13:14 proc.MYI
  10 -rw-r-----  1 mysql  mysql  9996 Jan  6 13:14 proc.frm
  2 -rw-r-----  1 mysql  mysql  898 Jan 15 19:58 procs_priv.MYD
  8 -rw-r-----  1 mysql  mysql  8192 Feb  1 17:31 procs_priv.MYI
  10 -rw-r-----  1 mysql  mysql  8875 Jan  6 13:14 procs_priv.frm
  2 -rw-r-----  1 mysql  mysql  693 Jan  6 13:14 proxies_priv.MYD
  6 -rw-r-----  1 mysql  mysql  5120 Jan  6 13:14 proxies_priv.MYI
  10 -rw-r-----  1 mysql  mysql  8800 Jan  6 13:14 proxies_priv.frm
  10 -rw-r-----  1 mysql  mysql  8692 Jan  6 13:14 server_cost.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 server_cost.ibd
  10 -rw-r-----  1 mysql  mysql  8838 Jan  6 13:14 servers.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 servers.ibd
  12 -rw-r-----  1 mysql  mysql  10908 Jan  6 13:14 slave_master_info.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 slave_master_info.ibd
  10 -rw-r-----  1 mysql  mysql  9468 Jan  6 13:14 slave_relay_log_info.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 slave_relay_log_info.ibd
  10 -rw-r-----  1 mysql  mysql  9364 Jan  6 13:14 slave_worker_info.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 slave_worker_info.ibd
  2 -rw-r-----  1 mysql  mysql  35 Jan  6 13:14 slow_log.CSM
  0 -rw-r-----  1 mysql  mysql  0 Jan  6 13:14 slow_log.CSV
  10 -rw-r-----  1 mysql  mysql  9016 Jan  6 13:14 slow_log.frm
  6 -rw-r-----  1 mysql  mysql  4495 Jan 15 19:58 tables_priv.MYD
  8 -rw-r-----  1 mysql  mysql  8192 Feb  1 17:31 tables_priv.MYI
  10 -rw-r-----  1 mysql  mysql  8955 Jan  6 13:14 tables_priv.frm
  10 -rw-r-----  1 mysql  mysql  8636 Jan  6 13:14 time_zone.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 time_zone.ibd
  10 -rw-r-----  1 mysql  mysql  8624 Jan  6 13:14 time_zone_leap_second.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 time_zone_leap_second.ibd
  10 -rw-r-----  1 mysql  mysql  8606 Jan  6 13:14 time_zone_name.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 time_zone_name.ibd
  10 -rw-r-----  1 mysql  mysql  8686 Jan  6 13:14 time_zone_transition.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 time_zone_transition.ibd
  10 -rw-r-----  1 mysql  mysql  8748 Jan  6 13:14 time_zone_transition_type.frm
  96 -rw-r-----  1 mysql  mysql  98304 Jan  6 13:14 time_zone_transition_type.ibd
  2 -rw-r-----  1 mysql  mysql  1468 Jan  7 17:45 user.MYD
  4 -rw-r-----  1 mysql  mysql  4096 Jan  7 18:12 user.MYI
  12 -rw-r-----  1 mysql  mysql  10816 Jan  6 13:14 user.frm
```


----------



## SirDice (Feb 16, 2016)

If you read the start script /usr/local/etc/rc.d/mysql-server you can see exactly what's happening:

```
if [ ! -d "${mysql_dbdir}/mysql/." ]; then
                mysql_create_auth_tables || return 1
        fi
```


```
mysql_install_db="/usr/local/bin/mysql_install_db"
mysql_install_db_args="--basedir=/usr/local --datadir=${mysql_dbdir} --force"

mysql_create_auth_tables()
{
        eval $mysql_install_db $mysql_install_db_args
        [ $? -eq 0 ] && chown -R ${mysql_user}:$(id -gn $mysql_user) ${mysql_dbdir}
}
```


----------



## rtwingfield (Feb 21, 2016)

This is probably my fifth or sixth install of MySQL.  Over the years, I don't recall this nuance being embedded in the start-up script.  Perhaps I'm just old and forgetful; regardless, clarification in the documentation would be helpful, rather than having to discover it by analysis of the startup script.  Frankly, at first (and second and third ...) I didn't attempt to start the mysql-server because after the install completed, I could not find the database, and I expected the server to fail to start because the database was missing.  (I'm and old-school programmer, and I look for these things first.)


----------



## SirDice (Feb 22, 2016)

For as long as I can remember the MySQL start scripts have always automatically created the initial databases when you first start it. Perhaps you never realized it?


----------



## rtwingfield (Feb 22, 2016)

You're probably right.  In the past, I probably just "started" the server following a successful install and never realized that the first-time startup installed the database tables.  Because of some other related problems, I looked for the database schema before attempting  a startup and became alarmed that they were not installed.  

Anyway, have a  on me (send me the tab.)


----------

