假设要将服务器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分钟
[[email protected] ~]# 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. [[email protected] ~]#
3.大概1165M，备份之间在3分钟左右
[[email protected] ~]# 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库上
[[email protected] ~]# 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，这样可以准确知道具体执行时间
[[email protected] ~]# 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.
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/database/4904.html