I am in the process of putting my notes into web pages, please keep checking in later for more contents

MySQL Quick Links

login  create user  permission  mysqldump  purge binlog  installation  setup replication  skip replication error  kill query  transaction and lock 
explain plan and hints  index  function and variables  storage engine  show 

External Links

mysqlperformanceblog  sqlyog  planet mysql  Oracle MySQL 


top  next 

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

top  prev  next 

login mysql using default password

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

login mysql without password

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

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;

top  prev  next 

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';

top  prev  next 

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;

top  prev  next 

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


top  prev  next 

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';

top  prev  next 

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;
top  prev  next 

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

top  prev  next 

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;


top  prev  next 

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;


top  prev  next 

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';


top  prev  next 

Information Schema And Dictionary Views

INFORMATION_SCHEMA.TABLES

show create table my_tablename\G

top  prev  next 

Storage Engine

ALTER TABLE artist ENGINE=MyISAM;
ALTER TABLE MyTable ENGINE = InnoDB;
top  prev  next 

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
top  prev  next 

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
top  prev  next 

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;
top  prev  next 

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%';


top  prev  next 

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);

top  prev  next 

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

top  prev  next 

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

top 

Working With Shell

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

top  prev  next 

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;
top  prev  next 

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';
top  prev  next 

disable foreign keys

set foreign_key_checks = 0; only kinda works
top  prev  next 

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;

top  prev  next 

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';

top  prev  next 

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;


top  prev  next 

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;

top