MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (7)

115
votes
5answers
160k 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.
47
votes
9answers
57k 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 ...
45
votes
1answer
48k 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 ...
40
votes
7answers
46k 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 ...
37
votes
7answers
30k 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 ...
32
votes
10answers
24k 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 ...
31
votes
7answers
34k 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 ...
29
votes
3answers
7k 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 ...
28
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 ...
26
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% ...
26
votes
6answers
30k 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
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 ...
25
votes
3answers
113k 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] ...
23
votes
1answer
42k 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 ...
22
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 ...
20
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 ...
20
votes
6answers
33k 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 ...
20
votes
3answers
12k 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 ...
20
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 ...
19
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
5answers
11k 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
2answers
22k 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 ...
18
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 ...
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
6answers
9k 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
4answers
20k views

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

is this right? mysql -uroot -ppassword mydb > myfile.sql.gz
16
votes
4answers
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
12k 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 ...
16
votes
4answers
7k 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 ...
15
votes
2answers
48k 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 ...
15
votes
4answers
5k 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
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
3answers
35k 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 ...
14
votes
1answer
20k 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
3answers
38k 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
3answers
25k 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 ...
14
votes
8answers
14k 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
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
4answers
30k 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
3answers
980 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
5answers
17k views

Log MySQL login attempts

From time to time there are failed login attempts in our MySQL production server (MySQL dashboard alerts us). Is there a way to log every single success and failed login to the MySQL server without ...
13
votes
4answers
22k 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 ...
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
3answers
591 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 ...
13
votes
7answers
1k views

Backup MySQL Server

What's the best way to back up a MySQL server? I would like a method that doesn't require bringing down the server. Is something like InnoDB hot backup necessary, or can I just use the backup tools ...
13
votes
5answers
9k 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 ...
13
votes
2answers
15k 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 ...