分类： Mysql/postgreSQL
2008-02-25 14:29:33
/etc/init.d/mysqld start
/etc/init.d/mysql.server stop
也可以这样启动：safe_mysqld
一种关闭数据库的方法
mysqladmin –u root –p shutdown windows中通过服务来起停。 mysqlshow –-help，查看版本：[root@DB-GATE-16 bin]# ./mysqlshow -V./mysqlshow Ver 9.10 Distrib 5.1.22-ndb-6.3.4-telco, for redhat-linux-gnu (x86_64)查看数据库, 可以用mysqlshow –-help看帮助
[root@DB-GATE-16 bin]# ./mysqlshow+--------------------+| Databases |+--------------------+| information_schema || employee || logs || lsdb || mysql || test |+--------------------+查看一个数据库的表:[root@DB-GATE-16 bin]# mysqlshow employee;Database: employee+----------------+| Tables |+----------------+| account || assignment || client || department || employee || employeeSkills |+----------------+获取更多信息:[root@DB-GATE-16 bin]# mysqlshow --status employeeDatabase: employee+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+| account | MyISAM | 10 | Fixed | 3 | 9 | 27 | 2533274790395903 | 2048 | 0 | 4 | 2008-02-22 14:26:24 | 2008-02-22 15:32:56 | | latin1_swedish_ci | | | || assignment | MyISAM | 10 | Fixed | 1 | 16 | 16 | 4503599627370495 | 2048 | 0 | | 2008-02-21 16:51:56 | 2008-02-21 16:51:56 | | latin1_swedish_ci | | | || client | MyISAM | 10 | Dynamic | 2 | 64 | 128 | 281474976710655 | 2048 | 0 | 3 | 2008-02-21 16:51:56 | 2008-02-21 16:51:56 | | latin1_swedish_ci | | | || department | MyISAM | 10 | Dynamic | 5 | 26 | 132 | 281474976710655 | 2048 | 0 | 132 | 2008-02-21 16:51:56 | 2008-02-21 16:51:56 | | latin1_swedish_ci | | | || employee | MyISAM | 10 | Dynamic | 6 | 32 | 196 | 281474976710655 | 2048 | 0 | 9845 | 2008-02-21 16:51:57 | 2008-02-21 16:51:57 | | latin1_swedish_ci | | | || employeeSkills | MyISAM | 10 | Dynamic | 8 | 20 | 160 | 281474976710655 | 2048 | 0 | | 2008-02-21 16:51:57 | 2008-02-21 16:51:57 | | latin1_swedish_ci | | | |+----------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ Mysql monitor中的等效命令:
show columns from tablename;show table status 查看数据运行状态:mysqladmin extended-status, 你关心的可能是这些:
mysqladmin extended-status |grep -i com其他重要的参数有threads_connected, slow_queries, uptime.查看变量: show variables; mysqladmin –u username –p variables
查看进程信息: show processlist; mysqladmin –u username –p showprocesslist 可以看到进程号,并且用kill process_id;可以杀进程
查看授权信息:mysql> show grants for root@localhost;+---------------------------------------------------------------------+| Grants for root@localhost |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------+show privileges; mysql> show table types;+------------+---------+----------------------------------------------------------------+--------------+-----+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+------------+---------+----------------------------------------------------------------+--------------+-----+------------+| ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || FEDERATED | YES | Federated MySQL storage engine | YES | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |+------------+---------+----------------------------------------------------------------+--------------+-----+------------+9 rows in set, 1 warning (0.01 sec)查看创建表信息
show create table tablename; mysql> show create table department;+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| department | CREATE TABLE `department` ( `departmentID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`departmentID`)) ENGINE=MyISAM AUTO_INCREMENT=132 DEFAULT CHARSET=latin1 |+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) ＊ 设置变量: set sql_safe_updates=1;
＊ 杀线程：kill process_id;先使用“show processlist” 查看。
＊ Cache相关的命令: flush privileges; flush query cache; reset query cache;
＊ Log文件
错误log：是一般是在数据目录，以.err结尾的文件。可以在配置文件中设置：log-error=filename
查询log：可以在配置文件中设置：log=filename.
Binary log：可以在配置文件中设置：log=filename.用来取代update log。查看方式：mysqlbinlog logfile
Slow query log：可以在配置文件中设置：log-slow-queries=filename
Linux下有rotate log的方法：mysql-log-rotate。其他系统先保存好log，而后使用mysqladmin flush-logs。
＊ Mysqladmin 命令小结:
mysqladmin create databasenamemysqladmin drop databasenamemysqladmin pingmysqladmin versionmysqladmin statusmysqladmin extended-statusmysqladmin processlistmysqladmin kill id1,id2,id3...mysqladmin variables Start the server with mysqladmin, by running the mysqld executable, or by using the Windows Services manager.
Shut down the server with mysql.server stop or mysqladmin shutdown.
Use mysqlshow or SHOW to get information about the current database or MySQL server.
Set variables with set variable=value;.
See threads with show processlist and kill them with kill processid.
Clear caches with FLUSH and RESET.
Improve the speed of joins with analyze table tablename;.
MySQL stores log information in the error log, the query log (all queries), the binary log (queries that change data), and the slow query log (queries that take longer than long_query_time).
chinaunix网友2008-02-25 14:30:02
§13.4 习题和答案 : Which of the following logs are enabled by default? query log slow query log error log binary log all of the above 2: The SQL command SHOW can be used to show the list of databases available the list of tables in a database the list of columns in a table all of the above 3: The script mysqladmin can be used to reload privileges to make sure that any changes take effect check on the current status of the server stop and start the server