Category Archives: databases

Howto check MySQL replication consistency

databases, ,

If you want to be sure the data on the slave is the same as the data on the master ( yes sometimes is can happen to bbe diffeent ) you can use pt-table-checksum ( part of percona-toolkit ) to compute checksums for the data in the tables and then compare the checksums from the master with the ones on the slave(s).

Some options

Use with replication

pt-table-checksum can be used to compare any two databases/tables but if you want to compare everything on master and it's slaves you can use the
--replicate option to connect only on the master and compute checksums. The checksums will then be computed on slaves too by replicating the checksum statements.

Detecting slaves

If you have slave hosts running on non standard ports use option --recursion-method=hosts to tell pt-table-checksum how to detect the slaves. Tell the slaves to report their hostname and and port with report-host and report-port in my.cnf . This will make them show in "show slave hosts" issued on the master.

If the "hosts" method doesn't work, try with --recursion-method=dsn=t=dbname.dsns_table . Create a table with the name "dsns_table" and the following structure in the "dbname" database:

CREATE TABLE `dsns` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `dsn` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
)

And put the dsns for accessing the slaves in the dsn field.
Example:

insert into dsns values ('','h=1.1.1.2,u=root,P=3306,p=slavepassword');

For non innodb plugin

--lock-wait-time is required if using a version of mysql without innodb plugin.

Database for storing checksums

pt-table-checksum stores the checksums in mysql so you have to create a database where to store the checksums table. I named mine "mk" since the tool was called mk-table-checksum before it became part of percona toolkit.

Use the --create-replicate-table option to create the checksums table if it doesn't already exist.

Example usage

pt-table-checksum --recursion-method=hosts --lock-wait-time=50 --defaults-file=/home/mihai/mysql.pass -u root --create-replicate-table --replicate-check --replicate mk.checksums 127.0.0.1

Example output

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-13T18:25:13      0      0      361       1       0   0.019 mydb.accounts
09-13T18:25:13      0      0       91       1       0   0.079 mydb.announcements

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.

managing mysql binary logs

databases, , , ,

Binary logs is how mysql keeps track of what changed in the databases. While it is recommended (in case you want to recover the database ) and sometimes even required ( if you want to replicate the db ) to keep such logs if you have a server where the database changes frequently those logs will occupy a lot of your disk space.

If you are tempted to just delete ( rm ) some of the old logs files DON'T do it. Or if you do it remember to also update the index file and remove the lines containing the log files you have deleted from it otherwise you will get in trouble, depending on your version mysqld server  might not start next time.

A better way then deleting them directly from the file system is to use the "purge logs" statement to delete all logs prior to a certain log file or prior to a certain date.The only problem with this is that you still have to remember to do this from time to time or set up a cron job to do it or else you will have to do it when mysql dies because it ran out of disk space. Luckily there is an even better solution.

There is a configuration option for mysql server that allows you to specify the number of days you want to keep logs for. Everything older then that number of days will be automatically deleted my the mysql server. The configuration variable is named: expire_logs_days. Something like expire_logs_days=30 will delete all log files older then 30 days

Warning! purge logs and expire_logs_days might not work if you deleted the bin logs files directly from the file system. To make them work  you will have check each line in the .index file. Each line in the .index file contains a bin log file name. If any file mentioned in the .index file doesn't exist on disk you will have to delete that line. Then just restart the mysql server.

One other tip to make the logs use less disk space is to tell the server not to record logs for databases where you don't care about loging ( like databases you only use for development or testing that might still get a lot of updates but you don't want to replicate them or you don't need to recover them if anything breaks ) .Here you can either tell mysql to only keep bin logs about some databases or to ignore others. The binlog-do-db and binlog-ignore-db configuration options will help you with this.

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.  

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.

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.  

mysql replication monitor

databases, shell, , ,

This is not a tutorial about how to set up mysql replication. You can find all the details about how to set up mysql replication in the official mysql documentation. This is just a script that can be used to monitor a MySQL replication setup. A MySQL replication setup consists of a master server and a slave server. On the slave server there are two threads that run continuously, one is the I/O thread that fetches changes that occurred on the master server and one is the SQL thread that tries to run the queries that were executed on the master server.

Continue reading mysql replication monitor →

Three methods to transfer a mysql database

databases

Most of the web sites use some form of database from text files to MySQL, PostgreSQL, Oracle, MSSQL , Sqlite and others.

At some point as a webmaster you may have to change the server hosting a website and If you have to transfer a mysql database from a server to another you have a various options. Not all of them can be used on any servers and each has it's advantages and disadvantages.

This is a list of methods alog with a description and howto for each method as well as advices about when to use each method. Continue reading Three methods to transfer a mysql database →

phpPgAdmin 4.1 released

databases,

Yesterday the phpPgAdmin Team announced a new major release of phpPgAdmin. Version 4.1 adds many new features, bug fixes and updated translations over the previous version.

Download

To download right now, visit:
http://phppgadmin.sourceforge.net/?page=download

Demo

To give the fully-functional demo a try, visit:

http://phppgadmin.kattare.com/phppgadmin4/

Continue reading phpPgAdmin 4.1 released →