MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
1answer
2k views

Mysql Memory table getting many locks

On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table. This table has the following activity: 1 SELECT per minute 1 DELETE per minute no UPDATE queries Lots of ...
8
votes
3answers
13k views

How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?

MySQL Server 1 is running as Master. MySQL Server 2 is running as Slave. With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will ...
4
votes
2answers
4k views

When should I think about upgrading our RDS MySQL instance based on memory usage?

It seems like our DB server is doing garbage collection at a increasingly faster rate, which seem normal since it's growing. What's a good rule of thumb of when to switch to a bigger instance, I'm not ...
3
votes
4answers
11k views

Optimized my.cnf for high-end and busy server

Curretly my main culprit of poor performance is MySQL and I'm not a DB savvy. So far, I've had little luck in optimizing mysql configs on my server, which has a Sandy Bridge processor with 16GB ram ...
6
votes
2answers
2k views

Insert-heavy InnoDB table won't use all my CPU

I have a packet log database, which is almost never queried. It just needs to be fast on inserts. I'm using InnoDB because I'd like to maintain ACID compliance, since even losing a single packet could ...
4
votes
3answers
1k views

Log MySQL DB changing queries and users

For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out ...
5
votes
1answer
774 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
1
vote
2answers
3k views

Stress test MySQL with queries captured with general log in MySQL

Is there any tool available which can do stress testing using the log file created by MySQL general log? After a lot of search on google I found few stress testing tools which only use some benchmarks ...
6
votes
4answers
6k views

MySQL gives “Out of Memory” error when inserting a large file. From where is this filesize limitation arising?

I'm using a MySQL database to store files. The table I'm using is structured as follows: +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default ...
5
votes
1answer
1k views

MySQL Replication : 1 Slave / Multiple Masters

I have a web app with 2 web servers and 2 database servers. The DBs are setup for multimaster replication. (this is the primary environment) I also have the exact same setup on a different location ...
4
votes
1answer
4k views

Mysql Master-Master Replication Topologies on >2 machines

I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote. Let's call my main server A, the secondary server B, and a remote, development ...
3
votes
1answer
905 views

query cache hit value is not changing in my database

The query cache hit value is not changing in my database only for some queries.If I write select * from drugs_info limit 1000; the query cache hit has changed, but if I execute the following ...
3
votes
1answer
1k views

FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional

fulltext match is ignoring its index when I add a number of words conditional for its boolean mode. The selects are as follows: explain select * from seeds WHERE MATCH(text) AGAINST ("mount ...
2
votes
1answer
358 views

row locking within ACID transaction innodb

I have a financial transaction that looks something like this (pseudo): begin select record for update update record(same record as selected) insert another record into another table ...
2
votes
2answers
1k views

How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
2
votes
1answer
2k views

MySQL upgrade 5.0.88 to latest

Very interesting and informative blog.. I have a question - i have a requirement of upgrading from 5.0.88 to latest version.. Why should i upgrade? if so, which version should i consider? What ...
2
votes
2answers
952 views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
2
votes
1answer
937 views

Create a custom log table in MySQL

Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. ...
57
votes
5answers
5k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
13
votes
7answers
21k views

Get the rank of a user in a score table

I have a very simple MySQL table where I save highscores. It looks like that: Id Name Score So far so good. The question is: How do I get what's a users rank? For example, I have a users ...
23
votes
1answer
27k views

intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”

Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
13
votes
7answers
21k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
25
votes
8answers
12k views

Tool to generate large datasets of test data [closed]

Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
9
votes
3answers
52k views

MySQL: how to restore table stored in a .frm and a .ibd file?

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install ...
5
votes
1answer
3k views

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

I just started dabbling with the query cache for our CMS. Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free? ...
2
votes
2answers
9k views

MySQL Error Reading Communication Packets

In MySQL error logs, I see these quite few a warnings like these: 120611 16:12:30 [Warning] Aborted connection 2619503 to db: 'db_name' user: 'user_name' host: 'webapp_hostname' (Got an error reading ...
8
votes
2answers
7k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
7
votes
1answer
8k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
6
votes
1answer
3k views

How large will a MySQL database be relative to the dump file?

I'm restoring a ~52GB dump to a MySQL database. The ibdata1 file has already exceeded the size of the dump file and the restore is still incomplete. Is there any way to estimate the final size of the ...
6
votes
4answers
3k views

MySQL Replication - Introduce new Slave to replication

I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL ...
4
votes
3answers
5k views

Disable trigger for just one table

Is it possible to disable a trigger momentarily but for just one table. For example I have table, TableA with an on insert, update and delete trigger. I also have a table B with the same triggers but ...
3
votes
2answers
2k views

MySQL on SSD - what are the disadvantages? [closed]

Recently, I heard that I should not host my MySQL database on an SSD drive because there is a big chance of disk failure and data lost. Given that 20% of all queries in my database are INSERTs and ...
3
votes
1answer
5k views

Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands: $ pt-table-checksum h=master,u=user,p=password ...
1
vote
2answers
778 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
7
votes
2answers
12k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
7
votes
4answers
4k views

One slave, multiple masters MySql

Is it possible to set-up MySql Replication to have one slave listening to two different masters?
5
votes
2answers
1k views

Choosing MyISAM over InnoDB for these project requirements; and long term options

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific. My project's aim is to let users search a (huge) database of variety of products. ...
5
votes
1answer
6k views

How to setup replication(Master/slave) in MySQL 5.5.20?

I have two servers both running on MySQL 5.5.20 and I want to setup replication, so every update/insert on Master would reflect immediately on Slave machine. I tried following this tutorial (I used ...
5
votes
1answer
2k views

What is the difference between XtraBackup and rsync?

Yes, I'm going to run strace on them both... However, I would like to know ... What is XtraBackup is doing over and above rsync -ac src dest ? What's so special about XtraBbackup ? How is XtraBackup ...
4
votes
2answers
2k views

Select rows where value of second column is not present in first column

I have this table structure: id1 id2 1 2 1 3 1 4 2 1 2 5 I need to build a query to select id2 where id1 is not in id2. For example if id1 = 1 then only id2=3 and id2=4. I tried ...
3
votes
1answer
11k views

Recover MySQL database from data folder without ibdata1 from ibd files

My WAMP directory accidentally get deleted by another user. Only data folder in MySQLis available. And, in that only database-folders (folders in "\bin\mysql\mysql5.6.12\data\" with name of databases) ...
3
votes
3answers
4k views

Can I specify a different disk for a specific MySQL table?

I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is ...
3
votes
2answers
2k views

Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE

If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction. This is done in order to "lock ...
2
votes
2answers
756 views

Mysql database replication on different vlan/subnet/another site

Saw the posts for "MySQL high availability, failover and replication with Latency" Is it possible to work it in a DR site when its not in the same vlan?
0
votes
1answer
734 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
10
votes
5answers
3k views

Is it unwise to run replication on same physical server?

I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
5
votes
1answer
461 views

“Site Offline” MySQL server failing to start and stop

I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the ...
5
votes
1answer
750 views

will replication from 5.5.20 to 5.0.XX server work?

I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible? Because I remember when I transferred ...
4
votes
3answers
5k views

What is the mysql.db table used for?

Related to my MySQL issue I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database. I can't find ...
4
votes
2answers
7k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...