`
willvvv
  • 浏览: 333125 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

mysqldump生成SQL脚本方式备份数据库

阅读更多

备份脚本:mysql-dump.sh

 

#!/bin/bash
export MYSQL_HOME=/usr/local/Percona-Server-5.5.21-rel25.0-227.Linux.x86_64/
export PATH=$MYSQL_HOME/bin:$PATH

mysql_dump_dir=/data/backup/mysqldump
mysql_username="YOURS"
mysql_password="YOURS"
mysql_databases="DBNAME1 DBNAME2 DBNAME3"

timeStart=$(date '+%Y%m%d%H%M%S')
sqlfile=$mysql_dump_dir/dump-$timeStart.sql
mysqldump --opt --user=$mysql_username --password=$mysql_password -B $mysql_databases --max_allowed_packet=1048576 --net_buffer_length=16384 > $sqlfile 

 

恢复时:使用root账号登陆到mysql命令行,执行:

 

source /data/backup/mysqldump/dump-20120629181412.sql

 

或者直接在linux的终端输入以下命令也可以

 

mysql -uYOURS -pYOURS -e"source /data/backup/mysqldump/dump-20120629181412.sql"
 

mysqldump参数:

Format Option File Description Introduced Removed
--add-drop-database add-drop-database Add a DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases all-databases Dump all tables in all databases
--allow-keywords allow-keywords Allow creation of column names that are keywords
--apply-slave-statements apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output 5.5.3
--bind-address=ip_address bind-address Use the specified network interface to connect to the MySQL Server 5.5.8
--comments comments Add comments to the dump file
--compact compact Produce more compact output
--compatible=name[,name,...] compatible Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert complete-insert Use complete INSERT statements that include column names
--create-options create-options Include all MySQL-specific table options in CREATE TABLE statements
--databases databases Dump several databases
--debug[=debug_options] debug Write a debugging log
--debug-check debug-check Print debugging information when the program exits
--debug-info debug-info Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin default-auth=plugin The authentication plugin to use 5.5.9
--default-character-set=charset_name default-character-set Use charset_name as the default character set
--delayed-insert delayed-insert Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
--disable-keys disable-keys For each table, surround the INSERT statements with statements to disable and enable keys
--dump-date dump-date Include dump date as "Dump completed on" comment if --comments is given
--dump-slave[=value] dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master 5.5.3
--events events Dump events from the dumped databases
--extended-insert extended-insert Use multiple-row INSERT syntax that include several VALUES lists
--fields-enclosed-by=string fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by fields-escaped-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string fields-optionally-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string fields-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--first-slave first-slave Deprecated; use --lock-all-tables instead 5.5.3
--flush-logs flush-logs Flush the MySQL server log files before starting the dump
--flush-privileges flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql database
--help Display help message and exit
--hex-blob hex-blob Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)
--host host Host to connect to (IP address or hostname)
--ignore-table=db_name.tbl_name ignore-table Do not dump the given table
--include-master-host-port include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave 5.5.3
--insert-ignore insert-ignore Write INSERT IGNORE statements rather than INSERT statements
--lines-terminated-by=string lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables lock-all-tables Lock all tables across all databases
--lock-tables lock-tables Lock all tables before dumping them
--log-error=file_name log-error Append warnings and errors to the named file
--master-data[=value] master-data Write the binary log file name and position to the output
--max_allowed_packet=value max_allowed_packet The maximum packet length to send to or receive from the server
--net_buffer_length=value net_buffer_length The buffer size for TCP/IP and socket communication
--no-autocommit no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db no-create-db This option suppresses the CREATE DATABASE statements
--no-create-info no-create-info Do not write CREATE TABLE statements that re-create each dumped table
--no-data no-data Do not dump table contents
--no-set-names no-set-names Same as --skip-set-charset
--no-tablespaces no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index
--password[=password] password The password to use when connecting to the server
--pipe On Windows, connect to server using a named pipe
--plugin-dir=path plugin-dir=path The directory where plugins are located 5.5.9
--port=port_num port The TCP/IP port number to use for the connection
--quick quick Retrieve rows for a table from the server a row at a time
--quote-names quote-names Quote identifiers within backtick characters
--replace replace Write REPLACE statements rather than INSERT statements
--result-file=file result-file Direct output to a given file
--routines routines Dump stored routines (procedures and functions) from the dumped databases
--set-charset set-charset Add SET NAMES default_character_set to the output
--single-transaction single-transaction This option issues a BEGIN SQL statement before dumping data from the server
--skip-add-drop-table skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks skip-add-locks Do not add locks
--skip-comments skip-comments Do not add comments to the dump file
--skip-compact skip-compact Do not produce more compact output
--skip-disable-keys skip-disable-keys Do not disable keys
--skip-extended-insert skip-extended-insert Turn off extended-insert
--skip-opt skip-opt Turn off the options set by --opt
--skip-quick skip-quick Do not retrieve rows for a table from the server a row at a time
--skip-quote-names skip-quote-names Do not quote identifiers
--skip-set-charset skip-set-charset Suppress the SET NAMES statement
--skip-triggers skip-triggers Do not dump triggers
--skip-tz-utc skip-tz-utc Turn off tz-utc
--ssl-ca=file_name ssl-ca The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name ssl-capath The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name ssl-cert The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list ssl-cipher A list of allowable ciphers to use for SSL encryption
--ssl-key=file_name ssl-key The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert ssl-verify-server-cert The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--tab=path tab Produce tab-separated data files
--tables tables Override the --databases or -B option
--triggers triggers Dump triggers for each dumped table
--tz-utc tz-utc Add SET TIME_ZONE='+00:00' to the dump file
--user=user_name user The MySQL user name to use when connecting to the server
--verbose Verbose mode
--version Display version information and exit
--where='where_condition' where Dump only rows selected by the given WHERE condition
--xml xml Produce XML output
分享到:
评论

相关推荐

    mysql 自动备份数据库脚本

    了解如何从SQL脚本中恢复数据库同样重要。这通常涉及到使用`mysql`命令来导入备份文件,如: ```bash mysql -u [username] -p[password] [database_name] [backup_file.sql] ``` 7. **优化备份**:对于大型...

    定时备份数据库,及问题总结 亲测有效

    【定时备份数据库】是保障数据安全的重要措施,尤其是在企业级应用中,确保数据的完整性和可恢复性至关重要。本文将详细介绍如何在Linux环境下,利用MySQL数据库的`mysqldump`工具实现定时备份,并提供了一个亲测...

    python定时备份mysql数据库脚本

    备份数据库通常涉及创建一个SQL导出命令,例如 `mysqldump`。在Python中,我们可以使用 `execute()` 方法执行SQL语句,创建数据库的SQL导出文件。 3. **写入文件**: 使用Python的文件操作功能,如 `open()` 和 `...

    mysql数据库每天凌晨3点备份数据库的脚本

    本文将详细介绍如何编写一个在Linux环境下,每天凌晨3点自动执行的MySQL数据库备份脚本,以及如何配置Linux的定时任务来执行这个脚本。 首先,我们来看`DB_backup.sh`这个脚本文件。它通常包含以下核心部分: 1. *...

    生成sql insert数据脚本更新

    在数据库管理中,生成SQL脚本是一项常见的任务,可以使用多种工具完成,如MySQL的mysqldump,Oracle的expdp,或者SQL Server的ssms等。这些工具能帮助我们生成包含INSERT语句的脚本,但有时也需要手动调整以适应特定...

    python自动备份mysql数据库,并删除七天前文件

    2. 执行备份:通过`subprocess.Popen()`或`subprocess.check_output()`调用`mysqldump`,指定数据库名、输出文件名等参数,生成SQL备份文件。 3. 设置备份时间:可以使用Python的`datetime`和`time`模块,根据用户...

    windows备份mysql数据库bat脚本.rar

    2. **生成SQL备份文件**:`mysqldump`命令将用于生成包含数据库结构和数据的SQL文件。例如,命令可能类似于`mysqldump -u username -p password --host=localhost --port=3306 database_name > backup.sql`,其中`...

    数据库自动备份脚本 数据库自动备份脚本

    备份命令会生成SQL文件或二进制文件。 4. **执行增量或差异备份**:这通常更复杂,需要跟踪上次备份的时间戳,然后只备份自那以后更改的数据。这可能涉及到额外的工具或脚本逻辑。 5. **压缩备份文件**:为了节省...

    mysql批处理 备份数据库-压缩文件

    2. **创建备份**:使用`mysqldump`命令生成SQL文件,这将包含数据库的结构和数据。例如: ``` mysqldump -hlocalhost -uusername -ppassword dbname > dbname_backup.sql ``` 3. **压缩备份文件**:使用`7z`或`...

    python备份mysql数据库

    3. mysqldump:mysqldump是MySQL提供的一款命令行工具,用于备份数据库结构和数据。它可以生成SQL语句,这些语句可以在需要时用于重建数据库。 4. PyMySQL:PyMySQL是Python的一个库,它提供了与MySQL数据库交互的...

    mysqldump导入导出mysql数据库.docx

    另外,mysqldump生成的备份文件在导入时需要先创建目标数据库,然后切换到该数据库并使用source命令导入。 source命令是在MySQL客户端中执行SQL脚本的命令,用于导入数据。例如: ``` mysql -u 用户名 -p use ...

    Windows下MySql自动备份脚本(有备份日志,且可以删除历史备份).zip

    "Windows下MySql自动备份脚本(有备份日志,且可以删除历史备份)"的资源提供了一个解决方案,它是一个批处理脚本(MysqlAutoBackup.bat),能够自动化执行MySQL的备份过程,同时生成备份日志并清理历史备份。...

    Windows下MySql数据库增量全量备份

    此脚本首先检查是否存在之前的备份位置信息,然后读取最新的二进制日志文件和位置,生成增量备份SQL文件。 数据库还原则涉及将备份文件重新导入到MySQL服务器。对于全量备份,可以直接使用`mysql`命令导入SQL文件;...

    一个自动备份mysql数据库的脚本

    2. 执行备份:使用`mysqldump`命令行工具生成SQL备份文件。这通常通过`exec`或`shell_exec`函数来调用系统命令。例如,备份整个数据库的命令可能是`mysqldump -u$user -p$pass $db > $backup_file.sql`,其中`$user`...

    linux下mysql数据库备份脚本

    `mysqldump`是一个用于备份数据库的实用程序,它能将数据库结构和数据导出为SQL语句。 2. **选择要备份的数据库**:在脚本中指定需要备份的数据库名称,或者使用通配符(如`--all-databases`)备份所有数据库。 3....

    mysqldump在线备份

    `mysqldump`是MySQL数据库管理系统自带的一个非常重要的工具,用于将MySQL数据库中的数据导出为SQL脚本文件。它可以对整个数据库或指定的表进行备份,并且支持增量备份。通过使用`mysqldump`工具,用户可以轻松地...

    java备份数据库信息

    因此,定期备份数据库是必要的预防措施,可以保护数据免受硬件故障、软件错误、恶意攻击等风险的影响。 在Java中,备份数据库主要涉及以下步骤: 1. 连接数据库:使用JDBC(Java Database Connectivity)API,Java...

    mydb数据库备份程序(PHP版)

    2. 执行备份命令:通过`exec()`或`shell_exec()`调用系统命令`mysqldump`,生成SQL文件。`mysqldump`可以生成包含CREATE TABLE、INSERT等语句的文本文件,可用于恢复数据库。 3. 处理输出:将`mysqldump`的输出保存...

    C#备份还原mysql数据库

    通常,备份数据库的SQL语句是`mysqldump`命令,但在C#中,我们需要通过执行系统命令的方式来调用它。例如: ```csharp ProcessStartInfo psi = new ProcessStartInfo(); psi.FileName = "mysqldump.exe"; psi....

Global site tag (gtag.js) - Google Analytics