Archive for March, 2013

SBDavid

MariaDB versus MySQL - Compatibility

MariaDB versus MySQL - Compatibility

MariaDB is a binary drop in replacement for MySQL

For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5). What this means is that:

Data and table definition files (.frm) files are binary compatible.
All client APIs, protocols and structs are identical.
All filenames, binaries, paths, ports, sockets, and etc… should be the same.
All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.

There are some installation issues with PHP5 that you should be aware of (a bug in how the old PHP5 client checks library compatibility).
The mysql-client package also works with MariaDB server.
The shared client library is binary compatible with MySQL’s client library.

This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1).

Reference : https://kb.askmonty.org/

Restoring a User’s Database Access For WHM version 11.36

If a database user loses access to databases, you may be able to restore the user’s grants to the database using the restoregrants utility.

Using Restoregrants to Restore the User’s Database Access

Execute one of the following commands from the command line:

/usr/local/cpanel/bin/restoregrants –cpuser=$cpuser –db={mysql, pg} –dbuser=$dbuser
or
/usr/local/cpanel/bin/restoregrants –cpuser=$cpuser –db={mysql, pg} –all

$cpuser The cPanel username which has lost access to databases.
{mysql, pg} The type of database: mysql for MySQL or pg for PostgreSQL.
$dbuser The database user whose privileges you wish to restore.

ERROR 2006: MySQL Server has gone away

When trying to load a large SQL dump, I get ERROR 2006: MySQL Server has gone away.

Version 5.0: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Version 5.5: http://dev.mysql.com/doc/refman/5.5/en/gone-away.html

Most likely, you will need to restart mysqld with the -O max_allowed_packet=# option.

A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1GB.

mysqlshow - display database, table, and column information

SYNOPSIS

mysqlshow [options] [db_name [tbl_name [col_name]]]

DESCRIPTION
The mysqlshow client can be used to quickly see which databases exist, their tables, or a tableĀ“s columns or indexes.

mysqlshow provides a command-line interface to several SQL SHOW statements.
The same information can be obtained by using those statements directly.

For example, you can issue them from the mysql client program.

Invoke mysqlshow like this:

shell> mysqlshow [options] [db_name [tbl_name [col_name]]]

If no database is given, a list of database names is shown.
If no table is given, all matching tables in the database are shown.