MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

153
votes
5answers
233k 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.
65
votes
1answer
77k 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 ...
63
votes
9answers
78k 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 ...
52
votes
7answers
67k 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 ...
50
votes
16answers
56k 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 ...
48
votes
4answers
52k 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
43
votes
8answers
48k 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 ...
43
votes
7answers
36k 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 ...
42
votes
4answers
11k 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 ...
34
votes
7answers
10k 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
6answers
45k 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.
29
votes
7answers
48k 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 ...
29
votes
1answer
71k 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 ...
29
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 ...
28
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 ...
28
votes
3answers
18k 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 ...
27
votes
4answers
5k 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
4answers
148k 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
2answers
64k 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 ...
24
votes
7answers
5k 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 ...
24
votes
2answers
31k 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 ...
22
votes
3answers
45k 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 ...
22
votes
4answers
14k 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 ...
21
votes
16answers
2k 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
56k 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
4answers
53k 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 ...
21
votes
4answers
20k 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 ...
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 ...
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
11answers
49k 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: :~# ...
20
votes
5answers
8k 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
3answers
18k 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 ...
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
27k views

How to force MySQL to connect by TCP instead of a Unix socket?

I would like to analyze mysql traffic. Right now, all mysql requests are sent to the MySQL unix socket: unix 2 [ ACC ] STREAM LISTENING 3734388 15304/mysqld ...
19
votes
6answers
13k 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
2answers
31k 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'; ...
19
votes
5answers
14k 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
3answers
5k 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 ...
18
votes
6answers
54k views

How to rename a MySQL database?

How to rename a MySQL database? The MySQL online manual says about the RENAME DATABASE command: This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL ...
18
votes
5answers
56k views

MySQL keeps crashing: InnoDB: Unable to lock ./ibdata1, error: 11

I have a simple webserver (Debian 6.0 x86, DirectAdmin with 1 GB of memory and still 10 GB free space, mySQl version 5.5.9), however the mySQL server keeps crashing and I need to kill all mySQL ...
18
votes
1answer
6k views

Why MySQL command line tool ignores --port parameter?

This is what I'm doing: mysql --host=localhost --port=9999 mysql -u root -p --execute="show tables;" The command works (connecting to port 3306) no matter what I provide in --port argument. I have ...
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
5answers
41k 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 ...
17
votes
5answers
50k views

How can I show mysql locks?

Is there anyway to show all the locks that are active in a mysql database?
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
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.
17
votes
2answers
21k 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 ...
17
votes
2answers
20k views

MySQL: creating a user that can connect from multiple hosts

I'm using MySQL and I need to create an account that can connect from either the localhost or from another server, i.e. 10.1.1.1. So I am doing: CREATE USER 'bob'@'localhost' IDENTIFIED BY ...
16
votes
3answers
83k views

How to check my PHP and MySQL version on Ubuntu VPS?

HI I am going to install MyBB but I am not sure whether I have installed the correct version of PHP and MySQL. PHP version 5.1.0 or above with XML Extension installed MySQL version 4.0 or ...
16
votes
9answers
120k views

Unable to connect to mysql through JDBC connector through Tomcat or externally

I've installed a stock mysql 5.5 installation, and while I can connect to the mysql service via the mysql command, and the service seems to be running, I cannot connect to it through spring+tomcat or ...