mysql包相关命令行工具
[root@manage ~]# rpm -qa|grep mysqlmysql-server-5.1.73-5.el6_7.1.x86_64mysql-5.1.73-5.el6_7.1.x86_64mysql-connector-java-5.1.17-6.el6.noarchmysql-libs-5.1.73-5.el6_7.1.x86_64[root@manage ~]# rpm -ql mysql/usr/bin/msql2mysql 没多大用/usr/bin/my_print_defaults 没多大用/usr/bin/mysql 有用/usr/bin/mysql_config/usr/bin/mysql_find_rows 比grep能更近一步，将有关系的块组织在一起，显示出来，而grep只是将匹配行过滤出来而已/usr/bin/mysql_waitpid 没多大用/usr/bin/mysqlaccess 没多大用/usr/bin/mysqladmin 非常有用/usr/bin/mysqlbinlog 有用 /usr/bin/mysqlcheck 没多大用/usr/bin/mysqldump 有用/usr/bin/mysqlimport 有用/usr/bin/mysqlshow 不太用/usr/bin/mysqlslap 不太用
mysql与mysqlimport工具配合使用
[root@manage ~]# mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' fgy -pEnter password:在fgy库中创建表imptest,两列[root@manage ~]# cat imptest.txt100 hello world200 ni hao[root@manage ~]# mysqlimport --local fgy imptest.txt -pEnter password:fgy.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 4[root@manage ~]# mysql -e 'select * from imptest' fgy -pEnter password:+------+------+| id | n |+------+------+| 100 | NULL || 200 | NULL |+------+------+之所以产生警告是因为imptest.txt里分隔符不是tab,而是空格，改成tab就可以了
#man mysql中的解释
When used interactively, query results are presented in an ASCII-table format.
When used noninteractively (for example, as a filter), the result is presented in tab-separated format.
[root@manage ~]# mysqlimport --local fgy imptest.txt -pEnter password:fgy.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0[root@manage ~]# mysql -e 'select * from imptest' fgy -pEnter password:+------+-------------+| id | n |+------+-------------+| 200 | ni hao || 100 | hello world |+------+-------------+
The following example demonstrates tabular versus nontabular output and the use of raw mode to disable escaping:
mysql
mysql -s -r
上面两条命令是不一样的
Then type an SQL statement, end it with “;”, g, or G and press Enter.
ego, G
Send the current statement to the server to be executed and display the result using vertical format.
my_print_defaults groups(groups代表多个/etc/my.cnf中的[]),非常方便，不用去看文件，但是好像用处也不是太大，如果配置项不多的话，直接vi /etc/my.cnf一眼就看完了，也就不需要在命令行敲入那么多字母了。
[root@manage ~]# my_print_defaults mysqld--datadir=/var/lib/mysql--socket=/var/lib/mysql/mysql.sock--user=mysql--symbolic-links=0--innodb_rollback_on_timeout=1--innodb_lock_wait_timeout=600--max_connections=350--log-bin=mysql-bin--binlog-format=ROW--character_set_server=utf8
mysqldump
这一段代码太不优化了，多余-重复
不需要sed来添加行，只需要加入 --add-drop-database就可以了
有--database或-A,添加--add-drop-database才有效。
还是要多看看 #man mysqldump手册页。了解具体需求，才有针对性。
drop database if exists spauth;
create database spauth;
use spauth;mysqldump -u root -d -R --add-drop-table basedata >basedata.sqlsed -i '1iuse basedata;' basedata.sqlsed -i '1icreate database basedata;' basedata.sqlsed -i '1idrop database if exists basedata;' basedata.sqlmysqldump -u root --add-drop-table basedata industry>>basedata.sqlmysqldump -u root --add-drop-table basedata data_dictionary>>basedata.sqlmysqldump -u root --add-drop-table --extended-insert=false basedata tb_sequence>>basedata.sql
-e, --extended-insert，长INSERT，多row在一起批量INSERT，提高导入效率，和没有开启 -e 的备份导入耗时至少相差3、4倍，默认开启；用--extended-insert=false关闭。强烈建议开启，通过下面的测试比较就会明白为什么了。（1）默认方式导出，也即--extended-insert=true-d只有表结构即列信息，无内容即行信息· --no-data, -d Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).· --routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.
mysqlbinlog
因为每次操作的时间和“位置”都会被记录下来。所以要想还原数据有两种途径通过“时间”或“位置”。
[root@manage mysql]# pwd/var/lib/mysql[root@manage mysql]# lscloud ibdata1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013cloudbridge ib_logfile0 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.indexcloud_usage ib_logfile1 mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql.sockfss mysql mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 spauth[root@manage mysql]# mysqlbinlog mysql-bin.000013 |more
mysqlshow
mysqlshow - display database, table, and column informationmysqlshow supports the following options, which can be specified on the command line or inthe [mysqlshow] and [client] groups of an option file.[root@manage ~]# mysqlshow -p123456 cloud vpc displayDatabase: cloud Table: vpc Wildcard: display+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+| display | tinyint(1) | | NO | | 1 | | select,insert,update,references | True if the vpc can be displayed to the end user |+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+
mysqlcheck
[root@manage ~]# mysqlcheck -p cloudEnter password:cloud.account OKcloud.account_details OKcloud.account_network_ref OKcloud.op_locknote : The storage engine for the table doesn't support checkcloud.op_networks OKcloud.op_nwgrp_worknote : The storage engine for the table doesn't support checkcloud.op_pod_vlan_alloc OKmysqlcheck is similar in function to myisamchk, but works differently. The main operationaldifference is that mysqlcheck must be used when the mysqld server is running, whereasmyisamchk should be used when it is not.
[root@manage ~]# mysqladmin extended-status|more+-----------------------------------+----------+| Variable_name | Value |+-----------------------------------+----------+| Aborted_clients | 0 || Aborted_connects | 1 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 7 || Bytes_received | 17954 || Bytes_sent | 83686 || Com_admin_commands | 2 |
fgy3是库名[root@manage ~]# mysqladmin create fgy3 create fgy4[root@manage ~]# mysqlshow[root@manage ~]# mysqladmin drop fgy4Dropping the database is potentially a very bad thing to do.Any data stored in the database will be destroyed.Do you really want to drop the 'fgy4' database [y/N] yDatabase "fgy4" dropped[root@manage ~]# mysqlshow
[root@manage ~]# mysqladmin proc stat
+----+-------+-----------------+-------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------------+-------------+---------+------+-------+------------------+
| 4 | cloud | localhost:46162 | cloud | Sleep | 24 | | |
| 5 | cloud | localhost:46163 | cloud_usage | Sleep | 32 | | |
| 30 | root | localhost | | Query | 0 | | show processlist |
+----+-------+-----------------+-------------+---------+------+-------+------------------+
Uptime: 16972 Threads: 3 Questions: 8410 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 10 Queries per second avg: 0.495
[root@manage ~]# mysqladmin version
[root@manage ~]# mysqladmin variables|more
[root@manage ~]# mysqladmin extended-status|more