Jul
18
2009
1

How to use the ODBC driver to Connect to MySQL database in Fedora 10

A few weeks back I covered connecting ODBC to MS-SQL, but today we are going to do the same thing with MySQL.  The process is very similar.  One might ask, why use ODBC driver in the first place?  The reason is simple, there are many flavors of SQL and the ODBC driver allows one standard to connect them.  For that reason many software developers use ODBC for connecting their applications to SQL.

For my purposes, the Asterisk PBX platform can use ODBC to call ‘dial plan’ functions from the database, but that is another article!  This post assumes you have a working MySQL server and at least one table with data to pull from.

Goal of this Post:

– Install ODBC driver for MySQL
– configure odbc.ini
– configure odbcinst.ini
– verify connectivity with isql

(more…)

Jun
07
2009
--

How to Configure Linux ODBC Connections for MS SQL

Last week at work(VoiceIP Solutions) I did some research for Asterisk PBX integration with Microsoft CRM.  The customer likes open source Asterisk because of the cost savings, but they requires screen pop-ups, and click to dial from their Customer Relationship Management software.  So while my manager worked on the TAPI middleware, I was charged with figuring out how to connect to the MS SQL database.  This article was prompted by a desire to connect an Asterisk PBX to MS SQL, but the tutorial applies to Apache, Postfix, CRM, PHP or any Linux app that needs to do a remote query.  Also, while the focus of this article is aimed at MS SQL the same steps(with a few tweaks) can be used for connecting to Postgre, Sybase, MySQL, etc…

I’m a lot more famalier with MySQL & PostgreSQL, but MS SQL I haven’t touched since I had the silly notion about 10 years ago to become a Windows 2000 MCSE.  Incidently, I never did take the exams, because I was a broke student at the time and I was becoming increasingly interested in Linux and Cisco.

The logical choice is to use the UNIX ODBC driver.  ODBC stands for Open Database Connectivity.  ODBC is a well documented set of API’s that is available on many platforms.  However, their are subtle differences in it’s implentation and the protocols that run at application layer.  In other words ODBC is encapsulated when making calls to a database over a network (in this case, the TDS protocol).

I did some googling and found a number of incomplete tutorials for connecting Linux to MS SQL.  This article is intended to clarify some common configuration errors and will present you with example files.  For my demonstration I’m running Fedora 10 with the latest updates as of this writing.  This article assumes you have a working MS SQL datebase with the proper user permissions in mixed mode.  I put this one in bold because it stumped the MCSE database guy for a while.

Again, I want to point out I’m not a Microsoft DBA and will likely not be able to help you on that side of the configuration.  Also, there are many versions of SQL out there and the syntax to pull data differs slightly from one version to the next.  So you may need to do a little research to make the proper pulls.

The Goals of this Post:

– install ODBC and TDS on Fedora 10

– verify TDS can login into MS SQL server

– configure odbcinst.ini, odbc.ini and freetds.conf configuration files

(more…)

Apr
24
2009
0

How to Change the root Password in MySQL and Backup with mysqldump

I’m not DBA, but most Linux system administrators need to at least know a little about SQL. Like adding users, making simple databases, changing passwords, backups, etc… This article describes backing up a database to a fresh machine. In my case I happen to be backing up my wordpress blog. However these steps can applied to Sugar CRM, Joomla, or any database driven site that uses MySQL. The following tutorial does not completely cover backing up a wordpress blog; it only covers the database portion.  Which applies to many LAMP projects.  Enjoy!

The Goals of this Post:

– to remind myself how to backup databases

LAB: Backup WordPress Blog Database to new machine

I’m changing the default password of nothing to the same password as my root user. Please note that mysql ‘root’ user is not the same as Linux system root. Also, they can be different passwords and probably should.

[root@mattcom1 ~]# mysqladmin -u root password mynewpassword
[root@mattcom1 ~]# mysql -u root -p
Enter password:
[root@mattcom1 ~]#

Create a blank database on the machine we will repopulating.

Welcome to the MySQL monitor. Commands end with ; or \g
Your MySQL connection id is 10
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database savelono;

Use mysqldump command to back up the database on the original server. The database can be named anything, I usually back up to savelono.com with a ‘DATE-NAME.sql’ convention.

[root@savelono ~]# mysqldump -u root savelono > savelono-04-24-09.sql

Now copy your sql database to the new server and import it.

[root@mattcom1 ~]# mysql -u root -p -D savelono < savelono-04-24-09.sql

That’s it!  Piece of cake.