MySQL

From Shiftyjelly

Contents 1 Common Commands 1.1 Login 1.2 List Settings Variables 1.3 Create a user account 1.4 Grant a user all permissions to the database 1.5 List all the databases 1.6 Drop and create a database 1.7 List all the tables 1.8 Drop a database table 1.9 Create a table 1.10 Create a foreign key 1.11 Export the database 1.12 Import the database 1.13 How to duplicate a database 1.14 How to set the root users password 1.15 How to execute some sql from the command line 1.16 Show indexes 1.17 Explain 1.18 Find out a table type 1.19 How to restart the root password 1.20 Reinstalling MySQL on Debian 2 Installation 2.1 Installing the MySQL Ruby gem on Leopard 2.2 Uninstalling MySQL on Leopard 2.3 What does this error mean - Can't create table './app/#sql-14f_a4d.frm' (errno: 121) 2.4 What does this error mean - Mysql::Error: Can't create table '#sql-47f_34e' (errno: 13): SQL 3 Profiling 3.1 Turn on individual query logging in MySQL

Common Commands

Login

Use the username “bob”, prompt for a password and connect to the database called “test”.

mysql -u bob -p test

List Settings Variables

SHOW VARIABLES;

Create a user account

CREATE USER user IDENTIFIED BY 'password';

Create a user account (less than MySQL version 5)

INSERT INTO mysql.user (user, host, password)
VALUES ('scott', 'localhost', PASSWORD('tiger'));

Grant a user all permissions to the database

GRANT ALL ON database_name.* TO 'username'@'%'

Create a user account and grant permissions just on localhost

GRANT ALL ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

List all the databases

SHOW DATABASES;

Drop and create a database

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

List all the tables

SHOW TABLES;

Drop a database table

DROP TABLE IF EXISTS Users;

Create a table

CREATE TABLE Users (
  id BIGINT NOT NULL AUTO_INCREMENT,
  invoiceId BIGINT,
  entryDate DATETIME NOT NULL,
  statusId INTEGER NOT NULL,
  group VARCHAR(100),
  description TEXT NOT NULL,
  perc FLOAT(10) NOT NULL,
  minimise BOOL,
  addressId BIGINT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (invoiceId),
  KEY (addressId),
  KEY (statusId)
) ENGINE=InnoDB;

Create a foreign key

ALTER TABLE Users ADD CONSTRAINT FK_Users_Invoice 
  FOREIGN KEY (invoiceId) REFERENCES Invoice (id)
;

Export the database

To SQL:

mysqldump -u username -p database_name > export.sql

To CSV:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Export just one table:

mysqldump  -u username -p database_name table_name > export.sql

Import the database

mysql -u username -p database_name < export.sql

How to duplicate a database

mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host DB_name

How to set the root users password

mysql -u root mysql
set password for root@localhost=password('__password__');

How to execute some sql from the command line

mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
or
mysql -u root -p -e "SELECT VERSION();SELECT NOW()"

Show indexes

SHOW INDEXES FROM tablename;

Explain

EXPLAIN SELECT fp_id FROM ldu_forum_posts WHERE fp_topicid = 24701 ORDER BY fp_id ASC LIMIT 2;

-- explain output in a different format
EXPLAIN SELECT fp_id FROM ldu_forum_posts WHERE fp_topicid = 24701 ORDER BY fp_id ASC LIMIT 2 \G

Find out a table type

show table status where name = 'User';
show table status like 'user';

How to restart the root password

/usr/bin/mysqld_safe --skip-grant-tables &
mysql
use mysql;
update user set password=password('secret') where user='root' and host='localhost';

# also check the user exists
/usr/bin/mysqladmin -u root password 'new-password'

# also note that on debian a default user is created
cat /etc/mysql/debian.cnf
user     = debian-sys-maint
password = ***
# this user can be used to create a root user

Reinstalling MySQL on Debian

aptitude purge mysql-server-5.0
aptitude purge mysql-common
aptitude install mysql-server-5.0

Installation

Installing the MySQL Ruby gem on Leopard

Install the 32 bit dmg edition from the mysql website and then install the gem with the following command:

sudo env ARCHFLAGS="-arch i386" gem install -V mysql -- --with-mysql-include=/usr/local/mysql/include/ --with-mysql-lib=/usr/local/mysql/lib --with-mysql-config=/usr/local/mysql/bin/mysql_config --with-mysql-dir=/usr/local/mysql

Uninstalling MySQL on Leopard

sudo rm /usr/local/mysql
sudo rm -rf /Library/StartupItems/MySQLCOM/

What does this error mean - Can't create table './app/#sql-14f_a4d.frm' (errno: 121)

The reason for this error is because the foreign keys I had created link two tables of different types. Make sure both the tables are the same type, for example ENGINE = InnoDB

What does this error mean - Mysql::Error: Can't create table '#sql-47f_34e' (errno: 13): SQL

This is a permissions error which can be fixed with the following command

cd /var/lib
chown -R mysql mysql

Profiling

Turn on individual query logging in MySQL

http://note19.com/2008/07/30/how-to-log-mysql-queries-on-os-xwhenyou/