Description
Dump table’s structure and data between MySql servers and databases.
INSTALL
$ [sudo] gem install mysql2mysql
Requirements
Sequel with the mysql driver.
Examples
require 'rubygems'
require 'mysql2mysql'
Mysql2Mysql.new.
from('mysql://mysql.server1/test?user=root&password=pass').
to('mysql://mysql.server2/test?user=root&password=pass').
tables('dbname').
dump(:charset => 'utf8')
Methods
from: parameters for connect to the source MySql server, it's used for Sequel.connect
to: parameters for connect to the target MySql server, it's used for Sequel.connect
tables: databases/tables need to dump
exclude: databases/tables need to exclude
dump: do the clone databases/tables action with some options
Connection
The "from" and "to" methods provide a DSN String or Hash for the database connection.
Mysql2Mysql.new.from('mysql://localhost/test?user=root&password=pass')
It's same as below
Mysql2Mysql.new.from(:host => 'localhost', :user => 'root', :password => 'pass)
BTW: if the source and target server in the same manchine, please don't use the "localhost" for 2 connections,
but it works if one connection use "localhost", another use "127.0.0.1".
tables && exclude
Both methods are very similar, but the "exclude" method will take some tables out from the "tables" method. So,
finall_tables_to_dump = tables - exclude
String, Hash, Array are supported. e.g.
# all databases/tables
tables('*')
# all tables in the database "dbname"
tables('dbname')
# same as above
tables(:dbname)
# all tables in the database "blog" and "log"
tables(['blog', 'log'])
# same as above
tables({'blog' => '*', 'log' => '*'})
# tables "posts" and "comments" in the database "blog" and
# all tables in the database "log"
tables({'blog' => ['posts', 'comments'], 'log' => '*'})
# Regular expression supported
# all tables with prefix 'wp_' in the database 'blog'
tables({:blog => /^(wp)_/})
# all tables with prefix 'wp_' in the database 'blog' and
# all tables only contains 26 lowercase letters in the database "log"
tables({:blog => /^(wp)_/, :log => /^[a-z]+$/})
# all databaes with prefix "www_"
tables({/^(www)_/ => '*')
# exclude
# only the 'log' database's tables will be cloned
tables('blog', 'log').exclude('blog')
# all databases/tables exclude the database "mysql", "test" and "information_schema"
tables('*').exclude(['mysql', 'test', 'information_schema'])
# all tables in the database "blog" exclude the "comments" table
tables('blog').exclude({'blog' => 'comments'})
dump
The "dump" method is expect a Hash as it's parameter, defualt is {}. options:
:charset => "utf8", "latin" or any other valid charsets, default is nil
:with_data => Dump data if it's true, otherwise only tables' structure will be dumped, default is true
:drop_table_first => Drop the table first if the table is already exists, default is true
:rows_per_select => Number of rows pre select from the source server for the import, default is 1000
:before_all => Proc style call back, it can be executed before the dump action started, default is nil
:after_all => Proc style call back, it can be executed after the dump action finished, default is nil
:before_each => Proc style call back, it can be executed before each table dump started, default is nil
:aftere_each => Proc style call back, it can be executed after each table dump finished,, default is nil
Cases
clone 2 databases:
Mysql2Mysql.new.
from('mysql://localhost:3306/test?user=root&password=').
to('mysql://127.0.0.1:3307/test?user=root&password=').
tables(['db1', 'db2']).
dump
clone all databases exclude "mysql", "test" and "information_schema":
Mysql2Mysql.new.
from('mysql://localhost:3306/test?user=root&password=').
to('mysql://127.0.0.1:3307/test?user=root&password=').
tables(:all).
exclude([:mysql, :test, :information_schema]).
dump(:charset => 'utf8')
clone all tables in the database "blog" and rename it to "blog2" in the same server:
Mysql2Mysql.new.
from('mysql://localhost:3306/test?user=root&password=').
to('mysql://localhost:3306/test?user=root&password=').
tables('blog').
dump(:charset => 'utf8', :before_each => lambda {|db, tb|
# change the target database name here from "blog" to "blog2"
# the table name can be changed as well, but we keep the table name here
return "blog2", tb
})
Version
v0.0.2
TODO
more docs and specs
Author
xianhua.zhou<[email protected]>