MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (7)

101
votes
9answers
139k 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.
43
votes
9answers
50k 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 ...
39
votes
1answer
37k 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 ...
37
votes
7answers
39k 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 ...
34
votes
7answers
28k 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 ...
27
votes
7answers
29k 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 ...
26
votes
7answers
6k 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 ...
25
votes
4answers
2k 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% ...
24
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 ...
22
votes
7answers
3k 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
3answers
6k 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 ...
21
votes
3answers
100k 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] ...
20
votes
6answers
29k 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
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
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 ...
19
votes
5answers
993 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
6answers
10k 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
19k 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
6answers
25k 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.
18
votes
1answer
33k 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 ...
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 ...
17
votes
9answers
13k 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 ...
17
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 ...
16
votes
3answers
10k 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 ...
16
votes
10answers
15k 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
6answers
8k 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 ...
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
2answers
41k 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

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 ...
15
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
2answers
10k 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
5k 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
1k 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
34k 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
813 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
15k 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
9answers
39k 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
27k 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 ...
13
votes
3answers
550 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
7k 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
3answers
24k views

MySQL: How do I get my “Maximum possible memory usage” down?

I've recently been having problems with thrashing as a result of running out of memory. (My VPS has 256M total) I'm trying to tune MySQL using mysqltuner.pl, and get the following results: -------- ...
13
votes
4answers
4k views

In Puppet, how would I secure a password variable (in this case a MySQL password)?

I am using Puppet to provision MySQL with a parameterised class: class mysql::server( $password ) { package { 'mysql-server': ensure => installed } package { 'mysql': ensure => ...
13
votes
3answers
2k views

Building a 1,000M row MySQL table

This question is reposted from Stack Overflow based on a suggestion in the comments, apologies for the duplication. Questions Question 1: as the size of the database table gets larger how can I tune ...
12
votes
8answers
2k views

If I send data to my computer using its public hostname, does it actually go over the internet?

To clarify: I'm using my public hostname to connect to a MySQL database. The hostname resolves to my server's external IP (e.g. 1.2.3.4). Is the data I'm sending/receiving via the MySQL connection ...
12
votes
8answers
1k views

is this a hack attempt?

Looking through my 404 logs I noticed the following two URLs, both of which occurred once: /library.php=../../../../../../../../../../../../../../../../../../../../../../../../proc/self/environ and ...
12
votes
4answers
14k views

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

is this right? mysql -uroot -ppassword mydb > myfile.sql.gz
12
votes
10answers
27k 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: :~# ...