MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
308 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
2answers
1k 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 ...
3
votes
2answers
422 views

Sorting the Table and getting the position

I am able to sort a table using the following code:SELECT * FROM Persons ORDER BY LastName But I wanted to get the ranking/position of a person using that order. For example when I sort Person table ...
3
votes
1answer
1k 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 ...
3
votes
1answer
1k 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 ...
3
votes
4answers
1k views

Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that ...
2
votes
1answer
1k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
2
votes
1answer
519 views

MySQL upgrade 5.0.88 to latest

Very interesting and informative blog.. I have a question - i have a requirement of upgrading from 5.0.88 to latest version.. Why should i upgrade? if so, which version should i consider? What ...
2
votes
2answers
266 views

MySQL export user with semi-colon “;” on the end

I am trying to set up automatic export of user accounts from one server to import into another. I am running the following command; $ mysql -u root -p -B -s -e "show grants for 'myuser'@'localhost';" ...
2
votes
4answers
407 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 ...
2
votes
1answer
968 views

Binlog has bad magic number

I keep getting this error whenever I start MySQL. 121028 1:38:55 [Note] Plugin 'FEDERATED' is disabled. 121028 1:38:55 InnoDB: The InnoDB memory heap is disabled 121028 1:38:55 InnoDB: Mutexes and ...
2
votes
1answer
111 views

Combining columns in index

With reference to RolandoMySQLDBA's answer on MySQL: Index when joining to tables not being used (Performance optimizing question) He mentioned Make sure you build an index that involves the ...
2
votes
1answer
3k 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 ...
2
votes
2answers
1k views

Problem with InnoDB “per table” file sizes

I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file. I have truncated the table now it has zero record. But the ...
2
votes
1answer
265 views

How to do MySQL User Accounting

I can't seem to find any information on this anywhere. I found a question here called Monitor MySQL activity per database? but that's not quite the answer either. I'd like to be able to log the ...
2
votes
2answers
2k 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
2answers
442 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 ...
2
votes
1answer
3k views

Fixing MySQL errors recorded in error logs

Below is the extract of the most common lines of error being recorded in error logs on my PC: 110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled. 110905 16:16:54 InnoDB: The InnoDB memory heap is ...
1
vote
3answers
2k views

Single slave - multiple master MySQL replication

I need to replicate different MySQL databases from multiple servers into a single slave server. How can this be done? is there a way to define multiple master hosts?
1
vote
1answer
134 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 ...
170
votes
15answers
47k 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 ...
6
votes
2answers
11k views

What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration. I face a lot of problems while setting up mysql master-slave replication. I also face regular mysql replication troubleshooting issues. Can anybody helps ...
10
votes
1answer
2k 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 ...
7
votes
2answers
3k 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 ...
5
votes
4answers
3k 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 ...
9
votes
2answers
6k 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.
8
votes
3answers
5k 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 ...
8
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?
6
votes
1answer
273 views

What corner cases exist when relying on undocumented behaviour to determine values selected by MySQL for hidden columns in GROUP BY operations?

Under GROUP BY and HAVING with Hidden Columns, the MySQL manual documents (added emphasis): MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named ...
6
votes
2answers
962 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 ...
6
votes
4answers
12k 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) ...
5
votes
1answer
6k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
5
votes
2answers
7k views

Why do MySQL tables crash? How do I prevent it?

I'm the Administrator of a Moodle site which had its Users table corrupted and it became unusable. Luckily, a simple REPAIR TABLE mdl_user made it work back again. The thing is that I don't know why ...
5
votes
3answers
10k views

How to recover MySQL table structure from FRM files

What I have learned from Google searching: With MyISAM tables you can simply copy the FRM, MYD, and MYI files. In the folder for the DB I'm trying to recover, there are only FRM files and a "db.opt" ...
5
votes
2answers
6k views

What is the safest way to switch the binlog format at runtime?

Because of the following warning in mysqld.log: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it ...
5
votes
3answers
4k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
4
votes
5answers
7k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
4
votes
2answers
6k 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 ...
4
votes
1answer
485 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 ...
3
votes
1answer
1k views

MySQL Replication and High Availability

I know this might not be a very good question to be asked in this site, but I really need some helps/hints to be directed to the right path. I want to have failover strategy for 2 MySQL servers which ...
3
votes
2answers
994 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
1answer
3k views

MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

When reading online about innodb_log_file_size, it says The combined size of the log files must be less than 4GB So what happens if it needs to be larger? In books and online, it says to set ...
20
votes
4answers
5k 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
4answers
9k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
5
votes
3answers
3k 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 ...
5
votes
1answer
648 views

Quick MySQL Backup (1 file per table)

I asked a relevant question few months back and got answer with wide variety of options for backup. But this time I have different scenario and I have got a database with 7 tables and 1 of them has ...
5
votes
1answer
2k 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 ...
4
votes
1answer
6k views

How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine. When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work. I manually ran OPTIMIZE TABLE ...
4
votes
1answer
700 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 ...
4
votes
2answers
565 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 ...