Linux下mysql基本操作
作者：浩浩哥来了
对mysql进行初始密码的添加
方法（一）
mysqladmin -uroot password 123
方法（二）
如果在添加初始密码是报错了可以进行我下面的方法修改密码。
ERROR1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
在/etc/my.cnf文件中添加skip-grant-tables
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
user=mysql
# Disabling symbolic-links is recommendedto prevent assorted security risks
symbolic-links=0
重启mysqld
[root@luowenhao ~]# /etc/init.d/mysqldrestart
停止 mysqld： [确定]
正在启动 mysqld： [确定]
使用/usr/bin/mysql进入数据库
/usr/bin/mysql
进入mysql数据库中修改user表中root的密码
mysql>use mysql;
Readingtable information for completion of table and column names
You canturn off this feature to get a quicker startup with –A
对root用户进行添加新密码为new-password
mysql>update user set password=password('new-password')where user='root';
Query OK,3 rows affected (0.00 sec)
Rowsmatched: 3 Changed: 3 Warnings: 0
在/etc/my.cnf文件中删除skip-grant-tables
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommendedto prevent assorted security risks
symbolic-links=0
重启mysqld
[root@luowenhao ~]# /etc/init.d/mysqldrestart
停止 mysqld： [确定]
正在启动 mysqld： [确定]
使用new-password这个密码进行登录
mysql-uroot -p
Enterpassword:
现在可以使用此命令修改密码为123
mysqladmin-uroot -pnew-password password 123
对mysql进行登录
[root@luowenhao ~]# mysql -uroot –p 登录为root用户
Enter password: 输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or itsaffiliates. All rights reserved.
This software comes with ABSOLUTELY NOWARRANTY. This is free software,
and you are welcome to modify andredistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> 如果出现了这样的标志恭喜你进入了mysql数据库
显示数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lwh |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
创建luowenhao这个数据库
mysql> create database luowenhao;
Query OK, 1 row affected (0.00 sec)
删除数据库
mysql> drop database luowenhao;
Query OK, 0 rows affected (0.01 sec)
进入数据库
mysql> use mysql;
Database changed
显示表信息
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.01 sec)
创建表
mysql> create table name(
-> id int auto_increment not null primary key ,
-> uname char(8),
-> gender char(2),
-> birthday date );
Query OK, 0 rows affected (0.03 sec)
显示表结构
mysql> describe lwh;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| uname | char(8) | YES | | NULL | |
| gender | char(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
在表中插入数据
mysql> insert intolwh(uname,gender,birthday) values('zhangsan','nv','1929-05-21');
Query OK, 1 row affected (0.00 sec)
在表中更新数据
mysql> update lwh setbirthday='1930-06-05' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
列举表中信息
mysql> select * from lwh;
+----+----------+--------+------------+
| id | uname | gender | birthday |
+----+----------+--------+------------+
| 1| zhangsan | nv | 1930-06-05 |
+----+----------+--------+------------+
1 row in set (0.00 sec)
导出数据库之前查看数据库的编码方式
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
character_set_client为客户端编码方式；character_set_connection为建立连接使用的编码；character_set_database数据库的编码；character_set_results结果集的编码；只要保证以上四个采用的编码方式一样，就不会出现乱码问题。character_set_server数据库服务器的编码；
再进行数据库导出
[root@luowenhao ~]# mysqldump -uroot -p lwh> /lwh.sql
Enter password:
数据库导入（一）
首先设置编码
mysql> set name utf8;
ERROR 1193 (HY000): Unknown system variable'name'
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
创建相同名称的数据库
mysql> create database lwh;
Query OK, 1 row affected (0.00 sec)
进入数据库同时确定当前数据库是不存在数据
mysql> use lwh;
Database changed
mysql> show tables;
Empty set (0.00 sec)
导入数据并查看表
mysql> source /lwh.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_lwh |
+---------------+
| lwh |
+---------------+
1 row in set (0.00 sec)
数据库导入（二）
[root@luowenhao ~]# mysql -uroot -p lwh</lwh.sql
Enter password:
进入数据库查看
[root@luowenhao ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or itsaffiliates. All rights reserved.
This software comes with ABSOLUTELY NOWARRANTY. This is free software,
and you are welcome to modify andredistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
进入lwh数据库
mysql> use lwh;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Database changed
查看表
mysql> show tables;
+---------------+
| Tables_in_lwh |
+---------------+
| lwh |
+---------------+
1 row in set (0.00 sec)
创建一个test用户，密码位test，“%“代表联网中的所有用户都能用test用户名访问数据库（所有数据库中的所有表）；
grant all on *.* to 'test'@'%' identifiedby 'test';
并将/etc/mysql/mysql.cnf中的bind-address一行注释
访问a_b中的所有表，用如下语句实现
grant all on a_b.* to 'test'@'%'identified by 'test';
限制权限，限制test用户只能查询a_b中的所有表
grant select on a_db.* to 'test'@'%'identified by 'test';
查看mysql中的所有数据库用户
select distinct concat('User:''',user,'''@''',host,''';') as query from mysql.user;
删除test用户
delete from user where User='test' andHost='%';
flush privileges;
