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.

yum install yum-plugin-replace

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:

# MariaDB 10.1 CentOS repository list - created 2016-04-17 23:39 UTC
# http://mariadb.org/mariadb/repositories/
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64

Do update your repo chache

yum update

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

yum replace mysql-libs --replace-with=MariaDB-common

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

yum replace mysql55w-libs --replace-with=MariaDB-common

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

yum replace mysql-community-common --replace-with=MariaDB-common

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

yum install MariaDB-*

Start MariaDB:

/etc/init.d/mysql start

Confirm that you’re using MariaDB:

[root@www ~]# mysql --version
mysql  Ver 15.1 Distrib 10.1.13-MariaDB, for Linux (x86_64) using readline 5.1

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)
javaaddpath 'mysql-connector-java-5.1.21-bin.jar';
  • Connecting to database
conn=database('cdcol', 'root', '', 'com.mysql.jdbc.Driver', 'jdbc:mysql://localhost/');
  • 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
e = exec(conn,'SELECT * FROM cds');
  • fetch 2 rows from the resultset/recordset. The return value would be a cell of matrices
dat=fetch(e, 2);
  • accessing the record value
% second row, first column ('titel' field)
  • INSERT query on cds table (you can use the same technique on UPDATE and DELETE query)
r=exec(conn, 'insert into cds(`titel`, `interpret`, `jahr`) values(''Kisah 2002 Malam'', ''Peterpan'', 2002)');
  • 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/”‏


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

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

[root@nq ~] mysqldump -u mv -p mv>mv.sql
mysqldump: Error: 'Can't create/write to file '/tmp/#sql_3c4_2.MYI' (Errcode: 13)' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `logs`': Can't create/write to file '/tmp/#sql_3c4_0.MYI' (Errcode: 13) (1)

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:

mysql> describe logs;
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_3c4_0.MYI' (Errcode: 13)

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


chmod 777 /tmp

Try it! (No need to restart mysql)