Linux + MySQL的基本配置

发布时间:2014-10-23 23:26:28
来源:分享查询网

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  

返回顶部
查看电脑版