Installing MySQL in Home Directory

Question

I'm having a bear of a time getting my new MySQL installation to work when following these instructions. I'm pretty sure the instructions at that link are not 100% correct/complete.

I have successfully installed MySQL in $HOME/mysql/ and I have successfully started it with cd $HOME/mysql; mysqld_safe --defaults-file=$HOME/mysql/my.cnf --socket=$HOME/mysql/mysql.sock --port={myport} &

When I try to set the root password with mysqladmin -u root password "aPasswordForRoot" -P {myport} -S $HOME/mysql/mysql.sock, I get an Access denied for user 'root'@'localhost error. Note - I MUST include the port and socket here - something that's not shown in the previously-linked instructions.

I can log in with the root user if I start MySQL with cd $HOME/mysql; mysqld_safe --defaults-file=$HOME/mysql/my.cnf --socket=$HOME/mysql/mysql.sock --port={myport} --skip-grant-tables &. I've found some instructions for resetting the root password, but when I follow them, mysql tells me that 0 rows were affected by the query. Here's the query I'm using to try to reset the password: use mysql; update user set password=PASSWORD("somepassword") where User="root";

I've tried logging in as root with no password and with the passwords I tried to set following the instructions in the forum. I'd like to get this running and connect MySQL WorkBench up to it via an SSH tunnel, if at all possible.

Accepted Answer

2

Stop your MySQL instance, then try re-initializing your MySQL data directory as follows:

cd ~/mysql
mkdir tmp
mv data data.old
mysql_install_db --basedir=$HOME/mysql --datadir=$HOME/mysql/data --tmpdir=$HOME/mysql/tmp
cd $HOME/mysql; mysqld_safe --defaults-file=$HOME/mysql/my.cnf &
mysqladmin -u root -P $MY_PORT -S $HOME/mysql/mysql.sock password "aPasswordForRoot"

Once you've done that, you should be able to establish a tunnel and connect to your MySQL server.

Hope that helps!

permanent link

answered 20 Oct '10, 18:03

seanf
12.2k●4●19●36
accept rate: 37%

edited 20 Oct '10, 18:03

The main difference here is the addition of the tmp directory. I've incorporated that into the original forum post.

(20 Oct '10, 18:08) seanf

Awesome, Sean. Thanks a ton! You guys are the best! This answer worked without a hitch, and I'm now connected to my own MySQL installation at WebFaction via MySQL Workbench with an SSH tunnel.

(21 Oct '10, 07:03) Tex

How does one stop the mysql instance?

(01 Nov '10, 02:50) sxalexander

1

"killall mysqld" will kill the private mysql server

Hope that helps!

(01 Nov '10, 03:01) neeravk

I'd like to set up a cron job to back the new databases up. Do the instructions in the WebFaction docs need to be adjusted to work with the new MySQL instance? If so, would it be possible to get some guidance?

(18 Nov '10, 08:06) Tex

You will simply have to add an extra parameter '-S $HOME/mysql/mysql.sock' , in order to connect to your own database server, rather than the shared one. This extra argument should work for any mysql/mysqldump commands you run.

(18 Nov '10, 08:26) tie

I wasn't able to start MySQL. What does "<< EOF" do/mean?

(23 Mar '11, 13:49) sevenupcan

In the following conext...

cat > $HOME/mysql/my.cnf << EOF
[mysqld]
port=$MY_PORT
socket=$HOME/mysql/mysql.sock
basedir=$HOME/mysql
datadir=$HOME/mysql/data

[client]
port=$MY_PORT
socket=$HOME/mysql/mysql.sock
EOF

... it means "write everything I'm about to type into $HOME/mysql/my.cnf and stop writing when I type EOF".

If your my.cnf contains "<< EOF" then delete it, it doesn't belong in that file.

(23 Mar '11, 14:01) seanf

Ah. I understand now. Unfortunately I'm still having trouble starting database. I get the following error when using cd $HOME/mysql; mysqld_safe --defaults-file=$HOME/mysql/my.cnf &:

mkdir: cannot create directory $HOME/mysql': No such file or directory chown: cannot access$HOME/mysql': No such file or directory chmod: cannot access `$HOME/mysql': No such file or directory

Any ideas on what I'm doing wrong?

(24 Mar '11, 08:12) sevenupcan

showing 5 of 9 show 4 more comments

Answer 2

I was having similar issues with install Mysql 5.1 in my home folder.

Whenever I would attempt to connect locally with the mysql command, I would get:

Can't connect to local MySQL server through socket '/tmp/mysql.sock'

Running mysql --help alerted me to this:

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /home/$HOME/mysql/etc/my.cnf ~/.my.cnf

So I altered the tutorial and replaced references to $HOME/mysql/my.cnf to $HOME/mysql/etc/my.cnf

invoking mysql now checks the appropriate locations for my.cnf and works without a hitch.

Installing MySQL in Home Directory

Follow this question

Related questions