mysql

If disk space has run low, you will observe:

1. Application query response time increases.
2. Few application queries will randomly fail with lock wait timeout.
3. /mnt/eprint/mysql/mysqld.err error log will display the message: /usr/libexec/mysqld: Disk is full writing '/mnt/eprint/mysql/replication/mysql-bin.000033' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)


sudo su
mysql -u root -popelin
show databases;
use cpgDB;
show tables;
show create table tablename;
drop table tablename;
set foreign_key_checks=0;
select database();
drop database testdb;
create database testdb;
describe tablename;
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET tx_isolation = 'READ-COMMITTED';
exit;
tx_isolation system variable. Has both global and session scope.
The default transaction isolation level. Defaults to REPEATABLE-READ.
You can refer to the values of specific global or sesson system variables in expressions by using one of the @@-modifiers. For example,
you can retrieve values in a SELECT statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MySQL returns SESSION values.
autocommit is a session variable and must be set for each session.
use \G for displaying columns linearly
You can also use scripts from the mysql prompt by using the source command or \. command:
mysql> source C:\srini\IPG\code\CloudPrint\database-main\src\main\sql\cpgdb\00289.DE11469.dmapi.cpgDB.insert.reserved.email.addresses.into.BlockedEmailAddress.table.sql
mysql> \. filename
SHOW INDEX FROM tbl_name
mysql -uroot -popelin -e 'show engine innodb status\G' >> /home/cp_user/srini/out.txt
mysql> tee /tmp/out.txt    //this commands makes mysql to write its output to the given file along with console
mysql> notee                //this command disables the effect of tee command
mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.5\my2.ini"
mysqladmin -u root –p shutdown
mysqldump --no-data -u root -p cpgDB > /tmp/cpgDB.sql    //This option exports only database and table schema, not data.
--no-create-info, -t     //This option instructs the utility not to add CREATE TABLE statements to the export file.
--no-data, -d            //This option exports only database and table schema, not data.
mysqldump -u russell -p workrequests work_req clients > /tmp/workreq_clients_tables.sql   //Here the database is workrequests and the tables to be backed up are
work_req and clients. Their table structures and data will be copied into the text file workreq_clients_tables.sql.
mysqldump --no-create-info --insert-ignore --where="service_name='storage'" -u root -p appconfig appconfig > /tmp/appconfig.sql
mysqldump -u root -p puma_db > /tmp/puma_db.sql
mysql -u root -p < /tmp/puma_db.sql
mysql -h host -u user -p
alter table deviceemailaddressalias add unique key (deviceId,creationTimeInMilliSec); to add unique key constraint
alter table deviceemailaddressalias add index index_name (deviceId,creationTimeInMilliSec); to drop unique key constraint
alter table deviceemailaddressalias drop index deviceId; to drop unique key constraint
show procedure status
show function status
CREATE INDEX idx_actor_first_name ON actor (first_name); The simplest CREATE INDEX statement adds a non-unique index to a table.
du -sk * | sort -nr | more
----------------------------
use cpgDB;
CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;//this statement starts writing (more than thrice per minute) enhanced output of 'show engine innodb status\G' to error log which in our case is /opt/apps/ms/mysql/mysqld.err. This is usfull for continuous monitoring.
drop table innodb_lock_monitor; //to disable lock monitoring, the table should be dropped.
-----------------------------
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_monitor;
----------------------------------
select Token from AccessToken where expiryTime<=1414728000000 order by expiryTime asc into outfile 'c:\\srini\\testmail\\prodissues\\renew tokens\\finalTokens.txt';
LOAD DATA INFILE 'c:\\srini\\oauth1_clients.txt' INTO TABLE ConsumerDetails;
select Token from AccessToken where expiryTime<=1414728000000 into outfile 'c:\\srini\\testmail\\prodissues\\renew tokens\\latest tokens\\googleTokens.txt';
mysql> show variables like '%general%';
mysql> set global general_log=ON;
mysql> show open tables in testdb; //shows table locks on all the tables
mysql> analyze table SessionToken; //allows query optimizer to choose  better indexes
mysql> optimize table SessionToken; //reorganizes data on the disk.

cat > /opt/apps/ms/mysql/mysqld.err
csplit -k -f finalTokens -n 3 finalTokens.txt 50000 {*}


enabling general log for mysql 5.0.x versions:

1. log variable value should be set to ON.
2. Mysql should be started with command line option --log=/mnt/eprint/mysql/genlog.log as below
/bin/sh /usr/bin/mysqld_safe --defaults-file=/mnt/eprint/mysql/my.cnf --datadir=/mnt/eprint/mysql/data --socket=/mnt/eprint/mysql/mysql.sock --log-error=/mnt/eprint/mysql/mysqld.err --pid-file=/mnt/eprint/mysql/mysqld.pid --log=/mnt/eprint/mysql/genlog.log --user=mysql