将mysql数据库从一台计算机复制到另一台计算机

I want to copy my mysql database from my computer to another computer. How can I do this?

我想把我的mysql数据库从我的电脑拷贝到另一台电脑上。我该怎么做呢?

4 个解决方案

#1

How to copy Mysql database from one Computer to another / backup database using mysqldump

如何使用mysqldump将Mysql数据库从一台计算机复制到另一台/备份数据库?

1. We can transfer a MySQL database from one PC to another PC using mysqldump command.
2. 我们可以使用mysqldump命令将MySQL数据库从一台PC转移到另一台PC。

3. We have to create dump file of database to transfer database from one PC to another PC.

   我们必须创建数据库转储文件，将数据库从一个PC转移到另一个PC。

4. MySQL database is not portable database i.e. we cannot transfer it from one PC to another PC by copying and pasting it.

   MySQL数据库不是可移植的数据库，我们不能通过复制和粘贴将它从一台PC转移到另一台PC。

5. We can use following method to transfer database.

   我们可以使用以下方法来传输数据库。

6. Creating a dumpfile from database/ Taking backup of MySQL database:

   从数据库创建一个dumpfile /备份MySQL数据库:

7. Open command prompt.

   打开命令提示符。

8. Execute following commands to change directory

   执行以下命令来更改目录

   c: “press enter”

   c:“回车”

   cd program files/MySQL/MySQL Server 5.1/ bin “press enter”

   cd程序文件/MySQL/MySQL服务器5.1/ bin“按回车”

   mysqldump -u root -p database_name > database_name.sql “press enter”

   mysqldump -u根-p database_name > database_name。sql“回车”

   Enter password: password of MySQL

   输入密码:MySQL密码

Copy sql file and paste it in PC where you want to transfer database.

复制sql文件并将其粘贴到您想要传输数据库的PC上。

      2. Dumping sql file into database:-

      - Open MySQL  command line client command prompt.

      - Execute following command to create database.

create database database_name;

创建数据库database_name;

“press enter” Database name is must as that of your database_name.

“按enter”数据库名称必须与您的database_name相同。

Copy that sql file into location “c:/program files/MySQL/MySQL Server 5.1/bin”

将sql文件复制到“c:/程序文件/MySQL/MySQL服务器5.1/bin”

      *- Now open command prompt and execute following commands.*


        >C: “press enter”

        >cd program files/MySQL/MySQL Server5.1/bin “press enter”

        >mysql –u root –p database_name < database_name.sql “press enter”

        Your database is created on PC.

        Now in MySQL command prompt check your database.  

Another one:1

另一个:1

This best and the easy way is to use a db tools(SQLyog)

最好的方法是使用db工具(SQLyog)

http://www.webyog.com/product/downloads

http://www.webyog.com/product/downloads

With this tools you can connect the 2 databases servers and just copy one database on server a to server b.

使用此工具，您可以连接两个数据库服务器，并将服务器a上的一个数据库复制到服务器b。

For more info

更多信息

http://faq.webyog.com/content/12/32/en/mysql-5-objects-are-greyed-out-in-copy-db-to-other-host-dialogue.htmlenter image description here

这里http://faq.webyog.com/content/12/32/en/mysql-5-objects-are-greyed-out-in-copy-db-to-other-host-dialogue.htmlenter形象描述

Another one:2

另一个:2

For a database named "lbry", try this:

对于名为“lbry”的数据库，请尝试以下方法:

mysqldump -u root -p lbry > dump-lbry.sql

Create a database of the same name ("lbry" in this example) on the computer to which you wish to copy the database contents

在希望复制数据库内容的计算机上创建同名数据库(本例中为“lbry”)

Then import it:

然后导入:

mysql -u root -p lbry < dump-lbry.sql

#2

You can do by this process step-by-step using MySQL WorkBench.

您可以使用MySQL WorkBench一步一步地完成这个过程。

1. Install MySQL Workbench
2. 安装MySQL工作台
3. Connect to existing Database
4. 连接到现有的数据库
5. Go to Navigator -> Management -> Data Export. (this will dump queries of tables one by one in a separate folder, Workbench uses the same folder to import)
6. Go to Navigator ->管理->数据导出。(这将把表的查询逐一转储到单独的文件夹中，Workbench使用相同的文件夹来导入)
7. Create Database on target PC.
8. 在目标PC上创建数据库。
9. Connect to Target Database (would consist of 0 tables in DB)
10. 连接到目标数据库(将包含DB中的0个表)
11. Go to Navigator -> Management -> Data Import/Restore. (this will use the dump folder and create tables in your target Database).
12. 转到Navigator ->管理->数据导入/恢复。(这将使用dump文件夹并在目标数据库中创建表)。

Hope this helps.

希望这个有帮助。

#3

The only SAFE way to copy databases from one machine to another is to first quiesce the database (make sure no clients are modifying it), then use the mysqldump command to create a text representation of your schema and the contents of your tables. Then copy that text file over to the other machine and read it in by specifying it as the input to the mysql command.

将数据库从一台机器复制到另一台机器的唯一安全方法是首先对数据库进行quiesce(确保没有客户机修改它)，然后使用mysqldump命令创建模式的文本表示和表的内容。然后将该文本文件复制到另一台机器上，并通过指定它为mysql命令的输入来读取它。

Attempting to copy the actual mysql data directories over is asking for trouble, since they are dependent on the architecture of the machine that mysql is running on and likely on the version of mysql and whatever storage engine is in use.

尝试复制实际的mysql数据目录会带来麻烦，因为它们依赖于mysql正在运行的机器的体系结构，可能还依赖于mysql版本以及正在使用的任何存储引擎。

#4

1. This tutorial is in Ubuntu but will work on Redhat, Centos, Fedora, Suse
2. 本教程使用的是Ubuntu，但将用于Redhat、Centos、Fedora、Suse
3. We can dump database, transfer it to another server, and restore it
4. 我们可以转储数据库，将其传输到另一个服务器，并恢复它。
5. It will show how to take care of things like modified credentials as a result and moving debain.cnf file 4 dump restore will slow down the serverHow it works
6. 它将显示如何处理修改后的凭证和移动的debain.cnf file 4 dump restore等内容，这会减慢服务器的工作速度

4.1 Run mysqldump on source server:this builds a MySQL executable script for the destination server. During this time the MySQL server will queue queries 4.2 Copy dump file to the destination server 4.3 Empty destination server 4.4 Execute dump file on the destintion server

4.1在源服务器上运行mysqldump:这将为目标服务器构建一个MySQL可执行脚本。在此期间，MySQL服务器将队列查询4.2将转储文件复制到目标服务器4.3空目标服务器4.4在destintion服务器上执行转储文件

Server A(Source Server) Server B (Destination Server)

服务器A(源服务器)服务器B(目标服务器)

Case 1:Server A

案例1:服务器

root@source$ mysql --defaults-file=/etc/mysql/debain.cnf
mysql>show databases;
mysql>use testdb;(The database to dump)
mysql>show tables;(To Check the tables)
mysql>^c

-- now dump the databses

——现在转储数据库

root@surce$ mysql --defaults-file=/etc/mysql/debain.cnf --all-databses | gzip -c > dump.sql.gz

root@surce$ gzip -dc dump.sql.gz

To copy the files create a ssh key on the source server

要复制文件，请在源服务器上创建ssh密钥

root@surce$ ssh-keygen
root@surce$ cat /root/.ssh/id_rsa.pub
select and copy all the ssh key string

root@surce$ scp dump.sql.gz ubuntu@destination:

goto destination server

转到目标服务器

last step copy the contents of debain.cnf file

最后一步复制debain.cnf文件的内容

root@surce$ cat /etc/mysql/debain.cnf
[client]
host        = localhost
user        = debain-sys-maint
password    = mysecret
socket      = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host        = localhost
user        = debain-sys-maint
password    = mysecret
socket      = /var/run/mysqld/mysqld.sock
basedir     = /usr

select all and copy this file to detination server.

选择all并将此文件复制到detination服务器。

Note: The sockey path can be different in your machine .use locate command to find the exact path

注意:在您的机器中，sockey路径可能不同。使用locate命令找到正确的路径

Case 2. Server B drop all databses

例2。服务器B删除所有数据库

root@destination$ echo show databases | mysql --defaults-file=/etc/mysql/debian.cnf --skip-column-names | awk '{print "drop database "$1";"}'

if this command doesnot drop databses use it with -force option

如果该命令不删除数据库，则使用-force选项。

root@destination$ echo show databases | mysql --defaults-file=/etc/mysql/debian.cnf --skip-column-names | awk '{print "drop database "$1";"}' | mysql --defaults-file=/etc/mysql/debian.cnf -f

copy the ssh key on the destination server

在目标服务器上复制ssh密钥

root@destination$ echo "paste the key here" >> /home/ubuntu/.ssh/authorised_keys

root@destination$ echo“将键粘贴到这里”>> /home/ubuntu/.ssh/authorised_keys

goto source Server and use scp command to move the dump on the destination server

访问源服务器并使用scp命令在目标服务器上移动转储

(inject the file)

(注入文件)

root@destination$ gzip -dc /home/ubuntu/dump.sql.gz | mysql --defaults-file=/etc/mysql/debain.cnf

root@destination$ > /etc/mysql/debain.cnf
root@destination$ nano /etc/mysql/debain.cnf

paste the contents of .cnf file from source server here and save the file :x

从源服务器粘贴.cnf文件的内容并保存文件:x

root@destination$ mysql --defaults-file= /etc/mysql/debain.cnf

if you get the mysql prompt then everything should be working file

如果你得到了mysql提示，那么一切都应该是工作文件。

mysql>