Talk:MySQL

1 user per database?
Why is it that we can no longer use the same database user on multiple databases?

Thorn in my side I tell ya!!

I create one admin, and then add under privileged db users for each app that needs it's own database.

However, i can still access them all from my admin db user.

Seems dreamhost suddenly decided NOT to allow this anymore.

Knightnet: This seems to work fine for me? (2007-03-18) Do you mean more than one hostname? I've not tried that but I do use a common user for all my databases and it works as expected.

1 database per subdomain?
What is the reason for requiring a new subdomain to be defined for each MySQL database?

There must be some reason, but what is it?

Requiring that I define a new subdomain for each database that I plan on making seems hard to manage. Why cant I just define a subdomain "mysql.myDomain.net" and have all my db's in one place?

-Codeword 15:48, 30 Nov 2005 (PST)

Each database has it's own users and subdomain for good reasons. The subdomain because each database could effectivly be on a different database server, dreamhost can map the subdomain as they please so if they get a new server (which happens often) they can move databases off a loaded one without you having to change your configuration.

Users in MYSQL aren't exactly users as you might be used to thinking about them. They're grants on a database. "I grant this username with this password access to this database from this host." Now mix that with the fact that your databases are probably not all on the same server and you can realize why we don't have a seperate user accounts for the mysql databases.

-reconbot 11:04, 5 Jan 2006 (EST)

Does this still apple though? In the April newslettery it said we can use one hostname for them all. However I haven't had much luck so far with getting them all to use one hostname although I'm putting this down to the other databases being much older.

-Hymagumba 01:40, 8 Jun 2006 (PDT)

Knightnet 2007-03-18

Seems to work fine now.

Max DB size?
Is there a maximum DB size for the databases hosted here on DreamHost?

i.e., would there be a problem with databases reaching/exceeding 1-5gb in size?

--NathanClayton 18:18, 13 Feb 2006 (PST)

mutliple DBs per hostname
as the newsletter confusingly said in march

1. No Multiple Databases per Hostname!

Um, heh, so here's a NOT new feature we're NOT announcing this newsletter. You can now NOT put all your MySQL databases on the same hostname (e.g. mysql.notyourdomain.com or whatever). This will NOT let you access all your databases at the same phpMyAdmin area. This will also NOT let your new databases get set up instantaneously because you don't have to wait for new hostname DNS information to propagate.

Finally, this will NOT allow you to use the same mysql users to access all your databases.

To NOT try out this new feature, please just check out our awesome MySQL area:

https://panel.dreamhost.com/?tree=goodies.mysql

Oh, and by the way, FEBRUARY FOOLS!!!! You suckers are soooo busted out.

so I presume the instruction to keep them on seperate subdomains is now out of date and can be removed? If nobody has any complaints I'll change it on Friday.

Hymagumba 12:06, 14 Jun 2006 (PDT)

MySQL 5 Triggers
It seems that until after v5.1.6, creating TRIGGERS requires SUPER privaleges! My server seems to be on v5.0.x currently.

MySQL Copy Database problem
In phpMyAdmin, Operation tab, the command "Copy database to" doesn't seem to work right. First I tried using it with the " CREATE DATABASE before copying" checked, but it said I didn't have permissions:
 * 1) 1044 - Access denied for user 'seek'@'208.113.128.0/255.255.224.0' to database 'cityseeker_bak'

So I went back to the Web Panel, created an empty database, and tried "Copy database to" again, without it checked. This time I got the error below. I can see there's a space missing in the last line in "11DEFAULT", but since this is automated, I don't have control over that. Any suggestions?

-- ERROR MESSAGE: -- CREATE TABLE `cityseeker_bak1`.`bibliography` ( `bib_id` bigint( 20 ) NOT NULL AUTO_INCREMENT, `bib_name` varchar( 100 ) NOT NULL , `bib_description` varchar( 250 ) NOT NULL , `bib_url` varchar( 250 ) NOT NULL , `bib_last_accessed` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY ( `bib_id` ) ) ENGINE = MYISAM AUTO_INCREMENT = 11DEFAULT CHARSET = latin1

MySQL said: Documentation
 * 1) 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '11DEFAULT CHARSET = latin1' at line 6

sqlyog community edition
I just downloaded the free "community" versions of sqlyog, and it doesn't seem to include the options to connect via ssh tunneling. Both versions 6 and 7 don't seem to have this feature. Has anyone tried it, or is this article referring to the Enterprise Edition?

--Thesentiment 19:52, 4 July 2008 (UTC)

Slow Query Log
I was wondering, is there any possibility to get the slow query log on shared hosts? If there isn't, then what's the point of loggin them at all? PAStheLoD

MySQL Workbench 5.2.16 (beta) and DH Private Servers
As a long time user of MySQL Administrator ver 1.2.17 and MySQL Query Browser (same version), I was excited to see a new unified SQL Workbench which included both along with a data modeling tool.

The SQL Development (Query Browser portion) works just fine. I connected as expected, and can use it at will.

The Server Administration however, is a different story. I can connect without using SSH Shell connection, but it's pretty useless. I tried following the MySQL Wiki - connect via SSH, but I still get various SSH errors.

Not only is MySQL Workbench beta, but I also DH Private Server for MySQL, and I wasn't sure how that would change my configuration.

Connect to Your MySQL Database from Third-Party Programs via a Secure SSH Tunnel
Regarding this section of the instructions, http://wiki.dreamhost.com/MySQL#Connect_to_Your_MySQL_Database_from_Third-Party_Programs_via_a_Secure_SSH_Tunnel, I found you can avoid this whole rigmarole by adjusting your settings in the cPanel. Just login, go to MySQL Databases, click on the username in question, then add your current computer's IP address to the list of "Allowable Hosts: From what hosts (computers) may [$USER] connect to these databases?".

Not sure if it's as secure, but I achieved what I wanted: to connect to my database from my local command line.