php 如何以最简单和最快的方式移动 mysql 数据库？

How to move mysql database easiest & fastest way?

提问by user1031143

Hi i have to move mysql database to another server ,

嗨，我必须将 mysql 数据库移动到另一台服务器，

It's nearly 5 gb

将近 5 GB

i can have root access at both servers?

我可以在两台服务器上都拥有 root 访问权限吗？

回答by Ahmed Ghoneim

Usually you run mysqldump to create a database copy and backups as follows:

通常你运行 mysqldump 来创建一个数据库副本和备份，如下所示：

$ mysqldump -u user -p db-name > db-name.out

Copy db-name.out file using sftp/ssh to remote MySQL server:

使用 sftp/ssh 将 db-name.out 文件复制到远程 MySQL 服务器：

$ scp db-name.out [email protected]:/backup

Restore database at remote server (login over ssh):

在远程服务器上恢复数据库（通过 ssh 登录）：

$ mysql -u user -p db-name < db-name.out

OR

或者

$ mysql -u user -p 'password' db-name < db-name.out

How do I copy a MySQL database from one computer/server to another?

如何将 MySQL 数据库从一台计算机/服务器复制到另一台计算机/服务器？

Short answer is you can copy database from one computer/server to another using ssh or mysql client.

简短的回答是您可以使用 ssh 或 mysql 客户端将数据库从一台计算机/服务器复制到另一台计算机/服务器。

You can run all the above 3 commands in one pass using mysqldump and mysql commands (insecure method, use only if you are using VPN or trust your network):

您可以使用 mysqldump 和 mysql 命令一次运行所有上述 3 个命令（不安全的方法，仅当您使用 VPN 或信任您的网络时才使用）：

$ mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don't have direct access to remote mysql server (secure method):

如果您无法直接访问远程 mysql 服务器（安全方法），请使用 ssh：

$ mysqldump db-name | ssh [email protected] mysql db-name

OR

或者

$ mysqldump -u username -p'password' db-name | ssh [email protected] mysql -u username -p'password db-name

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:

您可以使用相同的语法将名为 foo 的表复制到名为 bar 的远程数据库（和远程 mysql 服务器 remote.box.com）：

$ mysqldump db-name foo | ssh [email protected] mysql bar

OR

或者

$ mysqldump -u user -p'password' db-name foo | ssh [email protected] mysql -u user -p'password' db-name foo

Almost all commands can be run using pipes under UNIX/Linux oses.

几乎所有命令都可以在 UNIX/Linux 操作系统下使用管道运行。

More from Reference

更多来自参考

Regards,

问候，

回答by Umbrella

If you have Root, you might find it quicker to avoidmysqldump. You can create the DB on the destination server, and copy the database files directly. Assuming userhas access to the destination server's mysql directory:

如果您有 Root，您可能会发现避免mysqldump. 您可以在目标服务器上创建数据库，并直接复制数据库文件。假设user可以访问目标服务器的 mysql 目录：

[root@server-A]# /etc/init.d/mysqld stop
[root@server-A]# cd /var/lib/mysql/[databasename]
[root@server-A]# scp * user@otherhost:/var/lib/mysql/[databasename]
[root@server-A]# /etc/init.d/mysqld start

Important things here are: Stop mysqld on both servers before copying DB files, make sure the file ownership and permissions are correct on the destination before starting mysqld on the destination server.

这里重要的事情是： 在复制 DB 文件之前停止两台服务器上的 mysqld，在目标服务器上启动 mysqld 之前，确保目标上的文件所有权和权限是正确的。

[root@server-B]# chown mysql:mysql /var/lib/mysql/[databasename]/*
[root@server-B]# chmod 660 /var/lib/mysql/[databasename]/*
[root@server-B]# /etc/init.d/mysqld start

With time being your priority here, the use of compression will depend on whether the time lost waiting for compression/decompression (with something like gzip) will be greater than the time wasted transmitting uncompressed data; that is, the speed of your connection.

在这里，时间是您的首要任务，压缩的使用将取决于等待压缩/解压缩（例如gzip）所损失的时间是否大于传输未压缩数据所浪费的时间；也就是说，您的连接速度。

回答by Linztm

For an automated way to backup your MySQL database:

对于备份 MySQL 数据库的自动方式：

The prerequisites to doing any form of backup is to find the ideal time of day to complete the task without hindering performance of running systems or interfere with users. On that note, the size of the database must be taken into consideration along with the I/O speed of the drives - this becomes exponentially more important as the database grows ( another factor that comes to mind is the number of drives, as having an alternate drive where the database is not stored would increase the speed due to the heads not performing both reads and writes.) For the sake of keeping this readable I'm going to assume the database is of a manageable size ( 100MB ) and the work environment is a 9am-5pm job with no real stress or other running systems on off hours.

进行任何形式的备份的先决条件是找到一天中完成任务的理想时间，而不会影响正在运行的系统的性能或干扰用户。在这一点上，必须考虑数据库的大小以及驱动器的 I/O 速度 - 随着数据库的增长，这变得呈指数级增长（想到的另一个因素是驱动器的数量，因为有一个不存储数据库的备用驱动器会提高速度，因为磁头不同时执行读取和写入。）为了保持可读性，我将假设数据库具有可管理的大小（100MB）和工作环境是上午 9 点到下午 5 点的工作，没有真正的压力或其他非工作时间正在运行的系统。

The first step would be to log into your local machine with root privileges. Once at the root shell, a MySQL user will need to be created with read only privileges. To do this, enter the MySQL shell using the command:

第一步是使用 root 权限登录本地计算机。进入 root shell 后，需要创建一个具有只读权限的 MySQL 用户。为此，请使用以下命令进入 MySQL shell：

mysql -uroot -ppassword

Next, a user will need to be created with read only privileges to the database that needs to be backed up. In this case, a specific database does not need to be assigned to a user in case the script or process would be used at a later time. To create a user with full read privileges, enter these commands in the MySQL shell:

接下来，需要创建一个对需要备份的数据库具有只读权限的用户。在这种情况下，不需要将特定数据库分配给用户，以防稍后使用脚本或进程。要创建具有完全读取权限的用户，请在 MySQL shell 中输入以下命令：

grant SELECT on *.* TO backupdbuser@localhost IDENTIFIED BY ' backuppassword';
FLUSH PRIVILEGES;

With the MySQL user created, it's safe to exit the MySQL shell and drop back into the root shell using exit. From here, we'll need to create the script that we want to run our backup commands, this is easily accomplished using BASH. This script can be stored anywhere, as we'll be using a cron job to run the script nightly, for the sake of this example we'll place the script in a new directory we create called "backupscripts." To create this directory, use this command at the root shell:

创建 MySQL 用户后，可以安全地退出 MySQL shell 并使用 exit 返回到 root shell。从这里开始，我们需要创建我们想要运行备份命令的脚本，这可以使用 BASH 轻松完成。这个脚本可以存储在任何地方，因为我们将使用 cron 作业每晚运行脚本，为了这个例子，我们将把脚本放在我们创建的名为“backupscripts”的新目录中。要创建此目录，请在 root shell 中使用以下命令：

mkdir /backupscripts

We'll also need to create a directory to store our backups locally. We'll name this directory "backuplogs." Issue this command at the root shell to create the directory:

我们还需要创建一个目录来在本地存储我们的备份。我们将这个目录命名为“backuplogs”。在 root shell 发出此命令以创建目录：

mkdir /backuplogs

The next step would be to log into your remote machine with root credentials and create a "backup user" with the command:

下一步是使用 root 凭据登录远程计算机并使用以下命令创建“备份用户”：

useradd -c "backup user" -p backuppassword backupuser

Create a directory for your backups:

为您的备份创建一个目录：

mkdir /backuplogs/

Before you log out, grab the IP address of the remote host for future reference using the command:

在注销之前，使用以下命令获取远程主机的 IP 地址以供将来参考：

ifconfig -a

eth0 is the standard interface for a wired network connection. Note this IP_ADDR.

eth0 是有线网络连接的标准接口。注意这个 IP_ADDR。

Finally, log out of the remote server and return to your original host.

最后，退出远程服务器并返回到您的原始主机。

Next we'll create the file that is our script on our host local machine, not the remote. We'll use VIM (don't hold it against me if you're a nano or emacs fan - but I'm not going to list how to use VIM to edit a file in here,) first create the file and using mysqldump, backup your database. We'll also be using scp to After the database has been created, compress your file for storage. Read the file to STDOUT to satisfy the instructions. Finally, check for files older than 7 days old. Remove them. To do this, your script will look like this:

接下来，我们将在我们的主机本地机器上创建作为我们脚本的文件，而不是远程机器。我们将使用 VIM（如果您是 nano 或 emacs 粉丝，请不要反对我 - 但我不会在这里列出如何使用 VIM 编辑文件）首先创建文件并使用 mysqldump ，备份你的数据库。我们还将使用 scp 在创建数据库后，压缩您的文件以进行存储。将文件读取到 STDOUT 以满足说明。最后，检查超过 7 天的文件。删除它们。为此，您的脚本将如下所示：

vim /backupscripts/mysqldbbackup.sh

#!/bin/sh

# create a temporary file for the schema to be stored 
BACKUPDIR = /backuplogs/
TMPFILE = tmpout.sql
CURRTIME = $(date +%Y%m%d).tgz

#backup your database
mysqldump -ubackupdbuser -pbackuppassword databasename > $BACKUPDIR$TMPFILE

#compress this file and store it locally with the current date
tar -zvcf /backuplogs/backupdb-$CURRTIME $BACKUPDIR$TMPFILE

#per instructions - cat the contents of the SQL file to STDOUT
cat $BACKUPDIR$TMPFILE

#cleanup script
# remove files older than 7 days old
find $BACKUPDIR -atime +7 -name 'backup-db-*.tgz' -exec rm {} \;

#remove the old backupdirectory from the remote server
ssh backupuser@remotehostip find /backuplogs/  -name 'backup-db-*.tgz' -exec rm {} \;

#copy the current backup directory to the remote server using scp
scp -r /backuplogs/ backupuser@remotehostip:/backuplogs/

#################
# End script
#################

With this script in place, we'll need to setup ssh keys, so that we're not prompted for a password every time our script runs. We'll do this with SSH-keygen and the command:

有了这个脚本，我们需要设置 ssh 密钥，这样每次运行脚本时都不会提示我们输入密码。我们将使用 SSH-keygen 和命令执行此操作：

ssh-keygen -t rsa

Enter a password at the prompt - this creates your private key. Do not share this.

在提示符下输入密码 - 这将创建您的私钥。不要分享这个。

The file you need to share is your public key, it is stored in the file current_home/.ssh/id_rsa.pub. The next step is to transfer this public key to your remote host . To get the key use the command:

您需要共享的文件是您的公钥，它存储在文件 current_home/.ssh/id_rsa.pub 中。下一步是将此公钥传输到您的远程主机。要获取密钥，请使用以下命令：

cat current_home/.ssh/id_rsa.pub 

copy the string in the file. Next ssh into your remote server using the command:

复制文件中的字符串。接下来使用以下命令 ssh 进入您的远程服务器：

ssh backupuser@remotehostip

Enter your password and then edit the file /.ssh/authorized_keys. Paste the string that was obtained from your id_rsa.pub file into the authorized_keys file. Write the changes to the file using your editor and then exit the editor. Log out of your remote server and test the RSA keys have worked by attempting to log into the remote server again by using the previous ssh command. If no password is asked for, it is working properly. Log out of the remote server again.

输入您的密码，然后编辑文件 /.ssh/authorized_keys。将从您的 id_rsa.pub 文件获得的字符串粘贴到 authorized_keys 文件中。使用编辑器将更改写入文件，然后退出编辑器。注销远程服务器并通过尝试使用先前的 ssh 命令再次登录远程服务器来测试 RSA 密钥是否有效。如果没有要求输入密码，则它工作正常。再次注销远程服务器。

The final thing we'll need to do is create a cron job to run this every night after users have logged off. Using crontab, we'll edit the current users file (root) as to avoid all permission issues. *Note - this can have serious implications if there are errors in your scripts including deletion of data, security vulnerabilities, etc - double check all of your work and make sure you trust your own system, if this is not possible then an alternate user and permissions should be set up on the current server *. To edit your current crontab we'll issue the command:

我们需要做的最后一件事是在用户注销后每晚创建一个 cron 作业来运行它。使用 crontab，我们将编辑当前用户文件（root）以避免所有权限问题。*注意 - 如果您的脚本中存在错误（包括删除数据、安全漏洞等），这可能会产生严重影响 - 仔细检查您的所有工作并确保您信任自己的系统，如果这不可能，则使用备用用户和应在当前服务器上设置权限 *. 要编辑您当前的 crontab，我们将发出以下命令：

crontab -e

While in the crontab editor (it'll open in your default text editor), we're going to set our script to run every night at 12:30am. Enter a new line into the editor:

在 crontab 编辑器中（它将在您的默认文本编辑器中打开），我们将设置我们的脚本在每晚 12:30 运行。在编辑器中输入一个新行：

30 0 * * * bash /backupscripts/mysqldbbackup.sh

30 0 * * * bash /backupscripts/mysqldbbackup.sh

Save this file and exit the editor. To get your cronjob to run properly, we'll need to restart the crond service. To do this, issue the command:

保存此文件并退出编辑器。为了让您的 cronjob 正常运行，我们需要重新启动 crond 服务。为此，请发出以下命令：

/etc/init.d/crond restart