MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

16
votes
6answers
17k 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 ...
16
votes
4answers
7k 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
9answers
17k 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 ...
16
votes
1answer
9k views

What is “freeable memory”?

Amazon RDS has a metric for 'freeable memory'. It appears to go up & down in a sawtooth pattern. This leads me to believe that it's memory that is being used by MySQL for caching and that when ...
16
votes
2answers
42k 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, ...
16
votes
5answers
25k 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 ...
15
votes
6answers
21k views

From a shell script, how can I check whether a MySQL database exists?

mysqladmin -uroot create foo returns an exit status of 1 if foo exists, and 0 otherwise, but of course it will also create the database if it doesn't already exist. Is there some easy way to simply ...
15
votes
1answer
35k views

MySQL [Warning] IP address could not be resolved

I'm running MySQL5.6.3 on a CentOS 6.1 virtual machine running on Windows XP in VirtualBox. The network card is configured in bridge mode, so my physical & virtual machines share the same ...
15
votes
3answers
48k 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 ...
15
votes
4answers
23k 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
2answers
3k 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 ...
15
votes
3answers
34k 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: -------- ...
15
votes
2answers
19k 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 of from another server, 10.1.1.1. So I am doing: CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password123'; ...
15
votes
3answers
5k 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 ...
14
votes
3answers
28k views

How Export Result of MySQL Query on PHPMyAdmin 3.4.3?

1) I've got a 30K row table. 2) When I run a long, 50-line query on that table, a GROUP function reduces the number of rows to 7K. 3) I want to export the grouped 7K rows as a new table, or save them ...
14
votes
2answers
26k views

Best Mysql cache settings for 8gb RAM dedicated mysql server using only Innodb (5gb database)

I'm a pretty big noob when it comes to setting up mysql for performance. And honestly I'm not worried about the fine tuning to squeeze every last bit of performance out of mysql, but I do know that ...
14
votes
4answers
4k views

How to reset or recover admin account password for MySQL?

I have a MySQL database that I "inherited" and I was not given the admin credentials. I do however have access to the box that it runs on. Is there a way to either recover the admin credentials or ...
14
votes
5answers
4k 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
4k views

How fast is MySQL replication?

I'm considering setting up replication of our mysql db to be able to have local slaves in each of our branch offices, while having the master in the main office to improve application performance ...
14
votes
8answers
8k views

How do I backup a mysql database, but at low priority?

I want to backup a database, but during the day when there is load on the server. It's vital that the backup doesn't impact apache and other databases running on the same server. It should be ...
14
votes
7answers
6k views

How can I force a MySQL table to become corrupted?

I have written a simple Nagios plugin that calls mysqlcheck (which checks for corrupted tables) and will give a warning if any are corrupt. However none of my tables are corrupt now. So I'm not 100% ...
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
8answers
19k views

How to set ulimits for a service starting at boot?

I need, for mysql to use large-pages, to set a ulimit - I've done this in limits.conf. However, limits.conf (pam_limits.so), doesn't get read in for init, only for "real" shells. I solved this before ...
13
votes
9answers
44k 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
828 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
2k 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
6answers
2k views

Downtimeless MySQL backups on a budget

My current MySQL backup scenario is to replicate our db to a second server and run mysqldump on that server to remove any downtime from table or row locking. This is working well but costs $150 per ...
13
votes
1answer
973 views

PuTTY typing its name into the console

I was doing a MySQL dump over SSH using Putty, and now it is just typing PuTTY over and over again into the console, Ctrl+C doesn't do anything. Does anyone know why this is happening?
13
votes
2answers
8k views

Installing a third party application package on CentOS 6.4 fails due to missing dependencies libcrypto.so.10 and libssl.so.10

This is a Canonical Question about OpenSSL binary compatibility issues between Red Hat Enterprise Linux (and its derivatives) 6.4 and 6.5. This issue applies to a wide variety of third party ...
13
votes
2answers
16k views

Is MySQL port 3306 encrypted, and if no, how can I encrypt it?

I am working through a security audit for a system at my work and one of the requirements is to encrypt all traffic through public/unprotected networks. Since we are accessing the MySQL database (over ...
13
votes
4answers
14k views

Alter charset and collation in all columns in all tables in MySQL

I need to execute these statements in all tables for all columns. alter table table_name charset=utf8; alter table table_name alter column column_name charset=utf8; Is it possible to automate this ...
13
votes
3answers
4k views

MySQL Replication Performance

I'm having a serious issue with MySQL 5.5 replication performance between two machines, mostly myISAM tables with statement based replication. The binary logs and mysql data directory are both located ...
13
votes
4answers
5k 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 => ...
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
2k 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
5answers
17k views

Should I install 32-bit database or 64-bit database?

(I've read a lot about 64-bit versus 32-bit OS/Apps, but this question is specifically in regards to databases.) I'm trying to understand the pros and cons of 32-bit versus 64-bit databases, and ...
12
votes
6answers
6k views

Forward local port or socket file to remote socket file

Quick question - I run two linux boxes, one my own desktop and the other my VPS. For security reasons on the VPS end I opted for socket connections to MySQL (/var/run/mysqld/mysql.sock). I know I can ...
12
votes
1answer
9k views

Access to MySQL server via VirtualBox

I am trying to get MySQL server (which is inside VM) to respond to client (which is on the host machine). All methods return same: Host '10.0.2.2' is not allowed to connect to this MySQL server I ...
12
votes
2answers
64k views

How do i find out what my IP Address of my MySQL host is?

I have a free domain running at x10hosting (x10.bz), and I want to find out the IP Address of my MySQL host for it, so I can contact the MySQL database from another host. I've already added that host ...
12
votes
5answers
14k views

How to drop all tables in a MySQL database without dropping the database?

I need to drop all the tables in a database without knowing their names beforehand. The typical procedure is to drop and then recreate the database but this is not an option. What is the best way to ...
12
votes
3answers
44k views

RSA certificate configured for SERVER does NOT include an ID which matches the server name

I recently started a LAMP server (all the latest versions) w/ WordPress on it, and I'm trying to install a SSL certificate that I recently purchased. When I restart apachectl, error_log gives me this: ...
12
votes
1answer
7k views

php-mysqlnd or php-mysql

Long story short: just installed Ubuntu 12.04 (Debian based), nginx, php-fpm and mysql. Now I need the MySQL driver for PHP, but which should I choose? Which is better php-mysqlnd or php-mysql?
12
votes
3answers
18k views

How find and fix fragmented MySQL tables

I used MySQLTuner which pointed out some tables were fragmented. I used mysqlcheck --optimize -A to optimize all tables. It fixed some tables but MySQLTuner still finds 19 tables fragmented. how ...
12
votes
2answers
47k views

Tuning (and understanding) table_cache in mySQL

I ran the excellent MySQL performance tuning script and started to work through the suggestions. One I ran into was TABLE CACHE Current table_cache value = 4096 tables You have a total of 1073 ...
12
votes
3answers
39k views

What does MySQL Error 2013 mean?

I'm running mysqldump on a mysql server in a cron (as backup) and I'm getting warning messages like mysqldump: Got error: 2013: Lost connection to MySQL server during query when doing refresh What ...
12
votes
4answers
10k views

MySQL Master-Master Replication of ALL databases. How?

Background: I have two MySQL 5.1 servers setup in Master-Master row-based-replication (RBR) following this excellent guide. I want all databases replicated, and will be adding new databases ...
12
votes
3answers
1k views

The A to Z of setting up a Linux box for secure local hosting

I am in the process of reinstalling the OS on a machine that will be used to host a couple of applications for our business. The applications will be local only; access from external clients will be ...
11
votes
4answers
4k views

Should I backup and restore the `mysql` database?

In the process of creating an automated solution for backing up and restoring an entire MySQL server, I've come across the mysql database which seems to contain user accounts, permissions, metadata, ...
11
votes
5answers
759 views

Is it bad to have a very full hard drive on a high traffic database server?

Running an Ubuntu server with MySQL for a high traffic production database server. Nothing else is running on the machine except the MySQL instance. We store daily database backups on the DB server, ...