Moving MariaDB

[lewashby]

I'm working on a small web project (MariaDB LAMP stack) on my desktop but I'd like to be able to continue working on the project when I am away from home computer. I think I can sync my /var/www/html/ directory without a problem but does how can I export my MariaDB database and imort it into my laptops Linux OS for continued development when I'm away from my home computer?

[cereal]

Hi,

an easy solution is a mysqldump:

mysqldump --no-data --opt db_name | mysql --host=remote_host -C db_name

This command will dump from local to remote database (remote would be the laptops), the --no-data option dumps only the CREATE TABLE statements, without the insert statements. For each command you have to define also user and password, so it would look more like this:

mysqldump -uUSER -pPASS --no-data --opt db_name | mysql -uUSER -pPASS --host=remote_host -C db_name

But you can dump to a specific file:

mysqldump --opt db_name > latest.sql

then move the file (dropbox for example) and on laptops execute:

mysql -d db_name < latest.sql

For more mysqldump options then check:

• https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

[lewashby]

So this line
-> mysqldump -uUSER -pPASS --no-data --opt db_name | mysql -uUSER -pPASS --host=remote_host -C db_name
will only copy the DB layout and tables and not the data. Then I would need to use your third and fourth mysql lines to get the data? Is that the business?

[cereal]

The command to get just data is:

mysqldump --no-create-info --no-create-db --opt db_name > latest_data_db_name.sql

Otherwise with example 3 you get both create and inserts statements. I use this command line script when I want to backup databases:

<?php

// usage:
// php backup.php [backup type (default 1), [db list (csv list)]]

// example:
// php backup.php 2 project_a,project_b

// the above will backup two databases,
// by omitting the second argument (db list)
// it will backup all databases defined
// in the hardcoded list

$type = array_key_exists(1, $argv) ? $argv[1] : 1;
$list = array_key_exists(2, $argv) ? $argv[2] : FALSE;
$user = '';
$pass = '';

if($list)
    $dbs = array_map('trim', explode(',', $list));

// hardcoded databases list
else
    $dbs  = [
        'db_name',
        'another_database'
    ];

switch ($type) {
    // full, no routines
    case 1:
        array_walk($dbs, function($db) use($user, $pass) {
            exec("mysqldump -u{$user} -p{$pass} $db > full_{$db}.sql");
        });
        break;

    // only create statements
    case 2:
        array_walk($dbs, function($db) use($user, $pass) {
            exec("mysqldump -u{$user} -p{$pass} --no-data $db > create_{$db}.sql");
        });
        break;

    // only data
    case 3:
        array_walk($dbs, function($db) use($user, $pass) {
            exec("mysqldump -u{$user} -p{$pass} --no-create-info --no-create-db $db > data_{$db}.sql");
        });
        break;

    // only routines
    case 4:
        array_walk($dbs, function($db) use($user, $pass) {
            exec("mysqldump -u{$user} -p{$pass} --routines --no-create-info --no-data --no-create-db --skip-opt $db > routines_{$db}.sql");
        });
        break;
}