MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
998 views

php/mysql database redesign and migration: changing databases to tables

I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old ...
5
votes
1answer
1k 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
1answer
125 views

Complex MySQL Query returning almost all the correct rows

I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work ...
2
votes
1answer
262 views

Changing to InnoDB increases iostat

I have changed my MyISAM tables to InnoDB and from that day, iostat is increasing continuously. My findings on the server say that mysqld is the one who is increasing iostat. But how can it increase ...
0
votes
1answer
48 views

All manual settings get wiped out when saved in My.cnf, why? [closed]

When I open my.cnf to enter manual setting like: innodb_buffer_pool_size = 50M innodb_log_file_size = 15M bulk_insert_buffer_size=25M log-slow-queries = mysql-inja-slow.log All of my settings get ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
1
vote
1answer
398 views

Need help optimizing mysql on a busy server

I am managing a server for a very busy news website with lots of select queries (read-heavy), concurrent about 4000 users online at peak time. Around 1.4Mil pages views / day The server has 42G RAM, ...
6
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 ...
1
vote
1answer
636 views

Restore the data from Snapshot Backup when the data spans over multiple directories?

Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF. We have done some testing work on one of the slave say server A.and it takes a huge amount of ...
0
votes
1answer
1k views

Centos 6.2 mysql configuration file help

I have a new centos 6.2 setup. Below is the configuration file. The machine is going to be fully innodb and it had 8Gb ram. What else must I include in the .cnf? [mysqld] datadir=/var/lib/mysql ...
3
votes
1answer
798 views

MySQL - Installing InnoDB Plugin

I have a Master/Slave Replication Setup, both servers run MySQL 5.1.61. I want to upgrade the master server to a stronger machine with the following: 2 processors - 6 cores each 48GB of RAM To ...
0
votes
1answer
3k views

Slave start to generate relay log files but not master server

I am settings up two new servers. One is the master and the other is the slave. Below is the config file for both. Master DB [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ...
1
vote
2answers
293 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...
1
vote
1answer
356 views

Will a composite Primary Key use the 15/16 page fill factor or the 1/2?

I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2. I was wondering if my PK ...
1
vote
1answer
471 views

For 768M RAM server is 617M allocation in my.cnf good or bad?

I have a write-intensive web app, and I bought a VDS with 768M RAM Is this my.cnf good for my purposes? I specified bulk_insert_buffer_size to 126M and some other parameters as below: ...
2
votes
3answers
516 views

MySQL - Master/Slave Replication with different server specs

I have a Master/Slave replication (using MySql 5.1 / InnoDB). Currently both my master & slave are running on the same type of server (32 GB RAM, 2 x Intel Xeon 5520 Quad Core 2.26 GHz (8 cores)) ...
3
votes
3answers
21k views

How to select/count rows where the same value exists in a column but not in other column all at the same table

I have this table structure and data: CREATE TABLE IF NOT EXISTS `default_relations_users` ( `id_user_rq` int(11) NOT NULL, `id_user_ap` int(11) NOT NULL, UNIQUE KEY `rusers_rq_ap_idx` ...
3
votes
1answer
626 views

why is mysql still writing data two hours after all transactions have stopped

I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data). Lately, I noticed something peculiar. After I ...
1
vote
1answer
141 views

How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected ...
0
votes
1answer
101 views

InnoDB possible error

We are moving now from MyISAM to InnoDB. We have faced previous problems in MyISAM where the index fail and the table is not available for access. So we are wondering what type of errors that InnoDB ...
3
votes
2answers
9k views

Copy complete structure of a table in MySQL without using a backup?

In MySQL, how can someone copy a table structure without using a backup? jcho360> show create table actor\G *************************** 1. row *************************** Table: ...
1
vote
1answer
112 views

converting mysql columns to bits in a single column

I have a mySQL table with multiple columns (tinyint) with 0 or 1 values I want to convert all these values into bits in a single integer column So a column named column1 will go to MSB another column ...
2
votes
2answers
71 views

Are these extra Index fields needed?

I've designed a table as below for users who register a quiz: CREATE TABLE `registered_quiz` ( `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned NOT NULL, ...
2
votes
1answer
276 views

Replication with only update and insert

I have set up my replication but I want to delete some outdated data from my master tables. Is it possible that say if I have 10 records at master and I delete 5 of them but I want those 10 records to ...
4
votes
1answer
13k views

mysql: How do you determine the ideal 'open_files_limit' setting value?

I am facing this issue: http://serverfault.com/questions/104618/out-of-resources-for-mysqldump Questions I know I need to increase open_files_limit, but to how much? Can I call a command in mysql ...
1
vote
2answers
623 views

Using an older MySQL binlog filename (with position zero)? Risks? Downsides?

Let's say I'm setting up a new slave. I start with a mysqldump after running mysql -e "STOP SLAVE", transfer that and restore it to the new server. If I didn't capture the BINLOG filename and ...
2
votes
1answer
4k views

How to Create Snapshot Backups in MySQL

I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup. This is what the book says: flush tables ...
2
votes
1answer
800 views

Poor MySQL for Drupal on Shared Hosting

My Drupal site is on a shared host where I cannot modify the my.cnf configurations but the database seems to be performing poorly. Here are some of the flagged issues that I can see... In ...
15
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 ...
5
votes
4answers
2k views

Exporting\Importing a large MySQL data without mysqldump

I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy. After running a simple --all-databases dump succeeds, but the import ...
1
vote
2answers
2k views

InnoDB missing from MySQL

I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL server. I recently upgraded it from the dotdeb repository, then installed mysql-server. There is no ...
1
vote
1answer
1k views

--local-infile=1 using MySQL Workbench?

When I start the MySQL bash client, I have been starting it with --local-infile=1 so that I can import data from a .csv file. Is there any way of doing this within MySQL Workbench?
2
votes
3answers
295 views

How to get all non deleted messages from one user

I have two tables one for messages called default_messages and the other for recipients called default_recipient. This is how SQL for those table looks like: CREATE TABLE IF NOT EXISTS ...
3
votes
3answers
22k views

How can we add BOOLEAN datatype in mysql query browser?

I am trying to add a BOOLEAN column using EDIT TABLE in mysql workbench, but it automatically changes to TINYINT on applying changes. How can I get the column to be BOOLEAN?
0
votes
1answer
254 views

Normalization and Breaking down tables for InnoDB

I'm designing a web application, something small but deals with lots of read and inserts. Let's say 60% read and 40% writes. My storage engine is InnoDB on MySQL. I have a table called User which has ...
3
votes
3answers
3k views

slow query with fulltext and left join

I have 3 tables about 250,000 records. table1 is MyISAM, add fullindex with title and content, add index with pid. table2 and table3 are InnoDB, add index with pid. Only query table3, just cost ...
1
vote
1answer
90 views

how to use mysql temporal cache tables

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table. Create table ...
3
votes
2answers
2k views

ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

I'm trying to import a mysqldump backup of a drupal database taken from a server running MySQL 5.0 (which will be upgraded to 5.5) onto my local machine running MySQL 5.5.23. The import fails with the ...
3
votes
1answer
763 views

How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as SELECT * FROM posts JOIN tags USING(post_id) JOIN tag_map USING(tag_id) WHERE tags.tag IN('tag1', 'tag2', 'tag3') tag_map is a many-to-many relationship between tables ...
3
votes
2answers
1k views

Cannot update certain rows in innodb tables

I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are ...
1
vote
1answer
286 views

pt-table-checksum from remote server

I am using pt-table-checksum for the first time and have some questions that I can't seem to resolve by reading the documentation on Percona's website. Per the documentation, pt-table-checksum ...
7
votes
2answers
9k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
1
vote
1answer
236 views

Getting slaves of a master-master setup stopped in sync

I have a master-master setup where each master has it's own slave. They layout might be illustrated as: M1 <===> M2 | | \/ \/ S1 S2 That is both S1 and M2 slave ...
2
votes
1answer
5k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
1
vote
1answer
523 views

What language and data structures does MYSQL uses as backend?

I am just curious to know what data structures and languages does MYSQL exactly use as back end language? and what does the file format .MYD , .MYI and .frm stand for and how does MYSQL use them? ...
2
votes
3answers
516 views

MySQL Cluster Installation

I am very new to mysql-cluster.I downloaded the binaries and was trying to configure it on my local machine. Can anybody suggest how to install it on single machine with the 1 management node,2 data ...
1
vote
2answers
7k views

Script to show mysql health status?

Kev suggested this question ( http://stackoverflow.com/questions/10524981/php-script-to-show-mysql-health-status ) is better fitted here. I know phpMyAdmin can shows the lists of mysql vars and ...
1
vote
2answers
264 views

Why are my results returned twice?

I've this query: SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id, `default_wall_posts_comments`.`comments` as comments, UNIX_TIMESTAMP() - default_wall_posts_comments.date_created ...
2
votes
1answer
86 views

MySQL replication constantly checking tables

I recently moved one of our slave databases to a new server, the database is purely used for offsite replication/backup and doesn't handle any of the live traffic. Since moving, the new server seems ...
1
vote
1answer
980 views

Triggers vs Views in MySQL

We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...