1.冷备份

停库，备份

# which mysqladmin

# mysqladmin -uroot -pOracle123 shutdown

# ps -ef|grep mysql

进入数据文件所在位置进行物理cp或者scp

2.热备份

2.1 mysqldump备份

它的备份过程是先从buffer中找到需要备份的数据进行备份，不然就将数据文件中缓存到buff中进行备份 全库备份# mysqldump --single-transaction -uroot -pOracle123 -A > all_20200310.sql全库恢复# mysql -uroot -pOracle123 < all_20200310.sql 单库备份TEST# mysqldump --single-transaction -uroot -pOracle123 TEST > TEST_20200310.sql单库恢复# mysql -uroot -pOracle123 TEST < TEST_20200310.sql 注：数据库TEST如果被删除了，需要先创建空库TEST   备份单表TEST.TEST# mysqldump --single-transaction -uroot -pOracle123 TEST TEST > TEST.TEST_20200310.sql单表还原# mysql -uroot -pOracle123 TEST < TEST_20200310.sql  备份表结构TEST# mysqldump --single-transaction -uroot -pOracle123 TEST TEST -d >TEST_META_20200310.sql 备份表内容TEST# mysqldump --single-transaction -uroot -pOracle123 TEST TEST -t >TEST_DATA_20200310.sql 按要求备份TEST# mysqldump --single-transaction -uroot -pOracle123 TEST TEST -- where="id>3" >TEST_WHERE_20200310.sql 

2.2 select …into outfile

注：先确保secure_file_priv参数不为NULL，否则设置 secure-file-priv="" mysql> select * from TEST into outfile '/tmp/test.sql';mysql> delete from TEST;mysql> commit;mysql> load data infile '/tmp/test.sql' into table TEST;mysql> COMMIT; 或者# mysql -uroot -pOracle123 -e "load data infile '/tmp/test.sql' into table TEST.TEST" 

2.3 mydumper

下载地址 http://rpm.pbone.net/index.php3/stat/4/idpl/55203713/dir/redhat_el_7/com/mydumper-0.9.1-4.1.x86_64.rpm.html # yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel cmake make# cd /u01# yum install -y mydumper-0.9.1-4.1.x86_64.rpm# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock 备份TEST数据库# mydumper -u root -p Oracle123 -B TEST -o /u01/bak  还原TEST数据库mysql> drop database TEST;# myloader -u root -p Oracle -B TEST -d /u01/bak/   备份TEST表# mydumper -u root -p Oracle123 -B TEST -T TEST -o /u01/bak/还原TEST表# myloader -u root -p Oracle123 -B TEST -o TEST -d /u01/bak/