I think, therefore I am. I am, therefore I sail

Category: Programming Page 3 of 20

MySQL: Find the size of a database

MySQL has the ability to show you size of the information contained in your database. You can find this information per database or per table in a database pretty easily.

Size of databases

To list the size of all the databases on the server run the following:

SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 
"Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Size of tables in database

To list the size of all the tables in an individual database run:

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "{{NAME OF YOUR DATABASE}}";

MySQL who: See who is connected to your MySQL server with this script

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.

[bash]
#!/usr/bin/perl

use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = ‘localhost’;
my $db = ‘information_schema’;
my $user = ”;
my $pass = ”;

# Get commandline options
GetOptions (
"host=s" => \$host,
"db=s" => \$db,
"user=s" => \$user,
"pass=s" => \$pass,
) or die ( "Valid options\n–host\n–user\n–password\n–db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {‘PrintError’=>0} )
or die "** Connection error!\nTry different options:\n–host\n–user\n–password\n–db database\n\n $DBI:: +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {
$user{$row[1]}++;
}

# Display User details
print "MySQL – Logged in users\n";
print "=" x 15, "\n";

for my $x (keys %user) {
print "$x – $user{$x} connections\n";
}

print "\n";
[/bash]

Autocomplete git branches and commands in bash

The tab completion for commands in bash is awesome, but it does not work for bash out of the box. Lucky for us it is relatively easy to get bash completion working.

You will need two things, a copy of the completion script and an update to your bash profile.

The completion script is available at: https://github.com/git/git/blob/master/contrib/completion/git-completion.bash

Then in your .bashrc (or .bash_profile, or .profile, whatever you are setup for) add the following:

[bash]
if [ -f ~/.bin/git-completion.bash ]; then
. ~/.bin/git-completion.bash
fi

[/bash]

Be sure the path to the git-completion.bash file is correct for your system. On mine I keep extra commands in ~/.bin.

Start using your new completion with:

[bash]
source ~/.bashrc
[/bash]

Now simply type ‘git’ and hit tab to see all the commands available.

When switching branches type ‘git checkout’ and hit tab to see a list of the branches.

Getting social media shares with php

Quick snippets to help me (and maybe you!) remember how to quickly and easily get share counts from social networks.

What other networks would you like to see?

Facebook

[php]
function facebook_share_count( $post_id ) {
$api = "https://graph.facebook.com/";

$url = $api . urlencode( get_permalink( $post_id ) );

$response = wp_remote_get( $url );
if( is_wp_error( $response ) ) {
return 0;
}
if( ‘200’ != wp_remote_retrieve_response_code( $response ) ) {
// Bad url
return 0;
}
$body = wp_remote_retrieve_body( $response );
$body = json_decode( $body );
if( isset( $body->shares ) ) {
// Shares will not be set if there are none!
return $body->shares;
}
return 0;
}
[/php]

Twitter

NOTE: ***Twitter will be shutting down this API*** No replacement has been announced.

[php]
function twitter_share_count( $post_id ) {
// Check for transient
if ( ! ( $count = get_transient( ‘twitter_count’ . $post_id ) ) ) {
// Do API call
$response = wp_remote_retrieve_body( wp_remote_get( ‘https://cdn.api.twitter.com/1/urls/count.json?url=’ . urlencode( get_permalink( $post_id ) ) ) );
// If error in API call, stop and don’t store transient
if ( is_wp_error( $response ) )
return ‘error’;
// Decode JSON
$json = json_decode( $response );
// Set total count
$count = absint( $json->count );
// Set transient to expire every 30 minutes
set_transient( ‘twitter_count’ . $post_id, absint( $count ), 30 * MINUTE_IN_SECONDS );
}
return absint( $count );
}
[/php]

LinkedIn

[php]
function linkedin_share_count( $post_id ) {
$api = ‘http://www.linkedin.com/countserv/count/share?format=json&url=’;
$api .= urlencode( get_permalink( $post_id ) );
if ( ! ( $count = get_transient( ‘linkedin_count’ . $post_id ) ) ) {
$response = wp_remote_get( $api );
if( is_wp_error( $response ) ) {
// eat it
return 0;
}
// Should verify the correct json is returned
$json = json_decode( wp_remote_retrieve_body( $response ) );
$count = (int) $json->count;
set_transient( ‘linkedin_count’ . $post_id, absint( $count ), 30 * MINUTE_IN_SECONDS );
}
return $count;
}
[/php]

Script to convert MySQL collation from utf8mb4 to utf8

I ran into a strange situation with WordPress where the MySQL server did not support utf8mb4. I needed to quickly convert the tables back to utf8 to get the site running again.

Though this script does work I highly recommend if you are running WordPress that you upgrade your MySQL server to support utf8mb4 for security. WordPress made this change to fix a security vulnerability introduced by The Trojan Emoji.

[bash]
DB="your_database_name"
USER="your_db_user"
PASS="your_db_pass"
(
echo ‘ALTER DATABASE `’"$DB"’` CHARACTER SET utf8 COLLATE utf8_general_ci;’
mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" –batch –skip-column-names \
| xargs -I{} echo ‘ALTER TABLE `'{}’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’
) \
| mysql -p$PASS -u $USER "$DB"
[/bash]

Page 3 of 20

Powered by WordPress & Beards