# mysql mess



## Lem0nHead (Jul 18, 2009)

hello
MySQL was messly installed on my FreeBSD 6.2 and I'm having troubles now because of that

1) MySQL server was installed manually (downloading .tar.gz from mysql site and installing)

2) bsdpan-DBD-mysql-4.005 is installed on the server, but it seems it's not fully compatible with this MySQL server version, because when I run mysqlhotcopy (which comes with mysql .tar.gz), I get:

```
/libexec/ld-elf.so.1: /usr/local/lib/perl5/site_perl/5.8.8/mach/auto/DBD/mysql/mysql.so: Undefined symbol "mysql_server_init"
```

3) /usr/ports/databases/p5-DBD-mysql# make 2>&1 | grep error: | wc -l
     402


so, any ideas on how to fix that?

thanks


----------



## gilinko (Jul 18, 2009)

My advice would be to dump the entire database to file, do a fresh install from ports and import the database (and/or convert it depending on your version). I'm assuming that you can use the mysql binary to do the dump...

As much as I use MySQL, it is quite a hassle do "fix" if someone has done something special to it. And after swithing to freebsd, I only use the ports for installing and maintaining mysql.


----------



## Lem0nHead (Jul 18, 2009)

unfortunatelly I can't "touch" the mysql server, because it's running important applications
my real only problem is mysqlhotcopy not working (probably because a bad installation of DBD::mysql)
any other idea to solve that?

thanks


----------



## gilinko (Jul 19, 2009)

What exactly are you trying to do with mysqlhotcopy? Backup? Replace the server? Upgrade to a new MySQL version? Development snapshot?


----------



## Lem0nHead (Jul 19, 2009)

gilinko said:
			
		

> What exactly are you trying to do with mysqlhotcopy? Backup? Replace the server? Upgrade to a new MySQL version? Development snapshot?



backup


----------



## Lem0nHead (Jul 20, 2009)

I'm guessing this problem may be related to an outdated version of mysql-client

if I "portupgrade mysql-client" can it mess with my mysql server?


----------



## gilinko (Jul 20, 2009)

Lem0nHead said:
			
		

> I'm guessing this problem may be related to an outdated version of mysql-client
> 
> if I "portupgrade mysql-client" can it mess with my mysql server?



Probably not, if the server is installed in another location than /usr/local.

Why don't you use mysqldump, which is intended for backups? It does the same thing as mysqlhotcopy but works on all backend stores for mysql, not only MyISAM as mysqlhotcopy does.

Hotcopy does still lock the tables during the "dump", the same way that mysqldump (or a well crafted mysql -e statement does). So it's not a true "hotcopy", as then you need a enterprise grade database.

In general to get away from the "messy" situation. Do a full dump of your database, install mysql from ports on another machine and see if you can import it without errors. If this works(and the currently installed mysql server isn't in the /usr/local), install mysql from ports, change the port to 3316 and during a maintenance window dump the database from the old server into the new server, switch the port back to 3306 on the new server(or it's socket) and your off with a good version.


----------



## Lem0nHead (Jul 21, 2009)

and after some hours, I got to fix that without changing too much things on the server (although that's not the "correct" way, it's fine for me to update mysql server manually, because I fell can deal with problems better this way... and also I have special needs linking the data/ directory)

if anyone faces the same problem:
1) update mysql-client (although I think that isn't necessary)
2) update p5-DBD-mysql (yes, that's necessary... and you probably will have some errors compiling... I had to specify the libs to link manually and then it worked: perl Makefile.PL  --mysql_config=/mysqldpath/bin/mysql_config --libs="-lstdc++ `/mysqldpath/bin/mysql_config --libs`")
3) pkgdb -F is you see something wrong (as I did )

now it should work
except if you're using an older version of mysqldump like I was
so just follow Oleg Alexeenkov "better path" on http://bugs.mysql.com/bug.php?id=27694

and now it should be fine


----------



## Lem0nHead (Jul 21, 2009)

just answering the question, mysqlhotfix is way faster than mysqldump on my server
it's also easier/faster to recover in case there's a server crash

it may be less reliable (ie, you probably need the same or similar mysqld version), but that's not a big problem considering you can find this version


----------

