MySQL : Open-Source, Relational Database Management System
3
votes
1answer
524 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 ...
3
votes
1answer
725 views
Suddenly have to rebuild indexes to prevent site from going down
I have been having this problem for a while:
http://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column
It seems all is good and when I suddenly get a lot of writes to the table ...
3
votes
1answer
648 views
Determining max database and table size supported and present size
One of my MySQL databases has been running on a single PC from the last four years.
Here is my setup:
MySQL 5.5.14
Windows XP on NTFS partition
All of my tables are using InnoDB engine
Here are ...
3
votes
3answers
474 views
With MySQL Replication, what level of resilience is possible?
I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing ...
3
votes
3answers
2k 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 ...
2
votes
1answer
264 views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
2
votes
2answers
589 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 ...
2
votes
3answers
1k views
Mysql users deleted
I have a fresh centos 6.2 with mysql in 5.1.52. Then I saw two empty user name and I deleted that then I saw 3 root user deleted that and created one like this CREATE USER 'root'@'%' IDENTIFIED BY ...
2
votes
1answer
170 views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
2
votes
1answer
433 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 ...
2
votes
1answer
847 views
Stuck outside MySQL! XAMPP - help please
I have been using xampp (on windows XP/XAMPP 1.7.4 /PHP 5.3.5) for quite a while now, and last morning I started to get some weird messages when I was developing my website, It happened yesterday and ...
1
vote
2answers
193 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 ...
1
vote
4answers
151 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 ...
1
vote
1answer
647 views
How do I reduce the database size on AWS RDS MySQL?
I have an RDS MySQL database on Amazon Web Services.
I want to reduce the size of the database in the control panel in order to save money.
Q: How do I make it smaller? Their site says you have to ...
1
vote
1answer
144 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
2k views
Mysql master-master replication auto flush old logs
I have mysql-5.1.41 master-master setup running on ubuntu machines and below is the configuration of a master:
skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
...
1
vote
1answer
236 views
Understanding mysqltuner Recomendations w/ Unused Catalogs
I have a MySQL 5.0.77 instance running with three catalogs in it.
Two of the catalogs contain all InnoDB tables and are actively used.
The third is all MyISAM and is basically unused, and is only ...
1
vote
2answers
300 views
Any problem will all InnoDB and one MyISAM table?
I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM ...
0
votes
1answer
74 views
Translating backup size to database size
I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere.
Is there a good rule of thumb for translating the size of a backup into the size of the database once it ...
153
votes
14answers
35k 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 ...
5
votes
3answers
6k 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". ...
11
votes
2answers
2k 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 ...
3
votes
2answers
5k views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
23
votes
5answers
2k views
When is the right time to use MariaDB instead of MySQL, and Why?
What we earn and what we lost with this migration?
What should I expect as drawbacks after the migration?
Is it really unnecessary to change the applications in any situation?
9
votes
1answer
842 views
MySQL table with 100,000 records queried often
I have a single database of about 100 tables to store various kinds of information.
The most important table is our order table which is used to store customers orders and is over 100000 records as ...
8
votes
2answers
3k views
kill all queries - MySQL
Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing?
On account of how I don't like typing.
7
votes
1answer
907 views
Is it possible to have passwords configured per database or per host in .my.cnf
I have the following in my ~/.my.cnf
[client]
password="somepass"
but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different ...
6
votes
2answers
2k 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 ...
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
3
votes
2answers
4k views
How to cache mysql database in server memory
I am having problems with on website with 600MB of mysql database. The website is way too slow and i noticed the bigger the mysql database gets the slower it gets. When it was like 5MB website was ...
3
votes
1answer
374 views
What are the concepts that need to be gone through for MySQL DBA?
I am a MySQL Developer, besides I am handling some DBA activities like
backup, restore, mysql installation, etc at a top level.
Now I wanted to shift to DBA, So I wanted to know in depth, like which ...
17
votes
4answers
4k 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 ...
6
votes
3answers
3k 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 ...
6
votes
2answers
2k views
What are the benefits of using MySQL Cluster?
I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?
5
votes
2answers
3k views
InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)
This server is a virtual machine running CentOS 6.0, MySQL 5.5.21
There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 ...
5
votes
4answers
7k views
Why does MySQL say I'm out of memory?
I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception:
Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
...
4
votes
1answer
78 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
3answers
2k views
Fastest way to move a database from one server to another
I'm moving a single MySQL database (~10GB uncompressed) from one server to another on the same network. The current MySQL version is 5.1.41 and the new version is 5.5.24. The database contains both ...
4
votes
1answer
1k views
what are the conditions under which mysql replication might break?
I am running mysql server in master/slave configuration mode. There are multiple slaves replicating from a master. They are working fine and their replication status is monitored.
One of the slaves ...
3
votes
1answer
449 views
MySQL Server Backup
I was wondering if there was any way of making a backup of an entire MySQL Server at regular intervals to a specific location. (If the version is required I can get it easily).
If there is, I must ...
3
votes
2answers
430 views
Difference between backup methods: Oracle and MySQL
I was reading that Oracle databases have cold, warm, full and redo backups.
Questions
How is it possible that MySQL doesn't have as many options?
I have read that MySQL has cold backups (it puts ...
2
votes
2answers
1k views
Reset MySQL root password in LAMPP server on Ubuntu
I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do
# /opt/lampp/lampp security
it gives result as
XAMPP: Quick security check...
XAMPP: Your XAMPP ...
2
votes
1answer
223 views
Configured MySQL replication but its not working
I have configured MySQL master and slave replication setup on 5.5.x.
But for some reason data is not getting replicated properly. What might be the reason?
At Master:
/etc/my.cnf
[mysqld]
...
2
votes
2answers
578 views
Can we assign mysql Key_buffer_size value for particular table?
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:
SET GLOBAL myisam_table.key_buffer_size = 4194304;
CACHE INDEX test.myisam_table ...
2
votes
2answers
2k views
LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?
MySQL version: 5.5.13
The query that I'm using:
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ',';
COMMIT;
...
2
votes
3answers
1k 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 ...
8
votes
3answers
2k 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 ...
6
votes
1answer
2k views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
6
votes
1answer
2k 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?
5
votes
1answer
3k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
I'm a beginner system administrator for a small virtual server. The main function of the server is to run the open-source Moodle course management system software, written in PHP. It relies on a ...
