mysql 备份与恢复

1、备份 mysqldump -u x -p x dbname1 > dbname1.date.sql

还原 mysql -uroot -px <x.date.sql

mysqldump -ux -px -B db1>db1.date.sql

只备份表结构：-d

只备份表内容：-t

备份的时候锁表：-x

备份所有数据库： -A

常用备份、压缩：

msyqldump -ux -px -B dbname1|gzip>db1.date.sql.gz mysqldump -ux -px -B db1 table1|gzpi >db1.date.sql.gz 2、生产环境常用备份语句 mysqldump -A -B --master-data=2|gzip >/opt/all_$(date+%F).sql.gz

3、表导入导出 select * from x into outfile 'filename' terminated by '-' character set utf8; system cat filename

load data infile 'filename' into table tblname fields terminated by '-';

4、恢复 source xx.sql gzip -d xx.sql.gz

mysql -e ""

5、查看连接线程 show full processlist; 6、查看和设置超时 show variables like '%_timeout%'; set global wait_timeout=60; set global interactive_timeout=60;

7、binlog生成sql mysqlbinlog -d dbname1 xx-bin.000004 -r x.sql

--start-position --stop-position

--start-datetime --stop-datetime

8、恢复 iptables -I INPUT -p tcp --dport 3306 ! -s 127.0.0.1 -j DROP