Thanks to antville.org
log mysql output

tee output.txt
show global variables;
show engine innodb status\G
show full processlist;
show global status;
select sleep(60);
show engine innodb status\G
show full processlist;
show global status;
notee;

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


change log file size in MySQL

Here are the steps to be followed while changing the log file size in mysql:

1) SET GLOBAL innodb_fast_shutdwn = 1;
2) Stop the mysqld server and check the end of the error log file to ensure that the shutdown was clean.
3) Move the existing log files to another location for extra safety.
4) Make the size change in the my.cnf and restart.

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


MySQL common error messages

1146 Table does not exist
1054 column does not exist
1062 duplicate entry for primary key
1406 Data too long for column
1064 sql syntax error
1111 invalid use of group function
1366 incorrect integer value
1046 No database selected

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


compare mysql schema

And here is poor man's schema compare using good old diff command...

mysqldump nasik1 --no-data > nasik1.sql

mysqldump -h1.2.3.4 murtizapur --no-data > murtizapur.sql

diff murtizapur.sql nasik1.sql --side-by-side --suppress-common-lines | grep -v ENGINE | grep -v BTREE > to_study.txt

Or use one of the packages:

https://code.google.com/archive/p/phpmydiff/downloads

https://www.red-gate.com/products/mysql/mysql-comparison-bundle/

https://www.devart.com/dbforge/mysql/schemacompare/download.html

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


Rank mysql records using variables

SET @rank = 0, @prev_val = NULL;

SELECT rank, correct FROM (
SELECT @rank := IF(@prev_val=correct,@rank,@rank+1) AS rank,
@prev_val := correct AS correct, uid
FROM quiz_user ORDER BY correct DESC
)as result WHERE uid=xxxxxxxxxxxx

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


backup mysql users

It is easy to backup mysql users -

wget percona.com/get/pt-show-grants;
perl pt-show-grants

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


mysql and shell command

date -d"-1 day ago" +'%b%d'

equals to

mysql -Bse"select date_format(date(date_add(now() , interval 1 day)),'%b%d')"

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


Preserving comments in Stored Procedure

You can write multi line comments in a stored procedure by following mysql to think that it is a version specific code.

/*!9999
Multi line comment, retained by mysql
not executed till it reaches the mysql version 9999 :))
*/

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


percona utility

We can re-create mysql users using the percona utility.

wget percona.com/get/pt-show-grants

chmod 777 pt-show-grants

./pt-show-grants

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


Backup mysql data using innobackup

BACKUP

innobackupex --user=USERNAME --password=PASSWORD --no-timestamp /data/backups/new_backup <--(PATH OF BACKUP)
innobackupex --apply-log /data/backups/new_backup

RESTORE

service mysql stop
mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/
innobackupex --copy-back /data/backups/new_backup <--(PATH OF BACKUP WHERE IS STORED)
chown -R mysql: /var/lib/mysql
service mysql start

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


Online for 7856 days
Last modified: 1/9/23, 8:52 AM
Status
Youre not logged in ... Login
Menu
... Home
... Tags

Search
Calendar
February 2024
SunMonTueWedThuFriSat
123
45678910
11121314151617
18192021222324
2526272829
January
Recent updates
User Defined Property You
can create User Defined Property in libreoffice writer. File – Properties...
by shantanuo (1/9/23, 8:52 AM)
Arranging Chapters in the Navigator
To use a custom paragraph style for a heading, choose...
by shantanuo (1/4/23, 8:26 AM)
Use focus mode using Android
phones Settings > Digital Wellbeing and parental controls. Tap your...
by shantanuo (1/1/23, 3:59 AM)
Embed Fonts in document If
you use a font that the recipient is unlike to...
by shantanuo (12/18/22, 10:07 AM)
Using Navigator in Writer To
open the Navigator, select View > Navigator, or press the...
by shantanuo (12/18/22, 10:06 AM)
More about hyphenation The settings
for Tools > Options > Language Settings > Writing Aids...
by shantanuo (12/18/22, 10:04 AM)
link or unlink template If
you are using Libre office then template changer extension is...
by shantanuo (12/16/22, 5:27 AM)
Finding Styles you can select
Edit > Find and Replace > Other Options > Paragraph...
by shantanuo (12/14/22, 7:17 AM)
regex in clac In LibreOffice
Calc, you can use function REGEX for e.g. Begins with...
by shantanuo (12/14/22, 4:40 AM)
Libre Calc tips Turn Off
Grid Lines If you want to turn off grid lines...
by shantanuo (12/13/22, 8:14 AM)
More about Styles You can
goto View - Styles and change "All Styles" to "Applied...
by shantanuo (12/13/22, 7:49 AM)
Page Break Before Every Chapter
If your chapter titles are using the "Heading 2" Style:...
by shantanuo (12/13/22, 6:36 AM)
View and print in different
color Displaying Color in LO but Printing as White Page...
by shantanuo (12/13/22, 6:26 AM)
Change Normal Template in Libreoffice
Writer 1) Open a new file and set your font;...
by shantanuo (12/12/22, 8:45 AM)
Short english words in Devanagari
The list of short english words written in Devanagari. #...
by shantanuo (10/5/22, 9:05 AM)
Card issuing banks CITI
Standard Chartered HSBC American Express HDFC ICICI AXIS INDUS IND Kotak...
by shantanuo (7/16/22, 4:46 AM)
Activate IAM Access To activate
the Activate IAM Access setting, you must log in to...
by shantanuo (7/12/22, 5:52 AM)
use cheat instead of man
wget https://github.com/cheat/cheat/releases/download/4.2.3/cheat-linux-arm64.gz gunzip cheat-linux-arm64.gz chmod 770 cheat-linux-arm64 ./cheat-linux-arm64 mv cheat-linux-arm64...
by shantanuo (7/4/22, 8:53 AM)
python module itertools list of
useful methods of itertools module. permutations combinations combinations_with_replacement zip_longest count...
by shantanuo (9/8/21, 7:50 AM)
wikipedia tools 1) Collection of
useful utilities https://wikipediatools.appspot.com/ 2) all history of a user https://xtools.wmflabs.org/ec/mr.wikisource.org/Shantanuo...
by shantanuo (8/20/21, 6:36 AM)
Duration/Term of Copyright In the
case of original literary, dramatic, musical and artistic works, the...
by shantanuo (8/20/21, 6:26 AM)
Preserve seeds 6 types of
seeds to be preserved for next season. 1) laal Mula...
by shantanuo (5/7/21, 5:46 AM)
Why do I care for
Linux? I don't care that much for other software. Linux...
by shantanuo (12/17/20, 9:27 AM)
important leafy vegetables 1. Amaranthus
caudatus: rajgira. 2. Amaranthus tricolor: Math.  (Chhoti Chulai- Hindi, Cheera-...
by shantanuo (4/12/20, 2:01 PM)
program creek program creek
seems to be a good site for handy references of python...
by shantanuo (4/11/20, 11:24 AM)

RSS feed

Made with Antville
Helma Object Publisher