SaltyCrane Blog — Notes on PythonJavascript and web development

MySql notes

Using the shell

mysql -u root -p -h mydatabase

How to show a list of table names


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

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


How to delete a user

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

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:
    INSERT INTO mydb2.mytable SELECT * FROM mydb1.mytable;


mysql update from another table
UPDATE updatefrom p, updateto pp
SET pp.last_name = p.last_name
WHERE pp.visid =

How to dump table rows that match a WHERE clause

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);=3026 Add bind-address= in the [mysqld] section of /etc/mysql/my.cnf.

How to determine if a table is using MyISAM or InnoDB engine,132488,133090#msg-133090

mysqld does not start with upstart

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)


See datadir in /etc/mysql/my.cnf


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

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