Centos下安装mysql及常用mysql命令总结

[root@bogon ~]#  chkconfig mysqld on  

1.使用yum命令安装mysql

[root@bogon ~]#  yum -y install mysql-server  

2.设置开机启动

[root@bogon ~]#  chkconfig mysqld on  

3.启动MySQL服务

[root@bogon ~]#  service mysqld start  

4.建立新数据库

create database test;　

 建立名为test的数据库 (注意是否可以建立这个数据库是在上面建立新用户的时候就决定了的)
5.使用数据库

use test　 ← 连接到数据库show tables;　 ← 查看数据库中已存在的表

6.删除测试账户

revoke all privileges on *.* from centospub@localhost;　 ← 取消centospub用户对数据库的操作权限delete from mysql.user where user='centospub' and host='localhost';　 ← 删除centospub用户select user from mysql.user where user='centospub';　 ← 查找用户centospub，确认已删除与否flush privileges;　 ← 刷新，使以上操作生效

7.删除数据库

drop database name 直接删除数据库，不提醒mysqladmin drop databasename 删除数据库前，有提示。

8.表操作

show tables; 显示表describe tablename; 表的详细描述重命名表: mysql > alter table t1 rename t2;

9.CentOS系统中mysqldump
在shell中执行下面的命令

备份数据库 shell> mysqldump -h yourhost vi-u root -p dbname >dbname_backup.sql恢复数据库 shell> mysqladmin -h yourhost -u root -p create dbnameshell> mysqldump -h yourhost -u root -p dbname < dbname_backup.sql如果只想Dump建表指令，则命令如下： shell> mysqladmin -u root -p -d databasename > a.sql如果只想Dump插入数据的sql命令，而不需要建表命令，则命令如下： shell> mysqladmin -u root -p -t databasename > a.sql那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢？ mysqldump -T./ phptest driver

其 中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的目录，./表示当前目录，即与mysqldump同一目录。如果不指定driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。
可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。
可利用输入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查询，可如下执行这些查询：
如果您想将建表语句提前写在sql.txt中: mysql > mysql -h yourhost -u root -p yourdatabase < sql.txt
10.设置MySQL的root用户设置密码

[root@bogon ~]#  mysql -u root  mysql> select user,host,password from mysql.user;  +------+-----------+----------+  | user | host      | password |  +------+-----------+----------+  | root | localhost |          |  | root | bogon     |          |  | root | 127.0.0.1 |          |  |      | localhost |          |  |      | bogon     |          |  +------+-----------+----------+ 

 
5 rows in set (0.01 sec)  
查询用户的密码，都为空，用下面的命令设置root的密码为root

mysql> set password for root@localhost=password('root');  mysql> exit  

11.用新密码登陆

[root@bogon ~]#  mysql -u root -p  Enter password: 

 
12.创建mysql新用户test_user

mysql> create user 'test_user'@'%' identified by 'test_user';  Query OK, 0 rows affected (0.00 sec) 

13.授权mysql在外部可以访问

grant all privileges on test.* to centospub@localhost identified by '在这里定义密码';　 ← 建立对test数据库有完全操作权限的名为centospub的用户

创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令

mysql> grant all privileges on *.* to user@localhost identified by ’口令’

增加新用户
格式：

grant select on 数据库.* to 用户名@登录主机 identified by “密码”GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

删除授权：

mysql> revoke all privileges on *.* from root@”%”;mysql> delete from user where user=”root” and host=”%”;mysql> flush privileges;

创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’

14.查看mysql 的默认存储引擎
从下面的执行结果可以看出，mysql的默认引擎是MyISAM，这个引擎是不支持事务的。

mysql> show engines;  +------------+---------+------------------------------------------------------------+--------------+------+------------+  | Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |  +------------+---------+------------------------------------------------------------+--------------+------+------------+  | MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |  | CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |  | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |  | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |  | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |  +------------+---------+------------------------------------------------------------+--------------+------+------------+  5 rows in set (0.00 sec)  

也可以以下面的方式查看

mysql> show variables like 'storage_engine';  +----------------+--------+  | Variable_name  | Value  |  +----------------+--------+  | storage_engine | MyISAM |  +----------------+--------+  1 row in set (0.00 sec)

 
15.修改mysql的默认引擎为InnoDB
15.1 停止mysql

mysql> exit;  [root@bogon ~]# service mysqld stop  

15.2 修改/etc/my.cnf
[mysqld] 后加入

default-storage-engine=InnoDB  加入后my.cnf的内容为：[root@bogon etc]# more my.cnf  [mysqld]  datadir=/var/lib/mysql  socket=/var/lib/mysql/mysql.sock  user=mysql  # Disabling symbolic-links is recommended to prevent assorted security risks  symbolic-links=0    default-storage-engine=InnoDB    [mysqld_safe]  log-error=/var/log/mysqld.log  pid-file=/var/run/mysqld/mysqld.pid  

15.3 启动mysql

[root@bogon etc]# service mysqld start  Starting mysqld:  [  OK  ]  

15.4 查看mysql默认存储引擎

[root@bogon etc]# mysql -u root -p  Enter password:   Welcome to the MySQL monitor.  Commands end with ; or \g.  Your MySQL connection id is 2  Server version: 5.1.73 Source distribution    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.    mysql> show variables like 'storage_engine';  +----------------+--------+  | Variable_name  | Value  |  +----------------+--------+  | storage_engine | InnoDB |  +----------------+--------+  1 row in set (0.00 sec)  

16.CentOS 开放mysql端口3306
CentOS 默认是不开放端口的，如果要让外部的系统访问CentOS6.5上的mysql，必须开放mysql的端口3306
10.1 修改/etc/sysconfig/iptables
添加下面一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT  

修改后iptables中的内容是

[root@bogon etc]# more /etc/sysconfig/iptables  # Firewall configuration written by system-config-firewall  # Manual customization of this file is not recommended.  *filter  :INPUT ACCEPT [0:0]  :FORWARD ACCEPT [0:0]  :OUTPUT ACCEPT [0:0]  -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT  -A INPUT -p icmp -j ACCEPT  -A INPUT -i lo -j ACCEPT  -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT  #添加配置项  -A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT  -A INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT  -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT  -A INPUT -j REJECT --reject-with icmp-host-prohibited  -A FORWARD -j REJECT --reject-with icmp-host-prohibited    COMMIT 

17.重启防火墙

[root@bogon etc]# service iptables restart