Welcome to the MacNN Forums.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

You are here: MacNN Forums > Software - Troubleshooting and Discussion > macOS > Managing MySQL database

Managing MySQL database
Thread Tools
scip
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 8, 2004, 02:05 PM
 
I'm using MySQL4X Manager to try to connect to my MySQL database. It works locally, but when I try to connect to the db on the same subnet, it doesn't find the server, even by IP.

Surely, this can work over a network, so what are the obvious things to look for to get this working?

Also, how do I move my SQL database files to another volume? The one they are on currently has very little space, and I have another drive that I'd like to use for my SQL data.
     
Arkham_c
Mac Elite
Join Date: Dec 2001
Location: Atlanta, GA, USA
Status: Offline
Reply With Quote
Jan 8, 2004, 04:36 PM
 
Originally posted by scip:
I'm using MySQL4X Manager to try to connect to my MySQL database. It works locally, but when I try to connect to the db on the same subnet, it doesn't find the server, even by IP.

Surely, this can work over a network, so what are the obvious things to look for to get this working?

Also, how do I move my SQL database files to another volume? The one they are on currently has very little space, and I have another drive that I'd like to use for my SQL data.
1) To load over a network, you have to (a) make it listen on a socket and (b) make sure that you have the port open in your firewall if you have one.

The port is 3306. You set that in your my.cnf file, wherever you have that stashed (/etc/my.cnf is the preferred location I think).

2) To move it, simply stop it, move it, and start it up again. You could move just the data directory (/usr/local/mysql/data) or the whole tree. If you move just the data directory, just make a symlink from the old location to the new one in /usr/local/mysql.
Mac Pro 2x 2.66 GHz Dual core, Apple TV 160GB, two Windows XP PCs
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 8, 2004, 04:55 PM
 
Originally posted by Arkham_c:
1) To load over a network, you have to (a) make it listen on a socket and (b) make sure that you have the port open in your firewall if you have one.

The port is 3306. You set that in your my.cnf file, wherever you have that stashed (/etc/my.cnf is the preferred location I think).

2) To move it, simply stop it, move it, and start it up again. You could move just the data directory (/usr/local/mysql/data) or the whole tree. If you move just the data directory, just make a symlink from the old location to the new one in /usr/local/mysql.
This is excellent information, thank you.

I noticed that /etc/my.cnf does not exist by default. Does this mean that if I add one and then stop/start mysql it will look for it and process the directives in it?

By "the whole tree" you mean the entire mysql installation, right?
     
Kristoff
Mac Elite
Join Date: Sep 2000
Location: in front of the keyboard
Status: Offline
Reply With Quote
Jan 8, 2004, 11:33 PM
 
RTFM
signatures are a waste of bandwidth
especially ones with political tripe in them.
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 9, 2004, 10:29 AM
 
Originally posted by Kristoff:
RTFM
Been there, done that, thank you. The docs on mysql.com are NOT easy to read/follow at all.

I've moved my msql installation to a different volume, and when I start it, it still loads the databases from the old location. How do I tell it to look to the new location to load the databases?
     
Arkham_c
Mac Elite
Join Date: Dec 2001
Location: Atlanta, GA, USA
Status: Offline
Reply With Quote
Jan 9, 2004, 10:30 AM
 
Originally posted by Kristoff:
RTFM
Dude, that's not helpful. Sometimes findin an answer in TFM is a lot of work for a simple question that someone could answer quickly with a simple post. This is not slashdot, it's MacNN, and people here try to help one another.

Here's the header from the my-huge.cnf file that comes with MySQL in the support-files folder:

Code:
# Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/data) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option.
As to the whole tree, that would be /usr/local/mysql and below. Personally though I'd probably just move the data directory and leave the install where it belongs.
Mac Pro 2x 2.66 GHz Dual core, Apple TV 160GB, two Windows XP PCs
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 9, 2004, 11:05 AM
 
Originally posted by Arkham_c:


Here's the header from the my-huge.cnf file that comes with MySQL in the support-files folder:

Code:
# Example mysql config file for very large systems. # # This is for large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/data) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option.
As to the whole tree, that would be /usr/local/mysql and below. Personally though I'd probably just move the data directory and leave the install where it belongs.
Yes, I saw that my-huge.cnf file in the mysql install. My question is what .cnf file does mysql use by default. It is obviously using some type of config file somewhere, right? There is no /etc/my.cnf file in my installation; I didn't put one there.
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 9, 2004, 04:03 PM
 
Originally posted by scip:
Yes, I saw that my-huge.cnf file in the mysql install. My question is what .cnf file does mysql use by default. It is obviously using some type of config file somewhere, right? There is no /etc/my.cnf file in my installation; I didn't put one there.
Ok, so I've managed to find what I think is the config file mysql uses. It's the /usr/local/mysql/support-files/mysql.server file.

In it, it shows the following:

# The following variables are only set for letting mysql.server find things.

# Set some defaults
datadir=/usr/local/mysql/data

If I change the datadir to a different path, and restart the mysql service, it reports on the command line:

Starting mysqld daemon with databases from /usr/local/mysql/data


So obviously, it's not seeing my change.

What gives?
     
utidjian
Senior User
Join Date: Jan 2001
Location: Mahwah, NJ USA
Status: Offline
Reply With Quote
Jan 10, 2004, 02:36 AM
 
Originally posted by scip:
Ok, so I've managed to find what I think is the config file mysql uses. It's the /usr/local/mysql/support-files/mysql.server file.

In it, it shows the following:

# The following variables are only set for letting mysql.server find things.

# Set some defaults
datadir=/usr/local/mysql/data

If I change the datadir to a different path, and restart the mysql service, it reports on the command line:

Starting mysqld daemon with databases from /usr/local/mysql/data


So obviously, it's not seeing my change.

What gives?
First off... in a situation like this you need all the resources you can get your hands on... yes that means RTFM AND RTFWP (Read The Fine Web Page (usually at http://www.mysql.com/doc/en/index.html)) There are specifics in there for MySQL on Mac OS X. I find the web docs to be most useful especially the ones with user comments.

Without knowing how you installed MySQL in the first place it is difficult to tell where your specific system placed the configuration file (my.cnf). Since MySQL is a server type application and not in one of the default Mac OS X Applications folders you should use the Terminal.app for most things.

Next... Open a terminal and do this:

locate my.cnf

That should tell you where the my.cnf file is on your system. I suspect it will be in /usr/local/etc/ (since it isn't in /etc/ )

Once you find it you can set the location of datadir to wherever you like. Perhaps something like this:

Code:
datadir=/newloc/var/lib/mysql socket=/newloc/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/newloc/var/lib [safe_mysqld] err-log=/newloc/var/log/mysqld.log pid-file=/newloc/var/run/mysqld/mysqld.pid
Where "/newloc" is the mountpoint of your new disk. Alternatively you could do what Arkham_c suggested and just move all the data from /usr/local/mysql to /newloc/var/lib/mysql and symlink it. While that solution is simple it lacks elegance IMO. You SHOULD find where your configuration files are located regardless.

In general I disagree with the default location of MySQL (/usr/local/mysql) I think it should either be in /var/lib/mysql or /usr/local/var/lib/mysql or, possibly, /opt/var/lib/mysql. But thats only my opinion.
-DU-...etc...
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 10, 2004, 02:59 AM
 
Originally posted by utidjian:
First off... in a situation like this you need all the resources you can get your hands on... yes that means RTFM AND RTFWP (Read The Fine Web Page (usually at http://www.mysql.com/doc/en/index.html)) There are specifics in there for MySQL on Mac OS X. I find the web docs to be most useful especially the ones with user comments.

Without knowing how you installed MySQL in the first place it is difficult to tell where your specific system placed the configuration file (my.cnf). Since MySQL is a server type application and not in one of the default Mac OS X Applications folders you should use the Terminal.app for most things.

Next... Open a terminal and do this:

locate my.cnf

That should tell you where the my.cnf file is on your system. I suspect it will be in /usr/local/etc/ (since it isn't in /etc/ )

Once you find it you can set the location of datadir to wherever you like. Perhaps something like this:

Code:
datadir=/newloc/var/lib/mysql socket=/newloc/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/newloc/var/lib [safe_mysqld] err-log=/newloc/var/log/mysqld.log pid-file=/newloc/var/run/mysqld/mysqld.pid
Where "/newloc" is the mountpoint of your new disk. Alternatively you could do what Arkham_c suggested and just move all the data from /usr/local/mysql to /newloc/var/lib/mysql and symlink it. While that solution is simple it lacks elegance IMO. You SHOULD find where your configuration files are located regardless.

In general I disagree with the default location of MySQL (/usr/local/mysql) I think it should either be in /var/lib/mysql or /usr/local/var/lib/mysql or, possibly, /opt/var/lib/mysql. But thats only my opinion.
my.cnf DOES NOT exist with this mysql installation. Using the locate command returns NOTHING. It appears that the config it is using is a file called mysql.server within a directory under the mysql parent. (I detailed this in my previous post)

The Mac OS X specific documentation on mysql.com DOES NOT address my problem...tis the reason why I posted here. (again detailed in one of my previous posts)

I installed mysql using the Mac OS X Package installer as supplied by mysql.com.

/usr/local/etc does not exist on a default Jaguar install.
     
utidjian
Senior User
Join Date: Jan 2001
Location: Mahwah, NJ USA
Status: Offline
Reply With Quote
Jan 11, 2004, 10:02 PM
 
Originally posted by scip:
my.cnf DOES NOT exist with this mysql installation. Using the locate command returns NOTHING. It appears that the config it is using is a file called mysql.server within a directory under the mysql parent. (I detailed this in my previous post)

The Mac OS X specific documentation on mysql.com DOES NOT address my problem...tis the reason why I posted here. (again detailed in one of my previous posts)

I installed mysql using the Mac OS X Package installer as supplied by mysql.com.

/usr/local/etc does not exist on a default Jaguar install.
OK... I don't normally run MySQL on Mac so most of my installs are a bit different... the default installs that is.

I installed MySQL from http://www.mysql.com on my remaining Jaguar box. I the same would apply to Panther. I downloaded their "stable" version (mysql-standard-4.0.17.dmg), unpacked it and did the click and drool install. Is there any way to install .dmg file on Mac OS X from the commandline? Is there any way to check the contents of a .dmg file before/after install?

In any case... I dug around in the default install locations and it appears that this version of MySQL for Mac OS X has a default configuration NOT necessarily located in a my.cnf file. However, it will also look for such a file in the "usual places". According to the included docs that would be in /etc/my.cnf for global settings, /usr/local/mysql/data/[dbname]/my.cnf for per database settings, and $HOME/.my.cnf for per user settings.
I created an /etc/my.cnf file by copying the /usr/local/mysql/support-files/my-medium.cnf file to /etc/my.cnf Again, this is exactly what the manual.txt says to do in the docs/ folder. And it "just works". The file you found is just a script for starting and stopping the server. That file is more like a SysV initscript.

There are a few things I don't like about this installer for MySQL. Depending on how you look at it, it is good that it isolates itself in /usr/local/mysql. It is a bad thing that the manual pages are not installed in the default man page path. The installer assumes too much knowledge of the user/admin. It should have installed the manpages in /usr/local/man or /usr/local/share/man.

In your installation you will have to check if your firewall (if any) is blocking external access to the default mysql port (3306). You may also have to create a user and permissions for that user to access the db. Do that in mysql.

If you find the commandline too awkward for all this stuff you should look into webmin (http://www.webmin.com) and/or PHPMyAdmin (http://www.phpmyadmin.net) for some easier to use interfaces.

Anyhow... hope that helps.

-DU-...etc...
-DU-...etc...
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 14, 2004, 04:59 PM
 
So now, mysql won't start at all.

I decided to get silly and moved the data directory from one volume to another, then created a symlink to it. That didn't work so I copied (via the command line) the data back to its default location, /usr/local/mysql/data

Now, when I try to start mysql, I get the following error:

mysql ended

Eh?
     
Chris O'Brien
Grizzled Veteran
Join Date: Nov 2003
Location: Hebburn, UK
Status: Offline
Reply With Quote
Jan 14, 2004, 05:19 PM
 
That happened to me when I tried to update mysql without stopping my current version - very foolish, I know.

The problem was permissions as far as i recall. I think I got round the problem by chown -R on the root directory of mysql to mysql.

Don't know if that helps or not...
     
utidjian
Senior User
Join Date: Jan 2001
Location: Mahwah, NJ USA
Status: Offline
Reply With Quote
Jan 14, 2004, 05:33 PM
 
Originally posted by scip:
So now, mysql won't start at all.

I decided to get silly and moved the data directory from one volume to another, then created a symlink to it. That didn't work so I copied (via the command line) the data back to its default location, /usr/local/mysql/data

Now, when I try to start mysql, I get the following error:

mysql ended

Eh?
This most likely a permissions/ownership issue. Check the perms on /usr/local/mysql/data folder, they should be:
Code:
drwxr-x--- 10 mysql wheel 340 Jan 11 20:26 data
/usr/local/mysql should be:
Code:
lrwxr-xr-x 1 root wheel 45 Jan 11 17:38 mysql -> mysql-standard-4.0.17-apple-darwin6.8-powerpc drwxr-xr-x 18 root wheel 612 Jan 11 14:39 mysql-standard-4.0.17-apple-darwin6.8-powerpc
Any databases within /usr/local/mysql/data/ should also be owned by the mysql user.

When using cp to move files and folders around use the -p option. Better yet use tar which will bundle everything up and preserve permissions (also takes the -p option) then it will unbundle everything nicely.

I think this Unix permissions is a common sticking point for many Mac OS X users that are not used to it. Just be careful and check your work. Be sure you understand the meaning of the ownership and permissions settings and the tools for adjusting them. For the most part a regular user never sees this stuff... which is fine... but an admin will have to deal with it from time to time. After a while it becomes second nature.

If you have any data of any value in your databases, as soon as you can, do:
Code:
mysqldump -p -A > backup.sql
It may take a while to run. You will need a large amount of disk space if the db is large. You should be doing this daily anyhow.
-DU-...etc...
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 14, 2004, 07:12 PM
 
Originally posted by utidjian:
This most likely a permissions/ownership issue. Check the perms on /usr/local/mysql/data folder, they should be:
[code]
I had a feeling that it might lie in permissions. I didn't know what needed root and what needed to be mysql

I had chowned the data dir back to mysql, but not recursively. I did it again adding the -R and it worked...thanks.
     
scip  (op)
Forum Regular
Join Date: Mar 2001
Location: Brookfield, CT, USA
Status: Offline
Reply With Quote
Jan 15, 2004, 11:34 AM
 
Originally posted by scip:
I had a feeling that it might lie in permissions. I didn't know what needed root and what needed to be mysql

I had chowned the data dir back to mysql, but not recursively. I did it again adding the -R and it worked...thanks.
Ok, so I'm back to trying to move the data to another volume and symlink to it.

This is what I've done:

(as root)

cp -Rp data /path/to/new/location

rm -R data

ln -s /path/to/new/location data

and it breaks the connection to the db (I'm using phpBB and can't access the forums after I do this)

I did not restart the mysqld...do I have to?

Also, I noticed that the data symlink was owned by root, but when I tried to chown it to mysql, the change did not take effect...can you not chown links?

Does the mysqld service have to be restarted when a change like this is made?
     
utidjian
Senior User
Join Date: Jan 2001
Location: Mahwah, NJ USA
Status: Offline
Reply With Quote
Jan 15, 2004, 01:06 PM
 
Originally posted by scip:
Ok, so I'm back to trying to move the data to another volume and symlink to it.

This is what I've done:

(as root)
You should stop the db first... BEFORE you move any of the files... This is very important because you can corrupt you database if it is being accessed at the time you move it. Did you make a backup yet BTW?


cp -Rp data /path/to/new/location

rm -R data
NEVER delete a "known good" database BEFORE you have determined that the new one is also "good"


ln -s /path/to/new/location data

and it breaks the connection to the db (I'm using phpBB and can't access the forums after I do this)

I did not restart the mysqld...do I have to?
If you had stopped it first you would have to now start it.


Also, I noticed that the data symlink was owned by root, but when I tried to chown it to mysql, the change did not take effect...can you not chown links?
Yes, this is normal. It was root (presumably) that created the symlink so root owns it. What is important are the permissions and ownership of the target directory. By default, on Mac OS X, the permissions on a link are mode 0755 (lrwxr-xr-x) and owner and group are root:root. Again, what is important is the permissions, owner and group of the target directory and its contents. The target directory should have owner, group and permissions the same as the original. IOW the mode for
/path/to/new/location should be 0750 (drwxr-x---) and the owner:group should be mysql:wheel. You should check the contents of the new target directory. They should match the ownership and permissions of the old /usr/local/mysql/data/ contents. (Yet another good reason to leave the old directory in place until the new one is set up correctly). The command for changing ownership and group is:

chown owner:group filename

or dirname. Like chmod, chown takes the same options for recursive changes.


Does the mysqld service have to be restarted when a change like this is made?
The mysqld service should ALWAYS be *stopped* BEFORE making ANY changes.... then *started* AFTER the changes are completed. Please, once you have your database in a known good state, make that backup I suggested in an earlier post.

It is also important that the target directory, in this case /path/to/new/location, have the modes ALL ALONG that path set correctly. If you made the path as root then they will be set to mode 0777 (drwxrwxrwx) by default, which is fine. At minimum they should be set to mode 0755 (drwxr-xr-x). Check this with:

ls -l /
ls -l /path/
ls -l /path/to/
ls -l /path/to/new/
ls -l /path/to/new/location/

Also do a check on /usr/local/mysql/data, it should look identical to the old one.

If you look at how (see previous post) the entire MySQL installation is arranged in /usr/local/ you will see that (on my system anyhow) that /usr/local/mysql is already symlinked to /usr/local/mysql-standard-4.0.17-apple-darwin6.8-powerpc
That is how the installer installed it on my system.
For example, if I wanted to, I could FIRST stop the database, then move the entire MySQL installation to a new place with more room, delete the old *link*:

rm /usr/local/mysql

Make a new link to the new location:

ln -s /new/location/mysql-standard-4.0.17-apple-darwin6.8-powerpc /usr/local/mysql

Then restart the database. Once I am satisfied with the operation of the database in its new location I could THEN delete the old one:

rm -Rf /usr/local/mysql-standard-4.0.17-apple-darwin6.8-powerpc

All of this is really pretty simple once you get the hang of it. So keep trying... and DO make that backup.
-DU-...etc...
     
   
Thread Tools
 
Forum Links
Forum Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Top
Privacy Policy
All times are GMT -4. The time now is 01:39 PM.
All contents of these forums © 1995-2017 MacNN. All rights reserved.
Branding + Design: www.gesamtbild.com
vBulletin v.3.8.8 © 2000-2017, Jelsoft Enterprises Ltd.,