MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

122
votes
5answers
170k views

MySQL: Bind to more than one IP address?

Is there a secret way to bind MySQL to more than one IP address? As far as I can see the bind-address parameter in the my.cnf does not support more than one IP and you can't have it more than once.
51
votes
9answers
60k views

How can I export the privileges from MySQL and then import to a new server?

I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server. For extra points, there are a couple of existing databases on the new ...
47
votes
1answer
52k views

InnoDB: Error: log file ./ib_logfile0 is of different size

I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine. innodb_buffer_pool_size = 2560M innodb_log_file_size = 256M innodb_log_buffer_size = 8M ...
42
votes
7answers
50k views

mysqldump to a tar.gz

Usually after dumping a MySQL database with mysqldump command I immediately tar/gzip the resultant file. I'm looking for a way to do this in one command: So from this: mysqldump dbname -u root -p ...
38
votes
7answers
31k views

What is the best Linux filesystem for MySQL (InnoDB)?

I tried to look for benchmark on the performances of various filesystems with MySQL InnoDB but couldn't find any. My database workload is the typical web-based OLTP, about 90% read, 10% write. Random ...
36
votes
10answers
29k views

`mysql_upgrade` is failing with no real reason given

I'm upgrading from MySQL 5.1 to 5.5, running mysql_upgrade and getting this output: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck FATAL ERROR: Upgrade failed ...
34
votes
4answers
8k views

Postgres equivalent to MySQL's \G?

Does anyone know if Postgres has a way to display query results "prettily", like how MySQL does when ending a query with \G on the command line? For instance, "select * from sometable\G" as opposed ...
32
votes
7answers
37k views

What is the debian-sys-maint MySQL user (and more)?

I have been bitten several times by the 'debian-sys-maint' user that is installed by default on the mysql-server packages installed from the Ubuntu repositories. Generally what happens is I pull a ...
30
votes
7answers
7k views

Mysql: Working With 192 Trillion Records… (Yes, 192 Trillion)

Here's the question... Considering 192 trillion records, what should my considerations be? My main concern is speed. Here's the table... CREATE TABLE `ref` ( `id` INTEGER(13) AUTO_INCREMENT ...
27
votes
4answers
3k views

Why Swap is used when plenty of free memory is left?

I have pretty good web (dedicated) server with good memory resource System information Server load 2.19 (8 CPUs) Memory Used 29.53% (4,804,144 of 16,267,652) Swap Used 10.52% ...
27
votes
5answers
3k views

What configuration options for MySQL provide the biggest speed improvements?

What configuration options for MySQL provide the biggest speed improvements? I'm wondering about actual configuration file improvements, table types, hardware setups, replication, etc. Anything other ...
26
votes
6answers
32k views

Where is my mysql log on OS X?

I checked /var/log and /usr/local/mysql and i can't seem to find the log. I am trying to troubleshoot an error establishing a database connection with a php function.
26
votes
3answers
119k views

How to enable MySQL logging?

I am running MySQL 5.0.45 on OS X 10.6.1 and I cannot get MySQL to log anything. I am debugging an application and need to see all queries and errors. I added to the etc/my.cnf: [mysqld] ...
24
votes
1answer
46k views

How do I change the privileges for MySQL user that is already created?

I understand how to create a new user with privileges, but what is the correct way to change privileges for users that are already created? We are running a DB audit and some of the users have way ...
23
votes
7answers
4k views

Backing up a 22 GB MySQL database daily

Right now I am able to do the backup using mysqldump. But I have to take down the web server AND it takes around 5 minutes to do the backup. If I don't take down the web server, it takes forever and ...
22
votes
7answers
35k views

How can I copy MySQL users table from one server to another?

I'm setting up up a new MySQL server and I'd like to give it the same set of usernames, allowed hosts, and passwords as an existing server (which is going away). Would it work to just do a dump of ...
21
votes
16answers
1k views

Why is it bad to have a weak mysql user password?

I was presented with an argument to the tune of "you don't need a strong mysql user password because in order to use it, they'd already have access to your server." We're talking about a 4 digit ...
21
votes
3answers
13k views

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres?

Is there a MySQL equivalent of SHOW CREATE TABLE in postgres? Is this possible? If not what is the next best solution? I need the statement because I use it to create the table on an remote server ...
21
votes
4answers
2k views

Can MySQL effectively take advantage of 64 GB RAM?

We have been running into a problem where querying a table that has approximately 50 million rows, and has an index size of 4 GB (table size of about 6 GB) results in the database server swapping ...
21
votes
2answers
23k views

How bad is setting MySQL's bind-address to 0.0.0.0?

I'm trying to allow a remote server to access a MySQL instance that currently shares a Linux server with a web app. According to the documentation the only way this would be possible (unless I'm not ...
20
votes
5answers
1k views

Is MySQL supposed to be installed alone

I often hear people making statements such as "our MySQL server machine failed", which gives me the impression that they dedicate a single machine as their MySQL server (I guess they just install the ...
19
votes
14answers
2k views

Should I force my users to change passwords every n days/weeks/month?

Question says it all. We are designing a system where security is very important. One of the ideas someone had was to force users to change passwords every 3 months. My take on this is that while its ...
19
votes
2answers
50k views

How can I show user's privileges in MySQL?

I know that I can set user's privileges in the following simple way: grant all on [database name].[table name] to [user name]@[host name]; But how can I see existing privileges? I need to see data ...
19
votes
5answers
12k views

Scripted install of MySQL on Ubuntu

I need to write a script that will build my server from a fresh Ubuntu server install. Among things like Apache and PHP it needs to install MySQL. The only problem here is that when I install MySQL ...
19
votes
5answers
6k views

Minimum permissions for a user to perform a mysqldump?

I want to start scheduling remote mysqldump crons, and I'd prefer to use a special account for that purposes. I want to grant that user the minimum permissions for getting a full dump, but I'm not ...
19
votes
4answers
8k views

What's a secure alternative to using a MySQL password on the command line?

We have a PHP command-line script to version a database. We run this script whenever a developer has added a new database patch. The script runs the patch with the MySQL command-line: system('mysql ...
18
votes
9answers
5k views

Architecture for highly available MySQL with automatic failover in physically diverse locations

I have been researching high availability (HA) solutions for MySQL between data centers. For servers located in the same physical environment, I have preferred dual master with heartbeat (floating ...
17
votes
4answers
23k views

How do I load a sql.gz file to my database? (importing)

is this right? mysql -uroot -ppassword mydb > myfile.sql.gz
17
votes
6answers
10k views

MySQL InnoDB - innodb_file_per_table cons?

By default MySQL InnoDB stores all tables of all DBs in one global file. You can change this by setting innodb_file_per_table in the config, which then creates one data file for each table. I am ...
17
votes
6answers
13k views

Optimize apache/php/mysql running on VPS for heavy load

Question about optimizing an apache/mysql server on a VPS with 512m of RAM. Under normal load everything runs fast, no connection lag. However when we get our heavy traffic days (50k+ visits) the ...
16
votes
3answers
3k views

Why not use a WAMP stack?

I recently had a talk with some experienced people and they suggested to me not to use a WAMP stack, and instead install apache, mysql and php separately. I don't understand why they have suggested ...
16
votes
5answers
6k views

what is the difference between INNODB and MYISAM

I am using MYSQL as my database for the current web project. And i am a newbie to MYSQL. Could any one explain me please what is the difference between INNODB and MYISAM. I hope the answers will help ...
16
votes
5answers
3k views

How do you choose a MySQL database engine

In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys). Does it always come down to trying both and measuring? Or ...
16
votes
2answers
13k views

Out of resources for mysqldump

I'm trying to do a mysqldump on a Windows server and I get the following error message : mysqldump: Got error: 23: Out of resources when opening file '.\db\sometable.MYD' (Errcode: 24) when using ...
15
votes
4answers
25k views

Connecting to MySQL from PHP is extremely slow

I just did a fresh install of XAMPP. When first opening PHPMyAdmin I noticed it was extremely slow. It didn't make sense that on localhost it should take almost 5 seconds for every page to open. I ...
15
votes
3answers
39k views

MySQL lowering wait_timeout value to lower number of open connections

I run a rather busy site, and during peek hours I see over 10.000 open connections to my database server on my webserver when a run a netstat command. 99% of the connections are in the TIME_WAIT ...
15
votes
4answers
6k views

Which database server scales better: PostgreSQL or MySQL?

I'm working on a website that could eventually have a very large database. The website will be an art community not until deviantART. I'll be running a lot of queries for analyzing how the site is ...
15
votes
3answers
28k views

What is the ibdata1 file in my /var/lib/mysql directory?

Logging in to my Webmin control panel, I noticed that virtually all of my disk space is full. I searched for the ten largest files/ directories on my system and found that a file called ibdata1 is ...
15
votes
10answers
16k views

How do I profile MySQL?

What tools exist to profile MySQL, like how MSSQL 2000+ does with the SQL Profiler? I'd want to trace things like SQL statements executed, execution times, execution plan, etc.
15
votes
2answers
2k views

Is it ok to replicate the `mysql` database?

When I originally set up my master-to-master replication, I used: binlog-ignore-db=mysql and have been syncing the user accounts and grants manually. This is simply how it was done in the how-to I ...
14
votes
1answer
22k views

What value of thread_cache_size should I use?

I'm using Asp.Net together with MySQL. In the .Net connection string, I have set Max Pool Size to 150. If I run the following I get these values: SHOW GLOBAL STATUS LIKE 'max_used_connections'; ...
14
votes
4answers
33k views

Unknown/unsupported storage engine: InnoDB | MySQL Ubuntu

I recently upgraded from the previous LTS Ubuntu to Precise and now mysql refuses to start. It complains of the following when I attempt to start it: ╰$ sudo service mysql restart stop: Unknown ...
14
votes
8answers
17k views

MySQL cannot connect via “localhost”, only 127.0.0.1

this is somewhat of a mystery to me. The only way I can connect to MySQL is if I call it via "127.0.0.1" ... for example, my PHP connect script will NOT work with localhost I'm running Mac OS X Lion, ...
14
votes
3answers
40k views

How to setup a MySql server to accept remote connections?

I am installing a MySql server in Ubuntu desktop. I could connect MySql Query Browser to it when specifying the Server Hostname as localhost, but when I replace it by the machine's IP it stops working ...
14
votes
5answers
3k views

What is the best way to monitor a production server?

We are running two production servers with Apache 2 and MySQL. I am looking for a reliable way of monitoring our load, stability and uptime. I have come across monit, but are there better ...
14
votes
3answers
1k views

MySQL Error: (2003, “Can't connect to MySQL server on '2001:db8:81:2c::2' (-9)”)

I'm trying to set up Zenoss 4.2.0 on CentOS 6.3 to monitor a remote MySQL 5.5.25a server via IPv6. The firewall is open for the monitoring server and I can connect fine from the command line: ...
14
votes
2answers
27k views

How to increase memory usage in MySQL Server to improve speed?

I have a Windows 2008 server with 8GB of RAM running IIS7 and MySQL. I've been tracking the memory, cpu and disk usage on the server and I found out that the the MySQL is using only 250MB of RAM, ...
13
votes
9answers
40k views

phpMyAdmin: The additional features for working with linked tables have been deactivated

I'm getting this error in the main page of phpMyAdmin verson: 3.2.1deb1 The additional features for working with linked tables have been deactivated. To find out why click here. When I click the link ...
13
votes
11answers
34k views

Can't start/stop mysql service

Taking over a Debian Etch web server with MySQL running. I usually start, stop and restart msyql using: /etc/init.d/mysql restart For some reason on this set up I get the following: :~# ...
13
votes
3answers
629 views

Which MySQL users are necessary?

My MySQL server has a number of strange users which I did not add. Are these all necessary? 'root'@'127.0.0.1' 'root'@'localhost' 'root'@'SERVERNAME' 'root'@'::1' ''@'SERVERNAME' ''@'LOCALHOST' If ...