mysql crashing

I don't think I wrote "trust me, I know everything" :p

Probably best to bring this up on a mysql forum and ask the experts.

If it turns out it's safe I can certainly make changes to sysbk to disable the lock and the file backup, and only use the export.

Jeff
 
mysqldump is the recommended way of backing up mysql databases, AFAIK. mysqlhotcopy is another alternative which sets table locks while it's copying the databases. Both of these are supported and recommended by mysql.

You can always copy the db files to do a backup without shutting down mysql, but you have to ensure that nothing's being written. That's what mysqlhotcopy does by setting read locks on the tables.

Also note that simply copying the db files does not work for InnoDB tables and mysqlhotcopy only supports ISAM and MyISAM databases.

So, for support of all database types while the server is running, mysqldump is probably the preferred method.

http://dev.mysql.com/doc/mysql/en/backup.html
 
I've been digging into this some more... sysbk's approach is kind of interesting. The method there is to shut down mysql, archive the database files, run myisamchk on the tables, restart mysql and then run mysqldump on all the databases. myisamchk is usually a good thing to do since it optimizes databases and fixes problems which might cause a mysqldump to fail, but myisamchk also requires that no one is modifying the database while it runs.

So, sysbk itself has mysql running while it does the mysqldump with no locking on the tables, so the individual dumps could certainly be inconsistent.

Also, sysbk will send a stop command to the init service before beginning its backup. If this fails, you should see an error in the sysbk logs that mysql could not be shut down. Then it will try to start mysql again if the pid file doesn't exist. However, if mysql is taking a long time to shutdown (or hanging at shutdown because some process never releases the connection), but the pid file still exists, that would explain why it would become unavailable.
 
ballyn said:
So, sysbk itself has mysql running while it does the mysqldump with no locking on the tables, so the individual dumps could certainly be inconsistent.
Click to expand...
Does it? Or does it do dumps on the copies it's made? If not, would doing it be possible? Would it solve the problem?
Also, sysbk will send a stop command to the init service before beginning its backup. If this fails, you should see an error in the sysbk logs that mysql could not be shut down. Then it will try to start mysql again if the pid file doesn't exist. However, if mysql is taking a long time to shutdown (or hanging at shutdown because some process never releases the connection), but the pid file still exists, that would explain why it would become unavailable.
Click to expand...
I've wondered myself how to solve the problem and create a good backup if MySQL fails to stop.

You seem to know quite about about MySQL. Can you help us come up with a "best" solution? I'll be happy to make the changes to sysbk if you don't want to, but I don't know what changes to make.

Thanks.

Jeff
 
jlasman said:
Does it? Or does it do dumps on the copies it's made? If not, would doing it be possible? Would it solve the problem?
Click to expand...

mysqldump has to have access to the database itself, so it uses the live databases.

I've wondered myself how to solve the problem and create a good backup if MySQL fails to stop.

You seem to know quite about about MySQL. Can you help us come up with a "best" solution? I'll be happy to make the changes to sysbk if you don't want to, but I don't know what changes to make.

Thanks.

Jeff [/B]
Click to expand...

I think mysqlhotcopy is the way to do it. That script is pretty complete. The only hard part is sysbk's myisamchk. You kind of want to do that before you run mysqldump, because if there's a database problem the mysqldump will fail. But as I mentioned, myisamchk wants a read lock as well which is difficult to script.

I'm currently testing just replacing the shutdown, arc, myisamchk and restart with a
mysqlhotcopy --regexp=.* /home/tmp/mysql_bak

which does a hot copy of all the database files to /home/tmp/mysql_bak and then run an arc on that directory. Then the mysqldump runs as normal. Skipping the myisamchk isn't ideal, but I haven't found a good way to run that with read locks yet. Further, the mysqldump should probably run with the read lock set as well. The easy way to do that is with a "flush tables with read lock", but you need to keep that connection open and then do an "unlock tables" after the databases are dumped.
 
I went back and looked at mysql.bk... the mysqldump command includes the -l switch, so it does actually lock the tables before it does the dump.

So, we need to either:

1) find a way to lock the tables during the myisamchk
2) use a script that includes a "CHECK TABLES" or "REPAIR TABLES" instead of using myisamchk before the mysqldump
3) skip the myisamchk and report any errors that might occur from the mysqldump

I think I'd opt towards 2... should be pretty straightforward to loop through a sql query for each database as long as we have the mysql client installed.
 
It appears you're heading in the right direction.

If you care to, you might want to discuss it on the bind-users list (it's also a newsgroup), and get some advice from the self-proclaimed :) experts.

I'm going to leave it to you and others to figure it all out, and then I'll be happy to recode sysbk as necessary.

Jeff
 
I'm getting the same problem, on Fedora Core 3

050909 16:10:15 mysqld started
050909 16:10:15 [Warning] Asked for 196608 thread stack, but got 126976
050909 16:10:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
050909 16:10:15 [ERROR] Do you already have another mysqld server running on port: 3306 ?
050909 16:10:15 [ERROR] Aborting

050909 16:10:15 [Note] /usr/sbin/mysqld: Shutdown complete


But im not doing backups, does DirectAdmin do automatic backups?
 
Not by itself. However unless you're an admin, then you should know that the admin could have set up backups for the entire server.

And if you rent a dedicated or virtual server someone could have set it up for you; I know that we backup our clients servers unless they opt-out.

Jeff
 
It's my server, i own it un-managed, it happened EVERY morning, mysql either dies from too much activity or something and won't restart itself.

Where would i check the logs for why mysql dies in the first place?
 
My server isnt backing up as I hadn't got to setting that up yet.

I do know that my server goes through high cpu at that time, not high load, because its peak time for my server.

Ill try upgrading to the latest mysql today and see what happens.
 
ok its getting worse

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/naf-server01.com.pid --skip-locking

thats what the pid files say when it slows to a crawl.
 
NickFritz said:
thats what the pid files say when it slows to a crawl.
Click to expand...
I don't know what you mean. a pid file should only have process ID info in it; on most distributions the pid file only has a number in it.

Jeff
 
I was a little confused when i posted that hehe.

This happens every morning around the same time which is "I think" peak time

Here's my log file...
Code: 
050912 14:02:21 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:02:37 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:02:37 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:02:37 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:02:37 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:06 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:18 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:19 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:19 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:20 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:43 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:47 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:48 [ERROR] /usr/sbin/mysqld: Sort aborted
050912 14:03:48 [ERROR] /usr/sbin/mysqld: Sort aborted
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=2093056
max_used_connections=290
max_connections=1000
threads_connected=221
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4108376 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

You seem to be running 32-bit Linux and have 221 concurrent connections.
If you have not changed STACK_SIZE in LinuxThreads and built the binary
yourself, LinuxThreads is quite likely to steal a part of the global heap for
the thread stack. Please read [url]http://www.mysql.com/doc/en/Linux.html[/url]

thd=0x8fe0b38
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xfd81eae8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808f307
0x82eb5f8
0x830b067
0x80fc870
0x80fc4ba
0x80fb5bc
0x80cf8cc
0x80c2d5b
0x80c3250
0x80c02c6
0x809f9ea
0x80a427f
0x809e502
0x809dec4
0x809d577
0x82e8dac
0x831273a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read [url]http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html[/url] and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xecd01a50  is invalid pointer
thd->thread_id=64451
The manual page at [url]http://www.mysql.com/doc/en/Crashing.html[/url] contains
information that should help you find out what is causing the crash.

Number of processes running now: 6
mysqld process hanging, pid 7777 - killed
mysqld process hanging, pid 7777 - killed
mysqld process hanging, pid 7777 - killed
mysqld process hanging, pid 7777 - killed
mysqld process hanging, pid 7777 - killed
mysqld process hanging, pid 7777 - killed
050912 14:04:35  mysqld restarted
050912 14:05:24 [Warning] Asked for 196608 thread stack, but got 126976
050912 14:05:25 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
050912 14:05:25 [ERROR] Do you already have another mysqld server running on port: 3306 ?
050912 14:05:25 [ERROR] Aborting

050912 14:05:26 [Note] /usr/sbin/mysqld: Shutdown complete

050912 14:05:29  mysqld ended
 
Last edited:
Well, that one's beyond me.

What version of MySQL are you running? Where did you get it from? Did you install it or did it come with your DA install?

Jeff
 
I think i worked it out, eaccelerator doesn't like mutliple processors and was killing my mysql. Ill get back to you with the details if this is teh case.
 
Hi,

What is the correct answer to stop mysql crashing?

I'm running Fedora Core 3. I've optimized MySQL. Trying to manually test Dillard's script but it allready goes wrong at the first line i try: mysqladmin ping
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
Click to expand...
What should the correct script be in my situation?

Please help. Thank you!
 
I've been using this replacement for sysbk's mysql.bk module...

It uses mysqlhotcopy to copy the db files to the backup directory and then archives the full mysql backup from there. So, the paths will be a bit different in the archive since we're not archiving from /var.

It also uses mysqlcheck instead of misamcheck so the database need not be shut down before checking the tables. This requires mysql 3.23.38 or later.

It also requires that the two scripts be in root's path... if anyone would like to put those variables in the conf.sysbk file, feel free.

Replace the attached file with the one in /usr/local/sysbk/mod/mysql.bk.

Very lightly tested... Please post any errors or improvements...
 

Attachments

You must log in or register to reply here.
Back
Top