更改root密码
首次进入数据库是免密码的，如下：
[root@ahao-01 ~]# /usr/local/mysql/bin/mysql -uroot -p Enter password: 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>
说明：退出时候直接输入quit或者exit即可，但是每次都是使用绝对路径很麻烦的，这个时候，我们要设置下mysql安装目录的系统变量,并且让他开机加载
[root@ahao-01 ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@ahao-01 ~]# source /etc/profile //立即生效
这个时候我们就可以直接使用mysql -uroot -p //-u是指定要登录的用户，后面有无空格均可。
2、设置mysql的root密码
[root@ahao-01 ~]# mysqladmin -uroot password '密码' Warning: Using a password on the command line interface can be insecure. 警告信息：在命令行下面暴露了密码，这样不安全。
3、使用密码登入mysql
[root@ahao-01 ~]# mysql -uroot -p //提示需要密码Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@ahao-01 ~]# mysql -uroot -p //交互的方式输入密码登录Enter password: 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>
4.更改mysql的root密码
方法一，知道root密码
# mysqladmin -uroot -p'旧密码' password '新密码' //更改密码
方法二、不知道root的密码前提
# vim /etc/my.cnf增加如下内容：
skip-grant
如图，添加在【mysqld】这个下面
重启数据库
# /etc/init.d/mysqld
然后运行
[root@ahao-01 ~]# mysql -uroot //这个时候就可以免密码登入了 mysql> use mysql; //用户名密码存在user表里，而user表存在mysql这个库里，进入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> select * from user; //查看user表 mysql> select password from user where user='root' ; //查询语句查询密码表。加密的字符串是password这个函数生成
mysql> update user set password=password('新密码') where user='root'; //更改密码命令
改完密码后把vi /etc/my.cnf增加的skip-grant去掉，否则所有的用户登录都不需要密码，不安全。
连接MySQL
连接本机数据库
mysql -uroot -p123456 //默认他监听my.cnf配置文件里面定义的sock文件
远程连接登录mysql，A机器连接B服务器的mysql，就需要加上IP和端口,如下：
mysql -uroot -p123456 -h127.0.0.1 -P3306 //-h用来指定远程主机的IP -P指定端口
使用sock远程连接
mysql -uroot -p123456 -S/tmp/mysql.sock
说明：mysql我们本机不止监听了3306也监听了sock，所以就可以使用sock登陆，但这个时候不是使用TCP/IP连接，是使用sock,只适合在本机。
连接mysql后，把数据库表列出来，这种情况只适用在shell脚本里
[root@ahao-01 ~]# mysql -uroot -p123456 -e "show databases"
MySQL常用命令
在日常工作中，难免会遇到一些与Mysql相关的操作，比如建库、建表、查询MySQL状态等，掌握最基本的操作。
1、查看创建了什么数据库
mysql> show databases;
2、切换到某个库下
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
3、查看库创建了什么表
mysql> show tables;
4、查看表的字段，其中tb_name为表名
mysql> desc tb_name;
5、查看建表语句
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,`x509_issuer` blob NOT NULL,`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'1 row in set (0.00 sec)ERROR:
No query specified6、查看当前用户
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
7、查看当前所在的库
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
8、创建库
mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> show databases; //查看创建了什么数据库 +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
9、创建表
mysql> use db1; //进入指定数据库 mysql> create table t1(`id` int(4), `name` char(40)); //创建表t1，按照系统默认的字符集来 mysql> create table t112(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 指定表的字符级 mysql> drop table t1; //删除表 Query OK, 0 rows affected (0.01 sec)
10、查看数据库版本
mysql> select version(); //查看数据库版本 +-----------+ | version() | +-----------+ | 5.6.35 | +-----------+ 1 row in set (0.00 sec)
11、查看各参数
mysql> show variables; mysql> show variables like 'max_connect%'; 查看各参数,其中百分号表示*的意思，表示已max_connect开头的 +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)
12、修改参数
mysql> set global max_connect_errors=1000;
13、查看队列
mysql> show processlist; //查看队列 +----+------+-----------+------+---------+------+-------+------------------+ | 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)
