Tag Archives: MySQL

How to restore mysql replication

databases, , ,

Something went wrong and your mysql replication broke, I'm talking here about problems with the sql thread, not connection problems.

The sql thread shows you an error, what do you do to fix it and resume replication?

Here are 3 ways to fix it, each has advantages and disadvantages, pick the one that fits best to your problem.

1. Skip over the problem

You can try to just skip over the statement that broke the replication by changing the position in log file.

There are two ways to do this:

a) you can skip gradually

slave stop;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
slave start;
show slave status \G

That would skip the next 1 statement but you can set the counter higher to skip more the one.
Do it until the slave status shows the SQL thead is running.

b) skip to the current position

Use this is the first method keeps showing other statements that break replication and you don't have time to gradually skip statements.

First go on the master and type: show master status to find which is the current bin log file and the current position within the file.

Then go on the slave, stop it with "slave stop" and change the file name and position. Something like:

slave stop; 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001958', MASTER_LOG_POS=52562937;
slave start;

But do that with your own file name and position taken from the master.

Check the replication status with "show slave status".
If the results are good ( both Slave_IO_Running and Slave_SQL_Running are Yes ) then you can go to the next step otherwise skip to next methods.

At this point you have a working replication but probably the data on the slave is not the same as on master since you skipped a few sql statements.

To fix it you can use maakit ( mk-table-checksum and mk-table-sync )

2. Full Dump and Restore

Connect to master, dump everything in a sql file, copy to replication slave and load it in mysql.

Use --master-data so the replication position is set in the dump file and the slave will know where to start.

Use --disable-keys so the slave will not try to build indexes after each insert and only built them at the end of the import.

Use --add-locks to surround each table dump with lock table/unlock table - this makes the inserts faster in the slave.

Problem:
--master-data will put a read lock on tables so operations on your master will lock waiting for the dump to finish. On large databases this can take a long time and it's unacceptable.

Possible fix:
If you have innodb tables add --single-transaction so a single global lock will be used only for a short time at the beginning of the transaction.

The problem is not so big if you can have filesystem snapshots on the master like the ones created by lvm.

3. Inconsistent Full Dump

This is just another fix for the problem at #1. Dump the data just like before but without using --master-data. This means no locks so the master can still work normally.
But because you don't use --master-data you will have to set the position in the slave yourself.
on the master type:

show master status \G

Take the file name and position and use them in the CHANGE MASTER statement on the slave ( after you load the dump file ) . Something like:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001958', MASTER_LOG_POS=52562937;

Of course all of this will create an inconsistent slave but you can fix this easily with maakit.

If you know other methods I'd love to hear about them. Let me know in the comments.

Help saving MySQL

General, ,

MySQL is in danger of dying slowly.

If this happens, anyone running a blog on wordpress and lot of other web and non web applications are going to be in trouble. No more frequent updates, no more in time bug fixes, no more development, possibly higher prices for commercial licenses.

Here's the cause: Oracle is trying to buy Sun and since Sun bough MySQL last year, Oracle is going to end up owning MySQL.

Why is this bad for MySQL?

Well basically Oracle doesn't have any interest in promoting and developing this database server when they already have one that makes them more money. By buying MySQL Oracle eliminates the competition and everybody knows where there's no competition Bad Things Happen TM.

You can read more about the story and also find out how you can help with this on this post: Help Saving MySQL from the creator of MySQL.

Ask me questions

General, , , , , , , , , , ,

Have a question about unix, linux, freebsd.  Or maybe you want some advice about configuring apache, mysql,an email server like exim, qmail, postfix, a proxy server like squid cache or antinat, dns or anything else similar. Want some help with php programming or maybe you want to create a wordpress plugin?

Feel free to ask and I'll do my best to answer it on this blog. I will publish a new blog post for each question and my answer.

You can use the contact form or ( if your question is short enough ) you can send it to me over twitter

You can follow me on twitter or subscribe to my RSS feeds if you want to be notified when I post the answer to your question.

Recover plesk access

Linux, , , ,

Here's a scenario: you're locked out of plesk admin, you forgot the password and can't recover cause your email address is not set in the contact details.

Still have ssh access as root (ssh keys or can still remember password for root ) ? Most of the time I use dsa keys for ssh authentication.
If you do then you can change the password for admin.

Plesk keeps it's password in the psa mysql database so you just have to change it in the psa.accounts table . But to have access to it you need access as root in mysql.
If you don't have the password for root ( most likely on plesk servers ) you'll have to stop mysql and start it without privilege verification.

  1.  

That would work on most linux distros , on some the stop script would be /etc/init.d/mysqld and on others the path to the mysql server might be /usr/libexec/mysqld .
use psa

Once you're logged in run this sql to change the password:

  1. span style="color: #ff0000;">'newpasswordhere'

Now get out of the mysql client ( CTRL+C) and restart mysql to have privilege verification back or else everyone would be able to do what you just did:

  1.  

Now you can login to plesk with the new password.

MySQL: counting results

databases, , ,

You have a query and you want to display the results on a web page but because there are so many results you want to paginate the data so the user can have some links like "prev page, page 1, page 2, next page, last page" that you can see on a lot of sites these days. This is a common problem a web developer faces, it's not hard to solve but it is often not solved in the best way.

The pagination concept is based on the fact that you can retrieve just part of the results using a limit clause in the query and display them on a page. This usually makes the query faster and allows the user to easily navigate without crashing his browser or having to scroll long pages.

If you want to show the user the total number of results or you want to allow them to skip right to the last page then you need to count the total number of results that the query would return without the LIMIT clause.

How some do it?

I have seen some badly designed software that was just removing the LIMIT from the query running it and then calling mysql_num_rows() to count the rows. That may be ok if your table has just a few rows and the query returns quickly but if your table will grow to a few thousand rows or if your query joins several big tables you're going to get in troubles

So how can this be done better?

There is no way that would be best for any case but here is what you can do:

  1. if your query is simple enough to not use the "group by" or "having" clause  you can simply remove all fields in your query and replace them with "count(*)" this will be really fast especially if you have the right indexes set on the table(s) in the query
  2. if your query does use "group by" then modify the query to use SQL_CALC_FOUND_ROWS.

Here is an example of the second option that may be more general as it works with any query and I think it's preferable even if it may be slower then count(*)

We have this query:

  1.  

you would use that to display a list of ages and how many users have a certain age in your table, you want the list to have 10 results / page and your table is really big so it's very likely you will have more then one page to display.

As you can see this query already has a "count" and "group by" in it so you can't use count to get the total number of results.

If we modify this query like this:

  1.  

the query will return the exact results as the previous one but now if we do this :

  1.  

we will get the total number of rows that the last query would have returned without the LIMIT clause.

This is a lot faster then running the query without the limit and counting the results with mysql_num_rows because MySQL will to the counting internally and will not have to return the whole result set to the client .

Other ideas to improve performance

Fetch details for a record in separate queries. Let's say you have a query that joins several tables and you want to display details from all those tables in a single row in your list. The joins make your query slow because it will have to examine a lot of rows when doing the count .Try to remove as many of those joins as you can do the count and then for each row in your list just run separate queries to get the other details.This way you will examine just a few rows from the other tables because you'll do the extra queries only for the results you are currently showing on a page.

Enable mysql slow query log then watch it to see how long your queries take and how many rows are examined.

Use explain to see if your query is using the right indexes and create indexes where you think they will improve the performance. If the explain will show the query will use a temporary table make sure your temporary table can hold all data in memory, if you have enough
( check the tmp_table_size and max_heap_table_size variables )

Enable query cache so the server will just server the results from cache instead of doing all the work over and over for data that is unchanged.

There are a few other techniques I have found on the official mysql documentation site, but these presented here helped me a lot in working with lists and counting the results.

If you have other tips I'll be happy to see them in the comments.

MySQL and SSL

databases, , ,

I have been setting up a few mysql servers with SSL support for replication .

I used the script provided in the the official mysql documentation  for creating the ssl certificates cause I needed to do it on more then one server and it made more sense to use it then actually creating each certificate one by one.

If you just read the documentation and create the certificate one by one you will be fine but if you use the script your CA certificate will expire after 30 days and after a month you'll be banging your head trying to find out why suddenly SSL connections don't work anymore.
You know your certificates should be valid for a year or more but why doesn't it work anymore ... running this command :

  1.  

reveals it ...

notBefore=Apr 17 12:20:10 2008 GMT
notAfter=May 17 12:20:10 2008 GMT

Ah .... there you go ... just 30 days for the cacert file ... insane...
The problem was actually reported by someone else in the comments on that documentation page but I was in a hurry ( yeah right ) and didn't go that far with reading it.
Note to self: always read the comments on those pages
So if you use that script make sure you modify it to make the CA valid for more then 30 days.
This line:

  1.  

Should be something like:

  1.  

That is if you want the CA cert to be valid for a year.

Problem transferring a mysql database with rsync

databases, , ,

A little more then a year ago I wrote a post presenting three different methods to transfer a mysql database. The third method suggested in that post was copying the mysql database files directly from one server ( or location ) to another. This involved locking the tables with a read lock or even shutting down the mysql before the actual copy.

For my work I usually have a main system and a development system and each system have their own database so there is a need from time to time to copy the main database over the dev database but because the database is very big ( a lot of tables and some with a large size ) and not every table is changed I like using rsync to transfer only the changes especially when I'm transferring to remote locations because it saves bandwidth and is faster.

In this case where I found the problem I actually use the same mysql server to hold both main and dev database but I still use rsync to transfer just because it still is faster then cp.

So here is what I do: I stop the mysql server , run rsync -av /var/lib/mysql/main_db/ /var/lib/mysql/dev_db/ , the differences are transferred, I start the mysql server look at the dev_db and Boom! some of the tables are corrupt. The main database was fully functional before shutting down mysql , no tables were corrupt or needed a repair, and still don't need starting up mysql.

Maybe something even more interesting is that it's very likely noone was using any of the databases before mysql was shut down.

It seems that after the transfer I just have to "repair table table_name" for some of the tables in dev_db and the repair statements returns some message saying that the number of rows has changed. Of course since I don't want to go over each db and see if it actually needs a repair I chose to just repair all of them and I wrote a script for that.So I just run the bellow script after each transfer, just to make sure everything is ok:

  1. span style="color: #ff0000;">"localhost""root""";
  2. $dbname="dev_db""use $dbname""show tables""repairing table: ${arr[0]}\n""repair table ${arr[0]}"

This script should also show you the messages returned by the repair statements. So you can see if there really was a problem. Make sure you set the correct db connection parameters and database name before you try it.

When observing this problem I was using rsync version 2.6.9 and mysql 5.0.44 on gentoo x86_64. The problem doesn't come up on every transfer and not on all tables. Could this be a problem with rsync or mysql?

I'm thinking that if this is a problem with rsync then... wow...that is a big problem. I was relying on rsync for transferring a lot of stuff ... what if it didn't transfer something and who knows what else it didn't transfer?.

If it's a mysql problem, maybe mysql doesn't update the row counts on the tables correctly before shutting down so the files were actually correctly transferred just not correctly stored by mysql. If the row count is the only problem here then it's not such a bit problem. I'm hopping this is the case ...

I wonder if this problem would show up when using something like cp for the transfer. If that would happen then it's clearly a mysql problem but I cannot test with cp at the moment as my db is very large and that means I would have to keep the tables locked too much which is just not an option on a system that was just "promoted" to production.

I'll come back with another post once I find out more about this problem but until then just make sure to check your tables after the transfer if you are using something like rsync to transfer the files directly.

MacOSX case sensitivity

MacOSX, , , ,

I just got the biggest slap from MacOSX. Yes, I was punished for my ignorance.  I had not idea that the HFS filesystem defaults to case insensitive. I thought it would be case sensitive like in Unix but no.

When I first saw it I didn't believe it. The first clue was when I ran mysql_install_db an I got some warnings about about the filesystem being case insensitive. I didn't believe it because I knew I was able to create files with lower and upper cases so I just ignored it.

The confusion comes from the fact that the HFS+ filesystem is  MyFile.txt would be the same as myfile.txt .  The filesystem preserves cases so it will remember the original file name and a ls will show that but you would be able to access the file with different names. At the first glance this seems like a neat feature but it can actually create big problems when you're interacting with files from case sensitive filesystems.

The slap came from using rsync to backup files from a Linux machine to a macmini machine.   Just imagine a directory with many files split over directories from a to z and A to Z on Linux, something like files/a , files/A, files/b, files/B .

Now rsync -avz -e ssh  files/  backup@macmini:~/files/   and you'll end up with a folder named files on macmini but it will only have the upper case subfolders. The files in the lower case subfolders would be put in the upper case subfolders. This if really bad, it can really break consistency and mess up the data ( imagine different files with the same name one in A and the other in a as just one of the possibilities, there are other like this)

So now, what ? Reformat and change the filesystem to be case sensitive and journaled for easier recovery.
Be careful the next operation will remove all data on your disk:

  1. span style="color: #ff0000;">"Case-sensitive Journaled HFS+"

YourDiskId is the disk that you want to erase.It can be disk1, disk2,etc... Look for it using diskutil list

Repair a MySQL table

databases, , ,

I'm running mysql 5.0.23 on a FreeBSD server. I have several databases there and a few phpbb forums.

I noticed the tables used for searching the forums ( phpbb_search_wordlist and phpbb_search_wordmatch ) crash quite a lot lately from various reasons but mainly because of hardware problems ( like lack of power 🙂 ). Nothing unusual here so far. When I notice this I go into mysql and do a repair like :

  1.  

But this time I got this answer: " Table is already up to date". So MySQL client tells me the table is fine but in phpbb when I try to search something I get this error message: "SQL Error : 145 Table './simscripts_phpbb/phpbb_search_wordlist' is marked as crashed and should be repaired". I try to read the table from the mysql client and I get a similar message.

Going through the MySQL documentation I find some extra parameters i can pass to repair table. And use_frm seems to be the one that fixes the problem. This parameter should recreate the indexes by looking at the .frm file ( the structure definition of the table )

  1.  

did the job and search in phpbb is back online.

Unfortunately it seem that the table was so badly damaged that no rows could be recovered so the repair did was to recreate the database structure. Good thing I had a backup!

OR maybe it was just because my table was created on an older mysql version and as the documentation says:

Caution

Do not use USE_FRM if your table was created by a different version of the MySQL server than the one you are currently running. Doing so risks the loss of all rows in the table.

I don't know if this is a bug only in the version I run, but I think MySQL should really look at indexes and if they need to be recreated it should just do it automatically or atleast tell you the table is not ok instead of lying like that.

Update:

If you have shell access to your server with root or mysql user permissions you can go in the mysql data directory ( usually /var/lib/mysql or /var/db/mysql on freebsd ) go into your broken database directory and use myisamchk to repair the table without the risk of losing all the rows in it :

  1.  

freebsd 7.0 outperforms linux

freebsd, Linux, , , ,

The development version of FreeBSD ( 7.0 ) seems to scale a lot better then linux on SMP systems.

A combination of latest freebsd scheduler ULE 2.0 that is built into FreeBSD 7.0, the libthr threading library and a patch (not commited, yet ? ) that addresses poor scalability of file descriptor locking and some other patches is what made a system with 8 core amd64 cpu and 16 GB of ram outperform linux by a factor of 4 in MySQL tests.

The tests were performed using sysbench OLTP benchmark, a tool designed for testing mysql performance.

Brief test results: Linux is actually a little ( ~ 2% ) faster then FreeBSD for less then 9 clients, but when the number of clients grows to 20 linux performance drops a lot and FreeBSD's performance stays the same.

More details about the test here:

http://people.freebsd.org/~kris/scaling/mysql.html