MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

9
votes
2answers
6k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
9
votes
3answers
6k views

Replicate RDS-MySQL to a non-amazon host?

I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only ...
9
votes
3answers
18k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
9
votes
7answers
46k views

importing large SQL file phpmyadmin

I am trying to import this SQL file in phpmyadmin and got this message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
9
votes
2answers
14k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
9
votes
3answers
6k views

How to convert mysql to postgresql? [closed]

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
9
votes
2answers
22k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
9
votes
2answers
7k views

move data folder and drive mysql centos

i need to move the data to a different folder and drive on my cent os db server. i added new hard drive (since we ran low on space), i have a 1tb of hard drive and i have a new 2tb i want to put the ...
9
votes
1answer
234 views

Power went out - Did query finish?

Is there a way to check and see if a query finished? I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week. Unfortunately, somewhere during the ...
9
votes
3answers
2k views

MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

I am creating a module where every user often gets a record into a table for 10 to 300 seconds. When the time expires a record gets deleted. The case is: there will be a lot of users and records will ...
9
votes
3answers
13k views

How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
9
votes
6answers
13k views

Alternatives to MySQL Workbench [closed]

I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a ...
9
votes
1answer
4k views

Is there such thing as Custom Data Types?

Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
9
votes
3answers
6k views

MySQL status variable Handler_read_rnd_next is growing a lot

In MYSQL status, Handler_read_rnd_next value is very high. I am aware that, this value will be incremented when a query is executed which is not having proper indexes. But, even when we execute ...
9
votes
4answers
6k views

Adding an index very slow…is there a mysql cmd to get an ETA or show progress?

I'm currently running an alter query on my table (20M entries) in order to add an index. it's running for more than 3 days already (stuck on 'copy to tmp table'). is there a way I can see the ...
9
votes
1answer
596 views

Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

I am trying to have a score for entire section of SELECT statements SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword') UNION SELECT ...
8
votes
9answers
2k views

Which DBMS are fast enough for an online game (few thousand players)?

I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
8
votes
3answers
5k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
8
votes
1answer
2k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
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 ...
8
votes
5answers
14k views

Why is DROP DATABASE taking so long? (MySQL)

New CentOS installation. I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to ...
8
votes
4answers
28k views

mysql: Show GRANTs for all users

MySQL's SHOW GRANTS shows the permissions of the current user. Is there a way to log in as root and show the permissions of all users?
8
votes
2answers
9k views

What to set innodb_buffer_pool and why..?

I have 170GB of InnoDB index and Data. I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB. So what should be the optimal ...
8
votes
2answers
10k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
8
votes
2answers
20k views

How can I move a MySQL database to another drive?

I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: ...
8
votes
4answers
880 views

Is splitting a 'users' table for authentication purposes a good idea?

Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table. For speeding up my login process, is it a good approach to split my user table, one for ...
8
votes
5answers
37k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
8
votes
2answers
12k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
8
votes
3answers
11k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
8
votes
3answers
4k views

How to stress test a SQL server?

I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare ...
8
votes
2answers
20k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
8
votes
2answers
5k views

Running SQL Query on all databases

I have one hosting account using cPanel and phpmyadmin. I have 50 databases under this account, all WordPress. I need this query modified so that it runs through all databases to update the ...
8
votes
5answers
15k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
8
votes
6answers
3k views

What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
8
votes
3answers
2k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
8
votes
4answers
2k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
5answers
5k views

How to search a MySQL database with encrypted fields

Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt. How would one search those fields anyway? Decrypting each ...
8
votes
9answers
9k views

What tools are there for data masking? (MySQL, Linux) [closed]

I'm looking for (ideally free, open-source) data masking tools. Do any such exist? Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
8
votes
3answers
3k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
8
votes
2answers
322 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
8
votes
1answer
669 views

Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis

We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable. Right now we are running this query daily, at ...
8
votes
3answers
5k views

Monitor MySQL activity per database?

What is the best way to monitor activity on each database of my server ? I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it. I ...
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 ...
8
votes
2answers
14k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
8
votes
1answer
9k views

Error creating foreign key from MySQL Workbench

I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key: Executing SQL script in server ERROR: Error 1005: ...
8
votes
1answer
2k views

MySQL reads / writes per table

I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives. Is there a way ...
8
votes
1answer
6k views

Does mysqldump export indices, by default?

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option: --disable-keys, -K which suggests, ...
8
votes
1answer
1k views

Is there an execution difference between a JOIN condition and a WHERE condition?

Is there a performance difference between these two example queries? Query 1: select count(*) from table1 a join table2 b on b.key_col=a.key_col where b.tag = 'Y' Query 2; select ...
8
votes
1answer
3k views

MySQL Workbench Database Sizes

I'm trying to find the total size on the hard disk that all of my MySQL Workbench databases are using. Does anyone know of an easy way to figure this out? If nothing else, the default location ...
8
votes
1answer
3k views

Cannot drop anonymous user from mysql.user

I am trying to DROP the anonymous users from my mysql.users database. However, I have been getting odd behavior. When I enter the command: DROP User ''@'WOPR'; I was getting a generic error ...