1

I have the following issue. When mysql is started as service all works fine: e.g.

oleg: ~$ sudo service mysql start 
mysql start/running, process 9111
oleg: ~$

But when I am doing start via command line nothing works. The config itself does not look incorrect on the path below:

oleg: ~$ cat /tmp/tmpWtpvgM/etc/my.cnf 
[mysqld]
pid-file=/tmp/tmpWtpvgM/tmp/mysqld.pid
datadir=/tmp/tmpWtpvgM/var
port=3308
tmpdir=/tmp/tmpWtpvgM/tmp
socket=/tmp/tmpWtpvgM/tmp/mysql.sock



    oleg: ~$ /usr/sbin/mysqld --defaults-file=/tmp/tmpWtpvgM/etc/my.cnf
    --user=root 
2015-11-26 12:18:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use
    --explicit_defaults_for_timestamp server option (see documentation for more details). 
2015-11-26 12:18:24 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27-0ubuntu0.14.04.1) starting as process 8448 ... 

2015-11-26 12:18:24 8448 [Warning] One can only use the --user switch if running
    as root

    2015-11-26 12:18:24 8448 [Warning] Buffered warning: Changed limits:
    max_open_files: 1024 (requested 5000)

    2015-11-26 12:18:24 8448 [Warning] Buffered warning: Changed limits:
    table_open_cache: 431 (requested 2000)

    2015-11-26 12:18:24 8448 [Note] Plugin 'FEDERATED' is disabled.
    /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist 
2015-11-26
    12:18:24 8448 [ERROR] Can't open the mysql.plugin table. Please run
    mysql_upgrade to create it. 
2015-11-26 12:18:24 8448 [Note] InnoDB:
    Using atomics to ref count buffer pool pages 
2015-11-26 12:18:24 8448
    [Note] InnoDB: The InnoDB memory heap is disabled 
2015-11-26 12:18:24
    8448 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2015-11-26 12:18:24 8448 [Note] InnoDB: Memory barrier is not used

2015-11-26 12:18:24 8448 [Note] InnoDB: Compressed tables use zlib
    1.2.8 
2015-11-26 12:18:24 8448 [Note] InnoDB: Using Linux native AIO 
2015-11-26 12:18:24 8448 [Note] InnoDB: Using CPU crc32 instructions
    2015-11-26 12:18:24 8448 [Note] InnoDB: Initializing buffer pool, size
    = 128.0M 
2015-11-26 12:18:24 8448 [Note] InnoDB: Completed initialization of buffer pool 
2015-11-26 12:18:24 8448 [Note] InnoDB:
    Highest supported file format is Barracuda. 
2015-11-26 12:18:24 8448
    [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463

2015-11-26 12:18:24 8448 [Note] InnoDB: Database was not shutdown
    normally!
2015-11-26 12:18:24 8448 [Note] InnoDB: Starting crash
    recovery. 
2015-11-26 12:18:24 8448 [Note] InnoDB: Reading tablespace
    information from the .ibd files... 
2015-11-26 12:18:24 8448 [Note]
    InnoDB: Restoring possible half-written data pages  
2015-11-26
    12:18:24 8448 [Note] InnoDB: from the doublewrite buffer... InnoDB:
    Doing recovery: scanned up to log sequence number 1600607 
2015-11-26
    12:18:24 8448 [Note] InnoDB: Starting an apply batch of log records to
    the database... InnoDB: Progress in percent: 48 49 50 51 52 53 54 55
    56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
    79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
    InnoDB: Apply batch completed 
2015-11-26 12:18:24 8448 [Note] InnoDB:
    128 rollback segment(s) are active. 
2015-11-26 12:18:24 8448 [Note]
    InnoDB: Waiting for purge to start 
2015-11-26 12:18:24 8448 [Note]
    InnoDB: 5.6.27 started; log sequence number 1600607 
2015-11-26
    12:18:24 8448 [Note] Server hostname (bind-address): '*'; port: 3308

2015-11-26 12:18:24 8448 [Note] IPv6 is available. 
2015-11-26 12:18:24
    8448 [Note]   - '::' resolves to '::'; 
2015-11-26 12:18:24 8448 [Note]
    Server socket created on IP: '::'. 
2015-11-26 12:18:24 8448 [ERROR]
    Fatal error: Can't open and lock privilege tables: Table 'mysql.user'
    doesn't exist

Still could not find how to run mysql under ubuntu 14.04 and custom config :(

also, I don't seem to be able to call /usr/bin/mysql_install_db command, in order to setup database in new datadir

e.g.

oleg: /usr/share/mysql$ /usr/bin/mysql_install_db --verbose
--defaults-file=/home/oleg/testing/etc/my.cnf --basedir=/usr/share/mysql --user=root 
 FATAL ERROR: Could not find my-default.cnf

 If you compiled from source, you need to run 'make install' to copy
the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top level
of the extracted archive, or pass the --basedir option pointing to
that location.

oleg: /usr/share/mysql$
  • What are you trying to achieve by doing this ? – user9517 Nov 26 '15 at 10:48
  • Well actually this command is called by testing.mysqld module (pypi.python.org/pypi/testing.mysqld) which is responsible for creating testing databases for tests... – Oleg Nov 26 '15 at 11:33
  • run as root? > sudo /usr/sbin/mysqld ... – jerichorivera Nov 26 '15 at 13:02
1
+100

You probably installed mysql packages provided by Ubuntu repos. I recommend you to install mysql-apt-config or download the DEB files directly from MySQL downloads site. You will find the latest 5.6.28 there instead of the 5.6.27 version.

Once you install the mysql-community-* packages you will be able to initialize the datadir and be able to startup a new instance of mysqld on a different datadir.

sudo /usr/bin/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

Then start mysqld like this:

sudo /usr/sbin/mysqld --defaults-file=/etc/my.cnf --user=mysql

Here's my sample config file:

cat /etc/my.cnf 
[mysqld]
basedir=/usr
datadir=/srv/data
pid-file=/srv/data/mysqld.pid
port=3308
socket=/srv/data/mysql.sock

And to prove it is using the said datadir:

mysql> select @@datadir;
+------------+
| @@datadir  |
+------------+
| /srv/data/ |
+------------+
1 row in set (0.00 sec)

EDIT: Your mysql_install_db error could be related to bug #70067.

Quick workaround if you don't want to install any packages you can create this file and run mysql_install_db again.

cat /usr/share/mysql/my-default.cnf 
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/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 

Just make sure to use --user=mysql and not --user=root for /usr/bin/mysql_install_db or /usr/sbin/mysqld

  • Actually yes (regarding the EDIT: Your mysql_install_db error could be related to bug #70067). I have also came to the same conclusion. And after adding missing my-default.cnf file the mysql_install_db started to work – Oleg Dec 28 '15 at 7:25
0

The issue lies in the configurations. Replace this

pid-file=/tmp/tmpWtpvgM/tmp/mysqld.pid
datadir=/tmp/tmpWtpvgM/var
port=3308
tmpdir=/tmp/tmpWtpvgM/tmp
socket=/tmp/tmpWtpvgM/tmp/mysql.soc

with

pid-file=/tmp/tmpWtpvgM/tmp/mysqld.pid
datadir=/tmp/tmpWtpvgM/var
port=3308
tmpdir=/tmp/tmpWtpvgM/tmp
socket=/tmp/tmpWtpvgM/tmp/mysql.sock
  • Sorry, what is the difference? – Oleg Nov 26 '15 at 11:31
  • you are giving wrong name of the sock file, i-e mysql.soc change it to mysql.sock :) – Hammad Raza Nov 26 '15 at 11:38
  • Ah, that was just incomplete copy/paste. Sorry, fixed it in the description already. – Oleg Nov 26 '15 at 12:13
0

MySql has its precompiled location to look for configuration files and data. So your best bet is to compile mysql from source for yourself if you want to customize everything. Otherwise it can be messy.

According to your custom my.cnf file your new datadir is datadir=/tmp/tmpWtpvgM/var, and I guess it is empty, and that's why you are getting this error.

Either you should try to move your mysql data from it's default location (/var/lib/mysql) to your custom location first and then try to start mysql.

Or try to create it like this:

mysql_install_db --user=mysql --basedir=/tmp/tmpWtpvgM --datadir=/tmp/tmpWtpvgM/var

Check and make sure appropriate directory permission.

Check alos the warning you have got: One can only use the --user switch if running as root

So run the commands as root user.

Here are two very relevant posts:

https://stackoverflow.com/questions/4634163/changing-default-my-cnf-path-in-mysql https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory

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.