首页 / MYSQL / MySQL常用操作(上)
MySQL常用操作(上)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL常用操作(上),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7933字,纯文字阅读大概需要12分钟。
内容图文
当初次安装完mysql后,可以免口令直接登陆mysql。
[root@plinuxos ~]# /usr/local/mysql/bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
更改口令
1、更改PATH,增加MySQL路径
[root@plinuxos ~]# export PATH=$PATH:/usr/local/mysql/bin/ [root@plinuxos ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
这样,可以直接通过mysql命令登陆,而不再需要写绝对路径。如果要想永久生效,必须要添加到profile配置中。
2、设置密码
[root@plinuxos ~]# mysqladmin -uroot password ‘123456‘ Warning: Using a password on the command line interface can be insecure. [root@plinuxos ~]# mysql -uroot ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO) [root@plinuxos ~]# mysql -uroot -p123456 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
3、修改密码
[root@plinuxos ~]# mysqladmin -uroot -p123456 password ‘abcdefg‘ Warning: Using a password on the command line interface can be insecure. [root@plinuxos ~]# mysql -uroot -pabcdefg Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
4、忘记密码时,重置密码
[root@plinuxos ~]# vi /etc/my.cnf [mysqld] skip-grant ##新增该行,跳过授权 ...... [root@plinuxos ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@plinuxos ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> use mysql; 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> update user set password=password(‘88888888‘) where user=‘root‘; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select password from user where user=‘root‘; +-------------------------------------------+ | password | +-------------------------------------------+ | *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B | | *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B | | *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B | | *CB4AD0A70FCCF86E95DB2214F209ACB191DB847B | +-------------------------------------------+ 4 rows in set (0.00 sec) mysql> exit; Bye [root@plinuxos ~]# mysql -uroot -p88888888 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
连接MySQL
1、远程连接MySQL数据库
[root@plinuxos ~]# mysql -uroot -p88888888 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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> exit; Bye [root@plinuxos ~]# mysql -uroot -p88888888 -h127.0.0.1 -P3306 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
2、使用socket连接数据库
[root@plinuxos ~]# mysql -uroot -p88888888 -S/tmp/mysql.sock Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.35 MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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>
3、数据库外快速执行命令
[root@plinuxos ~]# mysql -uroot -p88888888 -e "show databases" Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
常用命令
mysql> show databases; ##显示数据库 mysql> use mysql; ##切换数据库 mysql> show tables; ##显示表 mysql> desc columns_priv; ##查看columns_priv表 mysql> show create table columns_priv\G; ##查看columns_priv的创建语句 mysql> select user(); ##查看当前登陆用户 mysql> select database(); ##查看当前数据库 mysql> create database db1; ##创建数据库db1 mysql> use db1;create table t1(`id` int(4),`name` char(40)); ##创建数据库和表 mysql> drop table t1; ##删除表 mysql> select version(); ##查看mysql版本 mysql> show status; ##查看数据库状态 mysql> show variables; ##查看参数 mysql> show variables like ‘max_connect%‘; ##模糊查询参数 mysql> set global max_connect_errors=1000;##修改参数的值,若要永久生效,在/etc/my.cnf配置 mysql> show processlist; ##查看队列 mysql> show full processlist; ##查看完整的队列信息
本文出自 “Gorilla City” 博客,请务必保留此出处http://juispan.blog.51cto.com/943137/1958191
MySQL常用操作(上)
标签:mysql
本文系统来源:http://juispan.blog.51cto.com/943137/1958191
内容总结
以上是互联网集市为您收集整理的MySQL常用操作(上)全部内容,希望文章能够帮你解决MySQL常用操作(上)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。