Showing entries 31 to 40 of 106
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Scripting (reset)
Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on …
[Read more]
Top 10 MySQL Tips and Mistakes for PHP Developers (Webinar)

Tuesday, September 25, 2012

MySQL and PHP are two key components in the open-source LAMP stack, and are widely used by web developers. One day you launched a website, and it gradually gained traction and now serves hundreds times more traffic than day one. As a PHP developer, have you ever looked back and adjusted the MySQL configurations accordingly to meet the most current website load? Have you ever wondered if there are secret tips you might have missed or common mistakes that you could have avoided but never thought of? If you answered yes to either question, this presentation is for you!

In this live webinar, PHP & MySQL experts Ulf Wendel and Johannes Schlüter will share the top 10 tips and mistakes for PHP developers. After a quick overview of lesser known but cool client and server features and plugins, and a recap of the benefits of the relational database model, we quickly jump to the hot irons, including:

[Read more]
MySQL Workbench Utilities – Clone MySQL users with mysqluserclone

This post is one in a series that I will be doing on MySQL Workbench Utilities – Administer MySQL with Python Scripts.

MySQL Utilities are a part MySQL Workbench. The utilities are written in Python, available under the GPLv2 license, and are extendable using the supplied library. They are designed to work with Python 2.x greater than 2.6. If you don’t have Workbench, you may download the MySQL Utility scripts from launchpad.net. You will also need to …

[Read more]
Scripting continued

Since I have been discussing scripting lately I thought I would continue with another topic I touched on briefly - backups.

I have written and modified the following script over the last few years. I have used it (and continue to use it) with multiple clients. It uses Percona's Xtrabackup to take the backup (although it can be easily modified to use mysqldump instead).

First the script

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

#!/bin/bash
SAVEIFS=$IFS
IFS=$""
day_of_week=`date +%a`
backup_dir=/mysql-backup/
logfile=/root/backup_log.txt
report=/tmp/report.txt
servername=slave1
email=bmurphy@paragon-cs.com
password=`cat /root/.ssh/.backup_password`

# run backup
echo ' ' > $report
echo 'The backup is now beginning:' >> $report
echo ' ' …

[Read more]
Interviewing tip..

I've been involved in a number of interviews over the last few weeks as a client has been loking for a MySQL DBA. When you are looking for position as a DBA in a large scale environement there are some very important things you have to know.

You absolutely must know a scripting language. In a smaller environment this often isn't necessary. You will live and die by this in a large environment. I asked every applicant one specific question..if you had to change a mysql server variable on a pool of 100 mysql servers how would you do this? It's easy when it's one,two or even a dozen servers. just log in, change the my.cnf and change it "on the fly" if you can. Restart mysql if you can't.

You going to do that to 100 servers? It will take all day and be prone to failures. Scripting is the key here. Even just bash shell scripting can be very powerful. In another post I will cover a simple bash script to loop through a …

[Read more]
How To Sort Columns of MySQL Data on a Web Page With Perl

A friend of mine was building a web site so his customers could view his current inventory of transportation containers, and he asked me for help on how to sort the rows of information that appeared on his site. So, in this post, I will give you a quick example on how to sort columns on a web page.

First, let’s start with an inventory database that we will build in MySQL:

CREATE TABLE `inventory` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`item_name` varchar(30) NOT NULL,
`item_SKU` varchar(20) NOT NULL,
`item_cost` decimal(4,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1

Next, here are some SQL statements to populate the MySQL database with some sample data.

use …
[Read more]
Automatically Download MySQL Enterprise Monitor Graphs as PNG Files Using Perl

I was giving a presentation of the MySQL’s Enterprise Monitor* application to a client recently. I was demonstrating the “graphs” section of MEM, where you can monitor MySQL sessions, connections, replication latency and more with 60+ graphs. Usually, you view the graphs from within the MEM Enterprise Dashboard (via a web browser). But the client asked if there was a way to automatically download graphs. I wasn’t sure why he wanted to download the graphs (I didn’t ask), but I knew it wasn’t possible by using MEM alone. However, in the past I have written Perl scripts to automatically download files from web sites, so I thought I would see if it was possible with MEM.

 
*The MySQL Enterprise Monitor (MEM) continuously monitors your MySQL servers and alerts you to potential …
[Read more]
Using MySQL and Perl to Create, Edit and Delete Information Via a Web Page

A friend of mine was asking me for my recommendation of a good desktop database program to use to keep track of his inventory of cargo containers. I suggested to him that he should use MySQL and write a web page interface to do everything that he needed. He then reminded me that he is a lawyer by trade, and that he doesn’t have any computer programming experience. Then I remembered that he has almost zero computer skills. And his Texas Hold-Em skills are even worse, but I don’t mind taking his money. In his case, he should just use a notepad and a pencil. (As for the question – what is a lawyer doing with cargo containers? – that is a different story.)

If he did decide to broaden his horizons a bit, he could easily write his own software web application for creating and storing almost any kind of data. In this post, I will show you how to create a MySQL database and then the web pages needed to create new addresses, edit the same …

[Read more]
Checking on the Progress of Large DML Commands in MySQL Using Perl – Part Two

Part Two of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part Two: Monitoring the activity via Perl and SHOW ENGINE INNODB STATUS. (part of the InnoDB Monitors)

In part one, I showed you how to use a Perl script to insert a million rows of dummy data into a table. I needed a large database in order to test a Perl script that I would use to monitor the activity when running a large DML statement.

The original reason for creating both of these scripts was to find a quick way to see if a large DML statement was actually being executed. A customer was performing some modifications on …

[Read more]
Checking on the Progress of Large DML Commands in MySQL Using Perl – Part One

Part One of Two: Checking on database activity when running a large DML (Data Manipulation Language) statement – such as INSERT, DELETE, UPDATE or SELECT.

Part One: Inserting a million rows into a database.

A friend of mine had asked a question – “Is there any way you can track how far you have advanced in a slow-moving ALTER or OPTIMIZE statement?”. A customer was performing some modifications on a database with tens of millions of rows, and they wanted to be able to see if the command was making any progress.

Since the customer was using the InnoDB storage engine, I thought of a way that you could check on the progress – but only given the fact that nothing else (major) was happening in the database (more on this reason later).

With InnoDB, you can …

[Read more]
Showing entries 31 to 40 of 106
« 10 Newer Entries | 10 Older Entries »