mysql数据库备份管理_Mysql 管理和备份

mysqladmin用于管理MySQL服务器的客户端，mysqladmin执行管理操作的客户程序，可以用它来创建或删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件，检索版本、进程，以及服务器的状态信息。

调用格式:mysqladmin [options] command [command-arg] [command [command-arg]] ...

FormatDescriptionIntroduced

--bind-address

Use specified network interface to connect to MySQL Server

5.6.1

Compress all information sent between client and server

Number of seconds before connection timeout

Number of iterations to make for repeated command execution

Write debugging log

Print debugging information when program exits

Print debugging information, memory, and CPU statistics when program exits

Authentication plugin to use

Specify default character set

Read named option file in addition to usual option files

Read only named option file

Option group suffix value

Enable cleartext authentication plugin

5.6.7

Continue even if an SQL error occurs

Display help message and exit

Connect to MySQL server on given host

Read login path options from .mylogin.cnf

5.6.6

Do not beep when errors occur

Read no option files

Password to use when connecting to server

On Windows, connect to server using named pipe

Directory where plugins are installed

TCP/IP port number to use for connection

Print default options

Connection protocol to use

Show the difference between the current and previous values when used with the --sleep option

Do not send passwords to server in old (pre-4.1) format

5.6.17

The name of shared memory to use for shared-memory connections

The maximum number of seconds to wait for server shutdown

Silent mode

Execute commands repeatedly, sleeping for delay seconds in between

For connections to localhost, the Unix socket file to use

Enable secure connection

Path of file that contains list of trusted SSL CAs

Path of directory that contains trusted SSL CA certificates in PEM format

Path of file that contains X509 certificate in PEM format

List of permitted ciphers to use for connection encryption

Path of file that contains certificate revocation lists

5.6.3

Path of directory that contains certificate revocation list files

5.6.3

Path of file that contains X509 key in PEM format

Security state of connection to server

5.6.30

Verify server certificate Common Name value against host name used when connecting to server

MySQL user name to use when connecting to server

Verbose mode

Display version information and exit

Print query output rows vertically (one line per column value)

If the connection cannot be established, wait and retry instead of aborting

使用举例：

//修改密码

shell>mysqladmin -u root -p password 'newpassword';//重新加载授权表

shell>mysqladmin -u root -p reload;//查看服务器版本

shell>mysqladmin -u root -p version;//检测服务器mysql是否可用

shell>mysqladmin -u root -p ping;//显示mysql进程表

shell>mysqladmin -u root -p processlist;

Mysqldump

Mysqldump是MySQL数据库逻辑备份的常用工具，在日常的维护工作中经常会用到,这里对这个工具的使用做一个简单的介绍。

有以下 3 种方法来调用mysqldump：

(1)备份指定的数据库，或者此数据库中某些表.

shell> mysqldump [options] db_name [tbl_name ...]

(2)备份指定的一个或多个数据库.

shell> mysqldump [options] --databases db_name ...

(3)备份所有数据库,如果没有指定数据库中的任何表，默认导出所有数据库中所有表.

shell> mysqldump [options] --all-databases

[options]选项如下：

FormatDescriptionIntroduced

--add-drop-database

Add DROP DATABASE statement before each CREATE DATABASE statement

--add-drop-table

Add DROP TABLE statement before each CREATE TABLE statement

--add-drop-trigger

Add DROP TRIGGER statement before each CREATE TRIGGER statement

--add-locks

Surround each table dump with LOCK TABLES and UNLOCK TABLES statements

--all-databases

Dump all tables in all databases

--allow-keywords

Allow creation of column names that are keywords

--apply-slave-statements

Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output

--bind-address

Use specified network interface to connect to MySQL Server

5.6.1

--character-sets-dir

Directory where character sets are installed

--comments

Add comments to dump file

--compact

Produce more compact output

--compatible

Produce output that is more compatible with other database systems or with older MySQL servers

--complete-insert

Use complete INSERT statements that include column names

--compress

Compress all information sent between client and server

--create-options

Include all MySQL-specific table options in CREATE TABLE statements

--databases

Interpret all name arguments as database names

--debug

Write debugging log

--debug-check

Print debugging information when program exits

--debug-info

Print debugging information, memory, and CPU statistics when program exits

--default-auth

Authentication plugin to use

--default-character-set

Specify default character set

--defaults-extra-file

Read named option file in addition to usual option files

--defaults-file

Read only named option file

--defaults-group-suffix

Option group suffix value

--delayed-insert

Write INSERT DELAYED statements rather than INSERT statements

--delete-master-logs

On a master replication server, delete the binary logs after performing the dump operation

--disable-keys

For each table, surround INSERT statements with statements to disable and enable keys

--dump-date

Include dump date as "Dump completed on" comment if --comments is given

--dump-slave

Include CHANGE MASTER statement that lists binary log coordinates of slave's master

--enable-cleartext-plugin

Enable cleartext authentication plugin

5.6.28

--events

Dump events from dumped databases

--extended-insert

Use multiple-row INSERT syntax

--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

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

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--fields-terminated-by

This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE

--flush-logs

Flush MySQL server log files before starting dump

--flush-privileges

Emit a FLUSH PRIVILEGES statement after dumping mysql database

--force

Continue even if an SQL error occurs during a table dump

--help

Display help message and exit

--hex-blob

Dump binary columns using hexadecimal notation

--host

Host to connect to (IP address or hostname)

--ignore-table

Do not dump given table

--include-master-host-port

Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave

--insert-ignore

Write INSERT IGNORE rather than INSERT statements

--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 across all databases

--lock-tables

Lock all tables before dumping them

--log-error

Append warnings and errors to named file

--login-path

Read login path options from .mylogin.cnf

5.6.6

--master-data

Write the binary log file name and position to the output

--max_allowed_packet

Maximum packet length to send to or receive from server

--net_buffer_length

Buffer size for TCP/IP and socket communication

--no-autocommit

Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements

--no-create-db

Do not write CREATE DATABASE statements

--no-create-info

Do not write CREATE TABLE statements that re-create each dumped table

--no-data

Do not dump table contents

--no-defaults

Read no option files

--no-set-names

Same as --skip-set-charset

--no-tablespaces

Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output

--opt

Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

--order-by-primary

Dump each table's rows sorted by its primary key, or by its first unique index

--password

Password to use when connecting to server

--pipe

On Windows, connect to server using named pipe

--plugin-dir

Directory where plugins are installed

--port

TCP/IP port number to use for connection

--print-defaults

Print default options

--protocol

Connection protocol to use

--quick

Retrieve rows for a table from the server a row at a time

--quote-names

Quote identifiers within backtick characters

--replace

Write REPLACE statements rather than INSERT statements

--result-file

Direct output to a given file

--routines

Dump stored routines (procedures and functions) from dumped databases

--secure-auth

Do not send passwords to server in old (pre-4.1) format

5.6.17

--set-charset

Add SET NAMES default_character_set to output

--set-gtid-purged

Whether to add SET @@GLOBAL.GTID_PURGED to output

5.6.9

--shared-memory-base-name

The name of shared memory to use for shared-memory connections

--single-transaction

Issue a BEGIN SQL statement before dumping data from server

--skip-add-drop-table

Do not add a DROP TABLE statement before each CREATE TABLE statement

--skip-add-locks

Do not add locks

--skip-comments

Do not add comments to dump file

--skip-compact

Do not produce more compact output

--skip-disable-keys

Do not disable keys

--skip-extended-insert

Turn off extended-insert

--skip-opt

Turn off options set by --opt

--skip-quick

Do not retrieve rows for a table from the server a row at a time

--skip-quote-names

Do not quote identifiers

--skip-set-charset

Do not write SET NAMES statement

--skip-triggers

Do not dump triggers

--skip-tz-utc

Turn off tz-utc

--socket

For connections to localhost, the Unix socket file to use

--ssl

Enable secure connection

--ssl-ca

Path of file that contains list of trusted SSL CAs

--ssl-capath

Path of directory that contains trusted SSL CA certificates in PEM format

--ssl-cert

Path of file that contains X509 certificate in PEM format

--ssl-cipher

List of permitted ciphers to use for connection encryption

--ssl-crl

Path of file that contains certificate revocation lists

5.6.3

--ssl-crlpath

Path of directory that contains certificate revocation list files

5.6.3

--ssl-key

Path of file that contains X509 key in PEM format

--ssl-mode

Security state of connection to server

5.6.30

--ssl-verify-server-cert

Verify server certificate Common Name value against host name used when connecting to server

--tab

Produce tab-separated data files

--tables

Override --databases or -B option

--triggers

Dump triggers for each dumped table

--tz-utc

Add SET TIME_ZONE='+00:00' to dump file

--user

MySQL user name to use when connecting to server

--verbose

Verbose mode

--version

Display version information and exit

--where

Dump only rows selected by given WHERE condition

--xml

Produce XML output

经常使用到的命令：

1.数据备份

//导出某个数据库--结构+数据

shell>mysqldump -u root -p --host='127.0.0.1' --opt python_db >test.sql;//导出某个数据库的表--结构+数据+函数+存储过程

shell>mysqldump -u root -p --host='127.0.0.1' --opt -R python_db >test.sql;//导出多个数据库

shell>mysqldump -u root -p --host='127.0.0.1' --opt --databases db_name1 db_name2 db_name3 >mul.sql//导出所有的数据库

shell>mysqldump -u root -p --host='127.0.0.1' --opt --all-databases >adb.sql//导出某个数据库的结构

shell>mysqldump -u root -p --host='127.0.0.1' --opt --no-data db_name >db.sql//导出某个数据库的数据

shell>mysqldump -u root -p --host='127.0.0.1' --opt --no-create-info db_name>db.sql//导出某个数据库的某张表

shell>mysqldump -u root -p --host='127.0.0.1' --opt db_name tbl_name >tb_name.sql//导出某个数据库的某张表的结构

shell>mysqldump -u root -p --host='127.0.0.1' --opt --no-data db_name tal_name >tb_name.sql//导出某个数据库的某张表的数据

shell>mysqldump -u root -p --host='127.0.0.1' --opt --no-create-info db_name tbl_name >db.sql//--opt==--add-drop-table + --add-locks + --create-options + --disables-keys + --extended-insert + --lock-tables + --quick + --set+charset//默认使用--opt，--skip-opt禁用--opt参数//mysqldump命令中带有一个 --where/-w 参数，它用来设定数据导出的条件，使用方式和SQL查询命令中中的where基本上相同，有了它，我们就可以从数据库中导出你需要的那部分数据了。//

//命令格式如下：mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径//从meteo数据库的sdata表中导出sensorid=11 且 fieldid=0的数据到 /home/xyx/Temp.sql 这个文件中

shell>mysqldump -uroot -p123456 meteo sdata --where="sensorid=11 and fieldid=0" > /home/xyx/Temp.sql

2.数据还原

(1).使用mysqldump命令还原数据库的语法如下：

mysql -u root -p [dbname] < backup.sq

示例：

mysql -u root -p < C:\backup.sql

(2).使用source命令还原数据

输入mysql进入mysql命令行模式，在输入：

use python_db　　　　 //首先选择数据库

source ./data.sql 　　　　//数据存放目录+文件名