Skip to content

mysql

mydumper-0.6 views dump and restore

Hi readers, i hope you are listening to the MUSE Drones Album,

The mydumper-0.6.2 (the current stable version) is not dumping views… Thankfully, the 0.9 branch (not stable at now) will… (MyDumper – Add support for all schema objects)

The question is, how to safely backup theses views (without using a beta version of mydumper) ?

I search into the myloader.c source code how the regular tables have theirs schema restored, and luckily, in the following function :

void restore_data(MYSQL *conn, char *database, char *table, const char *filename, gboolean is_schema);

we can see that the content of the schema dump file is ready and executed without special verification :

// [...]
if (!is_schema)
    mysql_query(conn, "START TRANSACTION");

while (eof == FALSE) {
    if (read_data(infile, is_compressed, data, &eof)) {
        // Search for ; in last 5 chars of line
        if (g_strrstr(&data->str[data->len >= 5 ? data->len - 5 : 0], ";\n")) {
            if (mysql_real_query(conn, data->str, data->len)) {
                g_critical("Error restoring %s.%s from file %s: %s", db ? db : database, table, filename, mysql_error(conn));
                errors++;
                return;
            }
            query_counter++;
            if (!is_schema &&(query_counter == commit_count)) {
                query_counter= 0;
                if (mysql_query(conn, "COMMIT")) {
                    g_critical("Error committing data for %s.%s: %s", db ? db : database, table, mysql_error(conn));
                    errors++;
                    return;
                }
                mysql_query(conn, "START TRANSACTION");
            }

            g_string_set_size(data, 0);
        }
    } else {
        g_critical("error reading file %s (%d)", filename, errno);
        errors++;
        return;
    }
}
if (!is_schema && mysql_query(conn, "COMMIT")) {
    g_critical("Error committing data for %s.%s from file %s: %s", db ? db : database, table, filename, mysql_error(conn));
    errors++;
}
// [...]

That mean we can generate – by any way we want – a fake schema-dump file, but with a query creating a view.

For that,  i first thought of patching mydumper.c ; but as mydumper-0.9 (not stable at now) already support this feature, i will prefer another solution.

I’ve made a modest python script which list view and generate files named database.view_name-schema.sql into the mydumper dump dir. With that, myloader will consider thoses files as regular table schema create statements.

Please note that i force a DROP VIEW IF EXISTS into the dump file…

#!/usr/bin/python2.7 -u

import MySQLdb
import os

# settings
MARIADB_USER = 'a_readonly_user'
MARIADB_PASS = 'a_good_password_MU5E_D3AD_IN7ID3_%!@'
MARIADB_HOST = '127.0.0.1'
MARIADB_PORT = 3306
MARIADB_SCKT = ''  # empty for TCP , or path if socket (host must be localhost)
MYDUMPER_DUMP_DIR = '/tmp'  # the path of the dump already done by mydumper

# connection to the MariaDB server
mariadb_connection = MySQLdb.connect(
    host=MARIADB_HOST,
    user=MARIADB_USER,
    passwd=MARIADB_PASS,
    port=MARIADB_PORT,
    unix_socket=MARIADB_SCKT,
    db='information_schema',
)

# creating cursors
views_list_cursor = mariadb_connection.cursor()
show_create_view_cursor = mariadb_connection.cursor()

# ask MariaDB for the views list
views_list_cursor.execute("SELECT `table_schema`, `table_name` FROM `tables` WHERE `table_type` = 'VIEW'")

for database, view in views_list_cursor:
    # and handle each results
    show_create_view_cursor.execute("SHOW CREATE VIEW `{}`.`{}`".format(database, view))
    view, create_view_stmt, character_set_client, collation_connection = show_create_view_cursor.fetchone()
    dump_file = os.path.join(MYDUMPER_DUMP_DIR, '{}.{}-schema.sql'.format(database, view))
    assert not os.path.exists(dump_file)
    with open(dump_file, 'w') as f:
        fwrite('DROP VIEW IF EXISTS `{}`.`{}`;'.format(database, view))
        fwrite('\n\n')
        f.write(create_view_stmt)
        f.write(';')

# release cursors and close the MariaDB connection
views_list_cursor.close()
show_create_view_cursor.close()
mariadb_connection.close()

Feel free to make it PEP8, or with better error handling. Actually, the return code will be 0 only if everything seems good.

socat permitting MySQL migration

Fast idea using socat (loosed as draft since 2013-02-01) to quickly migrate a tcp and/or unix socket MySQL installation. (socat-1.7.2.0 used for the test).

If the need is temporary, this avoid a MySQL Proxy setup.

Launch that as a daemontools service (for example):

1. /service/socat-mysql-tcp/run

#!/bin/bash
bind_port=3306
bind_addr=127.0.0.1
forward_port=3304
forward_host=sql.dedicated
exec setuidgid mysql socat -d TCP4-LISTEN:${bind_port},bind=${bind_addr},fork TCP4:${forward_host}:${forward_port}

2. /service/socat-mysql-unixsocket/run

#!/bin/bash
sock=/tmp/mysql.sock
forward_port=3304
forward_host=sql.dedicated
exec setuidgid mysql socat UNIX-LISTEN:${sock},fork TCP4:${forward_host}:${forward_port}