MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
4
votes
1answer
6k views

SQL command to remove prefix from a set of tables

I am no DBA. I have a situation with a MySQL database (5.0.51a) where I want to remove a 'dr_' prefix to all tables having this prefix (about 110 tables). I could rename them one by one of course, but ...
4
votes
1answer
1k views

Is there a way to find my.cnf in shared hosting?

I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?
2
votes
1answer
1k views

Is it possible to convert a .mysql file to a .sql file?

I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop. When browsing for ...
12
votes
4answers
17k 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
2answers
1k views

How to implement soft deletes?

What is your method of implementing soft deletes in a database? Ideally the solution would allow for decent performance on large tables take relationships into account take unique keys into account ...
10
votes
3answers
12k views

MySQL table_cache and Opened_tables

I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
9
votes
3answers
2k views

MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

I am creating a module where every user often gets a record into a table for 10 to 300 seconds. When the time expires a record gets deleted. The case is: there will be a lot of users and records will ...
5
votes
3answers
8k views

can't remove “GRANT USAGE”

I was testing some stuff, and added a: grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password'; so now when I do show grants for cptnotsoawesome@localhost; I can see ...
5
votes
1answer
6k 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
2answers
2k views

MySQL replication: “Houston, We've Got a Problem”

I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a Replicate_Do_DB: database1 ...
3
votes
2answers
2k views

How can I optimize performance on a MySQL ndbcluster?

So, this is what I have: A simple table classes__to_students: |class |student| --------------- Math Alice Math Bob Math Peter Math Anne Music Bob Music Chis Music ...
1
vote
1answer
988 views

Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite

Our old product supports 4 different database types for storing data: SQLite MySql Sql Server CE Sql Server There is also an export feature, which allows to export some data and here is the ...
2
votes
1answer
2k views

MySQL: What field types for large numbers?

I need to store some high numbers, some with decimal points (2 digits). 965.630,5 INT 47.320.500 INT -62.188,0 INT 32,8% FLOAT 225.165,7 DOUBLE 715.100,7 DOUBLE ...
6
votes
2answers
2k views

Why isn't mixed-mode replication default on MySQL

It is well known that mixed-mode replication was default from MySQL 5.1.12 to MySQL 5.1.28. However, this was reverted, and at the current moment, statement-based replication is default. In the ...
1
vote
1answer
160 views

Tick boxes and Dropdown

Have a look at the image below: This is "Plain Buger" item - customer can select sauces and extras with it. There are different sizes for Plain Burger. I need to translate this UI into database ...
2
votes
2answers
434 views

MySQL: Slow log; append CPU usage

I have the MySQL slow log feature enabled: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html But sometimes the query_times are high simply due to high CPU load. How can I append the current ...
0
votes
1answer
331 views

MySql - Global index

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table: CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, ...
1
vote
1answer
208 views

Randomly generated password for mysql admin over time?

I have this idea of making my database's Admin account secured by creating the following: Trigger is activated every hour in mysql this trigger activates a stored procedure Stored procedure ...
7
votes
1answer
2k views

MySQL reads / writes per table

I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives. Is there a way ...
1
vote
2answers
5k views

What would be the fastest approach to bulk update this mysql table (Innodb)?

I have a table with 100M rows. I am able update my table like 850 updates / minute. How to speed this up? I am using following approach: mysql -uroot < query_file where query_file contains 1M ...
2
votes
1answer
465 views

Programatically testing slave status - What fields in SHOW SLAVE STATUS indicated slave status is OK / in ERROR?

We use pingdom and a few internal heartbeat mechanisms on our product. We would like to add a 'are our slaves running and in good shape' check. What fields in SHOW SLAVE STATUS indicate that the ...
4
votes
1answer
840 views

Yum update mysql from 5.1 to 5.5

I was just in the process of 'yum updating' a server. One of the updates its suggesting to carry out is an upgrade from mysql 5.1 to 5.5. The mysql server operates in a master-master replication ...
5
votes
1answer
669 views

will replication from 5.5.20 to 5.0.XX server work?

I gave two machines which run on two entire different versions of MySQL server. I want to do the replication from 5.5.20 to 5.0 server. Will that be possible? Because I remember when I transferred ...
3
votes
1answer
4k 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 ...
4
votes
3answers
2k views

MySQL tables/databases into different disks

I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks. Lets say you have 5 ...
2
votes
1answer
3k views

How to recover a crashed innodb table…?

I Have a Innodb table i got the error as mysql> check table City; +-------------------+-------+----------+----------------------------------------------------------+ | Table | Op | ...
7
votes
1answer
5k views

Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings?

I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am ...
5
votes
1answer
335 views

Unexpected index scan when doing query against varchar column with numeric parameter

I have a test table with schema like this: CREATE TABLE `indextest` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ...
2
votes
1answer
3k views

mysqld - cannot connect to socket after changing datadir location

I have this weird problem after changing the MySQL datadir location from default "/var/lib/mysql" It was working yesterday, but today I get "Can't connect to local MySQL server through socket ...
2
votes
1answer
152 views

Mysql takes up to 50% cpu after aborting DELETE query

I have a problem I have never met before. I was clearing yesterday client's db and table with stats of visits that had 15GB of data. I decided to delete all rows older than 1 Feb, because we have all ...
1
vote
2answers
1k views

MySQL Database and Table Loss

I have installed mysql and XAMPP on my system. I wanted to remove the mysql service of XAMPP. After removing it, I tried to install the new service for mysql using "MySQLInstanceConfig.exe". but now I ...
3
votes
1answer
328 views

MySQL export/import yields error

When trying to export and import an 58G database yields the following error: ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Dump created using mysqldump -q --opt ...
2
votes
1answer
261 views

Converting 3 Master + 1 Slave to 4 Master - Any advice?

so we have a beautiful 3 master 1 slave cluster running. we would like to convert this to a 4 master cluster. here is how the topology currently looks s1 enslaved by s3 s2 enslaved by s1 s3 ...
3
votes
1answer
276 views

Xen snapshots and live Innodb tables?

I am wondering if I can trust my host doing it's nightly snapshots with live Innodb tables. From what I have read: "Innodb is ACID-compliant, so you can "safely" use fs snapshot with Innodb tables." ...
6
votes
2answers
6k views

Do I have to buy MySQL license?

I've read in a forum that if your database capacity is more than 1G you should buy its license. Is this true? How much will it cost?
6
votes
2answers
1k views

MySQL warm procedure

How do you usually warm up your database ? I run a similar queries to this for every table in DB: SELECT * FROM ip_log ORDER BY ID; SELECT ip, member_id FROM ip_log ORDER BY ip, member_id; Is that ...
2
votes
1answer
88 views

Moving the binary location of MySQL for replication

Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump > for export and mysql < for insert on the slave, because in our current setup it takes ...
1
vote
3answers
854 views

“Too many connections” when running MySQLDump

MySQL 5.5.x on Windows, 4GB RAM. I have a Wordpress Multi-Site install. The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now) I am running mysqldump ...
2
votes
1answer
1k views

MySql Switching Masters During Failover with log-slave-updates

I am still pretty newbie to mysql. This question is regarding promoting slave with option --log-slave-updates to become a master. The typical schema of promoting slave to master was described in ...
3
votes
2answers
5k views

Scheduled optimization of tables in MySQL InnoDB

What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the ...
2
votes
2answers
608 views

mySQL 5.1 changed innodb_log_file_size getting error on restart

In order to optimise my MySQL 5.1 (running on Window 2008 WebServer). I changed a few InnoDB options in my.ini namely the innodb_buffer_pool_size and innodb_log_file_size. But now when I am trying to ...
1
vote
1answer
394 views

Good strategy to replicate MySQL data

I'm working on MySQL database, well i have a master instance and slaves, and i need to add a new slave to Master. I adopt a simple strategy to do so: Stop slave, import data to new slave, and finally ...
0
votes
3answers
520 views

Is any tool for taking MySQL Dump Automatically with a particular time?

I am using VS2010 and MySQL for my project. My Client want to take MySQL Dump Every Day like Daily Backup. So I want to give a tool for Taking MySQL Dump with a particular time. Do you know any tool ...
0
votes
1answer
515 views

Changing MASTER_CONNECT_RETRY - Anything pit falls to keep in mind?

We run your basic Master + Multi-slave mysql replication setup. We would like to lower the MASTER_CONNECT_RETRY parameter on each of our slaves. Is there anything beyond re-executing CHANGE MASTER ...
1
vote
1answer
800 views

can mysql cli exit with error if no records returned?

I would like to execute a sql command in bash, and do something based on whether or not there was a result. e.g. $ LOOKUP=123; mysql -e \ 'SELECT id FROM table WHERE id='$LOOKUP dbname \ ...
8
votes
2answers
9k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
2
votes
1answer
1k 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

Copy blob from one row to another

I have a corrupt blob in a row. I'd like to replace that with another blob from the same table. How can I do that with sql statements only? (No file system access if at all possible.)
3
votes
2answers
2k views

pt-table-checksum with slaves using non-standard ports

Trying to run pt-table-checksum to detect slave drift in MySQL replication. My slaves are not using the standard TCP listening port however, and so when I run the tool I get an error: Cannot connect ...