【整 1.2.3】 MySQL数据库备份与恢复命令实战

首页 > 代码库 > 【整 1.2.3】 MySQL数据库备份与恢复命令实战

【整 1.2.3】 MySQL数据库备份与恢复命令实战

2024-09-06 03:02:05 211人阅读

【备份单个数据库 - 多种参数】

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

【查看数据库的字符集】

mysql> show variables like ‘character_set%‘;

+--------------------------+-------------------------------------------+

| Variable_name | Value |

+--------------------------+-------------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |

+--------------------------+—————————————————————+

【查看数据库对应的库的字符集】

mysql> show create database oldboy\G

*************************** 1. row ***************************

Database: oldboy

Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.00 sec)

【查看数据库表的结构】

mysql> show create table student\G

*************************** 1. row ***************************

Table: student

Create Table: CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

【备份数据库命令】
[root@localhost ~]# mysqldump -uroot -p‘oldboy123‘ oldboy >/opt/mysql_bak.sql

[root@localhost ~]# ll /opt/

-rw-r--r--. 1 root root 3221 Feb 16 21:02 mysql_bak.sql

【备份数据库命令】【检查备份的数据库是否正确】【备份数据库同时检查备份的数据库是否正确】

【可以不操作，只是在检查的时候显示正常的】

[root@localhost ~]# mysqldump -uroot -p‘oldboy123‘ oldboy >/opt/mysql_bak.sql

[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `student` WRITE;

INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);

UNLOCK TABLES;

【备份的时候指定字符集备份（导出）】

【这样在检查导出的数据库的时候就不会出现乱码的情况，指定的字符集为数据库表的字符集】

【恢复备份的数据，同时检查恢复的数据】

[root@localhost /]# mysql -uroot -poldboy123 oldboy < /opt/mysql_bak.sql

[root@localhost /]# mysql -uroot -poldboy123 -e "select * from oldboy.student";

+------+-----------+

| id | name |

+------+-----------+

| 3 | 测试003 |

| 1 | 测试001 |

| 2 | 测试002 |

| 4 | 测试004 |

| 5 | 测试005 |

+------+-----------+

【加-B 备份数据库】

[root@localhost ~]# mysqldump -uroot -poldboy123 -B oldboy >/opt/mysql_bak_b.sql

[root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak_b.sql

USE `oldboy`;

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `student` WRITE;

INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);

UNLOCK TABLES;

【加B 参数的作用是增加创建数据库和连接数据库的命令】

【以及利用加B 进行数据恢复】

【指定压缩命令压缩备份的MySQL数据】

[root@localhost ~]# mysqldump -uroot -poldboy123 -B oldboy|gzip >/opt/mysql_bak_b.sql.gz

[root@localhost ~]# ll /opt/

-rw-r--r--. 1 root root 2058 Feb 16 22:48 mysql_bak_b.sql

-rw-r--r--. 1 root root 771 Feb 16 22:57 mysql_bak_b.sql.gz

【数据库备份的过程重点】

1.导出数据的时候用-B 参数

2，用gzip对备份的数据压缩

5.1.3 mysqldump的工作原理

利用mysqldump 命令备份数据的过程，实际上就是把数据从MySQL库里以逻辑的sql语句的形式直接输出或者生成的备份的文件的过程

-B 参数说明:

【多库备份的步骤】

【1.查看数据库】

[root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| oldboy |

| performance_schema |

| t_a |

| test |

+——————————+

【2.过滤不需要的信息】

[root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"

mysql

oldboy

t_a

test

[root@lo

【3.替换要执行的语句】

[root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed ‘s#^#mysqldump -uroot -poldboy123 -B #g‘

mysqldump -uroot -poldboy123 -B mysql

mysqldump -uroot -poldboy123 -B oldboy

mysqldump -uroot -poldboy123 -B t_a

mysqldump -uroot -poldboy123 -B test

【4.替换成指定的备份路径】

[root@localhost opt]# mysql -uroot -poldboy123 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r ‘s#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1 >/opt/\1#g‘

mysqldump -uroot -poldboy123 -B mysql >/opt/mysql

mysqldump -uroot -poldboy123 -B oldboy >/opt/oldboy

mysqldump -uroot -poldboy123 -B t_a >/opt/t_a

mysqldump -uroot -poldboy123 -B test >/opt/test

【5.替换成指定备份的路径，压缩备份】

mysqldump -uroot -poldboy123 -B mysql|gzip >/opt/mysql.sql.gz

mysqldump -uroot -poldboy123 -B oldboy|gzip >/opt/oldboy.sql.gz

mysqldump -uroot -poldboy123 -B t_a|gzip >/opt/t_a.sql.gz

mysqldump -uroot -poldboy123 -B test|gzip >/opt/test.sql.gz

【6。执行命令】

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

【7.检查备份的结果】

[root@localhost opt]# ll /opt/

total 160

-rw-r--r--. 1 root root 144306 Feb 16 23:23 mysql.sql.gz

-rw-r--r--. 1 root root 769 Feb 16 23:23 oldboy.sql.gz

drwxr-xr-x. 2 root root 4096 Mar 26 2015 rh

-rw-r--r--. 1 root root 748 Feb 16 23:23 t_a.sql.gz

-rw-r--r--. 1 root root 508 Feb 16 23:23 test.sql.gz

【7.忽略警告信息】

[root@localhost opt]#

【备份单个表】

[root@localhost mysql]# mysqldump -uroot -poldboy123 --compact oldboy student

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);

【备份数据库的表结构（不包含数据）】

[root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -B -d oldboy

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `oldboy`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

[root@localhost mysql]#

【指定库中的某一个表的结构进行备份】

[root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -d oldboy student

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `student` (

`id` int(11) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

【 -T 分开到处数据和结构】

【直接备份数据】

【-t 参数】

[root@localhost mysql]# mysqldump -uroot -poldboy123 --compact -t oldboy student

INSERT INTO `student` VALUES (3,‘测试003‘),(1,‘测试001‘),(2,‘测试002‘),(4,‘测试004‘),(5,‘测试005‘);

【-A 】整个备份数据库 -包括可和表

[root@localhost mysql]# mysqldump -uroot -poldboy123 -A -B --events|gzip >/opt/a.sql.gz

[root@localhost mysql]# ll /opt/

total 308

-rw-r--r--. 1 root root 144664 Feb 17 01:45 a.sql.gz

drwxr-xr-x. 2 root root 4096 Feb 16 23:25 bak

在 my.cnf 中打开log日志记录

log-bin=mysql-bin 会将MySQL中的所有更改记录下来

【MySQL 日志的路径】

[root@localhost data]# pwd

/application/mysql/data

-rw-rw----. 1 mysql mysql 541 Feb 14 18:33 mysql-bin.000001

-rw-rw----. 1 mysql mysql 11815 Feb 14 22:24 mysql-bin.000002

-rw-rw----. 1 mysql mysql 3557 Feb 16 22:51 mysql-bin.000003

-rw-rw----. 1 mysql mysql 1851 Feb 17 01:31 mysql-bin.000004

-rw-rw----. 1 mysql mysql 76 Feb 16 22:51 mysql-bin.index

【 -F 刷新binlog】

[root@localhost data]# mysqldump -uroot -poldboy123 -A -B -F --events|gzip >/opt/a.sql.gz

【 - -master-data=http://www.mamicode.com/1 在备份的时候会自动记录binlog的当前位置】

[root@localhost data]# mysqldump -uroot -poldboy123 --master-data=http://www.mamicode.com/1 --compact oldboy

CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000008‘, MASTER_LOG_POS=107;

/*!40101 SET @saved_cs_client = @@character_set_client */;

【查看binlog记录】

mysqlbinlog

【总结：MySQL语句总结整理】

【 -T 分开到处数据和结构】

【 -t 直接备份数据】

【 -A 备份所有库】

【 -B 指定多个库，增加建库语句和use语句备份库】

【 -d 只备份表】 -d oldboy student

【 —compact 去掉注释，适合调试输出，生产不用。精简内容一般做测试的时候使用】

【 --events 忽略警告信息】

【 -F 刷新binlog日志，也就是会创建一个递增的新的日志文件切割（刷新）binlog 】

【 - -master-data=http://www.mamicode.com/1 增加binlog日志文件名以及对应的位置点在备份的时候会自动记录binlog的当前位置】

【 -x - - lock-all-tables 锁表】

【 -e 在数据库外面执行sql语句】

【生产场景备份】

myisam 备份命令

mysqldump -uroot -poldboy123 -A -B —master-data=http://www.mamicode.com/2 -x --events|gzip >/opt/all.sql.gz

innodb 备份命令：推荐使用的

mysqldump -uroot -poldboy123 -A -B —master-data=http://www.mamicode.com/2 -x —events —single-transaction|gzip >/opt/all.sql.gz

【专业DB 备份操作】

5.2 恢复数据库实践

【恢复命令 source】

mysql> source /opt/mysql.b.sql

Query OK, 1 row affected (0.00 sec)

【解压备份的压缩文件】

gzip -d msyql.bak.sql.gz

将备份出来的所有的库，恢复到数据库中

[root@localhost data]# for dbname in `ls *.sql|sed ‘s#_bak.sql##g‘`;do mysql -uroot -poldboy123 < ${dbname}_bak.sql;done

【MySQL 系统一些命令】

【查看当前链接数据库的用户以及数量】

[root@localhost data]# mysql -uroot -poldboy123 -e "show processlist;"

+----+------+-----------+------+---------+-------+-------+------------------+

+----+------+-----------+------+---------+-------+-------+------------------+

【整 1.2.3】 MySQL数据库备份与恢复命令实战

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 【整 1.2.3】 MySQL数据库备份与恢复命令实战

【整 1.2.3】 MySQL数据库备份与恢复命令实战

看完仍有疑问？有类似问题直接问程序猿