Linux下mysql基本操作

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;

本文出自 “浩浩哥来了” 博客，谢绝转载！