Thursday, April 28, 2011

Installing MySQL 5.5.11 Generic Tar Binaries in Ubuntu

I decided to install MySQL from binary in my fresh Ubuntu 10.10 Maverick machine. I installed from binary since I do not want to depend on the packages from Ubuntu again and its nice to have a the newest version of MySQL.

1) Download the binary package from mysql.com. I chose to download  mysql-5.5.11-linux2.6-x86_64.tar.gz since that matches my system:
$> cd /usr/local/src/
$> wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-x86_64.tar.gz/from/http://mysql.he.net/
$> mv index.html mysql5.5.11.tar.gz
$> tar -xzvf mysql5.5.11.tar.gz 

Note: that when i did wget it named the file "index.html" for some reason so i had to rename it back to "mysql5.5.11.tar.gz" then untar.
Tip: if you dont know your system version I did: $>uname -m

2.) Copy the files to /usr/local
$> cp -R mysql5.5.11 /usr/local/mysql

NOTE: At first. I wanted to copy to /usr/local/lib/mysql but for some reason when I try to do the install command, I got some file pointer problems such as not finding mysqld_safe file so I just decided to stick to /usr/local/mysql directory which is the recommended installation directory according to mysql docs. I think some install codes may have hard coded paths to "/usr/local/mysql" hence the reason for not working.

2b.)Optional. If you really want to copy to /usr/local/lib/mysql or some other directory for example besides the usual /usr/local/mysql directory you need to copy the mysql directory to /usr/local/lib first then soft link to it but notice that I am still using the /usr/local/mysql destination. This is much better setup if you want to upgrade mysql later, you just change the soft link!:
$> cp -R mysql5.5.11 /usr/local/lib/mysql5.5.11
$> cd /usr/local
$> sudo ln -s lib/mysql-5.5.11/ mysql

3) Install other libraries:
$> sudo apt-get install libaio

Notes: I installed a couple more libraries that i ended up not using since I chose to install Mysql binary. No need to cmake/make/make install.

4) Make sure mysql user exists so create "mysql" as a group and user. I have to because of my new computer.
$> groupadd mysql
$> useradd -R -g mysql mysql

5) Copy bin files to /usr/local/bin from /usr/local/mysql/bin. I prefer to soft link instead.
$> ln -s /usr/local/mysql/bin/* /usr/local/bin/

6) Create the socket directory if it does not exist:
$> mkdir /var/run/mysqld
$> chown -R mysql:mysql /var/run/mysqld

7) Copy the sample MySql Configuration file to /etc/ and edit it.
$> cd /usr/local/mysql/support-files/
$> cp my-large.cnf /etc/my.cnf

Note: you can also copy it to /etc/mysql/my.cnf if you prefer. Now edit my.cnf:
$> vim /etc/my.cnf

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
log_error = /var/log/mysql/error.log
bind-address = xxx.xxx.xx.xx

Note: I chose to bind mine to a different IP since I plan to make this box as an exclusive db server. You dont have to do this.
Warning: Make sure you get the paths correctly or MySql will complain upon running the install script. Trust me on this s double check!

8) Copy the MySql Server startup script to /etc/init.d
$> cd /usr/local/mysql/support-files
$> cp mysql.server /etc/init.d/mysql
$> sudo chmod +x /etc/init.d/mysql


Now this will install it as a startup:$> update-rc.d mysql defaults


9) Change owner in /usr/local/mysql to "mysql" then install.
$> cd /usr/local/mysql
$> sudo chown -R mysql:mysql ./data

Now run the install script. Dont forget to add --user=mysql
$> sudo ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

Note: I need to add --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data in order for my install to work so just to make sure. Notice too that I only did this if I don't have the grants table yet because this is a fresh install so you may not need to do this if upgrading.

10) Start the server daemon. Hopefully everything works if not check the folder permissions in /data.
$> sudo mysqld_safe --user=mysql &

Also try to run $>mysqld --user=mysql & if you are having problems. If mysqld does not work try running as a regular user, not sudo.

Notes: Notice the "&" . If you don't add the ampersand sign you'll get stuck in a blank command line. It means run the script in the background.

Most of my problem with MySql not starting up is the /var/run/mysqld socket not generating so you may try running as below if MySql is being stubborn:
$> sudo /etc/init.d/mysql start

11) Do some test to make sure everything is fine.
$> ps aux | grep mysql

Should output something like:

mysql    19272  0.0  1.0 592836 85036 pts/0    Sl   18:04   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/usr/local/mysql/data/Franz-Ubuntu-DB.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

Then do some test like below and it should produce some result:
$> mysqladmin version
$> mysqladmin variables

Shutdown the server and start it again. You should not have any problems if you did it right
$> sudo mysqladmin -u root shutdown
$> sudo mysqld_safe --user=mysql &

Test also mysql startup
$> sudo /etc/init.d/mysql stop
$> sudo /etc/init.d/mysql start

12) If everything is good then change root's password. If you are not sure about the hostname below, skip this step and edit it in phpmyadmin if you have it installed. Much easier there.
$> mysqladmin -u root password "newpassword"
$> mysqladmin -u root -h host_name password "newpassword"

NOTE: if you are unfamiliar with what host you are it is usually "localhost" or 127.0.0.1
I also wrote a blog here http://kelmadics.blogspot.com/2011/06/notes-target-mysql-hosts.html if you want to create specific db for certain users and host.

13) Optional, create a new user that you can use to access from outside the localhost
$> mysql -u root -p

Now in mysql command line:
mysql> CREATE USER 'new-user-name'@'%' IDENTIFIED BY 'new-user-password';

GRANT ALL PRIVILEGES ON * . * TO 'new-user-name'@'%' IDENTIFIED BY 'new-user-password' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;


14) Optional. To take it further if you have PhpMyAdmin, edit its config.inc.php and add this to be able to easily administer MySql:
$i++
$cfg['Servers'][$i]['verbose'] = 'New Mysql Server';
$cfg['Servers'][$i]['host'] = 'xxx.xx.xx.xx';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'new-mysql-user';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['AllowNoPassword'] = false;


Thats it!

12 comments:

Steve Lewis said...

Thanks for this tutorial/howto it has really helped me in getting my MySQL installation up and running.

I really like Ubuntu but for my web server I wanted to use the latest stable version of Apache2, PHP and MySQL, I'm still not quite there but after reading and following this page at least I'm 33% nearer to gaol.

Many thanks

kelmadics said...

Glad I've helped!

Anonymous said...

Thanks Franz,
I was banging my head about how to get mysql to start at boot time, but your 'update-rc..' helped.
Thanks again,
Ravi Joshi

TZ said...

Thank you very much! I got a working system by following these steps! :)

Michele said...

F**K YEAH MAN!

Thank you very much, the best guide all over the web to install MYSQL 5.5.

Anonymous said...

Do you have a spam problem on this website; I also
am a blogger, and I was wondering your situation; many of us
have developed some nice practices and we are looking to swap techniques with others, why not
shoot me an e-mail if interested.

Feel free to visit my web blog: ยาลดความอ้วน

Anonymous said...

Windows XP Method In the event that you have older XP business software and own a
business. In case you haven't heard about it, The Social Network is all about the development of Facebook.



Have a look at my web page :: whatsapp for pc - ,

Anonymous said...

Aw, this was an incredibly nice post. Spending
some time and actual effort to make a superb article… but what can I say… I put things off a whole lot and don't seem to get nearly anything done.


Feel free to surf to my web site - clash of clans hack

Anonymous said...

What's up colleagues, its great piece of writing on the topic
of educationand entirely defined, keep it up all the time.


Here is my webpage; شركة نقل عفش بالرياض

Anonymous said...

Every weekend i used to go to see this website, as i want enjoyment,
since this this web site conations genuinely good funny stuff too.



My web-site This Guy

Anonymous said...

Hola! I've been following your website for a while now and finally got the
bravery to go ahead and give you a shout out from Dallas Tx!
Just wanted to say keep up the excellent job!

Feel free to surf to my website - frank kern and john reese ()

Anonymous said...

I've been exploring for a little for any high
quality articles or weblog posts in this sort of house .
Exploring in Yahoo I finally stumbled upon this site.
Reading this information So i am happy to show that I have a very good uncanny feeling I discovered just what I needed.
I such a lot definitely will make certain to do not overlook this website and give it a look on a continuing basis.


Review my web-site: Not working?