Linux下mysql基本操作

首页 > 代码库 > Linux下mysql基本操作

2024-08-02 01:55:14 219人阅读

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

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;

+----------+---------+------+-----+---------+----------------+

+----------+---------+------+-----+---------+----------------+

| 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.07 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)

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

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;

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

Linux下mysql基本操作

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Linux下mysql基本操作

Linux下mysql基本操作

看完仍有疑问？有类似问题直接问程序猿