像这样的精确场景:
我有一个使用innodb表的MySQL服务器,大小约为20GB。
我想将其移动到新服务器上,最有效的方法是什么?
#1 楼
我最喜欢的方法是将sqldump命令传递给sql命令。您可以执行所有数据库或特定数据库。因此,例如,mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword
您可以使用
mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver
来处理所有数据库,唯一的问题是何时数据库太大,管道崩溃。在这种情况下,您可以逐表或下面提到的任何其他方法进行操作。
评论
提示:如果两个数据库都不允许远程连接,请通过netcat进行管道传输。
–巴特·范·休克洛姆(Bart van Heukelom)
13年1月16日在20:42
为此,我必须在远程服务器上创建一个具有相同名称的空数据库,然后将该数据库的名称添加到命令末尾。
–楚格沃尔特
13年11月18日在15:05
这很不错,但没有压缩就不足以容纳20GB数据库。
– Daddy32
2015年10月6日,12:46
该解决方案仅适用于完全受控的网络(如果且仅当在安全的专用网络上时,请阅读:而不是Internet)!但是快速简便的解决方案!
–tdaget
18年5月30日在19:59
这快如闪电!但是@Zugwalt所说的对我来说也是正确的。在创建数据库(空)之前,该命令不起作用。add将数据库名称添加到命令末尾。
–飞碟
19年5月15日在12:45
#2 楼
我最近移动了一个具有以下策略的30GB数据库:旧服务器
停止mysql服务器
将datadir的内容复制到磁盘上的另一个位置(
~/mysqldata/*
)启动mysql重新服务器(停机时间为10-15分钟)
压缩数据(
tar -czvf mysqldata.tar.gz ~/mysqldata
)将压缩文件复制到新服务器
新服务器
安装mysql(不启动)
解压缩压缩文件(
tar -xzvf mysqldata.tar.gz
)将mysqldata的内容移动到datadir
确保新服务器上的innodb_log_file_size相同,或者不同,请勿复制旧的日志文件(mysql将生成这些日志文件)
启动mysql
评论
压缩/解压缩后跳过副本。使用ssh在网络上流tar,或者(当且仅当在安全的专用网络上读取:不是Internet时)使用netcat避免加密开销。另外,如果在本地网络上跳过gziping,则如果您有快速的网络管道,则会在固定的核心旋转进行压缩时发现传输瓶颈
– atxdba
13年2月20日在21:03
这适用于innodb和myisam吗?另外,mysql用户也在datadir中吗?
–giorgio79
2013年6月26日14:19
@ giorgio79确保只要您也移动ibdata文件。默认情况下,它们在datadir中。 MySQL用户存储在用户表空间的mysql文件夹中。
–德里克·唐尼(Derek Downey)
13年6月26日在17:01
从Windows迁移到Linux是否可以使用此过程?
–超立方体ᵀᴹ
2014年5月25日19:43
@TypoCube很抱歉花了两年以上的时间来回答,但是如果有人明确地说:“是的,它可以在Windows到Linux上运行”,那对我很有帮助。就我而言,我从Windows Server 2012 R2转到了Cent OS(Red Hat 4.8.5-11)。特定的mysql版本是Maria DB 10.1。按照规定,我停止了两个mysql服务,重新同步了数据目录,并且在新服务器上启动mysql服务后,所有数据库,数据库表和数据库用户都完好无损。
– WEBjuju
17年1月7日在14:57
#3 楼
根据MySQL 5.0认证研究指南的第32章32.3.4节,第456,457页描述了二进制可移植性的条件,该条件带来以下内容:二进制可移植性很重要,如果您< />想要获取在一台计算机上进行的二进制备份,并在另一台具有不同架构的计算机上使用它。例如,使用
二进制备份是将
数据库从一台MySQL服务器复制到另一台
的一种方法。
对于MyISAM,二进制可移植性意味着
您可以将MyISAM表的文件直接从一台MySQL
服务器复制到另一台
计算机上的另一台服务器,第二台服务器将
能够访问该表。 br />
对于InnoDB,二进制可移植性意味着
,您可以将
表空间文件从MySQL服务器上的
直接复制到一台计算机上的另一台服务器上,计算机和第二台
服务器将能够访问
表空间。默认情况下,服务器管理的所有InnoDB
表都一起存储在表空间中,因此表空间的可移植性是是否所有单个表的功能。
InnoDB表是可移植的。如果一个表不能移植,那么表空间也不能移植。
MyISAM表和InnoDB表空间可以从一台主机二进制移植到另一台主机。如果满足两个条件:
两台机器都必须使用二进制补码整数算法
两台机器都必须使用IEEE浮点格式,否则
表必须包含没有浮点
列(FLOAT或DOUBLE)
实际上,这两个条件几乎没有限制。二进制补码
整数算法和IEEE
浮点格式是现代硬件上的标准。 InnoDB二进制可移植性的第三个条件是您
表和数据库应使用小写名称。这是因为InnoDB
在Windows的内部以小写形式在内部(在其
数据字典中)存储这些名称。使用小写名称允许Windows和Unix之间的二进制可移植性,以强制使用小写名称,您可以在选项文件中放置以下行
:
[mysqld]
lower_case_table_names=1
如果将InnoDB配置为使用每个表表空间
,则二进制可移植性的条件
将扩展到
/>还包括InnoDB表的.ibd文件。 (共享表空间的条件仍然适用
,因为它包含数据字典,该字典存储有关所有InnoDB表的信息
。)
如果条件如果不满足二进制可移植性
,则可以通过使用某些
文本格式转储MyISAM
或InnoDB表从一台服务器复制到另一台
(例如,使用
mysqldump)并将它们重新加载到目标服务器中。
有两种基于存储引擎的主要方法来移动单个表。
对于在给定的示例中,我们将假设以下内容:
datadir是mydb数据库中名为mytable的/ var / lib / mysql
数据库,名为mydb
表。
MyISAM表
如果mydb.mytable使用MyISAM存储引擎,则该表在物理上将表现为三个单独的文件
/var/lib/mysql/mydb/mytable.frm(.frm文件)
/var/lib/mysql/mydb/mytable.MYD(.MYD文件)
/var/lib/mysql/mydb/mytable.MYI(.MYI文件)
.frm包含表结构
.MYD包含表数据
.MYI包含表索引页
从mysql的逻辑观点来看,这些文件相互依赖地用来表示表。由于这些文件没有附加的逻辑关联,因此将表从一个DB服务器迁移到另一个。您甚至可以从Windows服务器到Linux服务器或MacOS。当然,您可以关闭mysql并复制3个表文件。您可以在一个ssh会话中运行以下命令:
LOCK TABLES mydb.mytable READ;
SELECT SLEEP(86400);
UNLOCK TABLES;
将表保持为只读状态并将锁保持24小时。一秒钟后,在另一个ssh会话中执行复制。然后用24小时锁定终止mysql会话。您无需等待24小时。
InnoDB表
基于《认证书》中的上述引用,有许多因素决定着如何备份特定的InnoDB表。为了简单,清楚和简洁起见,只需使用--single-transaction参数对所需表执行mysqldump,即可获得表的最佳时间点转储。如果您只想要一个表,则无需使用InnoDB语义。您可以将该转储文件重新加载到您选择的任何MySQL服务器上。
由于此处合并了两个问题(jcolebrand):编辑
如果您愿意与某些人一起生活数据库性能下降时,即使mysql仍在ServerA上运行,您也可以从旧服务器(ServerA)到新服务器(ServerB)进行一系列rsync。
步骤01)安装相同版本的ServerA具有的ServerB上的mysql
步骤02)在ServerA上,从mysql运行
SET GLOBAL innodb_max_dirty_pages_pct = 0;
大约10分钟(这将从InnoDB缓冲池中清除脏页。它还有助于更快地执行mysql关闭)数据库全部是MyISAM,您可以跳过此步骤。步骤03)
rsync --archive --verbose --stats --partial --progress --human-readable ServerA:/var/lib/mysql ServerB:/var/lib/mysql
步骤04)重复步骤03,直到rsync花费不到1分钟
br />步骤05)在ServerA上
service mysql stop
步骤06)再执行一次rsync
步骤07)
scp ServerA:/etc/my.cnf ServerB:/etc/
步骤08)ServerB上的
service mysql start
步骤08)ServerA上的
service mysql start
(可选)尝试一下!!! >
您可以创建一个复制从属。只需记住在主服务器/etc/my.cnf中明确设置了server-id,在从属服务器/etc/my.cnf中明确设置了server-id
#4 楼
如果要移动整个数据库架构,甚至不需要mysqldump,并且愿意停止第一个数据库(因此在传输时保持一致)停止数据库(或锁定它)
转到mysql数据文件所在的目录。
将文件夹(及其内容)转移到新服务器的mysql数据目录中
开始备份数据库
在新服务器上,发出“创建数据库”命令。'
重新创建用户并授予权限。
我不记得mysqldump是否处理了用户和权限。 ,或者只是数据...但是,即使这样做,它也比进行转储和运行它要快得多。仅当需要转储mysql数据库然后将其重新插入其他RDBMS,需要更改存储选项(innodb与myisam),或者如果要更改mysql的主要版本时,才使用该命令。我想我已经在4到5之间完成了此操作)
评论
这效率更高,尤其是如果是sysadmin / DBA。 BTW mysqldump使用--all-databases转储mysql模式。在下一台机器上启动mysql会弹出权限,前提是您已将数据文件夹转移到具有相同主要MySQL版本的另一台机器上。 (MySQL 5.5.x至MySQL 5.5.x,MySQL 5.1.x至MySQL 5.1.x,MySQL 5.0.x至MySQL 5.0.x)
– RolandoMySQLDBA
2011年7月20日在20:21
@Joe,是的,mysqldump处理用户和权限,因为它们存储在mysql模式中。
– Shlomi Noach
2012年8月26日下午4:17
此方法对AWS等云托管尤其有用。您可以停止mysql,卸载并从当前服务器分离;附加并挂载到新服务器并启动mysql。如果卷保留在同一服务器场中,则没有复制开销。
–LateralFractal
17-10-7在2:53
如果要复制大型数据库,请锁定数据库并在本地复制文件。记下显示主状态的信息;查询然后解锁表;您可以在方便时将文件复制到从属服务器。在这种情况下,这是我能想到的最短的停机时间。
– Nikola Novak
4月15日下午13:35
#5 楼
如果只想移动特定的表,请尝试:mysqldump -u username -ppassword databasename tablename > databasename.tablename.sql
您可以在同一命令中在上面指定更多表名。命令完成后,将databasename.tablename.sql文件移动到另一台服务器,然后使用以下命令进行还原:
mysql -u username -ppassword databasename < databasename.tablename.sql
请注意,使用。 mysqldump程序,还原直接在mysql中完成。
#6 楼
如果拥有ssh访问权限,则可以从命令行使用mysqldump
如果没有ssh访问权限,但是具有phpMyAdmin访问权限,则可以使用它导出/导入
如果没有没有phpMyAdmin访问权限,有一些方便的php脚本将转储和导入(但是从我自己的经验来看,我从未找到像phpMyAdmin一样可靠的脚本)。实际的数据库文件(对于我的安装,它们位于/ var / lib / mysql),但是我不太确定它的工作方式/解决方法。
#7 楼
您将需要停机。这将需要一段时间,具体取决于您的网络速度。我将假设您在Linux / Unix上运行MySQL。这是我使用的过程:在源主机上停止mysql守护进程。
在目标主机上创建一个tmp文件夹以接收文件。
使用屏幕创建一个Shell会话,如果您的ssh断开连接,它将继续存在。
使用rsync在主机之间传输文件。类似于:rsync -avhP source user @ targethost:/ path / to / folder /
运行测试用例,以确保您在传输中不会丢失任何内容。
然后照常进行操作,以设置本地MySQL。
*注意:您还可以将-c参数与rsync一起使用,以将校验和添加到传输中,但这取决于CPU速度。
#8 楼
我可以确认DTest的方法也可以在ubuntu和osx之间进行复制。要复制所有数据库而不必进行任何转储或类似操作:干净的mysql mysql(安装了从mysql http://cdn.mysql.com/Downloads/MySQL-5.1/mysql-5.1.63-osx10.6-x86_64.dmg下载的dmg),它从未已运行。
从Mac上的/ usr / local / mysql / data /内容顶部的ubuntu机器复制/ var / lib / mysql /文件夹内容。要访问ubuntu机器上的文件夹,我必须使用sudo,即:
sudo cp /var/lib/mysql /home/foouser/mysql_data_folder
sudo chown -R foouser /home/foouser/mysql_data_folder
我使用scp复制了该文件夹。
之前您开始在Mac上获取mysql文件夹的副本,以确保您不会弄乱任何东西。
复制文件夹后,请在mac机上执行以下操作:
sudo chown -R _mysql /usr/local/mysql/data/
sudo chgrp -R wheel /usr/local/mysql/data/
sudo chmod -R g+rx /usr/local/mysql/data/
第一次启动mysql服务器(从“系统偏好设置”->“ mysql”下的“偏好设置”窗格中)。现在应该正确设置所有用户和数据库。
#9 楼
我认为所有较早的答案都可以正常工作,但是并不能真正解决在传输过程中设置数据库名称的问题。这就是我使用bash的方式:
使用
rsync
可能比scp
更好,并且如果经常这样做,则不压缩文件。/>
在我的源服务器上:
me@web:~$ d=members
me@web:~$ mysqldump $d | gzip > $d.sql.gz
me@web:~$ scp -i .ssh/yourkeynamehere $d.sql.gz $sbox:$d.sql.gz
在我的目标服务器上:
me@sandbox:~$ d1=members
me@sandbox:~$ d2=members_sb
me@sandbox:~$ mysqladmin create $d2
me@sandbox:~$ cat $d1.sql.gz | gunzip | mysql $d2
在任一台机器上查看进度:
me@sandbox:~$ ls *.gz
me@sandbox:~$ cat $d.sql.gz | gunzip | less
这全部假设您在两台机器的主目录中都有MySQL配置文件,并设置了权限:
$ echo "
[client]
user=drupal6
password=metoknow
host=ord-mysql-001-sn.bananas.com
[mysql]
database=nz_drupal" > .my.cnf
$ chmod 0600 ~/.my.cnf
#10 楼
您要将其移动到另一个mysql服务器数据库吗?如果要使用,请对其进行导出
# mysqldump -u username -ppassword database_name > FILE.sql
评论
Mysqldump的?那是您处理少量数据的时候?
–哦,Chin Boon
2011-09-18 23:38
我认为这些天大/小都是相当主观的。当我看到问题的标题时,我期望数据库比20gb大很多,因此被认为是“大” ...
–亚伦·伯特兰(Aaron Bertrand)
2011-09-19 0:49
#11 楼
通用linux方法:/etc/init.d/mysqld stop
rsync -avz source_files destination
vi /etc/my.cnf
编辑mysqld和mysqld_safe的数据目录(和套接字)以指向新位置,然后
/etc/init.d/mysql start
我发布了此信息,因为似乎没有人简单地列出执行此操作的最少步骤,我个人认为这是最简单的方法。
#12 楼
也许这样做是更好的方法:版本1:数据文件复制(仅适用于MYISAM)
ssh server1
service mysql stop
cd $mysql-data-dir
rsync -avz dirs-or-files server2:$mysql-data-dir
service mysql start
ssh server2
服务重新启动mysql
如果数据库文件是只读的,则可以跳过停止服务器的操作。
版本2:mysqldump
在现代Xeon或Opteron处理器上安装pigz-尤其是当您具有2个或更多CPU时,它比gzip快得多。
ssh server1
mysqldump ... | pigz > backup-YYMDD.sql.gz
rsync backup-YYMDD.sql.gz server:location
ssh server2
pigz -dc location/backup-YYMDD.sql.gz | mysql ..
版本3:主/从+ mysqldump /文件复制
In HA environment you should use the following trick:
setup slave server & do all backups from it
before backups - do "slave stop";
then do version 1 or version 2
脚本:
touch full.start
mysqladmin -h slave-db stop-slave
echo "show slave status \G" | mysql -h slave-db > FULL/comfi-$NOW.master-position
/usr/bin/mysqldump -h slave-db --default-character-set=utf8 -A --opt --skip-lock-tables | pigz > "FULL/XXXX-$NOW.sql.gz"
mysqladmin -h slave-db start-slave
touch full.end
ln -fs "FULL/XXXX-$NOW.sql.gz" FULL.sql.gz
PS:
复制小表使用:
ssh server1 < mysql dump模式表| ssh server2 mysql模式
#13 楼
我建议通过两个简单步骤将整个数据库从一台服务器转移到另一台服务器。步骤1:使用mysqldump对源服务器中的数据库进行完整备份。
步骤2:您可以使用rsync命令将整个数据库传输到目标服务器。
评论
我将使用xtrabackuppercona.com/docs/wiki/…就像复制它一样,但是您可以保持服务器运行,并假设您主要使用innodb表(您曾说过),则可以认为它“很热”。也要备份。我不能从使用此工具的其他人中受益。它是免费的/开源的,即使它是由一家公司制造的,使用起来也很容易,您无需考虑从该公司购买支持。