mariadb 还原mysql_MySQL/MariaDB数据库的mysqldump工具备份还原实战

最新推荐文章于 2021-06-22 11:35:42 发布
最新推荐文章于 2021-06-22 11:35:42 发布
阅读量203 收藏
点赞数
文章标签： mariadb 还原mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31006689/article/details/113902641
版权
该博客记录了使用mysqldump进行数据库备份的命令，以及解压缩备份文件的过程。内容包括查看数据库文件、解压SQL文件、解析MySQL系统表结构，如用户权限、数据库、表、索引等详细信息。此外，还展示了如何通过shell脚本进行数据库备份操作。
摘要由CSDN通过智能技术生成

[root@node105.yinzhengjie.org.cn ~]# mysqldump -A --master-data=2 -F -d -uroot -pyinzhengjie -S /mysql/3307/socket/mysql.sock | xz > /root/alldb.sql.xz

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll

total8

-rw-r--r-- 1 root root 4344 Nov 6 08:37alldb.sql.xz

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# xz -d alldb.sql.xz

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# ll

total36

-rw-r--r-- 1 root root 34967 Nov 6 08:37alldb.sql

[root@node105.yinzhengjie.org.cn~]#

[root@node105.yinzhengjie.org.cn~]# catalldb.sql-- MySQL dump 10.16 Distrib 10.2.19-MariaDB, forLinux (x86_64)--

--Host: localhost Database:-- ------------------------------------------------------

-- Server version 10.2.19-MariaDB-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;/*!40101 SET NAMES utf8*/;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE*/;/*!40103 SET TIME_ZONE='+00:00'*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0*/;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0*/;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0*/;--

-- Position to start replication or point-in-timerecovery from--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=385;--

--GTID to start replication from--

-- SET GLOBAL gtid_slave_pos='0-1-331';--

--Current Database: `mysql`--CREATE DATABASE/*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1*/;

USE `mysql`;--

-- Table structure fortable `column_stats`--DROP TABLE IF EXISTS `column_stats`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `column_stats` (

`db_name` varchar(64) COLLATE utf8_bin NOT NULL,

`table_name` varchar(64) COLLATE utf8_bin NOT NULL,

`column_name` varchar(64) COLLATE utf8_bin NOT NULL,

`min_value` varbinary(255) DEFAULT NULL,

`max_value` varbinary(255) DEFAULT NULL,

`nulls_ratio`decimal(12,4) DEFAULT NULL,

`avg_length`decimal(12,4) DEFAULT NULL,

`avg_frequency`decimal(12,4) DEFAULT NULL,

`hist_size` tinyint(3) unsigned DEFAULT NULL,

`hist_type` enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') COLLATE utf8_bin DEFAULT NULL,

`histogram` varbinary(255) DEFAULT NULL,

PRIMARY KEY (`db_name`,`table_name`,`column_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `columns_priv`--DROP TABLE IF EXISTS `columns_priv`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `columns_priv` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Table_name`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`Column_name`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',

PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `db`--DROP TABLE IF EXISTS `db`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `db` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`Db`,`User`),

KEY `User` (`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `event`--DROP TABLE IF EXISTS `event`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `event` (

`db`char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`name`char(64) NOT NULL DEFAULT '',

`body` longblob NOT NULL,

`definer`char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`execute_at` datetime DEFAULT NULL,

`interval_value`int(11) DEFAULT NULL,

`interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SE

COND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL, `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`last_executed` datetime DEFAULT NULL,

`starts` datetime DEFAULT NULL,

`ends` datetime DEFAULT NULL,

`status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',

`on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',

`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBT

RACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT'', `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT'',

`originator` int(10) unsigned NOT NULL,

`time_zone`char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',

`character_set_client`char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`collation_connection`char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`db_collation`char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`body_utf8` longblob DEFAULT NULL,

PRIMARY KEY (`db`,`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `func`--DROP TABLE IF EXISTS `func`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `func` (

`name`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`ret` tinyint(1) NOT NULL DEFAULT 0,

`dl`char(128) COLLATE utf8_bin NOT NULL DEFAULT '',

`type` enum('function','aggregate') CHARACTER SET utf8 NOT NULL,

PRIMARY KEY (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User defined functions';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `gtid_slave_pos`--DROP TABLE IF EXISTS `gtid_slave_pos`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `gtid_slave_pos` (

`domain_id`int(10) unsigned NOT NULL,

`sub_id` bigint(20) unsigned NOT NULL,

`server_id`int(10) unsigned NOT NULL,

`seq_no` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`domain_id`,`sub_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Replication slave GTID position';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `help_category`--DROP TABLE IF EXISTS `help_category`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `help_category` (

`help_category_id` smallint(5) unsigned NOT NULL,

`name`char(64) NOT NULL,

`parent_category_id` smallint(5) unsigned DEFAULT NULL,

`url` text NOT NULL,

PRIMARY KEY (`help_category_id`),

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help categories';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `help_keyword`--DROP TABLE IF EXISTS `help_keyword`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `help_keyword` (

`help_keyword_id`int(10) unsigned NOT NULL,

`name`char(64) NOT NULL,

PRIMARY KEY (`help_keyword_id`),

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help keywords';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `help_relation`--DROP TABLE IF EXISTS `help_relation`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `help_relation` (

`help_topic_id`int(10) unsigned NOT NULL,

`help_keyword_id`int(10) unsigned NOT NULL,

PRIMARY KEY (`help_keyword_id`,`help_topic_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='keyword-topic relation';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `help_topic`--DROP TABLE IF EXISTS `help_topic`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `help_topic` (

`help_topic_id`int(10) unsigned NOT NULL,

`name`char(64) NOT NULL,

`help_category_id` smallint(5) unsigned NOT NULL,

`description` text NOT NULL,

`example` text NOT NULL,

`url` text NOT NULL,

PRIMARY KEY (`help_topic_id`),

UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='help topics';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `host`--DROP TABLE IF EXISTS `host`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `host` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`Db`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `index_stats`--DROP TABLE IF EXISTS `index_stats`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `index_stats` (

`db_name` varchar(64) COLLATE utf8_bin NOT NULL,

`table_name` varchar(64) COLLATE utf8_bin NOT NULL,

`index_name` varchar(64) COLLATE utf8_bin NOT NULL,

`prefix_arity`int(11) unsigned NOT NULL,

`avg_frequency`decimal(12,4) DEFAULT NULL,

PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `innodb_index_stats`--DROP TABLE IF EXISTS `innodb_index_stats`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `innodb_index_stats` (

`database_name` varchar(64) COLLATE utf8_bin NOT NULL,

`table_name` varchar(199) COLLATE utf8_bin NOT NULL,

`index_name` varchar(64) COLLATE utf8_bin NOT NULL,

`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,

`stat_value` bigint(20) unsigned NOT NULL,

`sample_size` bigint(20) unsigned DEFAULT NULL,

`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `innodb_table_stats`--DROP TABLE IF EXISTS `innodb_table_stats`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `innodb_table_stats` (

`database_name` varchar(64) COLLATE utf8_bin NOT NULL,

`table_name` varchar(199) COLLATE utf8_bin NOT NULL,

`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`n_rows` bigint(20) unsigned NOT NULL,

`clustered_index_size` bigint(20) unsigned NOT NULL,

`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`database_name`,`table_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `plugin`--DROP TABLE IF EXISTS `plugin`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `plugin` (

`name` varchar(64) NOT NULL DEFAULT '',

`dl` varchar(128) NOT NULL DEFAULT '',

PRIMARY KEY (`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `proc`--DROP TABLE IF EXISTS `proc`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `proc` (

`db`char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`name`char(64) NOT NULL DEFAULT '',

`type` enum('FUNCTION','PROCEDURE') NOT NULL,

`specific_name`char(64) NOT NULL DEFAULT '',

`language` enum('SQL') NOT NULL DEFAULT 'SQL',

`sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',

`is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',

`security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',

`param_list` blob NOT NULL,

`returns` longblob NOT NULL,

`body` longblob NOT NULL,

`definer`char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`modified` timestamp NOT NULL DEFAULT'0000-00-00 00:00:00',

`sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBT

RACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT'', `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

`character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`collation_connection`char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`db_collation`char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`body_utf8` longblob DEFAULT NULL,

PRIMARY KEY (`db`,`name`,`type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `procs_priv`--DROP TABLE IF EXISTS `procs_priv`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `procs_priv` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Routine_name`char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,

`Grantor`char(141) COLLATE utf8_bin NOT NULL DEFAULT '',

`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',

`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),

KEY `Grantor` (`Grantor`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `proxies_priv`--DROP TABLE IF EXISTS `proxies_priv`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `proxies_priv` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Proxied_host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Proxied_user`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`With_grant` tinyint(1) NOT NULL DEFAULT 0,

`Grantor`char(141) COLLATE utf8_bin NOT NULL DEFAULT '',

`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),

KEY `Grantor` (`Grantor`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `roles_mapping`--DROP TABLE IF EXISTS `roles_mapping`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `roles_mapping` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Role`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Admin_option` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

UNIQUE KEY `Host` (`Host`,`User`,`Role`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Granted roles';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `servers`--DROP TABLE IF EXISTS `servers`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `servers` (

`Server_name`char(64) NOT NULL DEFAULT '',

`Host`char(64) NOT NULL DEFAULT '',

`Db`char(64) NOT NULL DEFAULT '',

`Username`char(80) NOT NULL DEFAULT '',

`Password`char(64) NOT NULL DEFAULT '',

`Port`int(4) NOT NULL DEFAULT 0,

`Socket`char(64) NOT NULL DEFAULT '',

`Wrapper`char(64) NOT NULL DEFAULT '',

`Owner`char(64) NOT NULL DEFAULT '',

PRIMARY KEY (`Server_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `table_stats`--DROP TABLE IF EXISTS `table_stats`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `table_stats` (

`db_name` varchar(64) COLLATE utf8_bin NOT NULL,

`table_name` varchar(64) COLLATE utf8_bin NOT NULL,

`cardinality` bigint(21) unsigned DEFAULT NULL,

PRIMARY KEY (`db_name`,`table_name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `tables_priv`--DROP TABLE IF EXISTS `tables_priv`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `tables_priv` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Table_name`char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`Grantor`char(141) COLLATE utf8_bin NOT NULL DEFAULT '',

`Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),

`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')

CHARACTER SET utf8 NOT NULL DEFAULT'', `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',

PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),

KEY `Grantor` (`Grantor`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `time_zone`--DROP TABLE IF EXISTS `time_zone`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `time_zone` (

`Time_zone_id`int(10) unsigned NOT NULL AUTO_INCREMENT,

`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',

PRIMARY KEY (`Time_zone_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `time_zone_leap_second`--DROP TABLE IF EXISTS `time_zone_leap_second`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `time_zone_leap_second` (

`Transition_time` bigint(20) NOT NULL,

`Correction`int(11) NOT NULL,

PRIMARY KEY (`Transition_time`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `time_zone_name`--DROP TABLE IF EXISTS `time_zone_name`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `time_zone_name` (

`Name`char(64) NOT NULL,

`Time_zone_id`int(10) unsigned NOT NULL,

PRIMARY KEY (`Name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `time_zone_transition`--DROP TABLE IF EXISTS `time_zone_transition`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `time_zone_transition` (

`Time_zone_id`int(10) unsigned NOT NULL,

`Transition_time` bigint(20) NOT NULL,

`Transition_type_id`int(10) unsigned NOT NULL,

PRIMARY KEY (`Time_zone_id`,`Transition_time`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `time_zone_transition_type`--DROP TABLE IF EXISTS `time_zone_transition_type`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `time_zone_transition_type` (

`Time_zone_id`int(10) unsigned NOT NULL,

`Transition_type_id`int(10) unsigned NOT NULL,

`Offset`int(11) NOT NULL DEFAULT 0,

`Is_DST` tinyint(3) unsigned NOT NULL DEFAULT 0,

`Abbreviation`char(8) NOT NULL DEFAULT '',

PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `user`--DROP TABLE IF EXISTS `user`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `user` (

`Host`char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`Password`char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions`int(11) unsigned NOT NULL DEFAULT 0,

`max_updates`int(11) unsigned NOT NULL DEFAULT 0,

`max_connections`int(11) unsigned NOT NULL DEFAULT 0,

`max_user_connections`int(11) NOT NULL DEFAULT 0,

`plugin`char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',

`authentication_string` text COLLATE utf8_bin NOT NULL,

`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`default_role`char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

`max_statement_time`decimal(12,6) NOT NULL DEFAULT 0.000000,

PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `general_log`--

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

CREATE TABLE IF NOT EXISTS `general_log` (

`event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

`user_host` mediumtext NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL,

`server_id`int(10) unsigned NOT NULL,

`command_type` varchar(64) NOT NULL,

`argument` mediumtext NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `slow_log`--

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

CREATE TABLE IF NOT EXISTS `slow_log` (

`start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),

`user_host` mediumtext NOT NULL,

`query_time`time(6) NOT NULL,

`lock_time`time(6) NOT NULL,

`rows_sent`int(11) NOT NULL,

`rows_examined`int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id`int(11) NOT NULL,

`insert_id`int(11) NOT NULL,

`server_id`int(10) unsigned NOT NULL,

`sql_text` mediumtext NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL,

`rows_affected`int(11) NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';/*!40101 SET character_set_client = @saved_cs_client*/;--

--Current Database: `testdb`--CREATE DATABASE/*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8*/;

USE `testdb`;--

-- Table structure fortable `students`--DROP TABLE IF EXISTS `students`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `students` (

`StuID`int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID`int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

--Current Database: `yinzhengjiedb`--CREATE DATABASE/*!32312 IF NOT EXISTS*/ `yinzhengjiedb` /*!40100 DEFAULT CHARACTER SET utf8*/;

USE `yinzhengjiedb`;--

-- Table structure fortable `classes`--DROP TABLE IF EXISTS `classes`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `classes` (

`ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

`Class` varchar(100) DEFAULT NULL,

`NumOfStu` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`ClassID`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `coc`--DROP TABLE IF EXISTS `coc`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `coc` (

`ID`int(10) unsigned NOT NULL AUTO_INCREMENT,

`ClassID` tinyint(3) unsigned NOT NULL,

`CourseID` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `courses`--DROP TABLE IF EXISTS `courses`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `courses` (

`CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Course` varchar(100) NOT NULL,

PRIMARY KEY (`CourseID`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `scores`--DROP TABLE IF EXISTS `scores`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `scores` (

`ID`int(10) unsigned NOT NULL AUTO_INCREMENT,

`StuID`int(10) unsigned NOT NULL,

`CourseID` smallint(5) unsigned NOT NULL,

`Score` tinyint(3) unsigned DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `students`--DROP TABLE IF EXISTS `students`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `students` (

`StuID`int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID`int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `teachers`--DROP TABLE IF EXISTS `teachers`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `teachers` (

`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(100) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') DEFAULT NULL,

PRIMARY KEY (`TID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;--

-- Table structure fortable `toc`--DROP TABLE IF EXISTS `toc`;/*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `toc` (

`ID`int(10) unsigned NOT NULL AUTO_INCREMENT,

`CourseID` smallint(5) unsigned DEFAULT NULL,

`TID` smallint(5) unsigned DEFAULT NULL,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client*/;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE*/;/*!40101 SET SQL_MODE=@OLD_SQL_MODE*/;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES*/;-- Dump completed on 2019-11-06 8:37:57[root@node105.yinzhengjie.org.cn~]#

钢盅郭子
关注
centos7 mysql dump还原_CentOS 7.6使用mysqldump备份恢复MariaDB
weixin_35678674的博客
02-05 758
一、mysqldump简介：mysqldump是MariaDB自带的客户端逻辑备份工具，通过mysql协议连接至mysqld，将需要备份的数据查询出来，并转换成对应的insert语句，当需要恢复这些数据时，只要执行这些insert语句，适用于数据量较小的场景。逻辑备份是指备份SQL语句，在恢复时执行备份SQL，从而实现数据库数据的重现。备份完成后生成的SQL文件可以使用文本编辑工具直接修改。二、m...
mysql安装/mariadb安装
weixin_72898921的博客
07-23 961
mysql基础1 （安装）
mysql/mariadb命令如何获取帮助
Free雅轩的博客
03-28 209
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 当连接到mysql数据库以后，使用help命令或者\?表示获取帮助信息。 也可以指定关键字查看具体命令对应的帮助信息。 可以看到，上面列出了与”create”关键字相关的命令，同样，把列出的命令再次使用help查看即可获得对应命令的语法。 我们也可以通过分类查看帮助，那mysql将帮助分为了哪几类呢，使用help contents命令进行查看。
mysql还原_MYSQL数据库备份还原，并还原到最新状态(mysqldump)
weixin_28708781的博客
01-18 351
vim /etc/my.cnf 配置文件位置及文件名根据实际情况确定sql_log_bin=ON|OFF:是否记录二进制日志，默认为ON //在需要的时候设置为OFFlog_bin=/PATH/BIN_LOG_FILE:指定二进制日志文件位置; 通常单独存放到与数据库不同的机器中清理二进制日志文件show master logs; 查看二进制日志文件列表flush logs; 切换二进制日志文...
mysql 导入错误 /*!40101 SET NAMES utf8mb4 */;
热门推荐
武当山道士的博客
09-19 1万+
mysql导入 xxx.sql的时候发生的一个错误 原因： 可能是当前的msyql不支持 utf8mb4 解决： 打开需要导入的 xxx.sql文件，找到 /*!40101 SET NAMES utf8mb4 */;把 utf8mb4 改成 utf8，保存 xxx.sql 然后再导入 xxxsql 就可以了
mariadb-日志
weixin_34383618的博客
10-15 289
mariadb日志 mariadb日志： 1、查询日志：query log； 2、慢查询日志：slow query log 3、错误日志：error log 4、二进制日志：binary log 5、中继日志：reley log 6、事务日志：transaction log 查询日志 记录查询操作： 文件：file ...
MySQL(数据备份与恢复)
李立衡
05-20 828
本案例要求熟悉MySQL的备份与恢复，完成以下任务操作： 1)使用mysqldump备份数据库 2)使用mysql 恢复数据库 实现此案例需要按照如下步骤进行。 步骤一：使用mysqldump进行逻辑备份 1）备份MySQL服务器上的所有库 将所有的库备份为mysql-all.sql文件： [root@dbsvr1 ~]# mysqldump -u root -p --all-databases ...
mysql mariadb 备份数据库方法 批命令导出sql方法，带winrar最大压缩、恢复记录 Windows 平台 参数化
最新发布
09-19
在Windows环境下，可以使用`mysqldump`工具进行备份，它是一个命令行实用程序，能够将数据库结构和数据导出为SQL文件。 2. **批命令导出SQL**：批处理命令允许用户一次性执行多条命令，提高效率。在Windows的命令...
介绍备份和还原MySQL数据的两种方法
01-21
总之，MySQL的备份和还原可以通过`mysqldump`进行逻辑备份，也可以直接操作数据库文件进行物理备份。根据实际情况选择合适的方法，并确保在备份和还原过程中考虑到权限、日志和系统设置等因素，以确保数据的完整性和...
Learning-MySQL-and-MariaDB.rar_SQL_
08-11
《Learning MySQL and MariaDB》这本书深入探讨了SQL数据库管理和开发的核心技术，主要聚焦于MySQL和MariaDB这两个广泛使用的开源数据库系统。SQL（Structured Query Language）是用于管理关系数据库的标准编程语言...
MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述
weixin_34259559的博客
05-09 276
MariaDB/MySQL备份恢复系列： 备份和恢复(一)：mysqldump工具用法详述 备份和恢复(二)：导入、导出表数据 备份和恢复(三)：xtrabackup用法和原理详述 1.备份分类 按照是否能够继续提供服务，将数据库备份类型划分为： 热备份：在线备份，能读能写 温备份：能读不能写 冷备份：离线备份 ...
MySQL启动报错：/var/log/mariadb/mariadb.log：没有那个文件或目录
qq_41817925的博客
06-22 4524
1、启动mysql失败，报错误提示如图： 2、切换到root用户，新建目录：/var/log/mariadb，并在该目录下新建文件mariadb.log 命令1：mkdir /var/log/mariadb 命令2：touch /var/log/mariadb/mariadb.log 3、修改文件目录/mariadb以及里面的文件所属用户和用户组（我这里mysql的安装用户和启动用户均为XJ，用户组为XJ） 命令：chown -R XJ:XJ /var/log/mariadb/ 4、再次启动mysql，
MYSQL DEFAULT CURRENT_TIMESTAMP 在达梦数据库中如何处理？
龙山溪笔记
05-06 1万+
MySql数据库中在创建时间字段的时候 DEFAULT CURRENT_TIMESTAMP 表示当插入数据的时候，该字段默认值为当前时间 ON UPDATE CURRENT_TIMESTAMP 表示每次更新这条数据的时候，该字段都会更新成当前时间 这两个操作是mysql数据库本身在维护，所以可以根据这个特性来生成【创建时间】和【更新时间】两个字段，且不需要代码来维护 如下： ```language CREATE TABLE `TEST_CURRENT_TIMESTAMP` ( `fawen`..
mysql的.sql文件头部 /*!32312 IF NOT EXISTS*/;
weixin_34032792的博客
02-21 1911
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;...
MySQL Study之--Mysql数据库备份工具（mysqldump）
天涯客*Blog
07-03 1527
MySQL Study之--Mysql数据库备份工具（mysqldump） 对于Mysql Database的备份方式有很多种，此次文档主要介绍mysqldump工具： mysqldump：       mysqldump工具很多方面类似相反作用的工具mysqlimport。它们有一些同样的选项。但mysqldump能够做更多的事情。它可以把整个数据库装载到一
我的MYSQL学习心得（十三） 权限管理
weixin_33877885的博客
07-13 273
我的MYSQL学习心得（十三） 权限管理   我的MYSQL学习心得（一） 简单语法 我的MYSQL学习心得（二） 数据类型宽度 我的MYSQL学习心得（三） 查看字段长度 我的MYSQL学习心得（四） 数据类型 我的MYSQL学习心得（五） 运算符 我的MYSQL学习心得（六） 函数 我的MYSQL学习心得（七） 查询 我的MYSQL学习心得（八） 插入 更新 删除 我的MY...
2.MySQL用户管理，常用SQL语句，MySQL数据库备份与恢复
weixin_33717298的博客
03-24 117
[toc] MySQL用户管理，重用SQL语句，MySQL数据库备份与恢复 一、MySQL用户管理 1.创建一个普通用户并授权 首先启动mysql，然后进入 [root@xavi ~]# /etc/init.d/mysqld start Starting MySQL... SUCCESS! [root@xavi ~]# mysql -uroot -pxavilinux Warning: Usin...
使用mysqldump工具对数据库进行全备份
weixin_34024034的博客
04-09 406
需求描述: 　　通过mysqldump工具的--all-databases选项对所有数据库进行备份. 操作过程: 1.通过--all-databases选项对所有的数据库进行备份 [mysql@redhat6 MysqlDb_Backup]$ mysqldump -uroot -p --single-transaction --master-data --flush-logs --al...
使用达梦数据库的disql工具给数据库做热备份
weixin_42447367的博客
09-11 852
使用disql工具做热备份 热备份就是数据库实例的服务是开启的 [dmdba@localhost bin]$ ./DmServiceDMSERVER status DmServiceDMSERVER (pid 5107) is running. 1.整库备份： SQL> backup database full to “disql_quanbei” backupset ‘/dm8/dmbak/disql_quanbei’; 2.增量备份，基于一个备份集做增量备份 SQL> backup dat
淘宝数据库团队MySQL&MariaDB优化实践
本篇文章主要探讨了淘宝数据库团队在MySQL和MariaDB优化方面的实践，包括Metadata Lock子系统的优化、hash_scan算法的实现解析、TokuDB版本的优化以及MariaDB的性能提升策略。 1. MySQL 5.7 Metadata Lock子系统...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值