All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

7
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
4k 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 ...
5
votes
1answer
3k 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
4answers
2k views

Backup and restore “mysql” database

I use the following command to backup some normal databases: mysqldump --host=host --user=user --password=passwd --result-file=result.sql -R --single-transaction database_name My question is : do I ...
4
votes
2answers
13k views

I've got 16GB of ram, how should I configure MySQL Server?

I've got a dedicated production server with multiple cpus and 16GB of ram. I would like to know how can I configure it better to take advantage of the server resources for better performance because ...
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
2k 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' ...
16
votes
3answers
4k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
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 ...
8
votes
4answers
6k views

One slave, multiple masters MySql

Is it possible to set-up MySql Replication to have one slave listening to two different masters?
7
votes
3answers
331 views

Implementing versioning system with MySQL

I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help. Initially I had my blogstories table with this structure: | Column ...
6
votes
1answer
19k 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) ...
6
votes
1answer
2k views

Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
5
votes
1answer
7k 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 ...
4
votes
3answers
3k views

InnoDB vs MyISAM with many indexes

I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway: I have a ...
3
votes
1answer
4k views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
3
votes
1answer
15k views

How can extract the table schema from just the .frm file?

I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine. I have read a fair number of tutorials today ...
3
votes
4answers
8k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
3
votes
3answers
5k 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
966 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?
1
vote
1answer
884 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 ...
5
votes
2answers
781 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
4
votes
3answers
7k 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
9k 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 ...
3
votes
2answers
1k views

Quick MySQL Backup

I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack. Or mysqldump will work ...
3
votes
3answers
609 views

Dynamically changeable database

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
2
votes
4answers
7k views

Single slave - multiple master MySQL replication

I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? is there a way to define multiple master hosts?
2
votes
1answer
966 views

MySQL Cluster — Storing table data on data nodes

I have setup MySQL Cluster with the MySQL server node and the Management node on a single server, and 2 data nodes (each on a different server for 3 servers in all). After installing, I run ndb_mgm ...
1
vote
1answer
213 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
1
vote
1answer
1k views

mysql restart won't kill child processes on CentOS

I have a mysql database running on a CentOS server. I have noticed that, when left to run for several weeks without restarting the service, then when I need to do a restart (/etc/init.d/mysqld ...
1
vote
1answer
991 views

Removing/overwriting sensitive data located in already deleted records in ibdata files

As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file. I know this is by-design, and that I could switch the ...
1
vote
1answer
921 views

decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

i recently testing the move of our db from one server to another. as part of this process i copied all the data to the new db and started testing it. as a comparison i run a store procedure whose ...
1
vote
1answer
926 views

Secure method beside phpMyAdmin for remote mysql

Recently, one of my centos linux servers was hacked and the cause of due to phpMyAdmin. It was said due to this problem PhpMyAdmin vulnerability CVE-2011-2505 ...
214
votes
15answers
86k views

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
12
votes
5answers
22k views

Cannot open table mysql/innodb_index_stats [duplicate]

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well. The errors I ...
9
votes
3answers
17k 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 ...
7
votes
2answers
16k views

Why do MySQL tables crash? How do I prevent it?

I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable. Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why ...
5
votes
1answer
10k views

InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Server Specification: VPS with following info model name : Intel(R) Xeon(R) CPU E5649 @ 2.53GHz MemTotal: 2058776 kB MemFree: 244436 kB We are running IP.Board from Invision ...
8
votes
2answers
6k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
5
votes
3answers
5k views

MySQL general query log performance effects

One of our customers who has a somewhat problematic web application would like us to log all the queries for a period of 24 hours. I wouldn't know how much data it will actually write. The amount ...
7
votes
4answers
9k views

How to improve InnoDB DELETE performance?

So I have this audit table (tracks actions on any table in my database): CREATE TABLE `track_table` ( `id` int(16) unsigned NOT NULL, `userID` smallint(16) unsigned NOT NULL, `tableName` ...
17
votes
1answer
24k views

INT(5) vs SMALLINT(5)

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
16
votes
3answers
8k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
9
votes
3answers
8k 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 ...
2
votes
1answer
4k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
21
votes
4answers
8k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
4
votes
2answers
1k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
4
votes
2answers
8k views

Trying to understand SHOW SLAVE STATUS\G

I have slave setup and looks like it is running fine. Below is a snapshot of it. show slave STATUS\G; *************************** 1. row *************************** Slave_IO_State: ...
4
votes
2answers
3k views

What happens when InnoDB hits its tablespace autoextend max?

The latest version of High Performance MySQL (3rd ed.) recommends that one: ...place an upper limit on the tablespace's size to keep it from growing very large, because once it grows, it doesn't ...