MySQL ErrLog Rotate

I like to check on a daily basis if my MySQL slaves servers are synced with their masters.

For this task, I use the well known “pt-table-checksum” from Percona.

This tool has a drawback on the master, it create a lot of warning entries in the MySQL error log file. “…unsafe statement…”.

There is no way to suppress this warning from the log.

As you can see here “Don’t worry, nothing you can do”

Really ? Cool !

.. several weeks later…

Ugh.. my errlog grew up to 1,5GB !!!

For RedHat installation, MySQL is bundled with a logrotate configuration file (MySQL RefMan / LogRotate github repo), easy.

Nothing for Windows so, I have made another powershell script 🙂

Get it on bitbucket !

How To :
– Download and copy the files in a folder.
– Execute encrypt.ps1 to encrypt MySQL password (get the encrypted password in securepassword.txt)
– Edit settings.xml, put the MySQL connection information : host / port / user / encrypted password. You can also tell to the script how many log you want to keep with “KeepNbLog”

Then make a task in Windows task scheduler which execute the script at the frequency you want. (1 by week for me)

You can execute the script, if something goes wrong, you will get a log in the sub-folder “log”

That’s it.

Let’s fork it…

It’s seems like Undrop for Innodb official repo on githut is now private.

Bad news for the open world, i don’t know if the official repo will be accessible in the future. I don’t know if my 70 lines of script is a part of the problem and if it’s interfer with TwinDB’s business.

I forked the last version of undrop for innodb on bitbucket and i bundled my script with it. You can find the repo here : https://bitbucket.org/Marc-T/undrop-for-innodb

I’ve no project of maintaining undrop-for-innodb core code but the attached script may evolve in the future. Maybe it will be broken in the future. You can fork it / create pull request if you want.

If you have a broken database you could try my little script or try TwinDB’s professional services.

One last word, you should really take care of your backups & binlogs.

🙁

Mysql Undrop for Innodb Database recovery script v2

Hi,
This is an update, now the script supports the following option : “innodb_file_per_table=0” !

A big “thank you” to Nick for his help 😉

To get the update, just download the file again.

wget http://mysql.on.windows.free.fr/wp-download/RecovDB.sh

don’t forget to grant the right to execute the script

chmod +x RecovDB.sh

then, launch the script, command is the same !

./RecovDB dataDirPath dbname

Read my previous post to get more info

EOT

Mysql Undrop for Innodb Database recovery script

Now you have installed MySQL Toolbox, you probably want to use undrop for innodb.

There is a link on the desktop to TwinDB’s website, this help a lot to understand the concept and how to recover a corrupted table.

But when you want to recover a full database with let’s say 100+ table it could be a real pain because there is a lot of command to recover table structure and data.

So i made a little shell script (your database must be innodb_file_per_table=1)

So let’s start, first, download the script :

wget http://mysql.on.windows.free.fr/wp-download/RecovDB.sh

then grant the right to execute the scripts

chmod +x RecovDB.sh

then, launch the script !

./RecovDB dataDirPath dbname

  1. replace dataDirPath by the MySQL data directory path
  2. replace dbname by the name of the database you want to recover

Then you will have a subfolder “dump” created, you will find some files :

  • Load Data.SQL > script to reinject data
  • [tableX].sql > Table create statement (1 file per table)
  • [tableX].data > Table data (1 file per table)

Note that you can use this script anywhere , you only need undrop for innodb and a mysql service listening on port 3306.  With MySQL Toolbox, all you need is already there !

Hope this helps 🙂

MySQL Toolbox v0.4

Hi,

Changelog :
+ Undrop for innodb has moved to github so i moved to the new source in this v0.4
+ Now i use “git” to install / update MySQL Sandbox

If you have not previously installed MySQL Toolbox, just follow the tutorial, http://mysql.on.windows.free.fr/index.php/mysql-toolbox-v0-1/

If you have already installed MySQL Toolbox v0.3:

wget http://mysql.on.windows.free.fr/wp-download/MTB_Upgrade_03_to_04.sh

then grant the right to execute the scripts

chmod +x MTB_Upgrade_03_to_04.sh

then, launch the scripts !

./MTB_Upgrade_03_to_04.sh

done.

MySQL Toolbox v0.3

Hi,

I’ve added MySQL Sandbox to Mysql Toolbox https://github.com/datacharmer/mysql-sandbox

+ several more things :

  • zip
  • unzip
  • file-roller
  • sys_parser, part of undrop for innodb is now compiled
  • Mysql server is now installed for mysqlfrm to help recovery

If you have not previously installed MySQL Toolbox, just follow the tutorial, http://mysql.on.windows.free.fr/index.php/mysql-toolbox-v0-1/

If you have already installed MySQL Toolbox :

wget http://mysql.on.windows.free.fr/wp-download/MTB_Upgrade_01_to_02.sh
wget http://mysql.on.windows.free.fr/wp-download/MTB_Upgrade_02_to_03.sh

then grant the right to execute the scripts

chmod +x MTB_Upgrade_01_to_02.sh
chmod +x MTB_Upgrade_01_to_03.sh

then, launch the scripts !

./MTB_Upgrade_01_to_02.sh
./MTB_Upgrade_02_to_03.sh

done.

MySQL Toolbox v0.1

Sometimes, you can’t avoid it.. uh.. ?! Linux of course !

Some times ago, i needed to recover some broken tables on one of our databases.

When i saw that “innodb_force_recovery” did not help, i remembered a tool : Undrop For Innodb from TwinDB https://twindb.com/undrop-tool-for-innodb/

You know the drill : “Unfortunatly, this is a Linux Only Tool”

I decided to make a VM on VirtualBox with Centos & Undrop for InnoDB, and tried to recover the tables… At the end of the journey, table was recovered and they lived happily ever after and had many children !

So today i want to share this with a little tutorial, i’ve called this VM : “MySQL Toolbox” and i made 2 bash scripts to initialize and maintain it.

MySQL Toolbox contains the following MySQL tools :

  • Undrop for Innodb
  • Percona Toolkit
  • MySQL Workbench
  • MySQL Utilities
  • MySQL Clients
  • Several GUI productivity tools : Firefox, Text Editor, File browser…

How to setup ?

First Download Centos 7 Minimal
Then download & install VirtualBox

Then create a New VM in VirtualBox ; Name it, size it…

MTB1

MTB2

MTB3

MTB4

MTB5

MTB6

Then Start the VM

MTB7

Choose the iso previously downloaded

MTB8

MTB9

Choose language

MTB10

MTB11

Chose installation destination

 

 

MTB12

Then “Network & Hostname” > Activate network by default

MTB13

Then “Begin Installation”  ; During installation, set root password

MTB14

MTB15

At the end of the installation, reboot

Congratulation, you have now a clean install of centos 7 ! Then login with root account

MTB16

then type the following commands to make http request

yum -y install wget

Then, download the script

wget http://mysql.on.windows.free.fr/wp-download/MTB.sh

Now grant the right to execute the script

chmod +x MTB.sh

Launch the script !

./MTB.SH

After some time, the VM should reboot !

MTB18

Log in with root user and your password

MTB19

MTB20

MTB21

Then you have some documentation shortcuts on the desktop. The tools are available from Centos start menu and from Terminal for command-lines tools.

How To Update ?

All of this tools are updated regulary, so to update :

wget http://mysql.on.windows.free.fr/wp-download/MTB_Update.sh
chmod +x MTB_Update.sh
./MTB_Update.sh

If i find other great tools, i will update the script to include them. If you find & need other tools, let me know i will include them.

Project is on Bitbucket : https://bitbucket.org/Marc-T/mysql-toolbox

That’s all folks !