Connect Two MySQL Databases

Question

I have two servers.

Eg. server A and server B.

I want to select data from server A and insert into server B using MySQL.

How to make it? I can access server A's database remotely from server B but I can't do select and insert query. Because they are in different usernames.

Any ideas?

score 1 · Answer 1 · answered Sep 22 '15 at 07:17

1

mysqldump from the database on the source server.

Then still on the source server mysql -h[dest server hostname/IP] -u[user] -p[pass] < [location of the file you just made].

answered Sep 22 '15 at 07:17

jonbaldie

161
2

score 1 · Answer 2 · answered Oct 25 '15 at 19:20

The other answer (mysqldump, etc) is good for a 1-time operation.

If you need to do this a lot, then:

Plan A: Write code (PHP, Java, etc); have 2 connections; SELECT via one connection; INSERT via the other.

Plan B: Use MariaDB and the FederatedX Engine. FederatedX talks through one server to the other, making it feel like the table is local.

score 1 · Answer 3 · edited Sep 17 '18 at 14:16

I have done this previously using Laravel. This is the code I used:

ini_set('max_execution_time', 3600);
$old_users = DB::connection('mysql2')->table('user_master')->get();
foreach ($old_users as $old_user) {
    if ($old_user->usr_phone != "0") {
        $password = base64_decode($old_user->usr_pwd);
        $password = Hash::make($password);
        if ($old_user->device_type == "1") {
            $device_type = "ios";
        } else if ($old_user->device_type == "2") {
            $device_type = "android";
        } else {
            $device_type = "web";
        }
        $user = new User;
        $user->name = $old_user->usr_name;
        $user->email = $old_user->usr_email;
        $user->points_exp_date = "2018-08-02";
        try {
            $user->save();
        } catch (\Exception $e) {
            echo ("<script>console.log('duplicate entry in user: " . $user->name . "');</script>");
        }
        Log::info('user saved');
    }
}

Connect Two MySQL Databases

3 Answers3