========================================
Misc Info
========================================
select database(); <- List Current Database
select version(); <- List Current version
select current_date,
current_time,now(), database();
========================================
Create/Grant/Drop/Privs User
========================================
Note 1: user info is stored
in database name called "mysql" and table "user"
Note 2: If you specify only
the user name part of the account name, a host name part of '%' is used.
Note 3: If you want user to
connect database from any machine then do not provide HOST in the user creation
Note 4: Must Grant to
connect to specific database *.* will give access to all databases;
DESC mysql.user;
DROP user
'mysql'@'dbaovm03';
CREATE user user1 identified by 'mysql';
GRANT ALL ON db1.* to
db1_user1;
CREATE USER 'mysql'@'%';
identified by 'mysql' [WITH GRANT OPTION];
GRANT ALL ON *.* to user1
[WITH GRANT OPTION];
SELECT user,host,db from
mysql.db where db = "*<database_name>*";
SELECT user,host,db from
mysql.user;
SELECT * FROM
mysql.tables_priv ;
SELECT * FROM
mysql.columns_priv ;
SELECT * FROM
mysql.procs_priv ;
SELECT
CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
---------------------------------------------------------------------------------------------------------------------
Connect ->
mysql -uroot -pmysql [dbname] (Do not
put any space between -p (lower p) and pwd to avoid pwd prompt)
mysql --user=root --password=mysql
mysql -u root -pmysql -h localhost
mysql -u root -pmysql -h localhost -P
3306 [OK]
for port "Caps P"
mysql
-u root -p mysql --> prompt for the pwd and try to connect
dbname=mysql
mySql Process while
runnig -> ps -ef | grep -i
mysqld_safe
ps -ef | grep
-i /usr/bin/mysqld
list session current values -> mysql>show status ;
list parameteres ->
mysql>show variables like '%';
mysqladmin -u root -p variables
Change Parater ->
mysql>set bulk_insert_buffer_size=40000;
list Databases -> mysql>show databases
Create Database ->
mysql>create database [mycustomer];
List Tables ->
mysql>show tables in information_schema;
[ show tables in DATABASE]
Desc Tables -> mysql>describe
[tab]
mysql>show columns from [tab];
Create table -> mysql>use [db]create table [tab](id
integer primary key,nm varchar(30), ph varchar(10));
drop table -> mysql>use [db] , drop table [table
Name]
drop database -> mysqladmin -u root -p drop [dbname]
Error Log Default -> /var/lib/mysql...
Commit -> mysql>commit;
help ->
mysql>help , mysql> help
Load Data ->
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
-> LOAD DATA LOCAL INFILE
'/path/pet.txt' INTO TABLE pet LINES
TERMINATED BY '\r\n';
-> INSERT INTO pet VALUES
('Puffball','Diane','hamster','f','1999-03-30',NULL);
start/stop ->
service mysql start/stop
/etc/init.d/mysql stop
sudo -u mysql /usr/local/mysql/bin/mysqld
stop
mysqladmin -u root -p shutdown
status details -> mysql> status
#mysqladmin -u root -p status | extended-status
#mysqladmin -uroot -ppwd status | extended-status
Check Mysql is alive -> mysqladmin -uroot -p ping
Output:mysqld is alive
mysqladmin -uroot -p processlist
reload privs -> mysqladmin -u root -p
reload; mysqladmin -u root -p refresh
Dump a database -> mysqldump -u root -p [dbname] >
output_filename.sql
Restore database -> mysql> create database
[dbname]; mysql -u root -p [dbname] <
dumpfile_name.sql
Drop column -> msql> ALTER TABLE [tableName] drop [ColumnName];
Rename Table -> mysql> RENAME TABLE [tableA]
TO [tableB];
========================================
MYSQL from Command prompt
You can add all commands in one lin eand execute
========================================
-- Prompt update and
redirect output
mysql -u oas_user -p
oas_user oas "update accounts a, password_reset_tokens t set status =
'Stub_Expired'
where a.id = t.account_id and
t.created_at < date_sub(sysdate(), INTERVAL 47 HOUR); commit;" >
/tmp/expire_pending_accounts.output.txt
-- Prompt deletes and
redirect output
mysql -u oas_user -p
oas_user oas "delete from change_events where account_id in (select id
from accounts where username LIKE '%@system.test.com'); delete from
password_reset_tokens where account_id in (select id from accounts where
username LIKE '%@system.test.com'); delete from accounts where username LIKE
'%@system.test.com'; commit;" > /tmp/purge_test_accounts.output.txt
mysql -uoas_user -poas_user
oas -e"select count(1) from information_schema.tables;"
mysql -uoas_user -poas_user
oas -e"select count(1) from information_schema.tables;" > drew.txt
========================================
MYSQL FLUSH
========================================
# mysqladmin -u root -p
variables
# mysqladmin -u root -p
kill 5
# mysqladmin -u root -p
flush-hosts
# mysqladmin -u root -p
flush-tables
# mysqladmin -u root -p
flush-threads
# mysqladmin -u root -p
flush-logs
# mysqladmin -u root -p
flush-privileges
# mysqladmin -u root -p
flush-status
mysql> show storage
engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment
| Transactions | XA | Savepoints
|
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO
|
| CSV | YES
| CSV storage engine | NO | NO | NO
|
| MRG_MYISAM | YES
| Collection of identical MyISAM tables | NO | NO | NO
|
| BLACKHOLE | YES | /dev/null storage engine (anything you
write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for
temporary tables | NO| NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports
transactions, row-level locking, and foreign keys | YES | YES
| YES |
| ARCHIVE | YES
| Archive storage engine | NO | NO| NO |
| PERFORMANCE_ SCHEMA |
YES | Performance Schema | NO| NO| NO|
| FEDERATED | NO | Federated MySQL storage engine | NULL
| NULL| NULL|
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
mysql> SHOW ENGINE
INNODB MUTEX;
+--------+------------------------------+------------+
| Type | Name | Status |
+--------+------------------------------+------------+
| InnoDB |
&buf_pool->flush_state_mutex | os_waits=1 |
| InnoDB |
&log_sys->checkpoint_lock |
os_waits=2 |
+--------+------------------------------+------------+
========================================
Command Line
========================================
--basedir=e: /mysql
--datadir=d: /mysqldata
or
mysql
--defaults-file=c:\my-opts
refrence other file
!include file_name
!includedir dir_name
========================================
Logs and Locations
========================================
option file location : /etc/my.cnf or $MYSQL_HOME/my.cnfor or ~/.my.cnf
mysql can write to several
types of logs
syslog: mysqld_safe option
--syslog[=tag]
--skip-syslog(default)
Or
general Query log , slow query log . binay log
you can start server with
file name ow everything will goto screen
mysqld --log-error=filename
hostname.err
mysqld_safe
general query log - Logs
all SQL received by the server/Client Connections
You may also write in a
table general_log tables in mysql database
SET GLOBAL general_log='ON'
SET GLOBAL
general_log='OFF'
General QUERY LOG
------------------------------
set general_log to
enable/disable
Set general_log_file to
specify name of the logfile
--log-output options
1) TABLE -log to tables
2) FILE -log to files
1) NONE - do not log to tables or files
Slow Query Log ( file or
Table)
--------------------------------
Set server variable , Log
query executing time longer than
long_query_time server variable
(in Sec)
Enable log
slow_query_log or
slow_query_log_file=filename
Not Index Queries
--log-queries-not-using-indexes
mysql_install_db
--user=mysql --datadir=/data/mysql/data
mysql_install_db
--user=root --datadir=/data/mysql/data
DEFAULT database and Log
/Err Location : /var/lib/mysql
DEFAULY cnf location
/etc/my.cnf
========================================
==== MySQL Client Based programs ===
========================================
mysql
mysqladmin
mysqlimport
mysqldump
mysql -uroot
--skip-column-names -rsp
mysql --host=<>
--port=3351 --user=ro
Terminator
: or \g
\G ( Specifies vertical
display of output)
\c (don't send current
statement to server)
mysql> prompt aa >
PROMPT set to 'aa >'
aa >
root@localhostmysql>prompt
\u@\h \d>
PROMPT set to '\u@\h
\d>'
root@localhost mysql>
Adarsh Kumar
AK Technosoft Corp.
Database Design and Consulting
