Linux + MySQL的基本配置
来源:互联网 发布:淘宝私密优惠券地址 编辑:程序博客网 时间:2024/06/05 11:59
http://blog.csdn.net/shenfuli/article/details/9007815
1. MySQL客户端程序
[root@demoserver bin]# ./mysql 或者mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> quit
2. 更改管理员口令
MySQL安装后,管理员(root)口令默认为空。为了保证系统安全,应该更改root用户口令。命令格式:
[root@demoserver bin]# ./mysqladmin -u root password 123456
3. 使用密码登录两种方式
[root@demoserver bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
4. MySQL服务器管理程序mysqladmin
mysqladmin命令是mysql服务器的管理程序,可用于执行检查配置文件、检查服务状态、关闭服务器、创建数据库、删除数据库等的系统管理操作。
具体格式:
mysqladmin [options] command ....
例如:
刷新缓存中的所有信息
./mysqladmin -u root -p flush-hosts
显示正在运行的服务器线程列表
./mysqladmin -u root -p processlist
数据库管理
1. 查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.06 sec)
说明: information_schema保存系统云信息
mysql 保存系统授权表
tesst 是测试数据库
2. 选择数据库
mysql> use test
Database changed
3. 创建数据库
mysql> create database techdb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| techdb |
| test |
+--------------------+
4 rows in set (0.00 sec)
[root@demoserver bin]# find / -name techdb
/var/lib/mysql/techdb
备注: 数据库创建完成后,会在/var/lib/mysql/下创建一个相同文件夹,用于保存数据库文件,如上述所示。
数据库结构管理
1. 数据表结构
数据库中的数据以二维表形式保存在数据表中,每行代表一个记录,每条记录包含多个列,每列表示一个字段。
--使用information_sechema数据库
mysql> use information_sechema
--显示使用information_sechema数据库下所有表
mysql> show tables
-> ;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE |
| PROFILING |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
17 rows in set (0.00 sec)
--显示CHARACTER_SETS表的结构
mysql> describe CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2. 创建数据库表
--新建表
create table employees(
emp_id varchar(10) primary key not null,
first_name varchar(10) not null,
last_name varchar(10) not null,
hire_date date,
job_id int not null,
salary int default 0,
manager_id int ,
dept_id int not null
)
--linux下执行该表
mysql> create table employees(
->
-> emp_id varchar(10) primary key not null,
-> first_name varchar(10) not null,
-> last_name varchar(10) not null,
-> hire_date date,
-> job_id int not null,
-> salary int default 0,
-> manager_id int ,
-> dept_id int not null
-> )
-> ;
Query OK, 0 rows affected (0.03 sec)
--显示表的结构
mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | | |
| first_name | varchar(10) | NO | | | |
| last_name | varchar(10) | NO | | | |
| hire_date | date | YES | | NULL | |
| job_id | int(11) | NO | | | |
| salary | int(11) | YES | | 0 | |
| manager_id | int(11) | YES | | NULL | |
| dept_id | int(11) | NO | | | |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
3. 更改表结构
--添加字段
mysql> alter table employees add telphone char(20);
mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | | |
| first_name | varchar(10) | NO | | | |
| last_name | varchar(10) | NO | | | |
| hire_date | date | YES | | NULL | |
| job_id | int(11) | NO | | | |
| salary | int(11) | YES | | 0 | |
| manager_id | int(11) | YES | | NULL | |
| dept_id | int(11) | NO | | | |
| telphone | char(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
--更改字段:更改employees表中的telphone为tel,可以使用change命令
mysql> alter table employees change telphone tel char(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | | |
| first_name | varchar(10) | NO | | | |
| last_name | varchar(10) | NO | | | |
| hire_date | date | YES | | NULL | |
| job_id | int(11) | NO | | | |
| salary | int(11) | YES | | 0 | |
| manager_id | int(11) | YES | | NULL | |
| dept_id | int(11) | NO | | | |
| tel | char(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
--更改字段tel类型:使用modify命令即可。
mysql> alter table employees modify tel varchar(20);
mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | | |
| first_name | varchar(10) | NO | | | |
| last_name | varchar(10) | NO | | | |
| hire_date | date | YES | | NULL | |
| job_id | int(11) | NO | | | |
| salary | int(11) | YES | | 0 | |
| manager_id | int(11) | YES | | NULL | |
| dept_id | int(11) | NO | | | |
| tel | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
--删除字段
mysql> alter table employees drop tel;
mysql> describe employees;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| emp_id | varchar(10) | NO | PRI | | |
| first_name | varchar(10) | NO | | | |
| last_name | varchar(10) | NO | | | |
| hire_date | date | YES | | NULL | |
| job_id | int(11) | NO | | | |
| salary | int(11) | YES | | 0 | |
| manager_id | int(11) | YES | | NULL | |
| dept_id | int(11) | NO | | | |
+------------+-------------+------+-----+---------+-------+
4. 插入表数据
mysql> insert into employees(emp_id,first_name,last_name,job_id,dept_id) values(100000,'shen','Fuli',1000,100000);
mysql> insert into employees(emp_id,first_name,last_name,hire_date,job_id,dept_id) values(100001,'zhang','san',now(),1001,100001);
Query OK, 1 row affected (0.00 sec)
5. 查询数据库表
mysql> select * from employees;
+--------+------------+-----------+------------+--------+--------+------------+---------+
| emp_id | first_name | last_name | hire_date | job_id | salary | manager_id | dept_id |
+--------+------------+-----------+------------+--------+--------+------------+---------+
| 100000 | shen | Fuli | NULL | 1000 | 0 | NULL | 100000 |
| 100001 | zhang | san | 2013-06-02 | 1001 | 0 | NULL | 100001 |
+--------+------------+-----------+------------+--------+--------+------------+---------+
6.复制数据表
--仅复制数据结构
mysql> create table employees1 like employees;
Query OK, 0 rows affected (0.01 sec)
mysql> create table employees2 as select * from employees;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
--复制表结构和数据
mysql> create table employees2 as select * from employees;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from employees2;
+--------+------------+-----------+------------+--------+--------+------------+---------+
| emp_id | first_name | last_name | hire_date | job_id | salary | manager_id | dept_id |
+--------+------------+-----------+------------+--------+--------+------------+---------+
| 100000 | shen | Fuli | NULL | 1000 | 0 | NULL | 100000 |
| 100001 | zhang | san | 2013-06-02 | 1001 | 0 | NULL | 100001 |
+--------+------------+-----------+------------+--------+--------+------------+---------+
2 rows in set (0.00 sec)
用户和权限管理
--user: 可以连接本服务器用户,口令以及客户端主机,并指定这些用户拥有哪些权限。
MySQL备份与恢复 mysqldump -u root -p techdb employees >/backup/employees.dmp
2. 备份整个数据库 --database techdb > /backup/techdb.dmp
- Linux + MySQL的基本配置
- 【MySQL】Linux + MySQL的基本配置(二)
- linux + jdk + tomcat + mysql 基本配置
- Django框架下MySQL的【基本操作】以及MySQL在Linux下配置
- Linux Mysql 的基本操作
- linux网络的基本配置
- linux网络的基本配置
- 修改Linux的基本配置
- Linux服务配置:Vsftp的基本配置
- Linux服务配置:Vsftp的基本配置
- 菜鸟的《Linux程序设计》学习——MySQL数据库安装、配置及基本操作
- MySQL在Windows下的基本配置
- c++配置mysql,读取的基本api
- MySql的基本配置和使用
- MySQL的安装配置和基本操作
- MySQL的基本配置与创建数据库
- [Linux & Mysql] Linux下Mysql的基本操作
- Linux下Mysql的配置
- libev and libevent
- 密码算法
- chrome31 shell启动流程---初始流程及启动主进程
- 新员工
- git merage出问题,不能自动Merage
- Linux + MySQL的基本配置
- vim 新建文件后自动插入模板
- python
- 在windows7,windows8 64位操作下5种方法加载未签名的驱动程序
- MSSQL sysobjects 结构说明
- DSPARM资深软件工程
- 魔塔设计第二步
- linux 驱动分类
- Java的HashMap如何合理地初始化