Saltycrane logo

SaltyCrane Blog

Notes on Python, Django, and web development on Ubuntu Linux

    

MySql notes

Date: 2008-11-06  |   Modified: 2010-08-03  |   Tags: notestoself, sql

Using the shell

mysql -u root -p -h mymysql.server.hostname.com mydatabase

How to show a list of table names

SHOW TABLES;

How to show the CREATE TABLE statement

SHOW CREATE TABLE my_table_name;

How to create a database

CREATE DATABASE my_database;

How to create a user

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';

How to delete a database

DROP DATABASE my_database_name;

How to delete a table

DROP TABLE my_table_name;

How to convert a table to unicode

ALTER TABLE my_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

How to create an index

CREATE INDEX my_index_name ON my_table_name(my_column_name);

How to add a column

ALTER TABLE my_table_name ADD my_column_name varchar(255) null;

How to modify an existing column

ALTER TABLE my_table_name MODIFY my_column_name varchar(255) null;

How to rename a table

ALTER TABLE my_table_name RENAME my_new_table_name;
MySql ALTER TABLE syntax

How to rename a column

ALTER TABLE my_table_name CHANGE my_column_name my_new_column_name varchar(255) null;

How to count the number of rows in a table

SELECT count(*) FROM my_table_name;

How to list users

SELECT Host, User, Password FROM mysql.user;

MySQL monitoring

http://dev.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html

mysqladmin extended (absolute values):

mysqladmin extended -i10  | grep --color -i 'slave_running\|threads_connected\|threads_running'

mysqladmin extended -i10 -r (relative values):

mysqladmin extended -i10 -r | grep --color -i 'questions\|aborted_clients\|opened_tables\|slow_queries\|threads_created'

How to copy a database to another server

On first server

  • Dump the existing database 
    mysqldump -umyusername mydb > mydump.sql
  • Transfer the file 
    scp -i mysshkeyfile mydump.sql user@XX.XXX.XXX.XX:/my/path

On the second server:

  • Restore database: 
    mysql -umyusername mydb < mydump.sql

How to show the columns of a table

SHOW COLUMNS IN mytable;

How to delete a user

MySQL DELETE 
use mysql 
delete from user where user = "myusertodelete" and host = "127.0.0.1";

How to copy a table from one database to another

  • mysql mydb1
SHOW CREATE TABLE mytable;
  • Copy the output text, then run the exact same query with your destination database selected. 
    mysql mydb2

    Then paste in the CREATE TABLE command from above.

  • To move the data, you will then need to: 
    mysql
INSERT INTO mydb2.mytable SELECT * FROM mydb1.mytable;

    From: http://www.experts-exchange.com/Databases/Mysql/Q_20973316.html

mysql update from another table

http://worcesterwideweb.com/2007/07/03/mysql-update-from-another-table/ 
UPDATE updatefrom p, updateto pp
SET pp.last_name = p.last_name
WHERE pp.visid = p.id

How to dump table rows that match a WHERE clause

Documentation 
mysqldump mydb mytable --where="mycol='myvalue'" > mydumpfile.mysql

How to purge the binary log

How to bind to localhost (don't allow connections from outside localhost)

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_bind-address http://fudforum.org/forum/index.php?t=msg&goto;=3026 Add bind-address=127.0.0.1 in the [mysqld] section of /etc/mysql/my.cnf.

How to determine if a table is using MyISAM or InnoDB engine

http://forums.mysql.com/read.php?28,132488,133090#msg-133090 
SHOW TABLE STATUS;

mysqld does not start with upstart

http://www.uluga.ubuntuforums.org/showthread.php?p=9533623 https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.1/+bug/573318?comments=all

This works:

sudo -u mysql mysqld
This didn't work: 
sudo service mysql start
/etc/mysql/my.cnf was missing.

Where are data files location (on Ubuntu)

/var/lib/mysql

See datadir in /etc/mysql/my.cnf

. http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_datadir

How to show current mysql configuration variables

mysqladmin -u root variables

Can't connect to MySQL server error

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
  • Check socket location is the same for [client] and [mysqld]

Can't create test file error

Trying to move datadir from /var/lib/mysql to /mnt/mysql-data on EC2

http://ubuntuforums.org/showthread.php?t=782224

Had to edit /etc/apparmor.d/usr.sbin.mysqld

Post a comment
Required
Required, but not displayed
Optional

Format using Markdown. (No HTML.)
  • Code blocks: prefix each line by at least 4 spaces or 1 tab (and a blank line before and after)
  • Code span: surround with backticks
  • Blockquotes: prefix lines to be quoted with >
  • Links: <URL>
  • Links w/ description: [description](URL)
Created with Django | Hosted by Linode