首页 > 代码库 > mysql数据库备份与恢复

mysql数据库备份与恢复

mysqldump -u 用户名 -p 数据库名> 备份的文件名

本文中因服务器为多实例,所以在执行登陆等命令时指定了-S参数,即指定其中一个数据库

备份:

mysqldump -u root -proot oldboy>/opt/oldboy.sql

[root@MySQL opt]# mysql -S /data/3307/mysql.sock -uroot -p -e "show variables like %character%"
Enter password: 
+--------------------------+--------------------------------------------------------------+
| Variable_name            |                                                        Value |
+--------------------------+--------------------------------------------------------------+
| character_set_client     | utf8                                                         |
| character_set_connection | utf8                                                         |
| character_set_database   | utf8mb4                                                      |
| character_set_filesystem | binary                                                       |
| character_set_results    | utf8                                                         |
| character_set_server     | utf8mb4                                                      |
| character_set_system     | utf8                                                         |
| character_sets_dir       | /usr/local/mysql-5.7.9-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------+


指定字符集备份:

[root@MySQL opt]# mysqldump -u root -proot --default-character-set=utf8 oldboy>/opt/oldboy.sql

 

备份参数:

-e : 后面可以执行mysql 命令用;分开
-B :参数的作用是增加创建数据库和连接数据库的命令,即create databaseuse database
-d : 备份表结构
-t : 备份数据
-A :all databases
-F : 刷新binlog日志文件,切割binlog
-x,--lock-all-tables 锁表
-l,--lock-tables 只读锁表
--master-data=http://www.mamicode.com/1(=2) : 增加binlog日志文件名及对应的位置点
--compact :去掉注释,适用于debug,生产不用
--single-transaction : 适合innodb事务数据库备份
InnoDB表在备份时,通常启用选项 --single-transaction来保证备份的一致性,实际上它的工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其他会话已经提交了的数据。

库名|gzip:压缩备份(mysql数据都是文本,压缩效率高)

 

myisam备份时,将锁整个表,导致表无法进行访问

还原数据库:

[root@MySQL opt]# mysql -uroot -proot oldboy</opt/oldboy_bak.sql



查看备份内容:

[root@MySQL opt]# egrep -v "#|\*|--|^$" /opt/oldboy.sql


查看字符集:

[root@MySQL opt]# mysql -uroot -proot -e "show variables like %character%"

 

myqsl多库备份:

[root@MySQL opt]# mysqldump -uroot -proot -B (库名)oldboy (库名)ClasssInfo (库名)oldboy_gbk|gzip >/opt/mysql_bak.sql.gz

 


mysql分库备份:
每个库一个备份文件

mysql -uroot -proot -e "show databases;"|grep -Evi "database|info|perfor"|sed -r s#^([a-z].*$)#mysqldump -uroot -proot --events -B \1|gzip >/opt/\1.sql.gz#g|bash

mysql -S /data/3307/mysql.sock -uroot -proot -e "show databases;"|grep -Evi "database|info|perfor"|sed -r s#^([a-z].*$)#mysqldump -uroot -proot -S /data/3307/mysql.sock --events -B \1|gzip >/opt/bak/\1.sql.gz#g|bash

 

shell脚本:

for dbname in mysql -uroot -proot -e "show databases;"|grep -Evi "database|info|perfor"
do
mysqldump -uroot -proot -S /data/3307/mysql.sock --events -B $dbname|gzip >/opt/bak/${dbname}_bak.sql.gz
done

 

备份单个表和多表:
语法:mysqldump -u 用户名 -p 数据库名 表名 表名 表名>备份的文件名

mysqldump -u root -p ClassInfo score>/opt/table1.sql

 

分库分表备份:
shell脚本:

#!/bin/bash
USER=root
PASSWD=root
SOCK=/data/3306/mysql.sock
LOGIN="mysql -u$USER -p$PASSWD -S $SOCK"
DATABASES=`$LOGIN -e "show databases;"|sed "1d"|egrep -v "^.*sch|mysql"`
dump="mysqldump -u$USER -p$PASSWD -S $SOCK"
for database in $DATABASES
do
[ ! -d /back/$database ] && mkdir -p /back/$database
TABLE=`$LOGIN -e "show tables from $database;"|sed 1d`
for table in $TABLE
do    
$dump $database $TABLE|gzip >/back/$database/${database}_${table}_$(date +%F).sql.gz
done

 

只备份表结构和只备份数据:

参数-d 备份表结构
参数-t 备份数据

实例:

[root@MySQL bak]# mysqldump -uroot -proot -S /data/3307/mysql.sock --compact -d abbott t2
mysqldump: [Warning] Using a password on the command line interface can be insecure.
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

[root@MySQL bak]# mysqldump -uroot -proot -S /data/3307/mysql.sock --compact -t abbott t2 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
INSERT INTO `t2` VALUES (1,abbott),(2,zx),(3,bb);

 

备份全部数据库:

mysqldump -u root -proot -A -B --events|gzip >/opt/all.sql.gz

 

myisam与innodb备份的区别:

myisam推荐:
mysqldump -uroot -proot -A -B --master-data=http://www.mamicode.com/2 -x|gzip >/opt/all.sql.gz

mysqldump -uroot -proot -A -B --all-databases --flush-privileges --lock-all-tables --master-data=http://www.mamicode.com/1 --flush-logs --triqqers --routines --events --hex-blob|gzip > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql.gz

innodb推荐:
mysqldump -uroot -proot -A -B --master-data=http://www.mamicode.com/2 --singe-transaction|gzip >/opt/all.sql.gz

mysqldump -uroot -proot --all-databases --flush-privileges --master-data=http://www.mamicode.com/1 --singe-transaction --flush-logs --triqqers --routines --events --hex-blob|gzip >$BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql.gz

 


混合引擎,建议使用myisam的备份方式

 

恢复数据库实战:

利用source命令恢复数据库
进入mysql数据库控制台,mysql -u root -p 登录后
mysql>use 数据库名

然后使用命令source 命令,后面参数为脚本文件,可以利用system ls 查看文件路径

mysql>source /opt/all.sql.gz

 

批量恢复脚本实例:

#!/bin/bash
gzip -d /opt/*.gz

for dbname in `ls *.sql|sed ‘s#_bak.sql##g‘`;
do
mysql -u root -p‘root‘ <${dbname}_bak.sql;
done

 

=========================================================================================
mysql--show命令总结:

show status; 查看当前会话的数据库状态信息
show global status; 查看整个数据库运行状态信息,很重要,要分析并做好监控
show full processlist; 查看正在执行的完整的sql语句
show variables; 查看数据库参数信息


[root@MySQL ~]# mysql -S /data/3307/mysql.sock -uroot -proot -e "show full processlist;"
+----+------+-----------+--------+---------+------+----------+-----------------------+
| Id | User |      Host |     db | Command | Time |    State |                  Info |
+----+------+-----------+--------+---------+------+----------+-----------------------+
| 10 | root | localhost | abbott |   Sleep |   18 |          | NULL                  |
| 13 | root | localhost |   NULL |   Query |    0 | starting | show full processlist |
+----+------+-----------+--------+---------+------+----------+-----------------------+

 

mysql线程中大海捞针

[root@MySQL ~]# mysql -S /data/3307/mysql.sock -uroot -proot -e "show full processlist;"|grep -v Sleep


参数修改先在my.cnf中修改,然后再在数据库中修改,不在my.cnf中修改,重启后失效:

mysql> set global key_buffer_size=1024*1024*32; 
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like key_buffer%;
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)

 

 


---------------------------------------------
mysqlbinlog ->解析mysql的binlog日志
用来记录mysql内部增删改查等对mysql数据库有更新的内容记录。

mysqlbinlog参数:
-d 截取指定库的binlog
按照位置截取:

mysqlbinlog mysqlbin.000046 --start-position=365 --stop-position=456 -r /opt/pos.sql


按照时间截取:

mysqlbinlog mysql-bin.000046 --start-datetime=2017-06-26 20:16:30 --stop-datetime=2017-06-26 20:20:10 -r time.sql

 

mysql数据库备份与恢复