Entropy.ch Discussion Forums

[parakeet]

Hi there,

I was having problems with my installation of MySQL, like not being able to switch off/on the server, not being able to login successfully.
So I'm getting on top of things. I want to remove all traces of MySQL and start from scratch.
Of course, my Powerbook 15" purchased in August 2004 came with a version of MySQL, I believe, and I've installed a couple of that since then. You can see the versions present below...

lrwxr-xr-x 1 root wheel 48 4 Apr 19:11 mysql -> mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc
drwxr-xr-x 19 root wheel 646 30 Dec 13:02 mysql-standard-4.0.23-apple-darwin7.6.0-powerpc
drwxr-xr-x 19 root wheel 646 4 Apr 19:11 mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc
drwxr-xr-x 19 root wheel 646 30 Dec 11:41 mysql-standard-4.1.8-apple-darwin7.6.0-powerpc

... so I'd like some advice.

What's the best way to go about doing this? Maybe using your removal script at http://www.entropy.ch/software/macosx/mysql/remove-old-mysql.html? Would this get rid of everything? Not that I'm especially experienced with this stuff, but would it get rid of wherever the user accounts are stored as well? Incidentally, where are they stored - surely not in the MySQL folder itself; would it be that simple - just scrap the folder and the accounts are gone? One of the problems I was having was accounts overlapping in some way?

Any drawbacks in removing everything in this way? Would this get rid of the version that came with OS X; do I want to do that?

How do I back things up? Like, the databases already in my system? I'll want to scrap these, too, but keep a backup. I'm not certain how I'd copy those folders from /usr/ to my actual Mac file area and, if restoring them, vice versa.

I had been having problems with overlapping accounts etc. - there's something about creating a separate account for mysql; what's that about?

Many thanks

[steves93]

I had the same problem. I can't remember specifically how I solved it, but it had something to do with teaching myself some basic UNIX. One thing I've found since then that I'm sure would have helped is OnyX, which is a utility that will make it very easy for you to find/see all the hidden files and their structure.

Wish I could remember the specific solution for you, but I can tell you that the hard part was knowing where to look. Look to the Unix shell.

[ryandesign]

These comments apply to the MySQL installer provided by mysql.com and assume that you haven't changed the settings by editing the my.cnf file. (If you had, you'd know.)

MySQL is installed in a directory in /usr/local named for the version you installed. For example, MySQL 4.1.10a is installed in /usr/local/mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc. The installer also ensures that a symlink exists at /usr/local/mysql pointing to the version you just installed. The MySQL startup script (and you too) can therefore rest assured that the "correct" version is always at /usr/local/mysql. If you want to change to a different version that's already installed, you can simply delete and recreate the symlink. For example, if you currently have 4.1.10a installed but want to revert to 4.1.8, you could cd /usr/local ; sudo rm -f mysql ; sudo ln -s mysql-standard-4.1.8-apple-darwin7.6.0-powerpc mysql

Databases and some other data are stored in a directory called data within the mysql-<version> directory. When you first install MySQL, this gets you an empty environment with only two databases: mysql and test. The former is required by MySQL to store usernames and passwords and other information, and the latter is a sandbox for you to play in.

When you install a newer version of MySQL, the databases you had in your old version stay in the old version, and the new version comes with a new empty environment. If you want to use your old databases with the new version (which you most likely do) then you need to manually move the data folder from the old MySQL directory to the new one, best making a backup copy as well. The MySQL server needs to be stopped when you muck with its data directories.

For example, if you had 4.1.8 installed and now installed 4.1.10a, then you'll want to move out of the way the essentially-empty data directory provided with 4.1.10a: cd /usr/local ; sudo mv mysql/data mysql/data-dist (This is what I do; "dist" means "as distributed". You can use any other name if it makes more sense to you.) Then you move your 4.1.8 data into 4.1.10a: cd /usr/local ; sudo mv mysql-standard-4.1.8-apple-darwin7.6.0-powerpc/data mysql/data To make a backup of data at this point, you could make a compressed tar archive: cd /usr/local/mysql ; sudo tar cjf ../mysql-4.1.8-data.tar.bz2 data To extract this archive later you could use StuffIt Expander or cd /usr/local/mysql ; sudo tar xjf ../mysql-4.1.8-data.tar.bz2 For more information about the tar command, type man tar

Another way to make a backup is to use the mysqldump command. For example, mysqldump -uroot -p --opt > /tmp/mysql_dump.sql This requires the MySQL server to be running. You will be prompted for your MySQL root password, which is either empty or whatever you changed it to when you installed MySQL. To restore such a dump, you can mysql -uroot -p < /tmp/mysql_dump.sql but this will either overwrite databases of the same names, or require you to manually remove databases of the same names (I can't remember which) so make sure this is what you want. There are many options to mysqldump, such as dumping only specific databases. Read man mysqldump for the scoop.

If you did not know that you need to manually move the data directory, it is conceivable that you have the following situation: You installed MySQL 4.0.23 and started creating some databases. Then you installed MySQL 4.1.8, which gave you a fresh environment, and you started creating more databases there (without access to your old databases from 4.0.23). Then you installed 4.1.10a and started over with yet another fresh environment and made more databases there. Then you would have three separate sets of MySQL databases in the three data directories in the various mysql-<version> directories. If this is the case, and if you want to gain access to all of those databases, then you have two options. One, you could stop the MySQL server, change the /usr/local/mysql symlink to point to the older version, and then start it again. Or two, you could stop the server, move the data directory from the old version into the new version (moving the new version's data directory momentarily aside first), and start the server. In either case, you could then mysqldump the database you wanted to salvage. Once you'd done this for both of the older environments, you could switch back to the newest environment and import the dump files.

If the first or second digit of the MySQL version changes (for example for MySQL 5.0), it's possible that you may need to update the database files, in which case there will be a provided script that you have to run. Read the release notes to see if this is the case.

All MySQL files are in /usr/local/mysql*. If you want to completely erase everything, simply sudo rm -rf /usr/local/mysql* and it will be gone. (It will ask you for your administrator password.) You will then no longer have any MySQL database server, no MySQL client, no databases, and no MySQL user accounts (since these are stored in the mysql database). (Unless you are using Mac OS X Server, MySQL was not included with your system.) You should then also delete any MySQL receipts in /Library/Receipts Then you can begin again by installing from the official MySQL installer.

You asked about creating a separate mysql account. This has already been done for you as of Mac OS X 10.2 so you should not need to do it manually.

Some of the above tasks can also be done in the Finder. From the Go menu, select Go To Folder... and enter /usr/local and then you can possibly delete or copy things that way.

If you have any specific difficulties getting your MySQL working the way you want, please post here the exact error message you encounter; this will help us make the most specific recommendation on fixing it.