WebFaction
Community site: login faq
0
1

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.

asked 20 Oct '10, 08:20

Tex
16159
accept rate: 0%

edited 20 Oct '10, 08:27


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.2k41936
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

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.

permanent link

answered 01 Nov '10, 14:38

sxalexander
8125
accept rate: 0%

edited 02 Nov '10, 16:09

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×168
×44
×40

question asked: 20 Oct '10, 08:20

question was seen: 7,263 times

last updated: 24 Mar '11, 08:12

WEBFACTION
REACH US
SUPPORT
AFFILIATE PROGRAM
LEGAL
© COPYRIGHT 2003-2019 SWARMA LIMITED - WEBFACTION IS A SERVICE OF SWARMA LIMITED
REGISTERED IN ENGLAND AND WALES 5729350 - VAT REGISTRATION NUMBER 877397162
5TH FLOOR, THE OLD VINYL FACTORY, HAYES, UB3 1HA, UNITED KINGDOM