mysql数据库安装和基本操作

来源:互联网 时间:2017-07-27


一键安装脚本:

cat mysql_install.sh

#!/bin.bash
groupaddmysql
useradd-s/sbin/nologin-gmysql-Mmysql
cd/usr/local/
wgethttp://mysql.com//Downloads/MySQL-5.6/mysql-5.6.32-linux-glibc2.5-x86_64.tar.gz
tar-zxvfmysql-5.6.32-linux-glibc2.5-x86_64.tar.gz
mvmysql-5.6.32-linux-glibc2.5-x86_64mysql
/bin/cp/usr/local/mysql/support-files/my-default.cnf/etc/my.cnf
mkdir-p/usr/local/mysql/data#datafile
chown-Rmysql.mysql/usr/local/mysql/
/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--user=mysql
/bin/cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
chmod+x/etc/init.d/mysqld
/etc/init.d/mysqldstart
chkconfig--addmysqld
chkconfigmysqldon
echo'exportPATH=/usr/local/mysql/bin/:$PATH'>>/etc/profile
source/etc/profile

更改mysql数据库root的密码

mysqladmin-uroot-p“oldpassword”password“newpassword”

或登录修改

set password for [email protected] = password('123456');

登录mysql修改:

use mysql;

update user set password=password("123456") where user="root";

flush privileges;

mysql加入环境变量PATH中

[[email protected] ~]#

PATH=$PATH:/usr/local/mysql/bin

这样就可以了,但重启Linux后还会失效,所以需要让它开机加载:

[[email protected] ~]#

echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[[email protected] ~]#

source /etc/profile

连接数据库,通过使用 mysql -u root -p 就可以连接数据库了,但这只是连接的本地的数据库 “localhost”, 可是有很多时候都是去连接网络中的某一个主机上的mysql。

[[email protected] ~]#

mysql -uroot -p -h192.168.137.10 -P3306

Enter password:

其中后边的 -P(大写) 用来指定远程主机mysql的绑定端口,默认都是3306, -h 用来指定远程主机的IP.

一些基本的MySQL操作命令

1. 查询当前的库

mysql> show databases;

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

| Database|

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

| information_schema |

| mysql |

| test|

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

3 rows in set (0.06 sec)

mysql的命令,结尾处需要加一个分号。

2. 查询某个库的表

首先需要切换到某个库里去:

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.06 sec)

3. 查看某个表的全部字段

mysql> desc slow_log;

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

| Field | Type | Null | Key | Default| Extra|

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

| start_time | timestamp| NO| | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| user_host | mediumtext| NO| | NULL | |

| query_time | time | NO| | NULL | |

| lock_time | time | NO| | NULL | |

| rows_sent | int(11) | NO| | NULL | |

| rows_examined | int(11) | NO| | NULL | |

| db | varchar(512) | NO| | NULL | |

| last_insert_id | int(11) | NO| | NULL | |

| insert_id | int(11) | NO| | NULL | |

| server_id | int(10) unsigned | NO| | NULL | |

| sql_text| mediumtext| NO| | NULL | |

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

11 rows in set (0.04 sec)

也可以使用两一条命令,显示比这个更详细,而且可以把建表语句全部列出来:

mysql> show create table slow_log/G;

*************************** 1. row ***************************

Table: slow_log

Create Table: CREATE TABLE `slow_log` (

`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE

CURRENT_TIMESTAMP,

`user_host` mediumtext NOT NULL,

`query_time` time NOT NULL,

`lock_time` time NOT NULL,

`rows_sent` int(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id` int(11) NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`sql_text` mediumtext NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

1 row in set (0.01 sec)

4. 查看当前是哪个用户

mysql> select user();

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

| user() |

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

| [email protected] |

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

1 row in set (0.00 sec)

5. 查看当前所使用数据库

mysql> select database();

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

| database() |

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

| mysql |

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

1 row in set (0.01 sec)

6. 创建一个新库

mysql> create database db1;

Query OK, 1 row affected (0.05 sec)

7. 创建一个新表

mysql> use db1;

Database changed

mysql> create table t1 (`id` int(4), `name` char(40));

Query OK, 0 rows affected (0.02 sec)

要注意的是,字段名需要用反引号括起来。

8. 查看当前数据库版本

mysql> select version();

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

| version() |

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

| 5.1.40-log |

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

1 row in set (0.01 sec)

9. 查看当前mysql状态

mysql> show status;

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

| Variable_name | Value|

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

| Aborted_clients| 0|

| Aborted_connects | 5|

| Binlog_cache_disk_use | 0|

| Binlog_cache_use | 0|

| Bytes_received| 303 |

| Bytes_sent| 7001 |

10. 查看mysql的参数

mysql> show variables;

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

| Variable_name| Value|

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

| auto_increment_increment| 1|

| auto_increment_offset| 1|

| autocommit | ON |

| automatic_sp_privileges | ON |

| back_log| 50 |

| basedir | /usr/local/mysql/|

11. 修改mysql的参数

mysql> show variables like 'max_connect%';

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

| Variable_name | Value |

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

| max_connect_errors | 10|

| max_connections| 151|

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

2 rows in set (0.00 sec)

mysql> set global max_connect_errors = 1000;

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'max_connect_errors';

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

| Variable_name | Value |

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

| max_connect_errors | 1000 |

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

1 row in set (0.01 sec)

在mysql命令行, “%” 类似于shell下的 *, 表示万能匹配。使用 “set global” 可以临时修改某些参数,但是重启mysqld服务后还会变为原来的,所以要想恒久生效,需要在配置文件 my.cnf 中定义。

12. 查看当前mysql服务器的队列

这个在日常的管理工作中使用最为频繁,因为使用它可以查看当前mysql在干什么,可以发现是否有锁表:

mysql> show processlist;

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

| Id | User | Host | db| Command | Time | State | Info |

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

| 13 | root | localhost | db1 | Query|0 | NULL | show processlist |

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

1 row in set (0.01 sec)

13. 创建一个普通用户并授权

mysql> grant all on *.* to user1 identified by '123456';

Query OK, 0 rows affected (0.01 sec)

all 表示所有的权限(读、写、查询、删除等等操作), *.* 前面的 * 表示所有的数据库,后面的 * 表示所有的表,identified by 后面跟密码,用单引号括起来。这里的user1指的是localhost上的user1,如果是给网络上的其他机器上的某个用户授权则这样:

mysql> grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';

Query OK, 0 rows affected (0.01 sec)

用户和主机的IP之间有一个@,另外主机IP那里可以用%替代,表示所有主机,例如:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'wenlong'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

一些常用的sql

1. 查询语句

mysql> select count(*) from mysql.user;

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

| count(*) |

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

|8 |

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

1 row in set (0.00 sec)

mysql.user表示mysql库的user表;count(*)表示表中共有多少行。

mysql> select * from mysql.db;

这个用来表示查询mysql库的db表中的所有数据,也可以查询单个字段或者多个字段:

mysql> select db from mysql.db;

mysql> select db,user from mysql.db;

同样,在查询语句中可以使用万能匹配 “%”

mysql> select * from mysql.db where host like '10.0.%';

2. 插入一行

mysql> insert into db1.t1 values (1, 'abc');

Query OK, 1 row affected (0.02 sec)

mysql> select * from db1.t1;

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

| id| name |

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

|1 | abc |

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

1 row in set (0.00 sec)

3. 更改表的某一行

mysql> update db1.t1 set name='aaa' where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from db1.t1;

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

| id| name |

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

|1 | aaa |

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

1 row in set (0.00 sec)

4. 清空表数据

mysql> truncate table db1.t1;

Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from db1.t1;

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

| count(*) |

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

|0 |

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

1 row in set (0.00 sec)

5. 删除表

mysql> drop table db1.t1;

Query OK, 0 rows affected (0.00 sec)

6. 删除数据库

mysql> drop database db1;

Query OK, 0 rows affected (0.02 sec)

mysql数据库的备份与恢复

备份:

[[email protected] ~]#

mysqldump-uroot-p'yourpassword'mysql>/tmp/mysql.sql

使用 mysqldump 命令备份数据库,-u 和 -p 两个选项使用方法和前面说的 mysql 同样,而后面的 “mysql” 指的是库名,然后重定向到一个文本文档里。备份完后,你可以查看 /tmp/mysql.sql 这个文件里的内容。

恢复和备份正好相反:

[[email protected] ~]#

mysql-uroot-p'yourpassword'mysql</tmp/mysql.sql

MySQL官方中文参考手册(5.1) http://dev.mysql.com/doc/refman/5.1/zh/index.html


相关阅读:
Top