MySQL Command Line 2

To check mysql software version:

[root@192.168.33.121(none)]-> select version();
+---------------------+
version()
+---------------------+
5.0.85-community-nt
+---------------------+
1 row in set (0.00 sec)

MySql Error Message

Error 1:

[root@192.168.33.121mysql]-> INSERT INTO user (Host,User,Password) VALUES('%','nbpm',PASSWORD('nbpm'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

Fix:
[root@192.168.33.121mysql]-> INSERT INTO user (Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES ('%','nbpm',PASSWORD('nbpm'), 'NULL', 'NULL', 'NULL');
Query OK, 1 row affected (0.02 sec)

MySQL Command Line

Connect to MySQL:

c:\> mysql -h [hostname] -u [username] -p [password]

Connect to database:

mysql> show databases;

mysql> use [one database name];

e.g. mysql> use test;

To view Tables and Table Structure:

mysql> show tables;

mysql> desc [tablename]; = show columns from [tablename];

To view index:

mysql> show index from test.zoo; (= show keys from test.zoo; )

Using AUTO_INCREMENT

AUTO_INCREMENT is used to generate a unique identity for new rows:

mysql> CREATE TABLE zoo (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> ) auto_increment = 100;

mysql> INSERT INTO zoo (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');

mysql> SELECT * FROM zoo;
+-----+---------+
id name
+-----+---------+
100 dog
101 cat
102 penguin
103 lax
104 whale
105 ostrich
+-----+---------+

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
will start your records at 100.

* * ---------------------------------------- * * ---------------------------------------- * * ---------------------------------------- * * ---------------------------------------- * *
On current db, we could access the table on another db:

mysql> use mysql;
Database changed
mysql> select * from test.zoo;
+-----+---------+
id name
+-----+---------+
100 dog
101 cat

MySQL find duplicate repords example

select address, count(address) as cnt
from mailing_list
group by address
having cnt > 1
order by cnt;

Created_tmp_tables

The number of in-memory temporary tables created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

On my.cnf :
set-variable = tmp_table_size=128M

Chang the Mysql client prompt

Option 1: Changing the Mysql client prompt: (Mysql 4.0.2 and higher)

on my.cnf :

----------------------------

[mysql]

prompt=[\\u@\\h\\d]>\\_

----------------------------

It will show who is connecting which db on which host.

Option 2: use --prompt option on the command line to mysql

c:\> mysql --prompt="(\u@\h) [\d]> " -u root

MySQL Explain example

mysql> EXPLAIN SELECT * from zoo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 SIMPLE zoo ALL NULL NULL NULL NULL 6
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.02 sec)

built-in MySQL functions

mysql> select version();
+---------------------+
version()
+---------------------+
5.0.67-community-nt
+---------------------+

mysql> select database();
+------------+
database()
+------------+
test
+------------+

mysql> SELECT PASSWORD("mypass");
+-------------------------------------------+
PASSWORD("mypass")
+-------------------------------------------+
*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
+-------------------------------------------+

mysql> SELECT PASSWORD("test");
+-------------------------------------------+
PASSWORD("test")
+-------------------------------------------+
*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
+-------------------------------------------+

mysql> SELECT PASSWORD("");
+--------------+
PASSWORD("")
+--------------+

+--------------+

mysql> select md5('test');
+----------------------------------+
md5('test')
+----------------------------------+
098f6bcd4621d373cade4e832627b4f6
+----------------------------------+

MySQL INSERT SELECT example

insert into user (id, name, email, passwd, ref, joindate) select idm, user_name, email, pass, refferal, sysdate() from db.members;

Listing tables and their structure

mysql> use test;
Database changed

mysql> show tables;
+----------------+
Tables_in_test
+----------------+
animals
house
meds
patients
tree
zoo
+----------------+
6 rows in set (0.00 sec)

mysql> desc zoo; (= SHOW COLUMNS FROM zoo; )
+-------+--------------+------+-----+---------+----------------+
Field Type Null Key Default Extra
+-------+--------------+------+-----+---------+----------------+
id mediumint(9) NO PRI NULL auto_increment name char(30) NO NULL
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show index from test.zoo; (= show keys from test.zoo; )
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
zoo 0 PRIMARY 1 id A 2 NULL NULL BTREE
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Create a new database:

c:\ > mysql -u root -p password -h servername

mysql> CREATE DATABASE jp;

Create a new table:

CREATE TABLE customer (
cust_id int(5) DEFAULT '0' NOT NULL auto_increment,
surname varchar(50) NOT NULL,
firstname varchar(50) NOT NULL,
initial char(1),
title varchar(10),
addressline1 varchar(50) NOT NULL,
addressline2 varchar(50),
addressline3 varchar(50),
city varchar(20) NOT NULL,
state varchar(20),
zipcode varchar(5),
country varchar(20) DEFAULT 'Australia',
phone varchar(15),
fax varchar(15),
email varchar(30) NOT NULL,
salary int(7),
birth_date date( ),
PRIMARY KEY (cust_id),
KEY names (surname,firstname));

Add/Drop index:

ALTER TABLE customer ADD INDEX cities (city);

ALTER TABLE customer DROP INDEX names;

Log statements and their output by using the --tee option

mysql> \T c:\output.txt

Logging to file 'c:\output.txt'

mysql> select * from zoo;

+-----+---------+

id name

+-----+---------+

100 dog

101 cat

+-----+---------+

mysql> \t

Outfile disabled.

Date

mysql> SELECT YEAR('2003-03-31');
+--------------------+
YEAR('2003-03-31')
+--------------------+
2003
+--------------------+

mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
YEAR('2003-03-31')+5
+----------------------+
2008
+----------------------+

mysql> SELECT NOW();
+---------------------+
NOW()
+---------------------+
2003-03-31 00:32:21
+---------------------+

mysql> SELECT CURRENT_DATE();
+----------------+
CURRENT_DATE()
+----------------+
2003-03-31
+----------------+

mysql> SELECT MONTH(NOW()) AS m, DAYOFMONTH(NOW()) AS d, HOUR(NOW()) AS h, MINUTE(NOW()) AS m, SECOND(NOW()) AS s;
+------+------+------+------+------+
m d h m s
+------+------+------+------+------+
3 31 1 53 38
+------+------+------+------+------+

mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
DAYNAME('2000-01-01')
+-----------------------+
Saturday
+-----------------------+

mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
DAYOFWEEK('2000-01-01')
+-------------------------+
7
+-------------------------+

mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
DAYOFYEAR('2000-12-31')
+-------------------------+
366
+-------------------------+

mysql> select (TO_DAYS('2009-03-30')-TO_DAYS('2009-03-01'));
+-----------------------------------------------+
(TO_DAYS('2009-03-30')-TO_DAYS('2009-03-01'))
+-----------------------------------------------+
29
+-----------------------------------------------+

mysql> SELECT (TO_DAYS(CURRENT_DATE())-TO_DAYS('2009-03-01'));
+-------------------------------------------------+
(TO_DAYS(CURRENT_DATE())-TO_DAYS('2009-03-01'))
+-------------------------------------------------+
30
+-------------------------------------------------+

mysql> SELECT CURRENT_DATE();
+----------------+
CURRENT_DATE()
+----------------+
2009-03-31
+----------------+

mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
RIGHT(CURRENT_DATE(),5)
+-------------------------+
03-31
+-------------------------+

mysql> select DATE_SUB(CURDATE(),INTERVAL 1 DAY) ;
+------------------------------------+
DATE_SUB(CURDATE(),INTERVAL 1 DAY)
+------------------------------------+
2009-03-30
+------------------------------------+

mysql> select count(*) as cnt from log where date >= DATE_SUB(CURDATE(),INTERVAL 1 DAY);

MySQL Backup Script

This script is used on Windows 2003 (MySQL 5.0). It uses mysqldump to create a *.sql backup then sends a notification e-mail.It works online.Its Pre-Requisites are BLAT & GZIP, for e-mail and compression.

Script:

@ECHO OFF
CLS
Title MySQL Backup
ECHO ================================================
ECHO MySQL Backup Script
ECHO Description: This scrip does the following:
ECHO - Backs up the specified database.
ECHO - Compresses the backup.
ECHO - Backup dump @ d\:backups.
ECHO - Writes to a log file.
ECHO - Sends a notification e-mail.
ECHO Prerequisites:BLAT, GZIP, MYSQLDUMP
ECHO By: MySQL DBA
ECHO ================================================

@REM Date Configuration
for /f "tokens=1-4 delims=/ " %%a in ('date/t') do (
set dw=%%aset mm=%%b
set dd=%%c
set yy=%%d
)

@REM Backup Configuration
SET servername=server1
SET database=DB1
SET backupdir=D:\BACKUPS
SET workdir=c:\TEMP
SET mysqldir="C:\Program Files\MySQL\MySQL Server 5.0\bin"
SET utils=D:\UTILS
SET mysqlpassword=passwd
SET mysqluser=root
SET logdir=D:\log\mysql
SET filename=%servername%.%database%.%yy%%mm%%dd%.sql

@REM Mail Configuration
SET mailsmtp="192.168.2.11"
SET mail=%utils%\Blat\full\blat.exe
SET mailto=Notification@email.com
SET mailfrom="MySQL Backup Service on %servername% "
SET mailsubject="MySQL Backup of %database%"
SET start=%date% - %time%: Database Backup of %database% Started

@REM Start Logging
ECHO %start%
ECHO %start% >> %logdir%\mysqlbackup.log

@REM dump database. This is all one line.
mysqldump -u %mysqluser% -p%mysqlpassword% %database% > %workdir%\%filename%
if not exist %workdir%\%filename% goto FAIL_DUMP

@REM Zip up database
%utils%\gzip.exe -f %workdir%\%filename%
if not exist %workdir%\%filename%.gz goto FAIL_GZIP

@REM Move to random file name
move %workdir%\%filename%.gz %backupdir%\%filename%.gz
if not exist %backupdir%\%filename%.gz goto FAIL_MOVE

@REM All is well
GOTO SUCCESS

:FAIL_DUMPSET
message=%date% - %time%: Database Dump of %database% Failed
GOTO END
:FAIL_GZIPSET
message=%date% - %time%: Backup Compression of %database% Failed
GOTO END
:FAIL_MOVESET message=%date% - %time%: Backup Move of %database% Failed
GOTO END
:SUCCESSSET message=%date% - %time%: Backup of %database% Completed Succesfully
GOTO END

:END
ECHO %message%
ECHO %message% >> %logdir%\mysqlbackup.log
ECHO ===============================================================
ECHO Sending Notification E-Mail ...
ECHO To: %mailto%
ECHO From: %mailfrom%
ECHO Subject: %mailsubject%%mail% -q -server %mailsmtp% -f %mailfrom% -to %mailto% -subject %mailsubject% -body "%message%"
ECHO ===============================================================

MySQL Database Backup & Restore (MySQLDump)

MySQLDump is used to export tables to text files.

Syntax:

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]

[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup

In this article, our database is muffin.
username: root
password: password

Part I: BACKUP

1. Taking the full backup of all the tables including the data

c:\> mysqldump -u root -p passwd muffin> muffin.sql

2. Taking the backup of table structures only

c:\> mysqldump -u root -p passwd --no-data muffin> muffindatano.sql

3. Taking the backup data only

c:\> mysqldump -u root -p passwd --no-create-info muffin > muffindata.sql

4. Exporting few tables to text files

Syntax:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

--add-drop-table : This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

Example:

c:\> mysqldump --add-drop-table -u root -p passwd muffin table1 table2 > muffinTable.sql

5. back up a database from one MySQL server to another

mysqldump --host=host1 --opt mydatabase mysql --host=host2 -C newdatabase

Note that the target database must already exist on the remote server. To create newdatabase on host2(target box) first if newdatabase not exisit.

c:\> mysql -u root -p passwd

mysql > create database newdatabase;

6. You might zip backup dump on unix platform

c:\> mysqldump ---user root --password=passwd mydatabase gzip > /usr/local/mydatabase.gz

Part II: RESTORE

syntax:

mysql - u user_name -p your_password database_name < backup_dump.sql

e.g.

c:\> mysql muffin < muffin.sql -u root -p passwd

If restoring to a new db, you must create a new db first:

mysql > create database muffin;

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name backup_dump.sql

Improve Hard Disk Speed

If hard disk of your computer or server is taking too much time to get the result, then to improve your hard disk speed you will need to configure a special buffer in the computers memory in order to enable it to better deal with interrupts made from the disk. This tip is only recommended if your computer or server has 256MB RAM or higher.

Step 1:

Run SYSEDIT.EXE from the Run command.

Step 2:

Expand the system.ini file window.

Step 3:

Scroll down almost to the end of the file till you find a line called [386enh].

Step 4:

Press Enter to make one blank line, and in that line type

Irq14=4096

Note: This line IS CASE SENSITIVE!!!

Step 5:

Click on the File menu, then choose Save.

Step 6:

Close SYSEDIT and reboot your computer or server.

Done.

Speed improvement will be noticed after the computer reboots.

Update: The most speed improvement is visible with IDE drives, however there are reports that this tweak also does good for SCSI disks. In any case, it won't harm your computer or Dedicated Server, so try it out.

Suggest:
Hard disk speed also improve using hard disk cache settings.
Hard disk cache settings also depend on available RAM.
Done following settings in system.ini

MinFileCache=4096
MaxFileCache=8192

If you have lower RAM then above settings are very use full.