Sep 112006
 

Sponsored Link

MySQL is a widely used and fast SQL database server. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries.

Installing Mysql database in Ubuntu

#apt-get install mysql-server mysql-client libmysqlclient12-dev

MySQL initially only allows connections from the localhost (127.0.0.1). We'll need to remove that restriction if you wish to make it accessible to everyone on the internet. Open the file /etc/mysql/my.cnf

#vi /etc/mysql/my.cnf

Find the line bind-address = 127.0.0.1 and comment it out
...
#bind-address = 127.0.0.1
...

You can check your configuration using the following command

#netstat -tap

Output Looks like below

tcp 0 0 *:mysql *:* LISTEN 4997/mysqld

MySQL comes with no root password as default. This is a huge security risk. You'll need to set one. So that the local computer gets root access as well, you'll need to set a password for that too. The local-machine-name is the name of the computer you're working on. For more information see here

#mysqladmin -u root password your-new-password

#mysqladmin -h root@local-machine-name -u root -p password your-new-password

#/etc/init.d/mysql restart

If you are looking how to create database,tables and other things check here

If you are looking for webinterface administration or GUI tools for your mysql database check here

Sponsored Link

 Posted by at 3:31 pm
  • Lois

    Hi!
    I have installed mysql server on my Ubuntu Desktop; and works fine, but I want to change de mysql datadir. Do yo know how can I change that? If I edit my.conf the line datadir for my directori, mysql never restarts.
    The new datadir is with the same permissions, and content.

    Any ideas? Wy in apache is easy to change but in mysql not?

  • Admin

    Hi Lois,

    Here is the procedure how to change Mysql default data directory

    By default, MySQL’s datadir is placed in the /var/lib/mysql directory.

    Create the directory that will be new datadir (ex:-/home/db)

    chown the directory to the mysql:mysql user

    sudo chown -R mysql:mysql /home/db/*

    You need to stop the mysql server using the following command

    sudo /etc/init.d/mysql stop

    Now you need to edit the /etc/mysql/my.cnf file

    sudo vi /etc/mysql/my.cnf

    and look for “datadir = /var/lib/mysql” this si where mysql database default data directory here you need to change this one to your new directory

    datadir = /home/db

    copy the files from the old datadir to the new location. However, make sure that the files named
    ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location.

    Make sure that the files and directories are owned by mysql user

    Make changes in the my.cnf to point the new datadir.

    Restart the MySQL database

    sudo /etc/init.d/mysql start

    hope this helps

  • Lois

    Yeah!!! you’re my hero! my problem was the direcotri privilegies in the new datadir; but that’s it with the chown -R mysql:mysql.
    Do it after copying the files.

    Thanks for all!

  • http://no Tanya

    why can not I use
    sudo /etc/mysql/my.cnf

    and I have /etc/my.cnf
    so I used
    sudo /etc/my.cnf
    it said command not found

    I did not find “datadir = /var/lib/mysql” in the file my.cnf either.

    I want to change the directory of the database, because not enough disk space left.
    anybody knows , Please help me, Thanks a lot.

  • Admin

    you need to make sure you have vi editor installed in your machine otherwise install vi using the following command

    #apt-get install vim

    Now you need to edit the file using the following command

    sudo vi /etc/mysql/my.cnf

    If you install mysql in debian or ubuntu from packages you will find the above file without any problem.

    Please give us which operating system you are running and how did you installed mysql server in your machine.

  • nickrud

    Since apparmor is now standard in ubuntu (at least since hardy, which is the release I’m working with now) you will also have to make a change to /etc/apparmor/apparmor.d/usr.sbin.mysqld as well in order to change the datadir. I needed to add the lines

    /home/mysql/ r,
    /home/mysql/** rwk,

    to enable the correct access permissions to the new data directory.

  • Chris Chua

    Thank you so so so so soooo much, nickrud. Your comment saved me. I’ve been trying this for the past 3 hours and it’s 5am (over here) now. I wonder where to look for such gotchas like that… How would a newbie like me know about apparmor? It’s even a standard and I had no clue it existed…

  • HaC|away

    Another thing you should not forget is to restart /etc/init.d/apparmor after changing the /etc/apparmor.d/usr.sbin.mysqld file. I wondered why it did not work ;)

  • sarang

    Hey

    One of our application developer requested to change the ownership of my.cnf file from root to mysql user and mysql group.

    I was thinking about it could not think of any reason why I cannot do this.

    Would this be ok or is there a reason why we shoulc have /etc/my.cnf owned by root.

    Let me know.
    Thanks,
    SArang

  • sergey

    Thanks! It works!!!

  • Somnuk

    Greate nickrud, you really help me.

  • Anton

    Thanx!!! It almost worked… BUT I have a problem: after I configured directory privileges and apparmor and MYSQL conf file – everything seems to work fine. BUT the problem is – I can not create NEW DATABASES – it tells me error#13. Does anybody know something about this problem?

    Thanks in advance! :)

  • http://www.rabindrakarki.com.np Rabindra

    thanks for the solution

  • Grant

    Hello

    I am getting some errors that I cannot understand/resolve:

    I am trying to reset the root password for mysql and this happens

    mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Access denied for user ‘root’@'localhost’ (using password: NO)’

    How do I fix this.

  • Simon Taylor

    Hi,

    I get the same error:
    mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

    Don’t get it!

    Cheers

  • Grant

    Hello,

    How are you logging into mysql. Is it something like this?

    $ mysql -u root -p

  • Henk

    Hi, I am trying to use an alternate my.cnf file(/etc/mysql/my2.cnf), but the “CONF=/etc/mysql/my2.cnf” statement in /etc/init.d/mysql does not appear to do anything, and the original file (/etc/mysql/my.cnf) is used instead.

    Any ideas?

  • Omar

    Hi,
    i have installed mysql server and it’s working fine, any idea how i can disable it from auto starting as a service.
    i am new to ubuntu so maybe this is an ubuntu thing!
    thanks in advance

  • http://muhammadhaf1z.wordpress.com muhammad-hafiz

    Hi.
    I have a question. After I do #bind=127.0.0.1 and in my.cnf and put a new line in /etc/hosts like this:

    127.0.0.1 (hostname) localhost.localdomain localhost
    127.0.0.1 ubuntuseekers me.newdomain.com me (alias) <- new line in /etc/hosts

    After done 2 steps above, I still can't open the new domain at another PC?
    Can you assist me, and please e-mail me your answers.
    Thanks in advance ;)

  • iyanla

    Hi,
    I want to edit innodb_lock_wait_timeout from the default value of 50 to 500.
    But it seemed like i cant change it. I tried this using these command. sudo vi /etc/mysql/my.cnf and paste innodb_lock_wait_timeout=500

    Please help.
    Thanks

  • superkey

    Grant and Simon Taylor, you must start the daemon before logging in; remember, you are trying to log into the MySQL database management, not the MySQL user.

  • http://techvineyard.blogspot.com Alexis

    The 2nd comment did the trick for me. You’re my hero too.

  • Randy

    Okay so here goes a greenhorn looking for some help. I have installed Ubuntu 10.10 on a virtual drive (using VMWare). When I installed the ISO onto the virtual drive, it does not install LAMP, openSSH sevrer nor mySQL. Is there a way to manually install this from the Ubuntu 10.10-AMD64 ISO or the DvD I wrote? I am using the VMWare on a Windows 7 environment. I can get to the point of pinging the outside world, so I am good to that point. Any help is appreciated.

  • http://localhost DaveE

    Another Greenhorn. I have a LAMP installed on my Windows XP that I have had running for years, configuring websites before uploading them online. Now, having Meerkat 10.10 installed (dual booting with my XP) along with a LAMP, I have Apache2 and PHP working. To do this, I had to mount my existing Windows NTFS partition so it would recognize my websites under localhost, which I repointed from /var/www to my existing website directory /media/Sokkit/.

    Now, after installing MySql, I cannot get it to recognize my existing databases, already installed in XP under the same partition: /media/Sokkit/Sokkit/mysql4/data. Can someone advise what configurations are required to repoint the default database directory to my existing one, along with any permissions I might have to change on the databases? Being a Greenhorn, I would really appreciate if I need to change anything, if you would provide how I can find the existing settings too, so I know what I am changing to what?

    Thanks,