MySQL: Install locally (not as root) from binaries

Under Ubuntu, I have tried to setup MySQL from binaries for a local user (not root) with another MySQL instance already running. This works, but is documented rather vaguely (I did not find anything that documented the whole process). So I will sum up the solution I have come up with here:

  • download a MySQL archive and extract it to /mysql/install
  • create the directory /mysql/data
  • create the directory /mysql/run
  • create a custom /mysql/my.cnf with the following content:

    [mysqld]
     
    basedir=/mysql/install
    datadir=/mysql/data
     
    socket=/mysql/run/socket
    pid-file=/mysql/run/pid
     
    port=33060
     
     
    [client]
    socket=/mysql/run/socket
    port=33060
     
     
    [mysqld_safe]
  • set an environment variables for ease of use

    export MYSQL=/mysql
    export MYSQL_INSTALL=$MYSQL/install
  • to install MySQL (prior to MySQL 5.7.7) run

    $MYSQL_INSTALL/scripts/mysql_install_db --defaults-file=$MYSQL/my.cnf

    for later versions run

    $MYSQL_INSTALL/bin/mysqld --defaults-file=$MYSQL/my.cnf --initialize-insecure
  • now you can start the server using

    $MYSQL_INSTALL/bin/mysqld_safe --defaults-file=$MYSQL/my.cnf &
  • to stop the server use

    $MYSQL_INSTALL/bin/mysqladmin --defaults-file=$MYSQL/my.cnf -u root -p shutdown --port 33060 # passwort is empty at the moment
  • to set the admin password (after starting the MySQL server) run

    $MYSQL_INSTALL/bin/mysqladmin --defaults-file=$MYSQL/my.cnf -u root password
  • running the suggested mysql_secure_installation you have to modify the bin/mysql_secure_installation script (source)

    make_config() {
        echo "# mysql_secure_installation config file" >$config
        echo "[mysql]" >>$config
        echo "user=root" >>$config
        esc_pass=`basic_single_escape "$rootpass"`
        echo "password='$esc_pass'" >>$config
        #sed 's,^,> ,' < $config  # Debugging
     
        # ADD THIS LINE
        echo "socket=/mysql/run/socket" >> $config
    }

    then you can simply run

    $MYSQL_INSTALL/bin/mysql_secure_installation
  • to connect to the MySQL server as root, run the following

    $MYSQL_INSTALL/bin/mysql --defaults-file=$MYSQL/my.cnf -u root -p

    or assuming that MySQL is already installed system-wide, there is a little less overhead:

    mysql --defaults-file=$MYSQL/my.cnf -u root -p

Now, the reason for using the --defaults-file option lies in the order of reading options files (e.g., /etc/my.cnf) regarding the --user option. In contrast to other options, MySQL will always use the --user option it reads first instead of using the option it reads last. Thus, if a global instance of MySQL is installed that sets the --user option and the corresponding options file is located in a default look-up directory (again, e.g., /etc/my.cnf), then the local installation will fail since switching users from the one specified by the global MySQL instance (e.g., in /etc/my.cnf) to the current user is blocked by MySQL (source). By setting the --defaults-file option, MySQL will only use the options file given by this parameter skipping all other look-up directories. Thus, this solves the problem.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Leave a Reply