MySQL

Still have to try:

http://jwopitz.wordpress.com/2007/12/09/getting-mysql-mac-os-x-105-leopard-togetherfinally/

One issue is that in Drupal 5x the i18n_node table does not have indexes where you really need them.

See the CivicActions post When to Index - Databases, Tables, Explain.

Resolution

Working on configuring MySQL for our deployment system I was confronted with a little problem. To improve on safety it would be good to have an administrative user which may only create and drop users for our Drupal databases which have a common name pattern. Therefore I tried this GRANT statement: GRANT ALL ON drupal\_%.* TO 'drupal'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION. All is well documented in the MySQL online manual page GRANT syntax.

If, like Agaric, you're moving from multisite to separate installations for each site (or vice versa) you may find your user-uploaded file paths changing. Drag.

The solution is updating your database, and you can replace part of the file path with straight SQL:

UPDATE files SET filepath = REPLACE(filepath, "sites/help4computers.com/files/", "sites/default/files/")

Web Search Notes

B.1.2.7. Too many connections
http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

For reasons not known to man nor beast, on XAMPP on Mac OS X (and not XAMP but another all in one WAMP stack, but not that name either) will not work with the wildcard availability host name (%). It will work (or anyhow may work for you, it worked for Agaric!) with the @localhost for host name.

Resolution

Use the MySQL: SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name'

<?php
if (mysql_num_rows(mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". $dbname ."'"))) {
  echo "Database $dbname already exists.";
}
else {
  // go on and create the user, database, etc.
  // create the user

  if (!mysql_query("CREATE USER '". $dbuser ."'@'%' IDENTIFIED BY '". $dbpass ."'"))  die(mysql_error());
  echo "User $dbuser created.\n";

I changed the mysql root password through phpMyAdmin... and promptly lost the connection– no! The password also has to be changed in MAMP's phpMyAdmin configuration file.

vi /Applications/MAMP/bin/phpMyAdmin/config.inc.php

$cfg['Servers'][$i]['password'] = 'changedpass'; // MySQL password (only needed

mysqldump -u root -p internetbar_live > internetbar_backup.sql

(should gzip here)

This command compresses the file database.sql and replaces it -- deletes it -- with the compressed .gz file database.sql.gz

gzip database.sql

scp ben@66.135.41.18:~/internetbar_backup.sql internetbar_backup.sql

On the other end if you compressed, uncompress:

gunzip database.sql.gz

Note: tar is for making an archive of multiple files.

On Agaric's test server, i need to log into MySQL as root to do these grant commands.

/usr/local/mysql/bin/mysql -uroot -p

On Debian, simply

mysql -uroot -p

worked. You have to put in your root password, of course. (And the lack of a space between -u and root is not a typo.)

In here, you can:

SHOW DATABASES;

CREATE DATABASE example_database;

GRANT ALL ON example_database.*
TO example_user@localhost IDENTIFIED BY 'p4ssw0rd';