常用操作
查看mysql状态
-
ps查看mysql进程:
ps -ef|grep mysql
,是否有mysqld_safe 和mysqld进程$ ps -ef|grep mysqld root 4052 29869 0 10:25 pts/0 00:00:00 grep mysqld root 30905 1 0 10:14 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid mysql 31231 30905 1 10:14 pts/0 00:00:09 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log- error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/tmp/mysqld.sock --port=3306
-
如果已添加到系统服务中,可以使用:
service mysqld status
$ service mysqld status SUCCESS! MySQL running (31231)
启动msql服务
/etc/init.d/mysqld start
,其简化命令为:service mysqld start
停止mysql服务
/etc/init.d/mysqld stop
,其简化命令为:service mysqld stop
重启mysql服务
/etc/init.d/mysqld restart
,其简化命令为:service msqld restart
shell操作
-
使用shell登录mysql:
mysql -u root -p
$ mysql -u root -p # 使用root用户登录mysql;-u,user登录用户,-p,password密码 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 207 Server version: 5.1.72-2 (Debian) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
列出所有数据库:
show dotabases
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a1 | | cloud | | mysql | | phpmyadmin | | playsms | | sisso | | test | | ukolovnik | | wordpress | +--------------------+ 10 rows in set (0.14 sec)
-
切换数据库:
use database_name
mysql> use cloud; # 切换至cloud数据库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>
-
列出某个数据库内所有表:
show tables
mysql> show tables; # 当前数据库为cloud,列出cloud的所有表 +----------------------------+ | Tables_in_cloud | +----------------------------+ | oc_appconfig | | oc_calendar_calendars | | oc_calendar_objects | | oc_calendar_repeat | | oc_calendar_share_calendar | | oc_calendar_share_event | | oc_contacts_addressbooks | | oc_contacts_cards | | oc_fscache | | oc_gallery_sharing | +----------------------------+ 10 rows in set (0.00 sec)
-
获取表内所有 Field(字段)对象的名称和类型:
describe table_name
mysql> describe oc_users; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | uid | varchar(64) | NO | PRI | | | | password | varchar(255) | NO | | | | +----------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
-
删除表:
drop table table_name
mysql> drop table lookup; Query OK, 0 rows affected (0.00 sec)
-
删除数据库:
drop database database_name
mysql> drop database a1; Query OK, 11 rows affected (0.07 sec)