Drop all mysql tables without permission to delete and create new database

In mysql, the simplest way to flush all tables is to delete the db and create a new one. However, one might not have permission to do that. So we need a script to list all tables and delete it.

#!/bin/bash

USER=xxx
PASS=xxx
HOST=localhost
DB=xxx

mysqldump -u$USER -p$PASS -h$HOST --no-data --add-drop-table $DB | grep ^DROP | mysql -v -u$USER -p$PASS -h$HOST $DB

Dropping or updating uniq, foreign key in mysql tables

If you need to drop an index or uniq column from mysql, in the command line,

first find out the FK or UNIQ key,

“show create table table_name”;

now, you will see the sql for creating a table, copy and paste the key, eg

“drop index FK_40123345 on table_name;”

To double check if this is correct, run

“describe table_name”

If when updating tables, you get foreign key constraint error, you need to disable foreign key check first, then update the table. ie, in command line,

“set global foreign_key_checks=0”

after you run the sql updates, remember to turn the check back on

“set global foreign_key_checks=1”

Allow DB synchronisation in PHPMyAdmin

phpmyadmin has a useful feature to allow the ability to synchronise dbs from different servers. This is useful if you want to sync your production db to you dev db for example.

In the phpmyadmin config.inc.php in your dev machine, add the following

$cfg['AllowArbitraryServer'] = 1;

then go to the phpmyadmin dashboard and click on the synchronize option. the source db is your production db.

After clicking on the synchronize button and waiting for a while, you will be presented with a comparison table consisting of the differences between the 2 dbs. check the “Would you like to delete all the previous rows from target tables?” As we want similar tables. Then click on “synchronize database” again.

Finding a term in mysql database

one idea is to run a number of loops and query the db.

this mysql_search.php script tells you which table in a db has the match for the searched term. run the script in commandline, ie

<?php

// Config
$server = "localhost";
$username = "root";
$passwd = "";
$db = "moodle";
$searchTerm = "water_pics";
$log = "mysql_search.log";
// --- END OF CONFIGURATION -- //
mysql_connect($server, $username, $passwd);
mysql_select_db("$db");
$r = mysql_query("show tables");

while ($table = mysql_fetch_array($r, MYSQL_ASSOC)) {
  $r1 = mysql_query("show columns from ".$table['Tables_in_'.$db]);
  while ($col = mysql_fetch_array($r1, MYSQL_ASSOC)) {
    $r2 = mysql_query("Select * from ".$table['Tables_in_'.$db]." where ". $col['Field']." like '%$searchTerm%'");
    while ($match = mysql_fetch_array($r2)) {
        $str = "table(".$table['Tables_in_'.$db].") - First_column_id ($match[0]) - column_matched (". $col['Field'].")\n";
        echo $str;
        $fp = fopen($log, 'a+');
        fwrite($fp, $str);
        fclose($fp);
    }
  }
}

Quick Mysql Tips

mysql dump limited data

mysqldump -uroot -pxx dbname dbtable --where="true order by transactionid desc limit 1"

Logging slow queries

To enable the slow query log, start mysqld with the –log-slow-queries[=file_name] option. Use the mysqldumpslow command to summarize the queries that appear in the log. For example:

mysqldumpslow /path/to/your/mysql-slow-queries.log -t 10

shows you top 10 performance killers.

Lost Password

  • shutdown mysql, then
 mysqld –skip-grant-tables –u root
 mysql -u root
 mysql> use mysql;
 mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE User = 'root';
 mysql> FLUSH PRIVILEGES;
  • if get error on ip cannot access db
GRANT ALL ON *.* TO 'someuser'@'somehost';
  • if get error on password, check if the host is correct.

Add new User

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Remove foreign key constraint

Mass Update Substring

update table_name set column=replace(column, 'old_substring', 'new_substring') where title like '%/abc/123%'

* Sometimes it may be very difficult to use regex in mysql. If that is the case, use php or perl to do the query, extract the relevant variables, then do a manual update query. Abit more time consuming but this method is very reliable.

Exporting to cvs

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

or doing it from command line

mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Check Mysql Config

mysql -uroot -p
show variables;

sort mysql database/tables based on table size

Quick script to sort mysql database/tables based on table size

Mysql can show the table status with the row and size for each table but doesn’t do sorting. We can however store each row in an array and sort the array.

I am too lazy to write a script for it. 2 x for loops does the trick. This script is from adamyoung.net. Just run the script in command line. Dump it into csv and you can do wonders in excel.

<?php
# MySQL Table Size Ranker
# Usage: php tablesize.php > tablesizes.csv
# http://adamyoung.net
# Simple modification from http://azhowto.com

mysql_connect(‘localhost’,’root’,’your_pass’);
$result = mysql_query(‘SHOW DATABASES’);
$databases = array();
while ($row = mysql_fetch_array($result)) {
$databases[] = $row[‘Database’];
}

$totals = array();
foreach ($databases as $database) {
$result = mysql_query(“SHOW TABLE STATUS FROM {$database}”);
if (!$result) continue;
while ($row = mysql_fetch_array($result)) {
$totals[] = array(‘database’ => $database, ‘table’ => $row[‘Name’], ‘records’ => $row[‘Rows’],
‘indexes’ =>  $row[‘Index_length’], ‘data’ => ($row[‘Data_length’] + $row[‘Index_length’]));
}
}
usort($totals, ‘sort_total_desc’);

echo “Database,Table,Records,Indexes,Data\n”;
foreach ($totals as $total) {
echo “{$total[‘database’]},{$total[‘table’]},{$total[‘records’]},{$total[‘indexes’]},{$total[‘data’]}\n”;
}

function sort_total_desc($a, $b) {
if ($a[‘data’] == $b[‘data’]) return 0;
return ($a[‘data’] > $b[‘data’]) ? -1 : 1;
}
?>

Removing Foreign Key Constraints In MYSQL

Some Mysql db is using innoDB which implements foreign key contraints. If you can’t drop or alter a table, check that it doesnt have foreign key contraints. Well, innoDB can be an angel or devil…

In mysql command line, check existence of foreign keys

show create table demographic_type

CREATE TABLE `demographic_type` (
`id` int(10) unsigned NOT NULL auto_increment,
`user_group_id` varchar(255) default NULL,
`venue_channel_id` int(10) unsigned default NULL,
`product_list_id` varchar(255) default NULL,
`promotion_type_id` varchar(20) default NULL,
`required_drivers` varchar(32) default NULL,
`sequence` tinyint(3) unsigned default NULL,
`name` varchar(128) default NULL,
`description` varchar(255) default NULL,
`image` varchar(255) default NULL,
`author` int(10) unsigned default NULL,
`submitted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`ip` varchar(16) default '127.0.0.1',
`publish` enum('active','inactive','pending') default 'pending',
PRIMARY KEY (`id`),
KEY `venue_channel_id` (`venue_channel_id`),
KEY `product_list_id` (`product_list_id`),
CONSTRAINT `0_1013` FOREIGN KEY (`venue_channel_id`) REFERENCES `venue_channel` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 PACK_KEYS=1 COMMENT='List of demographic categories'

ok this table has a contraint. Let’s drop the contraint

alter table demographic_type DROP FOREIGN KEY 0_1013;

Sometimes its too hard to remove foreign key constraints. If the foreign is a key as well, remove the key first.

alter table table_name drop key xxx;
alter table table_name drop foreign key xxx;

or remove foreign key constraint check, then modify or drop the table.

SET FOREIGN_KEY_CHECKS = 0;
flush privileges;

Retrieve Lost Password in MYSQL

Sometime, we forget the root password for mysql. It happened to me once in my company. The previous developer left and didn’t document where to look for passwords. Well, it is possible for the root user of the system to reset it anyway…

First shutdown mysql,

/etc/init.d/mysql stop

then

mysqld –skip-grant-tables –u root
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE User = ‘root’;
mysql> FLUSH PRIVILEGES;

If there is an error about certain ip cannot access the db, grant permission like so:

GRANT ALL ON *.* TO ‘someuser’@’somehost’;

make sure the host name is correct.