Friday, May 10, 2013

Regex to replace charset in MySQL dump.

converting all our databases to charset utf8mb4 and collation utf8_general_ci.
Exported all structure using mysqldump from my last post.
Now for ease of completing over 5000 table I wanted to use regex, this is what I got...

/////////////////// USING Komodo Edit!! //////////////////////////////////////////


---- COLLATE STATEMENTS ----
FIND: COLLATE([ =]).*?([ ;])
REPLACE: COLLATE\1utf8_general_ci\2

---- COLLATE STATEMENTS 2 ----
FIND: collation_connection      = [^ ]*
REPLACE: collation_connection      = utf8_general_ci

---- CHARSET STATEMENTS ----
FIND: CHARSET([ =]).*?([ ;])
REPLACE: CHARSET\1utf8mb4\2
---- CHARSET STATEMENTS 2 ----
FIND: character_set_client = [^ ]*
REPLACE: character_set_client = utf8mb4
---- CHARSET STATEMENTS 3 ----
FIND: CHARACTER SET [^ ]*
REPLACE: CHARACTER SET utf8mb4
---- CHARSET STATEMENTS 3 ----
FIND: (character_set_[^ ]*[ ]*)= [a-z0-9_]+
REPLACE: \1     = utf8mb4

---- DEFINER STATEMENTS ----
FIND: DEFINER=`.*`@`.*`
REPLACE: DEFINER=`myuser`@`localhost`

---- ENGINE STATEMENTS ----
FIND: ENGINE=[^ ]*
REPLACE: ENGINE=InnoDB

---- ENGINE STATEMENTS ----
FIND: ENGINE=[^ ]*
REPLACE: ENGINE=InnoDB

other things I changed


----- standardize column lengths -----
FIND: (`columnprefix_.*`) varchar\(.*\)
REPLACE: \1 varchar(36)

----- standardize column lengths 2 -----
FIND: (`columname`) varchar\(.*\)
REPLACE: \1 varchar(36)

 

Thursday, May 9, 2013

MySQL export all databases structure with triggers

So, I had many issues exporting a dev db because of missing permissions and more.
I finally worked out a way that would finish on our development server...

    mysqldump -pmy1pass --no-data --allow-keywords --compact --triggers --single-transaction --all-databases | gzip -9 > backup/mysql/all_structure.zip

So this skips locking due to the --single-transaction
Allows keywords as column names with --allow-keywords
Compact export with --compact

Woot.

Monday, May 6, 2013

Going food geek now..


SPARKPEOPLE
 
I was curious about what to eat AFTER a workout.
I found this on SPARKPEOPLE.

So what does the ideal meal or snack look like?
  • Calories. Ideally, try to eat enough calories to equal 50% of the calories you burned during your workout. So if you burn about 600 calories during your workout, try to eat 300 calories afterward.
    Don’t worry about undoing the calorie-burning benefits of your workout–that’s not how weight loss works. As long as you're eating within your recommended calorie range (whether for weight loss or maintenance), you'll be on your way to reaching your goals.
     
  • Carbohydrates. Roughly 60% of the calories you eat at this time should come from carbohydrates. Contrary to popular belief, your body needs more carbohydrates than protein after a workout, to replace the muscle fuel (glycogen) you used up and to prepare for your next exercise session. Moderate exercisers need about 30-40 grams of carbohydrates after an hour of exercise, but high-intensity exercisers need more—around 50-60 grams for each hour they exercised.
    If you have some favorite high-carb foods that are lacking in the whole grains and fiber that are often recommended as part of a healthy diet, this is a good time to have them! Your body can digest refined carbohydrates faster during your "refueling window," but if you’re a whole foods foodie, don’t force yourself to eat processed foods.
     
  • Protein. While carbs are essential, it’s also important to include some high-quality protein in your post-workout meal or snack. This protein will stop your body from breaking down muscle tissue for energy and initiate the process of rebuilding and repairing your muscles. About 25% of the calories you eat after a workout should come from protein—that's about 10-15 grams for most people.
     
  • Fat. Fat doesn't play a big role in post-workout recovery, and eating too much fat after a workout won't help your weight control or fitness endeavors. Only 15% (or less) of your post-workout calories should come from fat—that's less than 10 grams.

The ideal time to eat after a workout is within 30 minutes to two hours, when your body is ready and waiting to top off its fuel tanks to prepare for your next workout.


Here are some sample food combinations for your post exercise meal:
  • Bread, a bagel, or an English muffin with cheese or peanut butter
  • Dried fruit and nuts
  • Cottage cheese with fruit
  • Fruit juice with cheese
  • Yogurt with fruit
  • Veggie omelet with toast or roll
  • Chocolate milk
  • Cereal with milk
  • Eggs and toast
  • Turkey, ham, chicken, or roast beef sandwich
  • Vegetable stir-fry with chicken, shrimp, edamame or tofu
  • Crackers with low fat cheese
  • Rice or popcorn cakes with nut butter
  • Smoothie (with milk, yogurt, or added protein powder)
  • A protein or energy bar
  • A protein or energy shake
  • Pancakes and eggs
  • Any regular meal that contains lean protein, starch, and vegetables

MySQL Error when importing LARGE files.

<quote> 
   Hi,

   I got the MySQL Error Nr.2006-MySQL server has gone away when trying 
   to restore a 580MB file with mySQL administrator 1.1.9. However when
    I set the max_allowed_packet=100M in the my.ini file everything works
    fine. I don't know if this helps anyone.
</quote> 

Yes Frank, years later it still helps.

I was getting a  
ERROR 2006 (HY000) at line #: MySQL server has gone away
message from MySQL and nothing in the error logs!
Very frustrating to not be able to SEE that it was hitting a limit.
 

Simply increase your max_allowed_packet while doing the import.

This will increase the limit temporarily (until mysql restarted).
<SQL>
   set global max_allowed_packet = 100*1024*1024;
   show global variables like "max_allowed_packet";
</SQL>
Note that the 100 in the SQL above is equivalent to 100M in bytes.
If you want to update your ini file to make this permanent,
 then just add this to your my.cnf
 
   max_allowed_packet = 100M 

Happy Coding!

Friday, May 3, 2013

Argument list too long error for rm (linux remove command)

...Via StackOverflow...
    thanks to Dennis and j0k

The reason this occurs is because bash actually expands the asterisk to every matching file, producing a very long command line.

Try this:
    find . -name "*.pdf" -print0 | xargs -0 rm

Warning: this is a recursive search and will find (and delete) files in subdirectories as well. Tack on -f to the rm command only if you sure you don't want confirmation.

If you're on Linux, you can do the following to make the command non-recursive:
find . -name "*.pdf" -maxdepth 1 -print0 | xargs -0 rm

Export MySQL databases to file..

Needed to export MANY databases, but only based on wildcard% search.
This also could be used to backup databases!
Here is the bash script I came up with...
Update: added another script to be table specific.

------------------------------------------------------------------------

backup_db.sh

------------------------------------------------------------------------

#!/bin/bash

# http://codeinthehole.com/writing/mysqldump-with-wildcard-table-matching/


if [ $# -lt 1 ]
then
    echo "Usage: DB (auto add the wildcard at the end)"
    exit 1
fi;

database=$1
echo "# $database DUMP" > $database.sql

echo "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES where TABLE_SCHEMA LIKE '$database%'" | mysql -pmypass --database=information_schema | while read -r line
do
    echo "$line";
    mysqldump --databases $line  -pmypass >> $database.sql
done;

gzip $database.sql

------------------------------------------------------------------------

backup_tables.sh

------------------------------------------------------------------------


#!/bin/bash

# http://codeinthehole.com/writing/mysqldump-with-wildcard-table-matching/


if [ $# -lt 1 ]
then
    echo "Usage: ./backup_tables.sh {DB} {TABLE} (auto add the wildcard at the end)"
    exit 1
fi;

database=$1
table=$2
echo "# $database $table DUMP" > $database.$table.sql

echo "SELECT \`TABLE_SCHEMA\` as \`db\`, \`TABLE_NAME\` as \`table\` FROM information_schema.TABLES WHERE \`TABLE_SCHEMA\` LIKE '$database%' AND \`TABLE_NAME\` LIKE '$table%'" | mysql -pmypass --database=information_schema | while read db table
do
    echo "$line";
    mysqldump -pmypass  --hex-blob --triggers --databases $db --tables $table >> $database.sql
done;

gzip $database-$table.sql
-----------------------------------------------------------------------------------

------ UNZIPPING AND IMPORTING TO SERVER ------


gunzip -c {filename}.sql.gz | mysql -u root -pmypass --force


MySQL Query Logging

Needed to log some queries today in MySQL.
Here is the long and the short of it....(more info)


set global log_queries_not_using_indexes = 'ON';
SET GLOBAL general_log = 'ON';
SET GLOBAL log_slow_queries = 'ON';
set global long_query_time= 1;

Now check your settings...

Thursday, May 2, 2013

Linux 7zip

Linux 7zip

-- install 7zip --

    yum install --enablerepo=remi p7zip

-- unzip 7zip files --

    7za x filename.7z

Wednesday, May 1, 2013

MySQL privileges export via CLI

Here is an awesome tidbit for getting permissions from your db to transfer to another...

# how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server
# mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret
# mygrants > grants.sql
mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}