Mysql – pt-table-checksum toolkit for few databases from file

MySQLperconapt-table-checksum

I've got a question. I'm doing a simple script right now and it isn't working like I want.

    #!/bin/bash

    cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)" > /tmp/databases

    while read line; do
    pt-table-checksum [options] --databases $line --quiet
    done < /tmp/databases

    rm /tmp/databases

As you see I put names of databases to file /tmp/databases. Then I want to use pt-table-checksum for every database in that file line by line. This script works but only for first database name in file. Then it stops.

Any ideas?

Best Answer

I would use a 'for' loop for this, which I have found easier to use for things like this. Often, either line break, spaces, or combinations of those can cause issues with your method. Also, remove the --quiet from your command line to see the errors that pt-table-checksum may be reporting.

#!/bin/bash

for db in `cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)"`
do
    pt-table-checksum [options] --databases $db --quiet
done

(Hope this reads ok, have answered on my phone!)

ANSWER #1

This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script

set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
echo %SQLSTMT%
mysql %MYSQL_CONN% -ANe"%SQLSTMT%" > C:\windows\InnoDBCheck.sql

After you run this script, add this to my.ini

[mysqld]
init-file=C:\windows\InnoDBCheck.sql

Then, restart mysql

net stop mysql
met start mysql

during the startup, just after crash recovery, the

Forgive me for such a crude solution since I have very little dealing with MySQL for Windows (that's not sarcasm, I am serious).

ANSWER #2

Here is something more serious

I just checked my MySQL 5.6.10 no-install ZIP file. It has innochecksum.

C:\>dir \MySQL_5.6.10\bin\i*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.6.10\bin

01/22/2013  07:05 PM         4,065,792 innochecksum.exe
01/22/2013  07:05 PM         3,706,880 innochecksum.pdb
               2 File(s)      7,772,672 bytes
               0 Dir(s)  160,596,770,816 bytes free

C:\>

Download that ZIP and try running that .

You could then collect all the .ibd files with something like this:

cd "C:\Program Files\MySQL\MySQL 5.5\data"
dir *.ibd /s/b > C:\ibdfiles.bat

You can then edit C:\ibdfiles.bat and prepend innochecksum against every filename.

Sorry this is not a full answer, but at least you can a hold of innochecksum.exe

Mysql – Create Table If Not Exists Hanging

I find this troubling for two reasons

REASON #1

This could be a bug that never got squashed out of MySQL 5.5.17

Nov 08, 2011 : http://bugs.mysql.com/bug.php?id=63144 (Status is Needs Triage on MySQL 5.5.17)
Mar 21, 2011 : http://bugs.mysql.com/bug.php?id=60563 (MySQL 5.5.10)
Jun 23, 2006 : http://bugs.mysql.com/bug.php?id=20662

REASON #2

You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!

Possible Workaround

Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:

SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.

Possible Solution

Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version

Give it a Try !!!

UPDATE 2013-07-11 15:16 EDT

Here is a rewrite of your bash code to check if the table already exists

    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql