Mysql useful production query script

How to start MySQL
# service mysqld start
# /etc/init.d/mysqld start
How to set root password in MySQL
# mysqladmin password “redhat”
How to Login in Mysql
# mysql -u root -predhat
mysql>

How to change root and Users password
mysql> UPDATE mysql.user SET Password=PASSWORD(‘redhat123′) WHERE User=’root’;

How to Create database
mysql> create database DB-NAME;

How to Check database
mysql> show databases;

How to Create User
mysql> CREATE USER ‘USER_NAME’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

How to given PRIVILEGES
mysql> GRANT ALL PRIVILEGES ON `DB-NAME` . * TO ‘USER_NAME’@’localhost’;

How to given one Privileges
mysql> GRANT SELECT, INSERT, DELETE ON database TO username@’localhost’ IDENTIFIED BY ‘password’;

How to delete User
mysql> drop user ‘USER_NAME’@’localhost’;
——————————————————————————————
How to create MySQL data file in Another Location# mysql_install_db –user=mysql –datadir=”your data Location path ” (example –datadir=/var/tmp/abc)

How to Select DB
mysql> use DB-NAME;

How To Create Table in DB defaults Engine
mysql> CREATE TABLE TABLE_NAME (name varchar(50), mobile varchar(50) , id varchar(50) , remark varchar(50));
How to check table Schema
mysql> desc TABLE_NAME;

How to Check Table ENGINE
mysql> show create table TABLE_NAME;

How To check MySQL ENGINES
mysql> show engines;

How to check data in TABLE
mysql> select * from TABLE_NAME;

—————————————————————————————————-
How to Import CSV File in MySQL
mysql> LOAD DATA INFILE’/path/abc.csv’ INTO TABLE TABLE_NAME FIELDS TERMINATED BY ‘,’ ;

How to Create Index in Coloumn
mysql> ALTER TABLE table_Name ADD INDEX (coloumn_name);
Query OK, 0 rows affected (1 min 35.39 sec)
Records: 0 Duplicates: 0 Warnings: 0

How to Check Cretaed INDEX
mysql> show index from table_Name;

How to check user privileges
mysql > SHOW GRANTS FOR ‘root’@’localhost’;

How to set REPLICATION privileges (Master)
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘username’@’%’ IDENTIFIED BY ‘redhat’;

How to set Master Podition (Slave)
mysql> CHANGE MASTER TO MASTER_HOST=’12.34.56.789′,MASTER_USER=’username’,MASTER_PASSWORD=’redhat’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;
mysql> CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;

How to Skip Slave Error in one time
mysql> stop slave; SET GLOBAL sql_slave_skip_counter = 1; start slave;

How to read mysqlbin log File
# mysqlbinlog –base64-output=DECODE-ROWS –verbose replication.383626 > /tmp/replication1111.383626.txt

How to check mysql running binlog_format
mysql> SHOW VARIABLES like ‘%binlog_format%’;

How to Change Binlog format (ROW | MIXED | STETMENT )
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;
===============================================================
mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;

# my.cnf: binlog_format=ROW

How to check PROCEDURE and FUNCTION
mysql> SHOW PROCEDURE STATUS;
mysql> SHOW FUNCTION STATUS;

mysql> SHOW CREATE PROCEDURE PROCEDURE_NAME;
mysql> SHOW CREATE FUNCTION FUNCTION_NAME;

MySQL Error Code: 1548 Cannot load from mysql.proc. The table is probably corrupted
# mysql_upgrade -uroot -p –force

——————————
How to take mysql dump
—————————

How to take dump with Procedures and Functions
E:\bin>mysqldump.exe -u root -p –routines –verbose –single-transaction –no-data misdata > E:\DUMP.SQL\routines1.sql

How to take complete dump
# mysqldump -u root -p –all-database > /tmp/dump.sql

How to take dump only specific databases# mysqldump -uroot -p db1 db2 db3 > dump.sql

How to take dump only specific table in database
# mysqldump -u root -p database_Name table_Name > dump.sql

How to restore Dump
# mysql -u root -p < /tmp/dump.sql Dump Only Table structure # mysqldump -u root -p –single-transaction –databases ivr_cms –no-data > /tmp/dump.sql

How to take dump only table structure
# mysqldump -u root -p –all-databases –no-data > /tmp/dump.sql

The following SQL statement selects all customers with Country NOT containing the pattern “22”:
mysql> SELECT * FROM tbl_contentusage_mod_aircel WHERE Duration NOT LIKE ‘%22%’;

SET GLOBAL log_bin_trust_function_creators = 1;
SET GLOBAL group_concat_max_len=15000;

How to Create new table with old table with data without index. (and Rename)
mysql> create table New_Table_Name select * from OLD_Table_Name;

How to Create blank table with indexing. without data. (and Rename)
mysql> create table New_Table_Name like OLD_Table_Name;

 How to take table data in CSV file
mysql> select * into outfile ‘/tmp/abc1.csv’ fields terminated by ‘,’ from chandan_rename_table ;

How to repair myisam tables (for all databases)
# mysqlcheck –repair -u root -p –all-databases

How to repair myisam Single tables (for Single Table)
# mysqlcheck –repair -u root -p database Table_Name

How to check Login databasemysql> select database();

How to check Login Usermysql> select user();

How to check Size in Databases
mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024 “size in MB” FROM information_schema.TABLES GROUP BY table_schema;

How do I change the privileges for MySQL user that is already created
select user,host from mysql.user;

To show privileges:
show grants for ‘user’@’host’;

To change privileges, first revoke. Such as:
revoke all privileges on *.* from ‘user’@’host’;

Then grant the appropriate privileges as desired:
grant SELECT,INSERT,UPDATE,DELETE ON `db`.* TO ‘user’@’host’;

Finally, flush:
flush privileges;

—————————————————————————————————-

Shows all queries running for 5 seconds or more:
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’ AND TIME >= 5;