MySQL Commands

MySQL Startup Script Location
vi /etc/init.d/mysql

Configure MySQL
vi /etc/mysql/my.cnf

MySQL Details
which mysqld # MySQL server location
which mysql # MySQL client location
whereis mysql # MySQL files location

Login to MySQL
mysql -u USERNAME -p

Table Create Statement
SHOW CREATE TABLE ForumPosting;

output:
CREATE TABLE `ForumPosting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `articleID` int(11) DEFAULT NULL, `forumID` int(11) DEFAULT NULL, `email` varchar(255) DEFAULT NULL,  `source` varchar(150) DEFAULT NULL, `sourceID` varchar(150) DEFAULT NULL, PRIMARY KEY (`id`), KEY `Posting_Article_idx` (`articleID`), KEY `Posting_Parent_idx` (`parentID`), KEY `Posting_ModerationState_idx` (`moderationState`), KEY `Posting_LastModified_idx` (`lastModified`), KEY `Posting_Thread_idx` (`threadID`), KEY `Posting_Forum_idx` (`forumID`), KEY `Posting_Source_idx` (`source`,`sourceID`) ) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=latin1

Create UTF-8 Database (it will create chatset utf-8 table):
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Check Database Charset
show variables like 'character_set_server';

Check Database Schema Information
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Export Database Dump
mysqldump -u userName -pMyPass dbName > dump_mah.sql

Import Database Dump
mmahmood@mmahmood-Vostro-3400:~$ mysql -u root -p
use dbName;
source /tmp/bangladb.sql;

Create MySQL User
mmahmood@mmahmood-Vostro-3400:~$ mysql -u root -p
mysql> CREATE USER 'mahout'@'localhost' IDENTIFIED BY 'mahout123321';
mysql> GRANT ALL ON healthappdb.* TO mahout@localhost IDENTIFIED BY 'mahout123321';

Grant User to a Db
mysql> GRANT ALL ON healthappdb.* TO mahout@localhost IDENTIFIED BY 'mahout123321';

phpMyAdmin not taking password
sudo emacs -nw /etc/phpmyadmin/config.inc.php
// comment following conf.
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

phpMyAdmin - #1146 - Table 'phpmyadmin.pma_recent' doesn't exist
Just use the import feature and choose "create_tables.sql.gz" from /usr/share/doc/phpmyadmin/examples/

phpMyAdmin - session timeout
Add or modify this conf. on /etc/phpmyadmin/config.inc.php
$cfg['LoginCookieValidity'] = 3600 * 12; //12 hr
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License