Tag Archives: mysql

CEntos 6.7 Migrating from mysql-server/mysql-community-server into latest MariaDB

Ok, first you should install yum-replace plugin if you haven’t already have it.

Get MariaDB packages repo from MariaDB repos builder here
MariaDB repo builder
You’ll get some repo config text that you can save to /etc/yum.repos.d/MariaDb.repo like these:

Do update your repo chache

If you’re installing mysql-server from CentOS 6.7 default repo, you should do this:

If you’re installing mysql server from webtatic repo (mysql55w-server), you should do this:

If you’re installing from MySQL Community repository, do this:

Just answer with ‘Y’ if the yum replace command show you some conflicts.
Next, just install all MariaDB related packages:

Start MariaDB:

Confirm that you’re using MariaDB:

Don’t worry, if you have successfully installing MariaDB over MySQL:

  1. your web app that use MySQL will not broken, no need to change anything
  2. your data will stay intact (no data loss)
  3. use MySQL commands as usual (such as: mysql -u root -p)
  4. the MariaDB config is still in /etc/my.cnf

In short, business as usual!

Connect and access MySQL database

Connecting to MySQL database from Matlab is not that hard (as I thought before). Matlab apparently use JDBC to connect to database server.
So, here’s simple steps to connect to MySQL server:

  • Open the zip/tar.gz file and extract only the .jar file on your Matlab project directory.
mysql connector/j archive

Only extract that .jar file

  • Now you are ready to use your MySQL driver inside Matlab.
  • Open Matlab and set your “Current Folder” to where you extract that MySQL Connector/J driver.
matlab current folder

matlab current folder

 

  • For this tutorial, I’ll use cdcols database from XAMPP sample MySQL database. (database name=cdcols, mysql username=root and mysql password is empty).
  • Load MySQL driver (as I’m writing this, the current driver version is: 5.1.21)

  • Connecting to database

  • We will access cds table, here’s the table structure (taken from open sourced HeidiSQL)
cds table structure

cds table structure

  • SELECT Query on cds table

  • fetch 2 rows from the resultset/recordset. The return value would be a cell of matrices

  • accessing the record value

  • INSERT query on cds table (you can use the same technique on UPDATE and DELETE query)

  • table cds after above INSERT query (screenshot also from HeidiSQL):
cds table after insert

data after INSERT query

  • Closing database connection

That’s it. It’s not that hard isn’t it?

Free and lightweight MySQL administrator

Some months ago I had a project that need to implement certain stored procedure. Creating stored procedure using PhpMyAdmin is a breeze but not exporting it. I found it difficult (actually I can’t find a way to export stored procedure via PhpMyAdmin). No luck using MySQL Administrator either. Finally found working solution: Navicat for MySQL but it’s not free.
Looking further (actually by accident when I’m looking for application that built using Delphi), found one that suit my needs:

HeidiSQL: a lightweight, Windows based interface for managing MySQL databases

It has session management
heidisql session management

Neat main interface
heidisql main interface

Mandatory SQL query inputs
heidisql sql query window

Command history window, useful for you that eager to learn what’s the command that sent to MySQL server
heidisql command history

What I need most: create and edit stored procedure (double click on the stored procedure name to edit it)
heidisql  stored procedure browser

Oh, it’s also work for MSSQL server

Update (April 2016):
It does work on MariaDB too!!! (using mysql driver and settings)

mysqldump failed to create backup with error “mysqldump: Error: Can’t create/write to file /tmp/”‏

System:

  • Debian 5 (Lenny)
  • MySQL 5.1.51
  • login as root

Problem:
mysqldump failed to create database backup even I’m logging-in as root. Error example:

It looks like mysqldump trying to write to /tmp even if  I’m tell it to save to my home (/root).
Apparently this problem also happen inside mysql console when running ‘describe’ command, example output inside mysql console:

Cause:
Apparently, mysqldump and ‘describe’ command need to write temporary file to /tmp
all mysql commands is ran by mysql server user, not as user currently logging-in (even root), so, mysql server user (in Debian: mysql) need to have permission to write to /tmp

Solution:

Try it! (No need to restart mysql)