MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

143
votes
5answers
202k 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.
58
votes
9answers
70k 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 ...
56
votes
1answer
65k 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 ...
49
votes
7answers
59k 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 ...
42
votes
12answers
43k 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 ...
40
votes
7answers
34k 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 ...
37
votes
4answers
9k 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 ...
36
votes
7answers
43k 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 ...
32
votes
7answers
9k 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 ...
30
votes
4answers
37k views

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

I'm trying to import a gzipped SQL file into mysql directly. Is this the right way? mysql -uroot -ppassword mydb > myfile.sql.gz
28
votes
7answers
41k 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 ...
27
votes
4answers
4k 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
6answers
38k 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.
27
votes
1answer
3k views

Is MariaDB a secure replacement for MySQL?

I've been using MariaDB, "an enhanced, drop-in replacement for MySQL" on my Debian stable servers for years, because of its increased performance. However I've noticed that it appears to lag with ...
27
votes
1answer
59k 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 ...
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 ...
27
votes
4answers
134k 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
3answers
15k 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 ...
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 ...
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
48k 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 ...
21
votes
2answers
57k 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 ...
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
27k 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 ...
20
votes
5answers
7k 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 ...
20
votes
4answers
11k 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 ...
19
votes
14answers
3k 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
6answers
12k 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 ...
19
votes
5answers
13k 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 ...
18
votes
11answers
42k 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: :~# ...
18
votes
3answers
38k 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 ...
18
votes
2answers
16k 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 ...
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
15k views

Why can't I connect to Amazon RDS after setting it up?

So, I just created the Amazon RDS account. And I started an instance of database. The "endpoint" is: abcw3n-prod.cbmbuiv8aakk.us-east-1.rds.amazonaws.com Great! Now I try to connect to it from one ...
17
votes
6answers
14k 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 ...
17
votes
1answer
26k 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'; ...
17
votes
5answers
7k views

What is the difference between InnoDB and MyISAM?

I am using MySQL as my database for my current web project. I am new to MySQL. Please explain to me the difference between InnoDB and MyISAM.
16
votes
3answers
4k 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
4answers
34k 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 ...
16
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 ...
16
votes
4answers
43k 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 ...
16
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.
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 ...
15
votes
4answers
17k views

Can't backup the mysql table with mysqldump. SELECT,LOCK TABL command denied for 'cond_instances'

I am having trouble running mysqldump as the mysql root user. When I try to backup the mysql table I get this error: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ...
15
votes
5answers
43k views

How can I show mysql locks?

Is there anyway to show all the locks that are active in a mysql database?
15
votes
9answers
27k 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, ...
15
votes
4answers
20k views

Unable to install mysql-server in Ubuntu

I am unable to install mysql-server on my ubuntu 9.10 server machine. When using apt-get install mysql-server the output is : # apt-get install mysql-server Reading package lists... Done Building ...
15
votes
6answers
14k views

How can I speed up a MySQL restore from a dump file?

I am restoring a 30GB database from a mysqldump file to an empty database on a new server. When running the SQL from the dump file, the restore starts very quickly and then starts to get slower and ...
15
votes
2answers
18k views

Reload my.cnf without restarting MySql service

I have to configure a Mysql server to act as a replication-master. I modified my.cnf to activate binary logs, but now in order to reload configuration I have to reload the service with ...