MySQL

Handy Drush DB commands

I use these commands to see drupal data, but not often enough to remember them when I need them.

drush sqlq --database=default "show tables"
drush sqlq --database=default "describe tablename"
drush sqlq --database=default "SELECT * FROM tablename"
drush sqlq --database=default "TRUNCATE TABLE tablename"

Get the number of rows in a table:

Drush sql-query output

Despite several tries, I have never had any luck using the native sql output formatting commands to work with drush

LAMP using XAMPP

XAMMP can be a quick and easy setup of apache, php and mysql.
http://www.apachefriends.org/

The working directory for the install is

/opt/lamp/htdocs

Apache Conf File (httpd.conf): /opt/lammp/apache/conf/httpd.conf

PHP Conf File (php.ini): /opt/lammp/xampp/apache/bin/php.ini

MySQL Conf File (my.cnf): /opt/lammp/xampp/mysql/bin/my.cnf

LAMP setup on xubuntu

Quick code for installing the LAMP stack on xubuntu:

#Install SSH
sudo apt-get install openssh-server

#Install Apache
sudo apt-get install apache2
# apache should now return a response  at http://127.0.1.1/

#likely not needed but for some reason a lot of sites reference it
sudo apt-get install libapache2-mod-auth-mysql

#Install MySQL
sudo apt-get install mysql-server

MySQL Process Inspection - Kill

When MySQL seems slow, there may be a process that has runaway. To see what is happening on MySQL, from your terminal run


mtop

This will show you a list of the MySQL processes currently running on that server. Look for any process that has been running from more than a few seconds by looking at the "TIME" column.

[[{"type":"media","view_mode":"media_original","fid":"70","attributes":{"alt":"","class":"media-image","typeof":"foaf:Image"}}]]

Restoring a Database using SSH

phpMyAdmin is useful but for database imports it has a maximum file size of 20MB. Fortunately it is almost as fast to use ssh to get the job done.

Steps to import a MySQL DB using SSH
1) Get the backup from host or export from current db (typically these are in a sql.gz format so they need to be unzipped first)
2) Use SFTP to move the file to your home directory on the server
3) If the file is zipped, using an SSH terminal type:

MySQL Database Backup Commandline

DATABASE BACKUP & RESTORE

Database restore:
mysql --host= -p -u[user] [database] < db_backup-file.dump

Database backup:
mysqldump --host=localhost -u [user] -[password] [database] > new_backup.sql

Subscribe to RSS - MySQL