常⽤MySQL操作设置更改root密码连接mysqlmysql常⽤命令
mysql⽤户管理常。。。
1、设置更改root密码
[root@localhost ~]# ps aux|grep mysql
root 11000.00.11154321716 ? S 20:030:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql 12157.844.8981304452364 ? Sl 20:030:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --us root 13910.00.0112724984 pts/0 S+ 20:040:00 grep --color=auto mysql
[root@localhost ~]# mysql -uroot
-bash: mysql: 鏈 壘鍒板懡浠?
[root@localhost ~]# ls /usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysql
[root@localhost ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost ~]# mysql -uroot
Welcome to the MySQ L 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> quit
Bye
[root@localhost ~]# mysqladmin -uroot password '123456'
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p //登录验证，输⼊密码后进⼊
Enter password:
[root@localhost ~]# mysqladmin -uroot -p'123456' password '654321'
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or\g.
在明⽂指定密码的时候，密码可以加单引号，也可以不加单引号，建议加上单引号，防⽌密码有特殊符号的存在——>（若是不加单引号，⽽密码中⼜有特殊符号，就有可能会不识别）
[root@localhost ~]# vim /etc/myf
[mysqld]
skip-grant //忽略授权
# innodb_buffer_pool_size = 128M
[root@localhost ~]# /etc/init.d/mysqld restart //重启⼀下mysql服务。
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot //然后再登录就不⽤密码了
mysql> use mysql //切换到mysql库，修改mysql库下的user表
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> select password from user where user='root'; //查询root⽤户的密码字段，密码是加密显⽰的，是password函数⽣成的
mysql> update user set password=password('123456') where user='root'; //在user表⾥更新root⽤户的密码
Query OK, 4rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
[root@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! SUCCESS! [root@localhost ~]# mysql -uroot -p Enter password:
2、连接mysql
mysql -uroot -p密码//直接输⼊⽤户名和密码，默认就是连接本机的mysql
连接远程的mysql： tcpip协议通信
mysql -uroot -p密码 -h127.0.0.1 -P3306 //-h指定远程ip，-P指定远程端⼝
mysql -uroot -p密码 -S/tmp/mysql.sock//也是连接本机的mysql
mysql -uroot -p密码 -e "show databases"
[root@localhost ~]# mysql -uroot -p'123456' -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
3、mysql常⽤命令
mysql> create database db1;
root的初始密码Query OK, 1 row affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.04 sec)
mysql> use db1;
Database changed
mysql> create table t1(id int(4), name char(40));
Query OK, 0 rows affected (1.19 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.35 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.35 |
+-----------+
1 row in set (0.04 sec)
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 |
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql> show processlist; //查看进程信息，mysql在做什么操作
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 8 | root | localhost | db1 | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> show full processlist; //完整的进程信息，什么⽤户连接、执⾏什么操作、有⽆锁表等+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 8 | root | localhost | db1 | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
4、mysql⽤户管理
登录到mysql ，创建普通⽤户user1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论