MySQL

Technical Notes

These notes are my personal online notebook of useful commands and "how-to's". You are welcome to make use of them if you find them helpful. They obviously don't come with any warranty! Click on one of the category tags above for the notes in any category.

Problems with MS Access and ODBC

A common problem is ‘#deleted” fields.

  • Use the following recommended best practices:
  1. You should have a primary key in the table, could be an auto-increment integer PK NN UQ UN AI
  2. You should have a timestamp in all tables you want to be able to update. Use DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
  3. Access can’t always handle DATE columns. If you have problems change to DATETIME.
  4. Only use double float fields
  5. Use the “Return matching rows” option in the DSN. This can be done by editing:
    .cxoffice/<bottlename>/drive_c/Program Files/Common Files/ODBC/Data Sources/xxx.dsn
    and adding the line:
    OPTION=2

Recovering Grub

grub>
root (hd0,1)  # "1" matches the partition number
linux /vmlinuz root=/dev/sda1 ro  #again, modify for the drive/partition
initrd /initrd.img
boot
  • Excellent troubleshooting page: help.ubuntu.com/…oubleshooting
  • Can try: dpkg --configure -a
  • edit /etc/default/grub and make sure timeout_quiet is false
  • run ubdate-grub

Creating an ssh tunnel for mysql


ssh -N -f -L 3307:localhost:3306 user@myxhost.com
ssh -L 3307:localhost:3306 user@myxhost.com

-f = go to background
-L = local port to remote port binding
-N = no remote command to be executed

test with either of the following:

mysql -P 3307 -u dbuser -h 127.0.0.1 -p
mysql -P 3307 -u dbuser --protocol=TCP -p

tunnel can be closed with ps -ef | grep ssh and then kill the pid

http://www.webmasterworld.com/forum40/1010.htm

http://support.suso.com/supki/SSH_Tutorial_for_Linux

for windows:

MySQL tuning

This is my experience tuning a MySQL server running on Rimuhosting for Drupal.
First I installed a tuning script in /root
wget http://day32.com/MySQL/tuning-primer.sh
chmod 700 tuning-primer.sh
yum install bc
cp /etc/my.cnf /etc/my.cnf.$(date +"%Y-%m-%d")
./tuning-primer.sh

Now add the following values under [mysqld]:
table_cache = 512
query_cache_size = 32M
query_cache_type = 1
max_heap_table_size = 32M
tmp_table_size = 64M

Here is some info: http://drupal.org/node/85768

Misc Drupal database queries

This are probably not of general interest, just some example queries from a particular database that I wanted to save here as snippets.

--view all category titles from node: n & d, category node: c
$sql="SELECT n.nid, c.title AS cname, d.field_catcache_value AS cat FROM node AS n"
. " JOIN content_type_document AS d ON d.nid=n.nid AND d.vid=n.vid"
. " JOIN node AS c ON c.nid=d.field_category_nid"
. " WHERE n.type = 'document'";

--get the body of the category definition c from a given node n & d
SELECT n.nid, c.title, r.body AS cname FROM node AS n

backup database

pre{border:none;background-color:#f4f3d7;padding:1em;-moz-border-radius:10px}

<?php
exec("mysqldump -uDBUSER -pPASSWORD DBNAME > DBNAME.sql");
exec("tar -czvf BACKUP.tar.gz .");
print "Done";

Benchmarking MySQL

Red Hat / CentOS Installation

yum -y install mysql-server mysql-bench
service mysqld start

# Make sure you have a root user created:
mysqladmin -u root password rootsqlpassword
# Navigate to the right directory:

MySQL backup

-create a user (such as backup) with select and lock priviledges for all databases from any host
-set MySQL server configuration to listen on external ports
pw=mybackuppassword
hst=MySQLhost
nice mysqldump -h $hst -u backup -p$pw --compress --add-drop-table --extended-insert -A | gzip -9 >/backups/$hst-$(date +"%Y%m%d").gz

MySQL misc tips

phpMyBackupPro: No simpler backup for MySQL: http://www.linux.com/feature/127811
Tunnel to locally running mysql server using ssh:
http://www.nerdlogger.com/2008/06/tunnel-to-locally-running-mysql-server...
create and setup remote administrator:
first make sure that there are not multiple name@addresses where the user can log in, else every one will have to get permissions separately.
CREATE USER 'andrewfoo'@'192.168.1.%' IDENTIFIED BY 'foomatic';
GRANT ALL ON *.* to 'andrewfoo'@'192.168.1.%' WITH GRANT OPTION;

Syndicate content