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 //数据存放目录+文件名