MySQL : Open-Source, Relational Database Management System
3
votes
1answer
3k 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 ...
2
votes
2answers
417 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 ...
2
votes
3answers
7k 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 ...
2
votes
3answers
1k 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 ...
2
votes
2answers
512 views
MySQL general log
Is there away to have MySQL dump its log to a remote MySQL server?
I would like to audit MySQL connections but I don't want the server to store all the information local on its own hardware.
2
votes
1answer
929 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 ...
2
votes
2answers
242 views
get column from too many tables in mysql
.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this
desc X1;
...
2
votes
1answer
468 views
LOCK IN SHARE MODE
On dev mysql LOCK IN SHARE MODE is described as...
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
1
vote
1answer
2k 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
2answers
469 views
MySQL Community Edition: Can I replicate my entire server with another server in one go?
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.
...
0
votes
0answers
279 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 ...
7
votes
2answers
3k views
MySQL Sharding vs MySQL Cluster
Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning
When I refer to sharding, I'm considering sharding made in the ...
28
votes
8answers
5k views
Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.
For ...
5
votes
3answers
3k 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 ...
8
votes
1answer
4k views
How is INFORMATION_SCHEMA implemented in MySQL?
The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?
When I connect to a fresh ...
5
votes
3answers
3k 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 ...
12
votes
2answers
857 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 ...
10
votes
4answers
8k views
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
5
votes
6answers
6k 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
...
4
votes
1answer
1k views
Capabilities of InnoDB INSERT Performance
Hi I am running the most recent version of Percona Server.
Server version: 5.5.24-55 Percona Server (GPL), Release 26.0
I have a 10 cpu box of these characteristics.
processor : 0
vendor_id ...
10
votes
5answers
2k views
How do I force one record to have a true value for a boolean column, and all others a false value?
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.
Basically, I want to guarantee that this query will always ...
4
votes
1answer
1k 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 ...
4
votes
6answers
2k views
Too many columns in MySQL
I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the ...
11
votes
5answers
3k views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
6
votes
1answer
206 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, ...
5
votes
1answer
347 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
2answers
3k views
Ibdata usage and Recommendations?
I have a Production server
On November 5,2011 the ibdata size was 100G.
In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G.
I ...
4
votes
1answer
992 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 ...
3
votes
3answers
559 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
3answers
483 views
How costly is opening and closing of a DB connection?
How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL
... when the DB software is on localhost?
... when the DB software is on another machine?
3
votes
1answer
1k 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 ...
3
votes
2answers
465 views
Concurrently update with limit mysql
I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db.
After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned.
I ...
2
votes
2answers
588 views
Separate tables or partition one huge table?
I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
1
vote
2answers
1k 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 ...
1
vote
1answer
729 views
importing myisam 5.0 database into a 5.5 innodb server
I have MySQL 5.0 and 5.5 servers (but not a 5.1 server). The 5.0 server is myisam only, the 5.5 default is innodb. I need to move a database from the 5.0 server to the 5.5 server. The database is ...
20
votes
5answers
32k 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?
8
votes
3answers
682 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
7
votes
1answer
2k views
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then ...
6
votes
2answers
1k views
Why does MyISAM support FULLTEXT Searching and InnoDB does not?
I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
5
votes
4answers
492 views
MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preface
Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
5
votes
2answers
196 views
Less restrictive query return less result due to simple removing one additional constraint
Look at this query
SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
...
4
votes
2answers
275 views
Less RAM than Index_length MyISAM
I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
4
votes
1answer
820 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
3answers
454 views
Separate databases for front-end and back-end
The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more.
I was ...
3
votes
1answer
184 views
Setting up MySQL circular replication in existing replication topology?
I have the following MySQL replication topology set up for fault tolerance/disaster recovery with master server A and slaves B and C:
A replicates to B
A replicates to C
Both B and C are fully up ...
3
votes
1answer
833 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 ...
3
votes
4answers
904 views
Is raid 5 suitable for a mysql installation?
Is raid 5 suitable for a mysql installation?
Let me explain my application further. My application is a socket programming which will connect with gps device to receive gps string and there after do ...
3
votes
2answers
718 views
One slave, multiple masters MySql
Is it possible to set-up MySql Replication to have one slave listening to two different masters?
3
votes
2answers
371 views
How to resolve the master server shut down/unavailability in mysql with master - slave replication
Let us suppose that we have a master/slave setup and the application is running fine for the time being. All of a sudden the master server suddenly shuts down due to some problem.
Now how can I ...
3
votes
1answer
979 views
Does `Seconds_Behind_Master` show exact Slave Lag from Master?
Does Seconds_Behind_Master show the correct result?
I have also Implemented the mk-heartbeat replication monitoring.
It was showing the other result in comparison with Seconds_Behind_Master.
Which ...
