MySQL Commands
Table of Contents
|
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