Sunday, 1 April 2012

Quick MySQL 5.6 manual instalation on Linux

Lately I've been building most software I need at work. Manually building from source you can minimize dependencies to what you need, and better familiarize yourself with all needed configuration files, than when installing from package - rpm, deb etc.

I got my src from this link.
At work I've been familiarized with PostgreSQL, so my dependency list will be similar to those standard for compiling Postgres : zlib, readline and ssl. CMake is needed for configuring source.

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DWITH_SSL=yes -DWITH_ZLIB=yes -DDEFAULT_CHARSET=utf8 -DWITH_READLINE=yes
make && make install

Prefix is for keeping everything in one place, no trashing in /etc or /usr.

Server starting script - needs to be copied to proper init folder
cp /opt/mysql/support-files/mysql.server /etc/init.d/

Server configuration - need to peek one cnf file as a base for customizing
cp  /opt/mysql/support-files/my-medium.cnf  /opt/mysql/mysql.cnf

I've set ownership to postgres user & group since I already had those in my system. I was curious if the developers considered using other user than default mysql - fortunately they did.
chown -R -h postgres.postgres /opt/mysql

I've added mysql lib folder to ld cache - just in case, couldn't find anything in /opt/mysql/bin that's linked against it.

echo /opt/mysql/lib > /etc/ld.so.conf.d/my.conf
ldconfig

Time to set proper paths and user in /opt/mysql/my.cnf

[client]
socket = /opt/mysql/mysql.sock

[mysqld]
collation-server = utf8_general_ci
user   = postgres
socket = /opt/mysql/mysql.sock

Same goes for the starting script /etc/init.d/mysql.server
#basedir=
basedir=/opt/mysql

#datadir=
datadir=/opt/mysql/data


# lockdir='/var/lock/subsys'
lockdir='/var/lock'


#mysqld_pid_file_path=
mysqld_pid_file_path=/opt/mysql/mysql.pid

Now we need to create the default database
cd  /opt/mysql 
./scripts/mysql_install_db --datadir=/opt/mysql/data --user=postgres --defaults-file=/opt/mysql/my.cnf

Now all that's left is to start the new server
/etc/init.d/mysql.server start

..and set password for user root
./bin/mysqladmin -u root -S mysql.sock password 'new-password'

To simply connect to server
./bin/mysql -S mysql.sock -u root -p

If you need a user with remote access - for example with MySQL Workbench - connect as root
mysql> use mysql
mysql> create user 'admin' identified by 'admin-pass';
mysql> grant all on *.* to 'admin';
mysql> flush privileges;
mysql> \q

What's weird is this won't work for socket and localhost.
I had to create user 'admin'@'127.0.0.1' and 'admin'@'localhost'.

mysql> select host, user  from mysql.user where user='admin';
+-----------+-------+
| host      | user  |
+-----------+-------+
| %         | admin |
| 127.0.0.1 | admin |
| localhost | admin |
+-----------+-------+


To use socket pointed in my.cnf
./bin/mysql --defaults-file=/opt/mysql/my.cnf -u admin -h localhost -p

No comments:

Post a Comment