Mysql 管理和备份
mysqladmin用于管理MySQL服务器的客户端，mysqladmin执行管理操作的客户程序，可以用它来创建或删除数据库，重载授权表，将表刷新到硬盘上，以及重新打开日志文件，检索版本、进程，以及服务器的状态信息。
调用格式:mysqladmin [options] command [command-arg] [command [command-arg]] ...
| Format | Description | Introduced |
|---|---|---|
| --bind-address | Use specified network interface to connect to MySQL Server | 5.6.1 |
| --compress | Compress all information sent between client and server | |
| --connect_timeout | Number of seconds before connection timeout | |
| --count | Number of iterations to make for repeated command execution | |
| --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 | |
| --enable-cleartext-plugin | Enable cleartext authentication plugin | 5.6.7 |
| --force | Continue even if an SQL error occurs | |
| --help | Display help message and exit | |
| --host | Connect to MySQL server on given host | |
| --login-path | Read login path options from .mylogin.cnf | 5.6.6 |
| --no-beep | Do not beep when errors occur | |
| --no-defaults | Read no option files | |
| --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 | |
| --relative | Show the difference between the current and previous values when used with the --sleep option | |
| --secure-auth | Do not send passwords to server in old (pre-4.1) format | 5.6.17 |
| --shared-memory-base-name | The name of shared memory to use for shared-memory connections | |
| --shutdown_timeout | The maximum number of seconds to wait for server shutdown | |
| --silent | Silent mode | |
| --sleep | Execute commands repeatedly, sleeping for delay seconds in between | |
| --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 | |
| --user | MySQL user name to use when connecting to server | |
| --verbose | Verbose mode | |
| --version | Display version information and exit | |
| --vertical | Print query output rows vertically (one line per column value) | |
| --wait | If the connection cannot be established, wait and retry instead of aborting |
更多信息查看：http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html
使用举例：
//修改密码 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]选项如下：
| Format | Description | Introduced |
|---|---|---|
| --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 |
更多信息查看官网:http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_routines
经常使用到的命令：
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 //数据存放目录+文件名
