MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

10
votes
2answers
5k views

Why are all MySQL InnoDB tables fragmented?

For some reason, all InnoDB tables in my MySQL server are being listed as fragmented when I run mysqltuner. I only installed the server a few hours ago (on OSX Lion), and it has a bunch of fresh data ...
10
votes
2answers
789 views

Is there a MySQL performance benchmark to measure the impact of utf8_unicode_ci versus utf8_general_ci?

I read here and there that using the utf8_unicode_ci collation ensures a better treatment of unicode text (for example, it knowns how to expand characters such as 'œ' into 'oe' for searching and ...
9
votes
9answers
5k views

How to store 3 million records in key value format?

We have to store basic information about 3 million products. Currently the info is one 180 mb CSV which gets updated quarterly. There will be about 30,000 queries per day, but the queries are just a ...
9
votes
8answers
912 views

What is a relational database? [closed]

I'm mostly a system administrator and I don't directly work with databases other than installing them, setting up accounts, granting privileges, and so on. I realized that if The Boss walked up to me ...
9
votes
4answers
11k views

MySQL 5.1 or 5.5?

Are there significant differences between versions 5.1 and 5.5? The server in question is used to host a medium-sized vBulletin forum. The main benefit of 5.1 is it's available through apt-get.
9
votes
9answers
30k views

How can I reset the password for MySQL? [duplicate]

I have an old MySQL installation for which I do not have the password. How can I reset the password? My first idea was to completely remove it and reinstall with a new password. I am using Windows ...
9
votes
3answers
9k views

How to automatically kill slow MySQL queries after N seconds?

I am looking for a well tested bash script (or alternative solution) to do so, in order to avoid max_connection to be exhausted. I know that it is fighting the symptoms, but really need such script as ...
9
votes
3answers
16k views

How Export Result of MySQL Query on PHPMyAdmin 3.4.3?

1) I've got a 30K row table. 2) When I run a long, 50-line query on that table, a GROUP function reduces the number of rows to 7K. 3) I want to export the grouped 7K rows as a new table, or save them ...
9
votes
2answers
307 views

My server setup for a heavily used API

I will soon be buying a bunch of servers for an application that I am about to launch but I have concerns about my setup. I appreciate any feedback I get. I have an application which will make use of ...
9
votes
3answers
656 views

Anyone used MySQL Forks in Production

Now that MySQL is owned by Oracle many are starting to consider using some of the popular forks such as MariaDB and Drizzle. Has anyone used these in production for any extended periods of time? If ...
9
votes
2answers
2k views

What should I know before going live with an InnoDB database?

What things should a server- or DB-admin know and do before making a InnoDB database available. I'm thinking about things like setting correct values for innodb_buffer_pool_size and ...
9
votes
3answers
337 views

Is it possible to extract last month's values from a MySQL server and build a new database from those values?

I have an assignment to build a development virtual machine template. I need to add to the server MySQL databases from each one my company's products so that new developers will have the ability to ...
9
votes
5answers
384 views

MyISAM for data reads

I have a table with around 1 Billion rows, and its 98% read intensive. I tried tuning the database, with different storage engines (MyISAM and InnoDB) Then ran a few test to view the performance In ...
9
votes
1answer
8k views

MySQL: InnoDB: ERROR: the age of the last checkpoint is X, which exceeds the log group capacity Y?

In a production MySQL environment the following error message is written to /var/log/mysql/error.log every fourth minute: 110723 18:36:02 InnoDB: ERROR: the age of the last checkpoint is 9433856, ...
9
votes
2answers
3k views

MySQL doesn't logs error to new file after rotating?

Problem solved but I'm writing down for the future reference. /root/.my.cnf [mysqladmin] user = root password = pa$$w0rd /etc/logrotate.d/mysql /var/log/mysql-slow.log ...
9
votes
2answers
5k views

How do I setup monitoring of MySQL with Fail2ban?

Searching the internet with search engines on MySQL and fail2ban yields lots of results on putting your fail2ban logs into MySQL, however I'm wanting to monitor failed MySQL attempts to log in and ban ...
9
votes
2answers
9k views

How do I create a read only MySQL user for backup purposes with mysqldump?

I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file. I've ...
9
votes
2answers
514 views

MediaWiki slow save of edited pages

I have MediaWiki 1.16.2 on a the following LAMP platform, which performs quite good for other apps on a 16 GB RAM dual processor machine: CentOS 5.7 (64-bit) Apache 2.2.3 MySQL 5.0.77 PHP 5.1.6 It ...
8
votes
6answers
8k views

Use LDAP for MySQL authentication?

We run a couple dozen different MySQL servers for our users. These use the free/Open Source version of MySQL, not the commercial version. Managing the Account Passwords on these servers is painful. ...
8
votes
5answers
21k views

How to improve MySQL INSERT and UPDATE performance?

This question can probably be asked on StackOverflow as well, but I'll try here first... Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance ...
8
votes
5answers
14k views

What should mysqld.sock contain, why don't I have it?

Does anyone know why my /var/run/mysqld/mysqld.sock socket file would not be on my computer when I install (or reinstall) MySQL 5.1? Right now, when I try to start up a server with mysqld, I get ...
8
votes
7answers
63k views

“Error 1067: The process terminated unexpectedly” when trying to install MySQL on Win7 x64

I've run into a brick wall trying to install MySQL v5.5 on my machine. My PC is Windows 7 x64, Enterprise edition. MySQL installs fine, but when I run the "MySQL Instance Configuration Wizard", it ...
8
votes
2answers
6k views

Installing a third party application package on CentOS 6.4 fails due to missing dependencies libcrypto.so.10 and libssl.so.10

This is a Canonical Question about OpenSSL binary compatibility issues between Red Hat Enterprise Linux (and its derivatives) 6.4 and 6.5. This issue applies to a wide variety of third party ...
8
votes
5answers
8k views

Can a MySQL slave be a master at the same time?

I am in the process of migrating 2 DB servers (Master & Slave) to two new DB Servers (Master and Slave) DB1 - Master (production) DB2 - Slave (production) DB3 - New Master DB4 - New Slave ...
8
votes
3answers
8k views

How to copy user privileges with MySQL?

I have a MySQL installation with many databases and users that I need to migrate to a new MySQL installation. I can use phpMyAdmin to export then import the databases/tables, but I don't know of ...
8
votes
2answers
8k views

MySQL InnoDB database 'hangs' on selects

I'm trying to fix MySQL configuration on our server. Our app's specifics is that a lot of data is stored in single table (currently over 300 millions of rows). This table is used often for inserts ...
8
votes
4answers
3k views

How to efficiently dump a huge MySQL innodb database?

I got an Ubuntu 10.04 production MySQL database server where total size of database is 260 GB while size of root partition is itself 300 GB where DB is stored, essentially means around 96% of / is ...
8
votes
3answers
4k views

Postgres vs MySQL: Which one is more difficult to scale out?

From your experience, which database serve is more difficult to scale? MySQL or Postgres? I know MySQL has several out-of-the-box scaling/clustering features, but with Postgres there is none that ...
8
votes
6answers
3k views

Optimal way to make MySQL backups for fairly large databases (MyISAM / InnoDB)

Currently we have one beefy MySQL database that runs a couple of high traffic Django based websites as well as some e-commerce websites of decent size. As a result we have a fair amount of large ...
8
votes
4answers
3k views

Has anyone migrated production machines from MySQL to MariaDB?

I'm running MySQL for most of our DBs right now, and would like to get off the Oracle ship before it takes more of a turn. My understanding is that the transition from MySQL->MariaDB should be ...
8
votes
7answers
2k views

Is MySQL better than PostgreSQL in something?

I know the question sounds provocative, but it really isn't. I'm lately finding MySQL limiting in a lot of areas and liking PostgreSQL more and more. It scales a lot better and it respects the SQL ...
8
votes
2answers
808 views

Accidently locked myself out of MySQL by changing the host

While trying to get remote MySQL working, I somehow managed to lock myself out with the root account. Normally, I would stop the MySQL service and restart it using /etc/init.d/mysql --init-file, but ...
8
votes
3answers
6k views

Cant connect to mysql using self signed SSL certificate

After creating a self-signed SSL certificate, I have configured my remote mysqld to use them (and ssl is enabled) I ssh into my remote server, and try connecting to its own mysqld using ssl (mysql ...
8
votes
1answer
7k views

Changing MySQL data directory in Ubuntu Server 10.04

I was getting the following error when trying to change the data directory in ubuntu server 10.04. 100809 19:33:00 [Note] Plugin 'FEDERATED' is disabled. /usr/sbin/mysqld: Can't find file: ...
8
votes
2answers
9k views

Postfix “warning: cannot get RSA private key from file”

I just followed this tutorial to set up a postfix mailserver with dovecot and mysql as backend for virtual users. Now I got the most parts working, I can connect to pop3 pop3s imap and imaps. Using ...
8
votes
3answers
4k views

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This ...
8
votes
2answers
6k views

How do I optimize MySQL's memory usage?

MY SETUP ( example ) I am running Linux on an Amazon High CPU Extra Large EC2 instance with the following specs: 7 GB of memory 20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units ...
8
votes
6answers
516 views

How to grow from single server setup

I'm looking for resources on how to grow our server setup. We currently have one dedicated server with Rackspace in the UK of the following spec: HPDL385_G2_PrevGen HP Single Dual Core Opteron 2214 ...
8
votes
3answers
1k views

Unsecured MySQL 'root'@'localhost' account accessed remotely?

A little background: We've just had our PBX system hacked. The server itself seems secure (no logged unauthorised console access - SSH etc), but somehow the hackers have managed to inject a new admin ...
8
votes
3answers
343 views

Adding CPUs puts MySQL 5.5 (Debian) performance down

I am about to setup a database server (MySQL) in OpenVZ container and I've been wondering how many CPUs I should assign to it. I decided to benchmark it. I compared two OS/MySQL distributions and ...
8
votes
1answer
2k views

Mysql Slave stuck in “System lock”

My MySQL slave is spending a lot of time in Slave_SQL_Running_State: System lock. I can see that the system is currently I/O write bound, and that it is processing the log, although slowly. Show ...
7
votes
3answers
1k views

Production server has 16GB of RAM and a 32 bit OS. Replace with 64 bit OS?

I have a production server with 16GB of RAM that came with a 32bit CentOS installation. The website hosted on this server is getting increasing amounts of traffic every day, which has led to some ...
7
votes
7answers
12k views

How to capture the queries run on mysql server?

We're trying to do some server performance debugging and I would like to capture a snapshot of the queries being run on our mysql server over a period of a couple minutes. I'm familiar with mysql's ...
7
votes
2answers
14k views

What kind of server do I need to handle 10 million requests and mySQL queries a day? [closed]

I'm a new bie of server administration and I'm looking for a powerful hosting service to host my new website. This website is basically a back-end of an mobile online game, and it will: handle up to ...
7
votes
3answers
6k views

MySQL users changed their IP address. What's the best way to deal with this?

I have a number of users who are connecting to MySQL over a VPN, so we have grants along the lines of "grant select on foo.* to user@ipaddress1" and so on. This week, the IP used on the VPN changed ...
7
votes
2answers
5k views

mysqldump prompting for password in shellscript

I wrote a simple shell script to dump a specific mysql database. problem is it is prompting me for a password even though I provide on. The mysql db is version 5.0.27 if that matters. Here is the ...
7
votes
9answers
2k views

How do you usually back up your database?

I am using mysql How often do you back up your database? How do you normally backup your database? Export all data into sql or cvs format and keep it in a folder??
7
votes
2answers
15k views

How big is too big for ibdata file?

My ibdata file is very large, at least it seems to me to be very large. Is this excessive or not that bad? -rw-rw---- 1 mysql mysql 15G Apr 18 10:11 ibdata1
7
votes
2answers
16k views

Can't create/write to file '/tmp/#sql_xxxx.MYI' (Errcode: 13)

I have installed Debian Lenny, PHPmyadmin and postfix. When using PHPmyadmin GUI and access any table with data i get: Can't create/write to file '/tmp/#sql_xxxx.MYI' (Errcode: 13) doing ...
7
votes
3answers
7k views

Set group_concat_max_len permanently (MySQL config)

I have MySQL installed on an Ubuntu machine. I added this line in /etc/mysql/my.cnf group_concat_max_len=15360 But there is no effect. Every time I restart mysql, the value is set to 1,024. I have ...