MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
3answers
2k views

Is MySQL Replication Affected by a High-Latency Interconnect?

We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master. The bandwidth between the datacenter is pretty high (in ...
3
votes
1answer
776 views

How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
4
votes
2answers
4k views

How can you have two auto-incremental columns in one table?

I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to ...
4
votes
2answers
2k views

Running multiple instances on the same host

I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with: mysqld_multi MySQL Sandbox This should be a long term solution for an ...
3
votes
2answers
5k views

Scheduled optimization of tables in MySQL InnoDB

What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
3
votes
2answers
2k views

Setting Circular Replication in mysql

I have 3 MySQL servers say A,B,C. What I want to do is that I want to make all of them master as well as slave.If there is update on any of the MySQL Servers it should be replicated to all of the ...
3
votes
1answer
4k views

Mysql fulltext search my.cnf optimization

I have open a question on http://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here. We built a news site. Every day we will input tens of ...
30
votes
3answers
14k views

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to ...
5
votes
1answer
9k views

Best way to setup master to multi master replication

ORIGINAL QUESTION: I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers ...
12
votes
2answers
12k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
7
votes
3answers
15k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
9
votes
2answers
2k views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
6
votes
2answers
3k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
6
votes
3answers
5k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
6
votes
1answer
4k views

When to switch from MyISAM to InnoDB?

We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM. My original plan was to upgrade ...
4
votes
3answers
1k views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
10
votes
3answers
7k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
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 ...
5
votes
2answers
3k views

Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
3
votes
3answers
3k views

MySQL slow log configuration

Is there a way of making MySQL slow logs to start new log file every day ? At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have ...
3
votes
3answers
717 views

Restoring an old backup to latest MySQL release

Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new ...
11
votes
5answers
20k views

MySQL server has gone away obstructing import of large dumps

I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a ERROR 2006 (HY000) at line 7758: MySQL server ...
8
votes
3answers
7k views

Audit logins on MySQL database

Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results. The ...
5
votes
2answers
3k views

Is this a normal set of MySQL privileges?

Using phpmyadmin on a local test wamp setup, and well as pretty much the title states, there are 3 users marked as user = ANY, password = NO, such as: USER | HOST | Password | Global Priv | Grant ...
5
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 ...
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 ...
5
votes
1answer
7k views

How to solve “The table … is full” with “innodb_file_per_table”?

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
5
votes
1answer
722 views

MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

I have a basic table : create table fullTextTest ( id INT(11) NOT NULL, superText CHAR(255) NOT NULL, superLongText TEXT NOT NULL, primary key (`id`), FULLTEXT KEY `superText` ...
1
vote
1answer
5k views

How to enable MySQL general log?

I would like to enable the MySQL General Log to view all the (slow) queries. I'm using XAMPP as server and have no idea how to enable it. I can't find a good guide on the internet. Can someone help ...
1
vote
1answer
3k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
92
votes
5answers
164k views

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?
14
votes
7answers
37k views

Need to migrate SQL Server to MySQL

I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
15
votes
3answers
4k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
6
votes
2answers
3k views

Scaling Percona datacenters: setup and replication

Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you. We have already scaled alot of our application stack: we offloaded ...
8
votes
2answers
7k 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 ...
4
votes
1answer
2k views

Proper tuning for 30GB InnoDB table on server with 48GB RAM

I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
12
votes
2answers
1k views

What can we do in MySQL 5.0 Replication to address bandwidth concerns?

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
5
votes
1answer
3k views

Best solution for cross-datacenter MySQL master-slave replication

We are working on a new system architecture for our company. We have a HPC, which runs in our own data-center and we are planning our front-end and a fallback system on Amazon Web Service. System ...
5
votes
1answer
600 views

Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
8
votes
3answers
12k 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
1answer
261 views

How badly does innodb fragment in the face of somewhat out-of-order insertions?

Let's say there's a composite primary key on (field1, field2) Also, let be X a theoretical set of n numbers Inserts look like this: INSERT INTO table(field1, field2) VALUES([a number picked at ...
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
10k 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
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 ...
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 ...
4
votes
3answers
17k views

Cannot GRANT privileges as root

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
4
votes
3answers
3k views

Changed max_allowed_packet and still receiving 'Packet Too Large' error

I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
1
vote
2answers
2k 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 ...
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 ...