PDA

View Full Version : How to upgrade MySQL from version 3.x to 4.x w/o Root access



webgraph
11-18-2004, 12:12 AM
For all of you who have been looking for instructions on how to upgrade the default version of MySQL to one of the current versions, I'm pleased to say I just did it in about 1 hour (including FTP upload time).

Here's the instructions:

First go to the MySQL website and download the binaries that are correct for you (I used the Linux (x86, glibc-2.2, static, gcc) version).

For basic upgrades click the link for the Standard version to get to the mirror download page. Select anyone of the mirrors.

Once you've downloaded the binaries create a folder on your VPS where you will upload the file to. I created a folder inside of the /usr/home/*username*/ called mysql4. Now upload the binary file, I used my favorite FTP app.

Once you've uploaded the binary file to the server, login via SSH (I used terminal on OS X) and go to the folder where you uploaded the file. (for me: cd /usr/home/*username*/mysql4). Now run gunzip on the file:


gunzip mysql-standard-4.1.7-pc-linux-i686.tar.gz

I had to perform a tar command as well, the combination unzip didn't work for me.

SO:


tar xvf mysql-standard-4.1.7-pc-linux-i686.tar

That now creates a folder called:

mysql-standard-4.1.7-pc-linux-i686

Now change directories to: /usr/local (cd /usr/local)

Create a symbolic link to the binary files in the /usr/local/ folder.


ln -s /usr/home/*username*/mysql4/mysql-standard-4.1.7-pc-linux-i686 mysql417

Now change directories into the symbolic link. (cd mysql417) copy the following file to the /etc/rc.d/init.d/ directory using the following command.


cp /support-files/mysql.server /etc/rc.d/init.d/mysqld-new

*Note* that I added -new to the end of the filename to insure the default file was not overwritten by the copy. Allows you to revert to the default version down the road if needed.

Now change directories to: cd /etc/rc.d/init.d/ then change the permissions on that file to 755 (chmod 755 mysqld-new).

Now open the default mysqld file in your favorite file editor (vi is the default editor for westhost) vi mysqld

*Note*
If you need help with VI commands do a quick search on google, I found a number of sites with useful clues and information.

Now take note of the datadir on line 8, the default seems to be /var/lib/mysql. Now exit VI (esc, :q) and stop the old mysqld process (/etc/rc.d/init.d/mysqld stop). Then rename the default mysqld file (I renamed mine to mysqld_org) this will allow you to switch back to the default version of MySQL if you ever need to. (mv mysqld mysqld_org). Now change the name of the new version to take the place of the old (mv mysqld-new mysqld).

Now open the newly renamed mysqld file in your favorite editor (vi mysqld) and edit the following lines:

Line 42: "basedir=" this should be the path to the symbolic link that was created. For me it was: basedir=/usr/local/mysql417

Line 47: "datadir=/usr/local/mysql/data" this should be set to the datadir we took note of in the original mysqld file. For me it was: datadir=/var/lib/mysql

Line 51: "basedir=/usr/local/mysql" this should be set to the same as line 42 to, the base directory path (basedir=/usr/local/mysql417).

Line 123: "if test "$datadir" != "/usr/local/mysql/data"" this should be the same as line 47 (if test "$datadir" != "/var/lib/mysql")

Now save that file. There's one more change needed!

Change directories to: cd /etc Now open my.cnf in your favorite editor. Now add/insert a new line right after "[mysqld]" (should be line 6) and put the base dir line there (basedir=/usr/local/mysql417).

So line 6 & 7 should look like:


[mysqld]
basedir=/usr/local/mysql417

Now start mysql: /etc/rc.d/init.d/mysqld start

YOU ARE DONE!!!

In case you want to check your work, run the following:


mysqladmin ver

it will show you which version of MySQL is currently running.

Have fun, Enjoy!

Alex Rubin

wildjokerdesign
11-18-2004, 07:18 AM
Thanks Alex for the detailed breakdown of what you went through. I like that you put the steps you went through to find out certian things like the entry for datadir. In theory it should be the same on all are systems but you never know so haveing that step allows others to make sure.

There are two aliases in most all the .bashrc files on the accounts I have worked with and was wondering it they would help with unzipping the file.


alias untar="tar -xpvf"
alias untarzip="tar -zxpvf"
I am not familiar with SSH commands so not sure how or what each would be used for and have not really seen them mentioned by others. I know in the past I have had problems like you getting something unzipped and had to play with it. Just wonder if you or one of the others would be able to shed some light on those two aliases.

Thanks again for your work and time in letting others know about this. I noticed you updated other threads to let folks know you had done this... that really helps.

FZ
11-18-2004, 10:35 AM
Thanks for the write up, Alex. I just want to point out that WestHost might be getting us mySQL 4.0 soon - so for the less experienced, it's better to wait.

The other thing I want to mention is that instead of downloading the binary to one's own PC and then uploading that, one can download the binary straight to the WestHost server, like so (at the SSH "prompt"):


wget http://url/to/mysql-binary

Saves a lot of trouble for those of us that are on slower connections ;)

webgraph
11-18-2004, 11:41 AM
FZ, I tried that and there seemed to be an issue with the MySQL mirror sites and so it didn't work. If anyone gets it to work and had to do anything special, please post and let us know OR if it just works for anyone I'd be interested to know.

Thanks!

webgraph
11-18-2004, 11:54 AM
wildjokerdesign, In answer to your question about the alias commands. Alias commands allow you to preset command line commands, so as to save you from remembering long commands with hooks.

So, the first alias command:


alias untar="tar -xpvf"

would allow you in ssh to type:


untar file.tar

which is also the same as typing:


tar -xpvf file.tar

It's like a shortcut of sorts. You can customize them with any hooks you want or have multiple "shortcuts" for different situations. Just in case anyone wants to add/edit the alias' in the .bashrc the file is located in the root directory near the top.

Cheers

medlinke
11-18-2004, 12:57 PM
Very good post indeed. Easily one of the more useful ones posted. I worked for nearly 9 hours to do the same thing and couldn't begin to re-trace my steps (I didn't upgrade, I did a fresh compile of mySQL 4.1) because it was different.

However, your efforts here look great and I think you have benefited many many people.

Your are a scholar and a gentleperson!

Armadillo
11-18-2004, 11:11 PM
Thanks webgraph.
:D
Have you noticed any better performance with mySQL?

Armadillo
11-20-2004, 12:09 AM
I was able to download it with wget from http://mirrors.combose.com/mysql/Downloads/MySQL-4.1/mysql-standard-4.1.7-pc-linux-i686.tar.gz

The links do not seem to be direct links. I started downloading it with my browser, found the true URL (above for Ft Lauderdale Fl.), stopped, then used wget to download it.

When I have time, I'll try to upgrade it.
8)

Does upgrading have any effect on the databases?

webgraph
11-20-2004, 04:24 PM
When I upgraded, the databases were not affected. You might, however, want to make a backup of the databases before trying the upgrade.

Let us know how you fare.

-Alex

Armadillo
11-21-2004, 12:36 AM
It worked!
MySQL 4.1.7-standard-log is running on my humble website.
My phpBB works, club database works, phpMyAdmin works.
http://www.auction-pix.com/katasha/stuff/rocker.gif
Thankyou for posting the instructions webgraph.
I dont know a lot about SSH, but this was easy and I learned a few things.
:o
The only problem I had was this line-
cp /support-files/mysql.server mysqld-new
should be-
cp /support-files/mysql.server /etc/rc.d/init.d/mysqld-new

Other than that, it was smooth.
Is there any reason why the basedir has to be pointing to the symlink and not directly to the directory? Would it make any performance difference?

Thanks!
:D

webgraph
11-21-2004, 08:21 AM
Excellent!

I edited my original post to reflect the copy command for the mysqld-new file. Good catch, Thanks.

I haven't seen any performance issues while using the sym link. I've tried it both ways and I just decided to go with the MySQL instructions on using a symbolic link to the binary files. I guess in the end, it's up to the individual performing the install.

Thanks for the post, Nice work!

-Alex

Armadillo
11-29-2004, 12:02 AM
In phpinfo, the Mysql Client API version is 3.23.54.
In phpMyAdmin it is reporting 4.1.7.
My club php software reports 4.1.7.

What does this mean?
Do I need to recompile PHP somehow?
Or does this not matter?

webbo
12-09-2004, 12:57 PM
Don't quote me on this but I think the v3 client API you refer to is the Client API within PHP 4.3.0 that runs on your VPS.

The actula MySQL server will be 4.1.7.

There are some issues if you try to connect to a v4.1 server from a v3.x client. For example, take a look at the following URL if you hit any bizare authentication issues:

http://dev.mysql.com/doc/mysql/en/Old_client.html

HTH

Webbo

webbo
12-10-2004, 03:49 PM
If you want to perform a clean installation rather than keep your existing database, here's what you need to do:

- Perform these steps before starting up v4.1 for the first time
- Backup your existing data directory (just in case) and create a new one:

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql

- Install the initial MySQL tables and user accounts:

cd /usr/local/mysql417
scripts/mysql_install_db --basedir=/usr/local/mysql417 --datadir=/var/lib/mysql

- Startup MySQL and run the post installation routines as documented here (http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html)

Webbo

coppercup
12-19-2004, 03:32 PM
Don't quote me on this but I think the v3 client API you refer to is the Client API within PHP 4.3.0 that runs on your VPS.

The actual MySQL server will be 4.1.7.



I believe this is correct. In order to use the 4.x Client API within PHP I think you have to recompile PHP.

coppercup
12-19-2004, 05:07 PM
If you want to perform a clean installation rather than keep your existing database, here's what you need to do:

- Perform these steps before starting up v4.1 for the first time
- Backup your existing data directory (just in case) and create a new one:

mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql

- Install the initial MySQL tables and user accounts:

cd /usr/local/mysql417
scripts/mysql_install_db --basedir=/usr/local/mysql417 --datadir=/var/lib/mysql

- Startup MySQL and run the post installation routines as documented here (http://dev.mysql.com/doc/mysql/en/Unix_post-installation.html)

Webbo

In doing this, I have solved some problems with the privileges tables that mysql_fix_privileges_tables couldn't seem to fix, but I noticed that there was previously a default sphera database that is no longer there. Do we need to replace it? And if so, how?

dferry23
04-01-2006, 06:08 PM
Hi,

I'm new to WestHost and to MySQL. I'm using your directions to update from MySQL4 to MySQL5. Ran into a problem when I tried to stop the current mysql:

$ mysqld stop
mysqld: Too many arguments (first extra is 'stop').
Use --help to get a list of available options

Well, "stop" isn't one of the available options.

If I were root, I'd just kill the processes, but I'm not.

Suggestions?

Thanks,
Dan

allyn
04-02-2006, 05:13 PM
try /etc/rc.d/init.d/mysqld instead. btw, mysqld doesn't run as root inside a vps, it runs as you. so you can kill it no problem.

dferry23
04-03-2006, 04:32 PM
MYSQL tries to start but gives an error message that tells me it doesn't know where the tablespaces are:

"Can't open and lock privilege tables: Table 'mysql.host' doesn't exist"

Ideas?

Thanks,
Dan

dferry23
04-05-2006, 04:42 PM
I had to set datadir in /etc/my.cnf and then MYSQL started OK.