MySQL is an open-source database owned by Oracle.

learn more… | top users | synonyms (2)

17
votes
5answers
8k 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
22k 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
6answers
22k 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 ...
16
votes
9answers
126k 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 ...
16
votes
6answers
19k 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
2answers
18k 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 ...
16
votes
1answer
10k 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
5answers
26k 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
3answers
32k 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 ...
15
votes
4answers
25k 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
3answers
49k 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
2answers
29k 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 ...
15
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 ...
15
votes
1answer
9k 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?
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
35k 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
1answer
6k views

Constantly have to reload PHP-FPM

We have a fairly heavily loaded server running nginx and PHP-FPM. We have 6 websites on this server, running PHP-FPM and nginx. Software is all vBulletin 3.8 and WordPress. Databases are on a separate ...
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
6answers
25k views

What should mysqld.sock contain, why don't I have it?

Does anyone know why my /var/run/mysqld/mysqld.sock socket file would not be on my computer when I install (or reinstall) MySQL 5.1? Right now, when I try to start up a server with mysqld, I get ...
14
votes
5answers
7k 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 ...
14
votes
3answers
72k 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 ...
14
votes
4answers
5k 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
8answers
9k 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
2k 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
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 ...
14
votes
3answers
5k 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 ...
14
votes
8answers
21k 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
5answers
19k 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 ...
13
votes
3answers
891 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
1answer
11k 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 ...
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
985 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
3answers
52k 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: ...
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
48k 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 ...
13
votes
4answers
11k 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 ...
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
3k 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
6answers
48k views

How to check what port mysql is running on

On my windows dev box mysql is running on port 3306 How can I check what port it is running on the unix server that I have to upload the app to.
12
votes
2answers
31k views

What kind of server do I need to handle 10 million requests and mySQL queries a day? [closed]

I'm a newbie of server administration and I'm looking for a powerful hosting service to host my new website. This website is basically a back-end of an mobile online game, and it will: handle up to ...
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
21k 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 ...