MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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 ...
2
votes
2answers
3k views

Mysql consumes a lot of memory

Here is my situation. I have an ubuntu server monitored by nagios. recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command ...
6
votes
1answer
3k 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 ...
2
votes
1answer
789 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 ...
3
votes
1answer
984 views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
1
vote
1answer
1k views

MySQL transfer ISO-8859-1 to UTF-8

How to transfer data from Database with latin1 character set, to database UTF-8. And on transferring convert data from latin1 to UTF-8 e.g., û->ū, ë->ė, þ->ž, á->į and etc.
6
votes
1answer
446 views

MySQL database design

I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past. Ok, my implementation needs ...
2
votes
1answer
91 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
1
vote
0answers
69 views

innner join troubles [closed]

Dang it's hard to send you well wishes here. I wish you all a good year before this post is deleted. Remember to love your fellow developers. I'd like to join using(love) where i=heartU group by ...
2
votes
1answer
3k views

MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run. Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH ...
1
vote
1answer
505 views

MySQL Replication: Slave with Different Column From Master

I have a Master/Slave replication. I had to change the name of one of my columns in one of my tables from temp_col to sent_at, this column is not being used yet. I've issued an alter table on an ...
2
votes
2answers
534 views

MySQL Full Text index issue

I Have a table articles with following data mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title | body ...
0
votes
1answer
200 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
3
votes
2answers
5k views

How to make CREATE INDEX in InnoDB table faster

I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time: Time | State | Info 2291 | copy to tmp ...
1
vote
2answers
494 views

Is there a tool or library to extract data from Mysql database files without having the database engine available?

I have a bunch of *.frm, *.myd and *.myi files, which are a MySQL database store. I need to extract the data kept in these files, but I do not have a MySQL database engine at my disposal. Is there a ...
2
votes
2answers
246 views

How much will computer hardware affect the speed of a “load data local infile” query?

I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has ...
11
votes
5answers
1k views

Infrastructure for Highly Concurrent, High Write DB

My requirements are: 3000 Connections 70-85% Write vs Read Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
2
votes
1answer
144 views

Why cp1256 collation is not 1 byte in MySQL?

I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for ...
4
votes
3answers
241 views

How to design this simple DB?

How to design this simple DB? I need to add some things to a DB I use to keep track of servers. Table looks something like this. Matrix | Name | Description | Etc | Etc | ...
1
vote
2answers
576 views

MySQL Sharding tables/servers or other methods to reduce table sizes?

I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. ...
1
vote
5answers
747 views

MySQL Replication monitoring [closed]

Im looking for a tool that could help me and my team monitor our MySQL Replication. I would love for a tool that could alert me if Seconds_Behind_Master passes a threshold Slave_SQL_running is ...
0
votes
1answer
1k views

I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

I have database "db2" with table "menjava" In table menjava have "id", "author" and "date_submitted" field id - auto_increment author - int(11) date_submitted - datetime I want to count all the ...
1
vote
2answers
1k views

Why a copy of my.ini of other MySQL installation not working on my PC

I have two MySQL installations on two different PCs not connected to each other. I made some changes in the my.ini file of PC 1 and tried using this file on PC 2, but MySQL service fails to start. Why ...
2
votes
1answer
98 views

How to insert fields to different tables at once?

5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT. TEMP: TempID, Email, Name, Product1, Product2, Date, Amount PAYMENT: PaymentID, Status, Amount, Date CUSTOMER: CustomerID, Email, ...
1
vote
1answer
1k views

How to set up triggers in MySQL for copying a field

I have an in-house application that I've put together, and I'm VERY new to SQL in general. I have a table that contains most of our data. Let's pretend it looks like this. Matrix |Name | ...
3
votes
1answer
170 views

Help configuring MySQL install for remote connections / root accounts understanding

I installed mysql/phpmyadmin/apache on a linux box for internal network use only. I don't have the strongest understanding of MySQL. Hostname: server.domain.lan IP: 192.168.0.54 After I installed ...
1
vote
1answer
380 views

View metadata refresh in MySQL

Like other RDBMS, view meta data is stored at view creation time. From CREATE VIEW in the MySQL docs The view definition is “frozen” at creation time, so changes to the underlying tables ...
7
votes
2answers
2k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
1
vote
2answers
5k views

Deleting duplicates with group by and count

What is the fastest method to convert the following query: SELECT COUNT(*) as c FROM tbl_fields WHERE fieldnotes IS NULL GROUP BY fieldno,fieldserial,id,fielddate,fieldsid HAVING COUNT(*) > 1; ...
3
votes
1answer
2k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
2
votes
3answers
2k views

What's the difference between master and slave nodes in MySQL Cluster?

I use MySQL Cluster 7.1.15a. I have 2 nodes, ndb_mgm -e shows: [ndbd(NDB)] 2 node(s) id=3 @X (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0, Master) id=4 @Y (mysql-5.1.56 ndb-7.1.15, ...
2
votes
1answer
1k views

MySQL Config for Large MyISAM tables

I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
2
votes
1answer
241 views

What's the purpose of /*!*/; in mysql binlogs?

The title pretty much sums up the extent of my question. They seem to appear after every statement. Sometimes on a new line following. At other times, at the end before the ;. What's the point?
2
votes
1answer
1k views

How to upgrade MySQL to a New version..?

I have installed mysql using the package management system.I am using ubuntu machine. After that i have very well configured the server for InnoDB and other server related parameters. I am using the ...
2
votes
2answers
3k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
2
votes
3answers
2k views

Improve `Update` performance (rows locking issue)

I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database. When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
1
vote
1answer
280 views

MySQL full text search ranking

I found in this article the formula that MySQL use to ranking in FTS w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf) Where dtf is the number of times the term appears in the ...
2
votes
1answer
3k views

Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ...
1
vote
2answers
116 views

Quantity of entries

I have the below tables.The first one has posts with a foreign key to a category table.I'm looking for a query that gives me the amount of posts on every category. The post table. select ...
2
votes
1answer
2k views

How can I get a list of dates between two given dates with mysql?

I need to generate a list of dates with MySQL between two given dates. This list of dates must be filtered to the dates that correspond to Tuesday and Thursday. Is it possible?
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 ...
2
votes
2answers
800 views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
3
votes
1answer
929 views

How do you gain access to embedded MySQL database via localhost command line?

We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so. A quick glance at the ...
6
votes
3answers
496 views

Combining InnoDB with MyISAM [duplicate]

Possible Duplicate: Is it common practice to mix InnoDB and MyISAM tables on same server? I have a movie clips and photos table in an application; these tables contain a tag column, which ...
3
votes
1answer
50 views

Is time to deliver data included in time measured for a Slow Query

I'm looking at some horrible numbers in a SLOW_QUERY_LOG (one example) Count: 2563 Time=23.21s (59498s) Lock=0.00s (1s) Rows=21.4 (54857) This is for a simple SELECT statement that runs for less ...
4
votes
1answer
1k 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 ...
5
votes
2answers
591 views

Is it a good idea to split MySQL DB into two servers

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate: I ...
3
votes
2answers
807 views

Website is too slow when number of records increases?

I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
2
votes
2answers
161 views

mysqldump seems to copy excessively

I have an old_server with 50 databases. I want to create new_server with just 3 databases. When I try using mysqldump, it seems like all the database directories (although they are empty on ...
4
votes
1answer
4k views

In a MySQL Master/Slave replication, what would happen if I write to the Slave?

Several Questions Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave? If not, how do I ensure that the Slave is in-sync with ...