MYSQL 设置更改 root 密码
export PATH=$PATH:/usr/local/mysql/bin/ 把 mysql 加入环境变量可以直接使用 mysql 命令, 永久生效要把这条命令放到 / etc/profile, 并 source /etc/profile
设置密码
[root@aminglinux-02 ~]# mysqladmin -uroot password 's5381561'
Warning: Using a password on the command line interface can be insecure.
- [root@aminglinux-02 ~]# mysql -uroot
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
更改密码
[root@aminglinux-02 ~]# mysqladmin -uroot -p's5381561' password '123456'
Warning: Using a password on the command line interface can be insecure.
- [root@aminglinux-02 ~]# mysql -uroot -p'123456'
- mysql>
忘记密码时, 进行密码更改
改配置文件
- [root@aminglinux-02 ~]# vim /etc/my.cnf
- [mysqld]
- skip-grant #忽略授权
- [root@aminglinux-02 ~]# vim /etc/my.cnf
- [root@aminglinux-02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
- Starting MySQL.. SUCCESS!
- [root@aminglinux-02 ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
更改密码
[root@aminglinux-02 ~]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
- mysql> use mysql;
- # 切换 mysql 库
- Database changed
- mysql> select * from user\G;
- # 查看用户的表信息, 该表中存放的是用户相关信息 (密码, 授权...)
- #G 选项的作用是使输出信息有序显示, 不加该选项, 显示内容会很乱
- mysql> select password from user;
- # 查看用户密码, 显示结果 Wie 加密字符串!
- mysql> update user set password=password('1234567') where user='root';
- Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0
- # 将密码更改为'1234567'
- mysql> quit
- Bye
恢复配置文件并重启
- [root@aminglinux-02 ~]# vim /etc/my.cnf
- [root@aminglinux-02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
- Starting MySQL. SUCCESS!
- [root@aminglinux-02 ~]# mysql -uroot -p1234567
Warning: Using a password on the command line interface can be insecure.
连接 mysql
远程连接
[root@aminglinux-02 ~]# mysql -uroot -p1234567 -h127.0.0.1 -P3306
Warning: Using a password on the command line interface can be insecure.
本地连接
[root@aminglinux-02 ~]# mysql -uroot -p1234567 -S/tmp/mysql.sock
Warning: Using a password on the command line interface can be insecure.
显示所有数据库, 该方法使用于 shell 脚本中
[root@aminglinux-02 ~]# mysql -uroot -p'1234567' -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; | 查询所有数据库
use db_name; | 切换库
show tables; | 查看库中的表
desc tb_name | 查看表里的字段
show create table tb_name\G; | 查看建表语句
select user(); | 查看当前用户
select database(); | 查看当前使用的数据库
select * from user\G; | 查看所有用户
编辑库
create database db_name; | 创建库
use db_name;create table tb_name | 在某库下创建表
select version(); | 查看当前数据库版本
show status; | 查看数据库状态
show variables; | 查看所有参数
show variables like 'max_connet%' | 查看某参数,% 为通配符
set global max_connect_errors=100; | 修改某参数 可以在 my.cnf 里永久修改
show processlist; | 查看 mysql 进程队列
show full processlist | 查看队列详细信息
drop database db_name | 删除库
代码
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
- 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> show tables;
- +---------------------------+
- | Tables_in_mysql |
- +---------------------------+
- | columns_priv |
- | db |
- | event |
- | func |
- | time_zone |
- | time_zone_leap_second |
- +---------------------------+
- 28 rows in set (0.00 sec)
- mysql> desc time_zone;
- +------------------+------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +------------------+------------------+------+-----+---------+----------------+
- | Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- | Use_leap_seconds | enum('Y','N') | NO | | N | |
- +------------------+------------------+------+-----+---------+----------------+
- 2 rows in set (0.11 sec)
- mysql> show create table time_zone\G;
- #G=grep 筛选文字内容, 规律显示出来
- *************************** 1. row ***************************
- Table: time_zone
- Create Table: CREATE TABLE `time_zone` (
- `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
- PRIMARY KEY (`Time_zone_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'
- 1 row in set (0.03 sec)
- ERROR:
- No query specified
- mysql> select user();
- +----------------+
- | user() |
- +----------------+
- | root@localhost |
- +----------------+
- 1 row in set (0.07 sec)
- mysql> select database();
- +------------+
- | database() |
- +------------+
- | mysql |
- +------------+
- 1 row in set (0.00 sec)
- mysql> select * from user\G;
创建库:
- mysql> create database db1;
- Query OK, 1 row affected (0.02 sec)
创建表:
- mysql> use db1;
- # 先切换到指定库下
- Database changed
- mysql> create table t1(`id` int(4),`name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- # 括号中是定义字段及字段格式, 使用反引号引起来
- Query OK, 0 rows affected (1.51 sec)
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 5.6.35 |
- +-----------+
- 1 row in set (0.06 sec)
- mysql> show status;
- +-----------------------------------------------+-------------+
- | Variable_name | Value |
- +-----------------------------------------------+-------------+
- | Aborted_clients | 0 |
- | Aborted_connects | 0 |
- +-----------------------------------------------+-------------+
- mysql> show variables\G;
- mysql> show variables like 'max_connect%'\G;
- #like 表示匹配;% 是通配符
更改参数:
- mysql> set global max_connect_errors=110;
- Query OK, 0 rows affected (0.04 sec)
- # 在此只是临时更改, 如果要永久更改, 需要编辑配置文件
查看队列:
- mysql> show processlist;
- +----+------+-----------+------+---------+------+-------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+------+-----------+------+---------+------+-------+------------------+
- | 5 | root | localhost | db1 | Query | 0 | init | show processlist |
- +----+------+-----------+------+---------+------+-------+------------------+
- 1 row in set (0.01 sec)
- mysql> drop table t1;
- Query OK, 0 rows affected (0.32 sec)
- mysql> drop database db1;
- Query OK, 0 rows affected (0.10 sec)
扩展
mysql5.7 root 密码更改 http://www.apelearn.com/bbs/thread-7289-1-1.html
myisam 和 innodb 引擎对比 http://www.pureweber.com/article/myisam-vs-innodb/
mysql 配置详解: http://blog.linuxeye.com/379.html
mysql 调优: http://www.aminglinux.com/bbs/thread-5758-1-1.html
同学分享的亲身 mysql 调优经历: http://www.apelearn.com/bbs/thread-11281-1-1.html
来源: http://www.bubuko.com/infodetail-2651410.html