MySQL 相关命令

来源：互联网发布：火炬之光mac版汉化教编辑：程序博客网时间：2024/06/05 23:05

1. 查看是否安装

rpm -qa | grep -in mysql

或yum list installed | grep mysql

2. 普通删除

rpm -e mysql

强力删除

rpm -e --nodeps mysql

--------------------

[yum way]: yum remove mysql mysql*

彻底删除：

rm -rf /var/lib/mysql/

rm -rf /etc/my.cnf

3. yum提供版本查看

yum list | grep -in mysql

4. 安装

yum install -y mysql-server mysql mysql-deve

mysql 5.5 安装：

refer to：http://blog.csdn.net/shootyou/article/details/8236886

rpm -Uvh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
rpm -Uvh http://mirrors.neusoft.edu.cn/epel/6/i386/epel-release-6-8.noarch.rpm
rpm -Uvh http://packages.sw.be/rpmforge-release/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
rpm -Uvh http://dl.iuscommunity.org/pub/ius/stable/Redhat/6/x86_64/epel-release-6-5.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

yum --enablerepo=remi,remi-test info mysql mysql-server

yum --enablerepo=remi,remi-test install mysql mysql-server

5. 启动和查看状态

service mysqld start

service --status-all | grep -in mysql

6. 开机启动

查看：chkconfig --list | grep mysqld

设定：chkconfig mysqld on

7. root密码设置

mysqladmin -u root password 'root'

8. 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

9. 登录数据库

mysql -u root -p

mysql -uuser -ppassword -h127.0.0.1

mysql -uuser -ppassword -h192.168.1.50

10. 配置文件

/etc/my.cnf

11. 是否在监听3306端口

netstat -anp | more

12. 授权

显示授权

mysql> show grants
-> ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

授权远程
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

授权本地

mysql> GRANT ALL PRIVILEGES ON *.* TO user@"localhost" IDENTIFIED BY "password" WITH GRANT OPTION;

mysql> flush privileges; //使改变生效
Query OK, 0 rows affected (0.00 sec)

13. linux数据库导入导出

导出： mysqldump -u用户名 -p密码数据库名 > 数据库名.sql (表+数据)

mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql (仅表)

导入：mysql -u用户名 -p密码数据库名 < 数据库名.sql

导入错误：

[root@localhost deploy_bond]# mysql -uartogrid -partogrid idb_bond < idb_bond.sql
ERROR 2006 (HY000) at line 4193: MySQL server has gone away

Solution：

临时生效：
mysql> show glowbal variables like 'max_allowed_packet'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'glowbal variables like 'max_allowed_packet'' at line 1
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.05 sec)

mysql> set global max_allowed_packet=1024*1024*16;
Query OK, 0 rows affected (0.03 sec)
永久生效：
/etc/my.cnf
max_allowed_packet = 16

PS：

mysql 导入数据库可以使用 source命令
17:14:49
mysql> source /sumscope/idb_bond.sql
17:15:10
应该不需要调整packet

0 0