Thanks to antville.org
Percona xtrabackup commands

/ Tar-compatible file
innobackupex-1.5.1 --stream=tar ./ > backup.tar

/ Compress
innobackupex-1.5.1 --stream=tar ./ | gzip - > backup.tar.gz

/ Copy to remote host
innobackupex-1.5.1 --stream=tar ./ | ssh vadim@desthost "cat - > /data/vol1/mysqluc/backup.tar“

/ set up slave
innobackupex-1.5.1 --stream=tar /tmp/ --slave-info | ssh user@DESTSERVER "tar xfi - -C /DESTDIR“

innobackupex-1.5.1 --apply-log --use-memory=2G /DESTDIR

... Link (0 comments) ... Comment


Innodb Monitor table

Monitor table is a way of communicating directly to the InnoDB storage engine by creating a table of a special name.
Additional debugging information is then written to MySQL’s error log. For example:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

/ view to the error log /

DROP TABLE innodb_monitor;

... Link (0 comments) ... Comment


Starting mysql

When you have installed mysql from source, you will need to change the directory to the source installation and then start the mysql in safe mode.

cd /usr/local/mysql/

./bin/mysqld_safe

If the socket file location is chantged in the my.cnf then you will need to provide it while logging into mysql.

/usr/local/mysql/bin/mysql --socket=/var/lib/mysql/mysql.sock

... Link (0 comments) ... Comment


mysql troubleshooting

The following information is required to analyse any mysql issue

  1. my.cnf configuration file
  2. mysql error log
  3. Output of SQL commands:

show global variables;
show global status;
show /!50101 engine / innodb status\G

  1. Output of shell commands in file /tmp/linux_output.txt:
    top -b -n 1 >> /tmp/linux_output.txt
    mount -v >> /tmp/linux_output.txt
    df -h >> /tmp/linux_output.txt
    egrep -i "err|fault|mysql" /var/log/* >> /tmp/linux_output.txt

... Link (0 comments) ... Comment


Killing mysql

Here are a few ways to kill mysql process.

mysqladmin shutdown
kill -TERM nnnn
killalll mysqld
kill -TERM pidof mysqld
kill -TERM cat /var/run/mysqld.pid

... Link (0 comments) ... Comment


remove all files

If you have problems to delete many files in a folder using rm * because you get the error that the argument list is to long you can use the combination of ls and xargs to get rid of the files.

Instead of
rm *

Solution:
ls | xargs rm -f

... Link (0 comments) ... Comment


Check for table corruption quick and fast

!/bin/sh

for dbname in mysqlshow | awk '{print $2}'
do
mysqlcheck $dbname ticket -Fq
done

call this shell script and find the errors

time sh check.sh | grep error

... Link (0 comments) ... Comment


mysql analysis

When there is an issue, We should gather some preliminary data to analyze first.

tee 'output.txt';
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;
SELECT SLEEP(60);
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;

SHOW GLOBAL VARIABLES;
notee;

Study the 'output.txt' file. Also the my.cnf and slow query log
From the OS;

free -m
ps aux
df -h

iostat -x 1 5
vmstat 1 5
top -b -n 3
mysql> show global status;

... Link (0 comments) ... Comment


mysql to postgresql

It is incredibly easy to migrate from MySQL to PostgreSQL. Just migrate your schema first (leaving foreign keys out until after restore) and then the mysqldump files are almost perfectly compatible with PostgreSQL when using:

mysqldump --compatible=postgresql --no-create-db --no-create-info --add-locks=false --quote-names=false --complete-insert=true

I just had to convert \' to two single quotes and fix MySQL's infamous null dates (0000-00-00 00:00:00) to be true nulls and was able to pipe the backup right into psql.

sed -e "s/\\'/\'\'/g" -e "s/\'0000-00-00 00:00:00\'/null/g" backup.sql > output.sql

... Link (0 comments) ... Comment


import data faster

Following my.cnf parameter is highly recommended on high configuration machine. This will make importing myisam only data much faster.

datadir=/var/lib/mysql

datadir=/mnt/data/mysql/
user=root
skip-innodb
default-storage-engine=myisam
key-buffer=1G
bulk_insert_buffer_size=1G
myisam_max_sort_file_size=20G

tmpdir=/mnt/data/mysql

... Link (0 comments) ... Comment


Online for 6297 days
Last modified: 11/21/19 1:09 AM
Status
Youre not logged in ... Login
Menu
... Home
... Tags

Search
Calendar
November 2019
SunMonTueWedThuFriSat
12
3456789
10111213141516
17181920212223
24252627282930
October
Recent updates
Very useful I needed this
thank you! Between the house cleaning and the tow trucks...
by MarbD (11/21/19 1:09 AM)
Bucket list one of my
favorites sites www.drawinnovations.com
by Monica11 (11/20/19 9:04 PM)
Incredible post! This is
greatly amazing. | roofing contractors near me
by Danielperez123 (11/20/19 8:03 PM)
Nice Antville.org is a great
article to be discussed! Thanks. Medical Converting Company
by Danielperez123 (11/20/19 6:26 PM)
Hello, Thank
you soo much for this great code of java. I am trying...
by Martin Moolley (11/20/19 11:37 AM)
Crack software has ultra-current
Vulnerability Shield. Free download Eset brilliant security eight complete break Direct...
by theumar20 (11/19/19 10:33 PM)
This site provides free serial
keys and activation codes. https://serialkeysfree.org This site provides free serial/activation...
by ayatnoor (11/19/19 7:14 PM)
Nice ohh! This is a
great reference. Thanks for sharing. | phillips norelco beard trimmer
by Danielperez123 (11/19/19 7:12 PM)
Bucket list Nice to know
that there is a site like this who are providing...
by Mikmik (11/19/19 2:55 PM)
Microsoft Office 365 Product Key
List for Free Microsoft office 365 Product key Microsoft Office...
by emilyisa bellaamelia1 (11/19/19 1:31 AM)
Bucket list Thank for this
post . this really help me a lot. carpet cleaning...
by Monica11 (11/18/19 8:09 PM)
The backup worked effectively
and effeciently for me. Great post! click here
by brielleluna (11/18/19 7:36 PM)
WinX HD Video Converter Deluxe
Crack WinX HD Video Converter Deluxe 5.15.3 Crack Key...
by meci12 (11/17/19 4:03 PM)
Nice Same here. I used
the same product. Thanks! | junk yards near me
by Danielperez123 (11/15/19 4:12 PM)
tellthebell Nice share. But, am
getting an error whenever I setup . So, what should...
by zenvanik (11/12/19 9:22 AM)
great to learn Thanks I
was needing this. As someone who prefers tree service winnipeg...
by TheManofRight (11/11/19 11:26 PM)
Noted. Got to apply
this over our website. Tampa SEO
by brielleluna (11/11/19 5:22 PM)
Is this another kind of
AWS management console that was posted on the other...
by brielleluna (11/11/19 5:19 PM)
sitelinx great work פרסום
בטאבולה
by sitelinxblog (11/9/19 8:36 PM)
Amazing! Thanks for welcoming
us with your great information. Keep it up! https://thesoutherninstitute.com
by Danielperez123 (11/8/19 4:05 PM)
Great Good to know you
have also provided which file type should it be saved....
by brielleluna (11/7/19 5:35 PM)
I will try to
apply them. cheapest seo services
by louishill6427 (11/6/19 9:19 AM)
I use these amazon
products too. Credit Card Processing Companies
by louishill6427 (11/6/19 9:14 AM)
Thanks for sharing how
to convert private to public key. Outbound Lighting
by louishill6427 (11/6/19 9:07 AM)
Thanks for sharing the
link for bookmarklet. Cincinnati lot for sale
by louishill6427 (11/6/19 8:42 AM)

RSS feed

Made with Antville
Helma Object Publisher