数据库 备份

常规备份

1 导出一个数据库的结构
mysqldump -d dbname -uroot -p > dbname.sql
2 导出多个数据库的结构
mysqldump -d -B dbname1 dbname2 -uroot -p > dbname.sql
3 导出一个数据库中数据（不包含结构）
mysqldump -t dbname -uroot -p > dbname.sql
4 导出多个数据库中数据（不包含结构）
mysqldump -t -B dbname1 dbname2 -uroot -p > dbname.sql
5 导出一个数据库的结构以及数据
mysqldump dbname -uroot -p > dbname.sql
6 导出多个数据库的结构以及数据
mysqldump -B dbname1 dbname2 -uroot -p > dbname.sql
7 导出一个数据库中一个表的结构
mysqldump -d dbname1 tablename -uroot -p > tablename.sql
8 导出一个数据库中多个表的结构
mysqldump -d -B dbname1 –tables tablename1 tablename2 -uroot -p > tablename.sql
9 导出一个数据库中一个表的数据（不包含结构）
mysqldump -t dbname1 tablename -uroot -p > tablename.sql
10 导出一个数据库中多个表的数据（不包含结构）
mysqldump -t -B dbname1 –tables tablename1 tablename2 -uroot -p > tablename.sql
11 导出一个数据库中一个表的结构以及数据
mysqldump dbname1 tablename -uroot -p > tablename.sql
12 导出一个数据库中多个表的结构以及数据
mysqldump -B dbname1 –tables tablename1 tablename2 -uroot -p > tablename.sql
 
存储过程&函数操作
1 只导出存储过程和函数(不导出结构和数据，要同时导出结构的话，需要同时使用-d)
mysqldump -R -ndt dbname -u root -p > dbname.sql
2 只导出事件
mysqldump -E -ndt dbname -u root -p > dbname.sql
3 不导出触发器（触发器是默认导出的–triggers，使用–skip-triggers屏蔽导出触发器）
mysqldump –skip-triggers dbname1 -u root -p > dbname.sql

其他备份

#!/bin/bash
# ./output_db_object_definition.sh > /dev/null 2>&1
db_user=""
db_pwd=""
db_host=""
db_port=3306
db_name="" 
save_file="/root/${db_name}_object_definition.sql"
output_type='view,function,procedure,event,trigger' # view,function,procedure,event,trigger
 
(cat <<out
/*
ouput object‘s definition for database "$db_name"
ouput time: $(date "+%Y-%m-%d %H:%M:%S")
ouput object type: $output_type
*/
out
)>$save_file
echo "">> $save_file
echo "">> $save_file
 
# 视图
if [[ $output_type == *"view"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- views" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views where table_schema='$db_name'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/Create View: /kk_begin\n/g' | sed 's/[ ]*character_set_client:/;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'  >> $save_file
fi
 
# 函数
if [[ $output_type == *"function"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- function" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE FUNCTION ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='FUNCTION'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/Create Function: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
 
fi
 
# 存储过程
if [[ $output_type == *"procedure"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- procedure" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE PROCEDURE ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='PROCEDURE'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/Create Procedure: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# 事件
if [[ $output_type == *"event"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- event" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE EVENT ',EVENT_SCHEMA,'.',EVENT_NAME,';') from information_schema.events where EVENT_SCHEMA='$db_name'" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/Create Event: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# 触发器
if [[ $output_type == *"trigger"* ]]
then
	echo "-- ------------------------------------------------------------" >> $save_file
	echo "-- trigger" >> $save_file
	echo "-- ------------------------------------------------------------" >> $save_file
	mysql -h$db_host -P$db_port -u$db_user -p$db_pwd --skip-column-names \
	-e "select concat('SHOW CREATE TRIGGER ',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';') from information_schema.triggers where TRIGGER_SCHEMA='$db_name';" |\
	sed 's/;/\\G/g' | mysql -h$db_host -P$db_port -u$db_user -p$db_pwd |\
	sed 's/SQL Original Statement: /kk_begin\ndelimiter $$\n/g' | sed 's/[ ]*character_set_client:/$$ \ndelimiter ;\nkk_end/g' |\
	sed -n '/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}' >> $save_file
fi
 
# ^M, you need to type CTRL-V and then CTRL-M
sed -i "s/\^M//g" $save_file