centos mysql备份scp_centos 7 MySQL数据库备份之mysqlhotcopy安装

使用mysqlhotcopy命令可以进行MySQL数据库备份，它使用FLUSH TABLES、LOCK TABLES和cp或scp来进行数据库备份，这是一种快速备份数据库或表的方法，用于备份MyISAM和ARCHIVE表。

MySQLhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包.

安装perl-DBI 和 perl-DBD-MySQL,否则在运行mysqlhotcopy时会保错

[root@localhost ~]# mysqlhotcopy -u root -p mysqlname /opt/sql_back/

Warning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version.

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ( 11) line 3.

Perhaps the DBD::mysql perl module hasn't been fully installed,

or perhaps the capitalisation of 'mysql' isn't right.

Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.

at /usr/bin/mysqlhotcopy line 199.

安装方法

[root@localhost ~]# yum install perl-DBD* -y

备份

[root@localhost ~]# /bin/mysqlhotcopy -u root -p '' --addtodest mysqlname /opt/sql_back/

还原

[root@localhost~]# cp -arp /opt/sql_back/mysqlname /var/lib/mysql/        --将备份的数据移到mysql数据根目录

备份一个数据库到一个目录中

[root@localhost~]# mysqlhotcopy -u root -p system tong /opt/

[root@localhost~]# mysqlhotcopy -u [用户名] -p [密码] [数据库名] [目标目录]

备份多个数据库到一个目录中

[root@localhost~]# mysqlhotcopy -u root -p system tong mysql /opt/tong

[root@localhost~]# mysqlhotcopy -u [用户名] -p [密码] [数据库名1 数据库名2...] [目标目录]

备份数据库中某一个表

[root@localhost~]# mysqlhotcopy -u root -p system mysql./user*/ /opt/

[root@localhost~]# mysqlhotcopy -u [用户名] -p [密码] [数据库名]./[表名]/ [目标目录]