MySQL Quick Links

External Links

how to login

mysql --database fdw_web;

mysql -u username -ppassword databasename < /tmp/databasename.sql

mysql -umy_username -pmy_password_10 -S ${SOCKET_FILE}
mysql -uroot -p -S
mysql -uroot -pmy_password_10 -S ${SOCKET_FILE}

mysql -u -p -P -h
mysql -h myhostname -P 3309 -umyuser -pmypass

alias myroot='mysql -uroot -pmy_password -S ${SOCKET_FILE}'
myroot -A -ss --database my_db -e "show tables"|awk '{printf ("drop table %s;\n", $1);}'

mysql -ss -A -e "flush privileges;"  # avoid use my_database hanging issue
mysql -vvv -->  print the sql commands befroe print the results

login mysql using default password

#in client side $HOME/.my.cnf file
[client]
user=myusername
password=mypassword

login mysql without password

# in server side my.cnf file before db start
[mysqld]
skip-grant-tables

Variables And Functions

select database();
select @@version;

CNF_FILE:
#set-variable=key_buffer=16M

select database(),user(),@@hostname,@@port,@@server_id;
select version();
select @@sql_mode;
# select @@pseudo_thread_id;
# show variables like '%id%';
# show databases;

SET @schema = IFNULL(@schema,DATABASE());
SET @table='album';

select concat_ws(',',code,ref_code_id,ifnull(expiration_date,''),ifnull(description,'');

select concat(code,',',ref_code_id,',',expiration_date,',',description,',',max_uses,',',times_used);

SELECT CONCAT_WS(',', field1, field2, field3) FROM table;


show errors;
show plugins;

SHOW ENGINE INNODB STATUS;

SET FOREIGN_KEY_CHECKS=0;
set-variable=record_buffer=16M


set global max_allowed_packet=1000000000;

set global net_buffer_length=1000000;


SELECT @@MAX_ALLOWED_PACKET;

set global max_allowed_packet=1000*1024*1024;

set max_allowed_packet=1000*1024*1024;

set net_buffer_length=1000000;
max_allowed_packet=16M
max_allowed_packet=4096M

show global variables like 'max%';

SET GLOBAL max_connect_errors=10000;

SET GLOBAL max_connections=2000;

purge logfile

show global variables like 'expi%';
set global expire_logs_days=18;
# ls -lart
PURGE BINARY LOGS TO 'IDSS1_bin_.000025';
PURGE BINARY LOGS BEFORE '2011-10-31 09:00:00';

SHOW BINLOG EVENTS \G
show binlog events in 'MYDB_bin_.000286';

Create MySQL Users

select user from mysql.user;

show grants for myuser;

create user my_username identified by 'my_password';
grant usage on *.*  to my_username@'%';
grant usage on *.*  to my_username@'%' WITH MAX_USER_CONNECTIONS 3;

grant select on my_db.*  to my_username@'%';
grant select on my_db.my_table to my_username@'%';

create user dixcm identified by 'dixcm_xx';
grant usage on *.*  to dixcm@'%';

grant select on log_sbfe.transaction_log to dixcm@'%';
grant select on log_sbfe.transaction_log_online to dixcm@'%';
grant select on delta_sbfe.corrections_dd to dixcm@'%';
grant select on delta_sbfe.corrections_g1 to dixcm@'%';
grant select on delta_sbfe.corrections_g2 to dixcm@'%';
grant select on delta_sbfe.corrections_o1 to dixcm@'%';
grant select on delta_sbfe.corrections_o2 to dixcm@'%';

flush privileges;

create user xxx identified by 'yyyy';
show grants for myuser;
GRANT SELECT ON *.* TO 'mbs_reports'@'%' IDENTIFIED BY PASSWORD '*B4424F49FE30FF0954EC06D234CAA3C39C768736' WITH MAX_USER_CONNECTIONS 30;

dbssh dbpdba 
set linesize 250 trimspool on
select sid,vip,host,port,db_owner,version,db_type from db_info where sid='EOMRPTP1';
select sid,vip,host,port,db_owner,version,db_type from db_info where sid like '%EOM%'

select user,host from user;
select concat('show grants for ', user, ';') from mysql.user where host='%';
myroot -ss  -A -e "select concat('show grants for ', user, ';') from mysql.user where host='%';"

*** sample:
myroot -ss  -A -e "select concat('show grants for ', user, ';') from mysql.user where host='%' order by user;"|myroot|sed 's/$/;/'

*** replace old user:
myroot -ss  -A -e "select concat('show grants for ', user, ';') from mysql.user where user='laufenta' and host='%' order by user;"|myroot |sed 's/laufent/levinedx/'|sed 's/$/;/'

myroot -ss  -A -e "flush privileges;"

test: mysql -umy_username -pmy_password_10 -S ${SOCKET_FILE}

show databases;

MySQL Permission and Grants

mysqld_safe init_file
/etc/init.d/mysql start/stop $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &

SET PASSWORD FOR 'root'@'%' = PASSWORD('myrootpassword');
flush privileges;

UPDATE mysql.user SET Password=PASSWORD('myrootpassword')
  WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

generate a random password: openssl rand -base64 8

grant all privileges on databasename.* to username@localhost;

select user,password,host from user order by user,host,password;
show grants for root@localhost;

***** select concat('show grants for ', user,'@', "'", host,"'", ';') from user;

select 'show grants for ''' || user || ''';' xx from mysql.user order by user;
select concat(concat('show grants for ''',  user ), ''';' ) xx from mysql.user order by user;

-- grab user
mysql_mbsi_var -ss  -e"select user from mysql.user"|grep -i mbsi
mysql_mbsi_var -ss  -e"select concat(concat('show grants for ', user),';') from mysql.user"|grep -i mbsi

# == Best All
mysql_mbsi_var -ss  -e"select concat(concat('show grants for ', user),';') from mysql.user"|grep -i mbsi|mysql_mbsi_var -ss|sed -e 's/$/;/g'

# == Best Selective
mysql_mbsi_var -ss  -e"select concat(concat('show grants for ', user),';') from mysql.user"|grep -i mbsi|mysql_mbsi_var -ss|sed -e 's/$/;/g'|grep -i fip_region

mysqldump

mysqldump -u root mysql user>mysql_users.sql
mysqldump -u root --databases rt3>rt3.sql
mysqldump --all-databases>all_databases.sql

**** mysqldump --databases --skip-add-drop-table -u root db1

-- use mydb;
\. xxx.sql

mysql -u root -p --socket=/tmp/mysql_restore.sock \
   < ${BACKUP_LOC}/20090620.sql

mysql database < db-dump-file.sql

mysqldump --opt database > db-dump-file.sql
mysqldump --opt database table-name > db-dump-file.sql
mysqldump --opt --databases database1 database2 database3 > db-dump-file.sql
mysqldump --opt --all-databases > total-db-dump-file.sql
mysqldump -u user-id -h host-name --opt --all-databases > total-db-dump-file.sql
mysqlcheck -r -u root -p Database-Name

mysqldump --opt database | mysql --host=host-name -C database

*** locking or blocking issue: 
mysqldump  -S${SOCKET_FILE} -uroot -p${p} --no-data  --single-transaction --databases  -R ${db}
mysqldump  -S${SOCKET_FILE} -uroot -p${p} --opt   --no-create-info --single-transaction --databases ${db}  | gzip > ${loc}/${db}_${INSTANCE_NAME}_data.sql.gz

mysqldump --databases jiradb >aa.sql

***** mysqldump --max_allowed_packet=512M --force --flush-privileges --log-error=mysqldump_errors.log -u dbadmin -S /var/lib/mysql/mysql.sock --master-data=2 --all-databases --single-transaction|gzip>mbsp1_from_01.sql.gz

LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES  TERMINATED BY '\n' (field1,field2,field3);
load data infile 'Web_Appl_Button.txt' into table web_appl_button fields terminated by '|'   OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
load data local infile '/home/dbadmin/fastwebdata/web_button.txt'      into table web_button fields terminated by '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

*** trim date_format:        update web_application_x set last_update_dt=substr(last_update_dt,1,19);
*** set back to null field:  update web_application_x set num_questions=null where num_questions='';


mysql -h localhost -u root -p -e "select host,db,user from db" mysql
mysqladmin -h localhost -u root -p processlist
mysql -h localhost -u root -p database-name < text-file-with-sql-statements.sql
mysql -u -p -P -h
mysql -uroot -p -S

mysql -uxxxx -pyyyyy -P3309 -hzzzzzz
mysql -h myhostname -P 3309 -umyuser -pmypass

mysql -ss -A 

**** mysql -vvv -->  print the sql commands befroe print the results


mysql  -S${SOCKET_FILE} -uroot -p${p} --skip-column-names -e "select user, host from user" mysql

SET SESSION SQL_LOG_BIN=0;
SET SESSION SQL_LOG_BIN=1;

SHOW TABLE STATUS FROM radioshack like 'p_form_app_answers';

mysql installation

$HOME/.my.cnf
[client]
user=myusername
password=mypass

my.cnf
[mysqld_safe]
ledir=/db/p/scralf/v1/mysql5170/libexec  # grab $MYSQL_HOME/libexec contents
[mysqld]
basedir=/db/p/scralf/v1/mysql5170  # grab $MYSQL_HOME errormsg contents
# base_dir=/usr
datadir=/db/p/scralf/v3/table/ALFP3
tmpdir=/db/p/scralf/v1/admin/ALFP3/tmp
skip-slave-start

export MYSQL_HOME=/db/p/scralf/v1/mysql5170
export PATH=$PATH:$MYSQL_HOME/bin
export MANPATH=$MYSQL_HOME/man:$MANPATH
export LD_LIBRARY_PATH=$MYSQL_HOME/mysql/plugin  # grab $MYSQL_HOME/mysql/plugin contents

-- --------------------
unset MYSQL_HOME
my.cnf --> base_dir=/usr

mysql_install_db --ldata=${DATADIR}
mysql_install_db --ldata=${DATADIR} --user=${USER}

mysql_install_db --defaults-file=/opt/mysql/backup/data/my.cnf
--
mysqld --bootstrap
mysqld_install_cmd_line="$mysqld_bootstrap $defaults $mysqld_opt --bootstrap \
--basedir=$basedir --datadir=$ldata --skip-innodb \
--skip-bdb --skip-ndbcluster $args --max_allowed_packet=8M \
--net_buffer_length=16K"

mysqld --skip-grant &


nohup mysqld_safe --language=/db/p/wiki/v1/product/mysql-5.0.51a-linux-i686-icc-glibc23/share/mysql/english --defaults-file=${CNF_FILE} 2>&1 &
--

/usr/bin/mysql_secure_installation
1): change root password
UPDATE mysql.user SET Password=PASSWORD('$password1') WHERE User='root';
flush privileges;

2): remove anonymous users
DELETE FROM mysql.user WHERE User='';

3): remove remote root user
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';

4): drop test database
DROP DATABASE test;

MySQL Software Installation

yum list 2>/dev/null |grep -i percona|grep -i 51|egrep "client|devel|server|shared"|awk 'BEGIN {print "yum install \\";} {printf "%s-%s \\\n", $1, $2;} END {print " ";}'
yum list 2>/dev/null |grep -i percona|grep -i 51|egrep "client|devel|server|shared"|awk 'BEGIN {print "yum install \\";} {printf "%s-%s \\\n", $1, $2;} END {print " ";}'|sed -e 's/.x86_64//g'
yum list 2>/dev/null| grep -i mysql |grep -i installed|awk 'BEGIN {print "yum install \\";} {printf "%s-%s \\\n", $1, $2;} END {print " ";}'

yum install MySQL-client-5.5.21-1.rhel5.x86_64 MySQL-server-5.5.21-1.rhel5.x86_64 MySQL-shared-5.5.21-1.rhel5.x86_64

yum update xtrabackup.x86_64

yum update Percona-Server-client-51.x86_64 \
            Percona-Server-devel-51.x86_64 \
            Percona-Server-server-51.x86_64 \
            Percona-Server-shared-51.x86_64

mysql_upgrade -uroot -p`cat ${CFG_LOC}/.pswd` -S ${SOCKET_FILE} -v -d ${DATADIR}


rpm -qa|grep -i mysql

rpm -ihv *.rpm
rpm -uhv *.rpm

rpm -ihv http://www.percona.com/downloads/percona-Server-5.1/Percona-Server-5.1-61-13.2/RPM/rhel5/x86_64/Percona-Server-client-51-5.1.61-rel13.3.435.rhel5.x86_64.rpm
rpm -ihv http://www.percona.com/downloads/percona-Server-5.1/Percona-Server-5.1-61-13.2/RPM/rhel5/x86_64/Percona-Server-devel-51-5.1.61-rel13.3.435.rhel5.x86_64.rpm
rpm -ihv http://www.percona.com/downloads/percona-Server-5.1/Percona-Server-5.1-61-13.2/RPM/rhel5/x86_64/Percona-Server-server-51-5.1.61-rel13.3.435.rhel5.x86_64.rpm
rpm -ihv http://www.percona.com/downloads/percona-Server-5.1/Percona-Server-5.1-61-13.2/RPM/rhel5/x86_64/Percona-Server-shared-51-5.1.61-rel13.3.435.rhel5.x86_64.rpm
rpm -ihv http://www.percona.com/downloads/percona-Server-5.1/Percona-Server-5.1-61-13.2/RPM/rhel5/x86_64/Percona-Server-test-51-5.1.61-rel13.3.435.rhel5.x86_64.rpm

MySQL Replication Setup

grant replication slave, replication client on *.* to rep_user@'%' identified by 'somepassword';

grant replication slave, replication client on *.* to repl@'%' identified by 'superSecretpwd';

mysqldump --user=root --password --extended-insert --all-databases --master-data  >full_backup.sql 
CHANGE MASTER TO MASTER_LOG_FILE='MYITLOP1_bin_.000010', MASTER_LOG_POS=174; 
replicate-do-db=mydb1,mydb2
server-id=1
auto_increment_increment=10
auto_increment_offset=1 
server-id=2
auto_increment_increment=10
auto_increment_offset=2 
log-bin-index=/db/p/itlo/v2/archlog/MYITLOP1/MYITLOP1_bin.index
relay-log=/db/p/itlo/v1/admin/MYITLOP1/logs/relay.log
relay-log-info-file=/db/p/itlo/v1/admin/MYITLOP1/logs/relay-log.info
relay-log-index=/db/p/itlo/v1/admin/MYITLOP1/logs/relay-log.index 

innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

lower_case_table_names=1

slave-net-timeout = 30
master-connect-retry = 30 

--slave-skip-errors=1032,1062,1053
--slave-skip-errors=all

master-host=dbpitlo-vip4
master-user=rep_user
master-password=somepassword 

Modify the datadir value
datadir=/opt/mysql/backup/data

set-variable    =       ft_max_word_len=254
set-variable    =       key_buffer_size=384M
set-variable    =       max_allowed_packet=5M
set-variable    =       max_connections=1000
set-variable    =       myisam_sort_buffer_size=128M
set-variable    =       open_files_limit=5010
set-variable    =       query_cache_size=64M
set-variable    =       query_cache_type=1
set-variable    =       read_buffer_size=32M
set-variable    =       sort_buffer_size=50M
set-variable    =       table_cache=1024
set-variable    =       thread_cache_size=8
set-variable    =       thread_concurrency=10

set-variable=record_buffer=16M
set global max_allowed_packet=1000000000;
set global net_buffer_length=1000000;
SELECT @@MAX_ALLOWED_PACKET;
max_allowed_packet=16M

Add --defaults_file to the mysql-safe and mysqlmanager startup commands in the start option
--defaults-file=$datadir/my.cnf

SET @@binlog_format="STATEMENT"
SET @@binlog_format="MIXED"
SET @@binlog_format="ROW"
select @@binlog_format;

MySQL Replication Error Fixes

replication error and latency:
show global variables like '%skip%';

set global sql_slave_skip_counter=1;
# set global sql_slave_skip_counter=0;
start slave;
show slave status\G

stop slave;

select * from latency;
show global variables like '%bin%';
set sql_log_bin=0;

update latency 
set time_stamp='2013-01-23 10:56:19'
where server_name='other_host_name';

commit;

Explain Plan and Hints

EXPLAIN SELECT select_options;
EXPLAIN extended SELECT select_options;
EXPLAIN partitions SELECT select_options;
EXPLAIN tbl_name

EXPLAIN SELECT artist_id, type, founded  FROM artist WHERE name = 'Coldplay'\G
EXPLAIN EXTENDED SELECT artist_id, type, founded  FROM artist  WHERE name = 'Coldplay'\G

SELECT SQL_NO_CACHE * FROM tbl1;

SELECT user_id FROM users USE INDEX(user_type) 
WHERE user_type=2
     AND user_id > 2938575 AND parent_id=0 AND status=1 
ORDER BY user_id LIMIT 1;


SET [GLOBAL|SESSION] optimizer_switch="index_merge_intersection=off";
SET @@session.optimizer_switch='index_merge_intersection=off';
SET @@session.optimizer_switch='index_merge_intersection=on';

Information Schema And Dictionary Views

INFORMATION_SCHEMA.TABLES

show create table my_tablename\G

Storage Engine

ALTER TABLE artist ENGINE=MyISAM;
ALTER TABLE MyTable ENGINE = InnoDB;

Index

SHOW CREATE TABLE artist\G
SHOW INDEXES FROM my_tablename\G
SHOW INDEXES;

EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G
EXPLAIN SELECT artist_id, name, country_id FROM artist FORCE INDEX (type) WHERE founded = 1980 AND type='Band'\G

# not in Official MySQL Statements
SHOW INDEX_STATISTICS

EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G

EXPLAIN SELECT artist_id, name, country_id FROM artist FORCE INDEX (type) WHERE founded = 1980 AND type='Band'\G


ALTER TABLE my_table ADD PRIMARY KEY [index-name] (column1,column2...);

ALTER TABLE my_table ADD [UNIQUE] KEY|INDEX [index-name] (column1,column2...);

ALTER TABLE artist DROP INDEX founded,ADD INDEX founded_name (founded,name);

EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G
EXPLAIN SELECT artist_id, name, country_id FROM artist FORCE INDEX (type) WHERE founded = 1980 AND type='Band'\G

ALTER TABLE artist ADD INDEX (founded);
ALTER TABLE album ADD INDEX m1 (country_id, album_type_id);
ALTER TABLE album ADD INDEX m2 (album_type_id,country_id);
ALTER TABLE artist DROP INDEX name, ADD UNIQUE INDEX(name);

EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G
EXPLAIN SELECT artist_id, name, country_id FROM artist FORCE INDEX (type) WHERE founded = 1980 AND type='Band'\G

EXPLAIN SELECT artist_id, name, country_id FROM artist IGNORE INDEX (founded);
EXPLAIN SELECT artist_id, name, country_id FROM artist IGNORE INDEX (founded,founded_2) USE INDEX (type_2);

EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G
EXPLAIN SELECT artist_id, name, country_id FROM artist FORCE INDEX (type) WHERE founded = 1980 AND type='Band'\G

Kill Query Or Thread

mysql -e "show processlist"|grep -i select|grep -i lock
mysql -e "show processlist"|grep -i select|grep -i lock|awk '{print "kill " $1 ";"}'

mysql -e "show full processlist"|grep -i select|grep -i lock
mysql -e "show full processlist"|grep -i select|grep -i lock|awk '{print "kill " $1 ";"}'

set @@autocommit=0;
select connection_id();

show processlist;
show full processlist;
show status\G 
show innodb status\G to verify
show engine innodb status\G  # version 5.6 
show engine innodb status\G

SELECT * FROM information_schema.INNODB_TRX\G  

KILL [CONNECTION | QUERY] thread_id
KILL QUERY will keep the connection
KILL or KILL CONNECTION will disconnect the session and kill the user process
show innodb status\G to verify

transaction_lock

flush tables with read lock; 
FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;

show global variables;

show global variables like '%tx%';
show global variables like 'tx_isolation';

select @@tx_isolation;

set global tx_isolation='REPEATABLE-READ';
set global tx_isolation='READ-UNCOMMITTED';
set global tx_isolation='READ-COMMITTED';
set global tx_isolation='SERIALIZABLE';

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  # default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

show commands

show databases;
show tables;

show processlist;
show full processlist;

FLUSH STATUS;
SHOW SESSION STATUS LIKE 'Handler_read_next';
SHOW SESSION STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE '%sort%';
show status like "Qcache_hits";

show slave status \G
show master status \G

SHOW VARIABLES LIKE 'profiling';

show profiles;
show profile for query 6;
show profile CPU for query 6;

show global variables like 'max_connections';

show global status like 'max%';

Basic MySQL Commands

mysql> \?
create database [database name];
use db_name;
show databases;
show databases like 'ba%'

show tables;
describe table_name;

show tables from db_name;
show tables from db_name like '__ab%';

show table status;
show table status from db_name;

show index from table_name;
show index from table_name from db_name;

show columns from table_name;
show columns from table_name from db_name;

select user(), now(), version(), database();
select database(),user(),@@hostname,@@port,@@server_id;

desc mysql.user;
select * from mysql.user;

show grants; 
show grants for user_name;

show processlist;
show full processlist;

show status;
show global status;

flush status;
flush logs;
flush tables with read lock;

show variables;

drop database [database name];
drop table [table name];

SELECT * FROM [table name];

show columns from [table name];

SELECT * FROM [table name] WHERE [field name] = "whatever";
SELECT DISTINCT [column name] FROM [table name];
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
SELECT COUNT(*) FROM [table name];
SELECT * FROM [table name] WHERE rec RLIKE "^xyz";
SELECT SUM(*) FROM [table name];
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

alter table [table name] drop column [column name];
alter table [table name] add column [new column name] varchar (20);
alter table [table name] change [old column name] [new column name] varchar (50);
alter table [table name] add unique ([column name]);
alter table [table name] modify [column name] VARCHAR(3);
alter table [table name] drop index [colmn name];

LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES  TERMINATED BY '\n' (field1,field2,field3);

Read Mysqlbinlog

mysqlbinlog --base64-output=DECODE-ROWS --verbose {log file} > {output file} 
mysqlbinlog --verbose  --hexdump  --base64-output=DECODE-ROWS --result-file=mantisp2_relay_log_000662_keep.txt --start-position=794955262 relay.000662.keep
mysqlbinlog --verbose  --hexdump --base64-output=DECODE-ROWS --result-file=mantisp2_relay_log_000662_keep.txt relay.000662.keep
mysqlbinlog --verbose  --hexdump --base64-output=DECODE-ROWS --result-file=mantisp2_relay_log_000663_keep.txt relay.000663.keep

mysqlbinlog --start-date="2009-06-20 9:50:00" \
     --stop-date="2009-06-20 10:05:00" \
     ${ARCH_LOC}/bin.123456 \
> /tmp/mysql_restore.sql

mysqlbinlog --stop-date="2009-06-20 9:50:59" \
      ${ARCH_LOC}/bin.123456

mysqlbinlog --start-date="2009-06-20 9:55:00" \
        ${ARCH_LOC}/bin.123456

Network Binding

mysqld --socket=/tmp/mysql_restore.sock --skip-networking

my.cnf
#Add the following
skip-networking
#Comment out the following
#bind-address = ip-address

Working With Shell

myroot -A -ss --database alfresco402 -e "show tables"|awk '{printf ("drop table %s;\n", $1);}'

Percona Tool Kit

pt-show-grants | grep dveeden
-- Grants for 'dveeden'@'localhost'
GRANT USAGE ON *.* TO 'dveeden'@'localhost';

working: running on slowest server to avoid error
./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --create-replicate-table --empty-replicate-table --recursion-method hosts --max-lag 3000000 --nocheck-replication-filters --lock-wait-time=50 -h bctlppnc066 -P 3306 -d ecrash -t agency -u dbamaint -pkungfudba1!



working:
./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --nocheck-replication-filters --lock-wait-time=50 -h alalpdba015 -P 3306 -d ecrash -t vehicle -u dbamaint -pkungfudba1!


./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --max-lag 3000000 --nocheck-replication-filters --lock-wait-time=50 -h alalpdba015 -P 3306 -d ecrash -t vehicle -u dbamaint -pkungfudba1!

./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --recursion-method hosts --max-lag 3000000 --nocheck-replication-filters --lock-wait-time=50 -h alalpdba015 -P 3306 -d ecrash -t agency -u dbamaint -pkungfudba1!

./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --create-replicate-table --empty-replicate-table --recursion-method dsn="-h alalpdba015 -P 3306 -t ecrash.agency" --max-lag 3000000 --nocheck-replication-filters --lock-wait-time=50 -h alalpdba015 -P 3306 -d ecrash -t
agency -u dbamaint -pkungfudba1!

./pt-table-checksum-mbsi --replicate-database ecrash --replicate ecrash.percona_checksums --create-replicate-table --empty-replicate-table --recursion-method hosts --max-lag 3000000 --nocheck-replication-filters --lock-wait-time=50 -h alalpdba015 -P 3306 -d ecrash -t agency -u dbamaint -pkungfudba1!

pt-table-sync --print --lock=0 -pkungfudba1! -udbamaint --no-check-slave --no-check-triggers h=bctlpdba006.risk.regn.net,D=framework,t=value_grp2 h=alalpdba038.risk.regn.net>value_grp3_diff.sql

pt-table-sync --print --lock=0 -pkungfudba1! -udbamaint --no-check-slave --no-check-triggers h=bctlpdba006.risk.regn.net,D=framework,t=value_grp3 h=alalpdba038.risk.regn.net>value_grp3_diff.sql

mysql -h alalpdba038.risk.regn.net -P 3306 -pkungfudba1! -udbamaint
use framework;
set sql_log_bin=0;
\. value_grp3_diff.sql
set sql_log_bin=1;

Implicit Data Type Conversion

Implicit Data Type Conversion During MySQL Replication ( master/slave data inconsistent )
set global slave_type_conversions=ALL_NON_LOSSY;
show global variables like  'slave_type_conversions';
set global slave_type_conversions='ALL_LOSSY,ALL_NON_LOSSY';

disable foreign keys

set foreign_key_checks = 0; only kinda works

schema space

https://github.com/effectiveMySQL/BackupAndRecovery/blob/master/links.txt

https://raw.github.com/effectiveMySQL/BackupAndRecovery/master/links.txt

https://github.com/effectiveMySQL

# Effective MySQL: Replication Techniques In Depth by Ronald Bradford and Chris Schneider
http://effectivemysql.com/book/replication-techniques/

Replication Related:

-- BIN LOG on/off
SET SQL_LOG_BIN=0;
SET SQL_LOG_BIN=1;

-- SKIP ERROR
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE SQL_THREAD;
SHOW SLAVE STATUS\G
SHOW MASTER STATUS\G

SET SESSION binlog_format=ROW;  cf: STATEMENT, MIXED


-- Find Log Bin Directory
grep log-bin ${MY_CNF} | cut -d'=' -f2

-- Read Bin Log or relay log Content
mysqlbinlog mysql-bin.000001
mysqlbinlog mysqld-relay-bin.000005

-- Purge Bin Logs
PURGE BINARY LOGS TO 'mysql-bin.000005';
PURGE BINARY LOGS BEFORE '2011-10-31 09:00:00';

SHOW MASTER LOGS;
SHOW WARNINGS\G
PURGE MASTER LOGS TO 'mysql-bin.000012';

SHOW GLOBAL STATUS LIKE 'Com_alter%';

# Identifying Schema Inconsistencies
SHOW GLOBAL STATUS LIKE 'Com_alter%';

# Identifing Schema Inconsistencies
MASTER="alpha"
MASTER="beta"
mysqldump -uroot -ppasswd -h${MASTER} --no-data --skip-dump-date --databases test | md5sum
mysqldump -uroot -ppasswd -h${SLAVE} --no-data --skip-dump-date --databases test | md5sum
mysqldump -uroot -ppasswd -h${MASTER} --no-data --skip-dump-date --databases test | sed -e "s/AUTO_INCREMENT=[0-9]* //" | md5sum
mysqldump -uroot -ppasswd -h${SLAVE} --no-data --skip-dump-date --databases test | sed -e "s/AUTO_INCREMENT=[0-9]* //" | md5sum


mysqldump --no-data --skip-lock-tables --skip-dump-date --skip-comments

LOAD DATA LOCAL INFILE 'mbdump/artist' INTO TABLE artist FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n';


CREATE TABLE artist_credit_name (
    artist_credit INTEGER NOT NULL,
    position SMALLINT NOT NULL,
    artist INTEGER NOT NULL,
    name INTEGER NOT NULL,
    join_phrase TEXT NULL,
PRIMARY KEY (artist_credit,position),
INDEX (artist),
INDEX (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER $$
DROP PROCEDURE IF EXISTS new_artist$$
CREATE PROCEDURE new_artist(p_artist_id INT)
BEGIN
END$$

-- set variable
SET @schema = IFNULL(@schema,DATABASE());
SELECT NOW(), VERSION();

# Per Schema Queries
SET @schema = IFNULL(@schema,DATABASE());

# One Line Schema Summary
SELECT table_schema,
         SUM(data_length+index_length)/1024/1024 AS total_mb,
         SUM(data_length)/1024/1024 AS data_mb,
         SUM(index_length)/1024/1024 AS index_mb,
         COUNT(*) AS tables,
         CURDATE() AS today
FROM information_schema.tables
WHERE table_schema=@schema
GROUP BY table_schema;

# Schema Table Usage
SELECT if(length(table_name)>20,concat(left(table_name,18),'..'),table_name) AS table_name,
         engine,row_format as format, table_rows, avg_row_length as avg_row,
         round((data_length+index_length)/1024/1024,2) as total_mb,
         round((data_length)/1024/1024,2) as data_mb,
         round((index_length)/1024/1024,2) as index_mb
FROM information_schema.tables
WHERE table_schema=@schema
ORDER BY 6 DESC;

Space Usage Calculation

select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE from information_schema.routines where ROUTINE_NAME='GET_LOCK';

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();


SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';

Trouble Shooting Tricks

Trouble Shooting
1): mysql: Cannot load from mysql.proc. The table is probably corrupted
SHOW FUNCTION STATUS WHERE Db = 'mysql';

mysql -V

mysql_upgrade -uroot -p --> mysql already upgrade, please use the force option
mysql_upgrade -uroot -p --force


http://serverfault.com/questions/361838/mysql-cannot-load-from-mysql-proc-the-table-is-probably-corrupted


2): 
mysqld: unknown variable 'defaults-file=

mysqld: unknown variable mysqld: unknown variable defaults-file/defaults-extra
this can be fixed by setting the --defaults-file option at the beginning of the options list!!!!!weird, but works :)



3): skip grant table

http://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/

mysqld_safe --skip-grant-tables &

mysqld --defaults-file="c:\mysql\my.ini" --init-file=c:\\init.txt

--skip-grant-tables  --skip-networking
mysql_upgrade


http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

--init-file=

-- --------------



This installation of MySQL is already upgraded to 5.6.10, use --force if you still need to run mysql_upgrade
$ mysql_upgrade -uroot -p`cat ${CFG_LOC}/.pswd` -S ${SOCKET_FILE} --force



4):
show table;
show table status;

select TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.tables order by TABLE_SCHEMA, TABLE_NAME, ENGINE;

Communication Error fix

set global max_allowed_packet=18777216;

show variables like 'max_allowed_packet';
 
show variables like 'net_%';
net_write_timeout=60; # default values
net_read_timeout=30;  # default values

set global net_read_timeout=300;