【MYSQL备份】利用mysqldump将一个数据库复制到另一个数据库
假设要将服务器A上的数据库test备份到服务器B
1.在服务器B上新建数据库cp_test
mysql> create database cp_test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cp_test | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
mysql>
2.在服务器B上远程访问服务器A,并将服务器A上的数据库备份到服务器B(本人只是作为测试,所以就跳过了锁表)
执行时间3分钟
[root@zhangmeng ~]# mysqldump -h192.168.1.1-ubob -p --skip-add-locks test >/root/cp_test.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@zhangmeng ~]#
3.大概1165M,备份之间在3分钟左右
[root@zhangmeng ~]# ll total 1192548 -rw-------. 1 root root 959 Jun 22 11:24 anaconda-ks.cfg -rw-r--r--. 1 root root 1221135418 Aug 18 17:29 cp_test.sql -rw-r--r--. 1 root root 8785 Jun 22 11:24 install.log -rw-r--r--. 1 root root 3161 Jun 22 11:24 install.log.syslog -rw-r--r--. 1 root root 5824 Nov 12 2015 mysql-community-release-el6-5.noarch.rpm
4.将刚才的备份文件还原到服务器B的cp_test库上
[root@zhangmeng ~]# mysql -f -uroot -p cp_test <cp_test.sql Enter password: ERROR 1839 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON.
5.,提示报错,上网查资料,加上-f参数虽然报错,但是仍会执行,其实可以在命令前面加上time,这样可以准确知道具体执行时间
[root@zhangmeng ~]# mysql -uroot -p test <cp_test.sql Enter password: ERROR 1839 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON.