This week, ten years ago, I was in London for MySQL AB‘s first “train the trainer” course, also meeting (for the first time) my first boss at MySQL Kaj. I’d been hired mid August as employee#25, also doing training but actually primarily as tech-writer for the MySQL documentation (taking over from Jeremy Cole, and essentially I was the documentation team for quite some time . So from this you can deduce that yes, I was hired without meeting either Kaj or anyone in-person! I don’t think we even had a phone call, only email. Oh the days

The training week itself was of course disrupted quite a bit by the events in New York. We had Jeremy who had come on a UA flight from the US, and others from all over the place… it also taught …

In an earlier post Connecting to MySQL with Python, we only returned a single result line from a “SELECT VERSION()” query. We will now take a look at returning multiple rows of data from a MySQL database using Python.

You will need to have Python (including the MySQLdb and sys modules) and MySQL installed. We will also use the same database table (address) from a previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL, …

If you are interested or concerned about MySQL Security you may want to check out OurSQL episode 55 where I discuss transparent encryption with Sheeri Cabral and Sarah Novotny and how it can help to secure mysql data and other linux apps (apache, nosqls, drizzle) plus protection for config files, code ….. and more –

Sheeri and Sarah dive deep with many of the technical questions you might have yourself.

This should be valuable for you whether your concerns are directly mysql related or more general as related to encrypting data or further securing your linux ecosystems.

OurSQL Episode 55: MySQL Data Encryption

http://bit.ly/oursql055

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor.

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a centralized Service Manager and lightweight Service Agent this is installed on each monitored MySQL …

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this spreadsheet had 45 columns of varying lengths. Ouch.

Please keep in mind that this was a quick hack. …

WikiLeaks Cable Offers New Insights into Oracle-Sun Deal (PC World)

Nothing too new or shocking in there, but the cable does offer some interesting insights.

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider …

Full-text searching with InnoDB tables is now available for your evaluation at Labs.MySQL.Com. Those used to the full-text search from MyIsam will find the basics are familiar. The MATCH() ... AGAINST syntax is there and has been augmented with some new features. How about better performance? You can use innodb_ft_sort_pll_degree to set the number of parallel threads used during tokenization and sorting (see this Transaction on InnoDB for details and timings). So if you wanted to move completely over to InnoDB but were holding back because of full-text searches, you need to check out MySQL 5.6!

…

In our last post about connecting to MySQL with Perl, we simply connected to the database and retrieved the MySQL version information. We have also looked at inserting data into MySQL via Perl.

In this post, we will connect to a MySQL database, retrieve multiple rows of data, and print a table in HTML with the data.

As in previous posts, we will continue to use an address table. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
…

So far, we have looked at connecting to a MySQL database via Perl and Python. Next, we will look at connecting via PHP. I am assuming that you have already installed PHP or have the ability to run PHP scripts on your server, and that you have MySQL installed as well.

This example will use the same address table from the previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,
`address_state` varchar(20) NOT NULL,
…