User login

MySQL

Poor performance of Drupal 5 internationalized site due to lack of database table indexes?

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

How to create an administrative MySQL user for Drupal databases

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.

Fixing file paths after a change in location of a site's files directory

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/")

With XAMPP, permission denied for MySQL user with % (all) host access but works for localhost

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

In a shell script, create a database if it does not exist

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";

Setting up MAMP to work for the same scripts we use on the Debian server

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

Moving a MySQL Database by Command Line Interface

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.

MySQL database and user creation from the command line

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';

Syndicate content