MySQL数据库自带了一个很方便备份的命令: mysqldump,其备份数据就是把数据从数据库里以逻辑的sql语句的形式直接输出或者生成备份文件的过程,属于逻辑备份,而对文件系统的数据进行打包属于物理备份,基本格式:
mysqldump -u {user_name} -p {user_passwd} {database_name} > /{path}/{file_name}.sql
恢复:
mysql -u {user_name} -p {user_passwd} {database_name} < /{path}/{file_name}.sql
上面的备份没有创建数据库,可以加-B参数来备份创建数据库的语句,这种情况可以备份多个数据库,因为会加上建库语句(备份多个数据库,只需要以空格隔开多个数据库名即可):
mysqldump -u {user_name} -p {user_passwd} -B {database_name} > /{path}/{file_name}.sql
这种情况下,恢复可以不需要指定库:
mysql -u {user_name} -p {user_passwd} < /{path}/{file_name}.sql
备份时,在数据库名后可以设置压缩:
mysqldump -u {user_name} -p {user_passwd} -B {database_name}|gzip > /{path}/{file_name}.sql.gz
如果数据类型有blob类型,需要加参数--hex-blob,综合示例:
date_now=$(date +%Y%m%d%H%M)
db_name=`mysql -u${db_user} -P${db_port} -p123456789 -e "show databases;" | grep 'linjk'`
mysqldump -u${db_user} -P${db_port} -p123456789 --hex-blob --single-transaction -B ${db_name}|gzip > ./${db_name}_${date_now}.sql.gz
分库备份
可以多次执行一个备份库的语句,但是多个库时就麻烦了,可以执行如下命令批量备份:
mysql -uroot -p'123456' -e "show databases;"| grep -Evi "database|info|perfor"|sed -r ' s#^([a-z].*$)#mysqldump -uroot -p'123456' --events -B \1|gzip > /opt/\1.sql.gz#g'| bash
这里指定备份时不备份数据库名包含database或info或perfor的数据库,命令执行后如下:
使用分库备份,可以区分每个库,在一个数据库出问题时,容易排查,因此建议分库备份而不使用一个文件。
备份单个表
mysqldump -u {user_name} -p {user_passwd} {database_name} {table_name} > /{path}/{file_name}.sql (注意:这时候不需要-B参数)
加-t参数可以只备份这个表的数据
备份多个表
和备份多个库类型原理,但是有个缺点就是文件比较多,一般按需求处理。
备份数据库表结构
备份加参数-d即可:
mysqldump -u {user_name} -p {user_passwd} -d {database_name} > /{path}/{file_name}.sql
全备份
mysqldump -u {user_name} -p {user_passwd} -A -B --events|gzip > /{path}/{file_name}.sql.gz
可以加-F参数刷新、切割binlog
增量备份时,需要找到备份点,可以加参数--master-data=2,会输出备份文件和备份的位置,然后从那个位置往后备份即可。
另外,还有-x参数、-l参数,用于锁表,具体可以查看mysqldump的帮助: mysqldump --help,对于InnoDB事务数据库的备份,通常还加--single-transaction参数来保证本次备份操作不会看到其他会话已经提交了的数据,从而保证数据一致性,而对于MyISam数据库引擎来说,通常加-x参数锁表来保证数据一致。但是,如果一个数据库使用了两种存储引擎,备份时要以myisam为主,即加-x参数,但现在一般建议使用InnoDB了。
----------------------------- 华丽丽的分界线(以上为备份,以下为恢复) -----------------------------
登录数据库后,可以使用source命令来恢复数据库: source /opt/haha.sql
(在登录数据库后,可使用system {command}来执行linux的常用命令)
----------------------------- 华丽丽的分界线 -----------------------------
扩展:
1. 查看数据库连接情况: show processlist; 或: show full processlist;
2. 查看当前连接(会话)的状态: show status;
3. 查看全局状态: show global status;
4. 登录后更改数据库参数,且要求重启后参数还能生效:修改my.cnf即可,数据库会自己读取