首页 > 代码库 > MySql服务基础
MySql服务基础
[root@localhost ~]# yum remove -y mysql mysql-server
yum -y install ncurses-devel
2.Mysql5.5需要cmake编译安装，所以先安装cmake包
[root@localhost ~]# tar zxf cmake-2.8.6.tar.gz -C /usr/src/ [root@localhost ~]# cd /usr/src/cmake-2.8.6/ [root@localhost cmake-2.8.6]# ./configure [root@localhost cmake-2.8.6]#gmake && gmake install
3源码编译及安装
[root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -M -s /sbin/nologin mysql -g mysql //创建运行用户
4.解包
[root@localhost ~]# tar zxf mysql-5.5.22.tar.gz -C /usr/src/ [root@localhost ~]# cd /usr/src/mysql-5.5.22/
5.配置
[root@localhost mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
6.编译并安装
[root@localhost mysql-5.5.22]# make && make install
- -DCMAKE_INSTALL_PREFIX:指定将mysql数据库程序安装到某目录下，如目录/usr/local/mysql
- -DSYSCONFDIR:指定初始化参数文件目录
- -DDEFAULT_CHARSET:指定默认使用的字符集编码，如utf8。
- -DDEFAULT_COLLATION:指定默认使用的字符集校对规则，utf8_genneral_ci是适用于UTF-8字符集的通用规则
-DWITH_EXTRA_CHARSETS：指定额外支持的其他字符集编码
[root@www ~]# cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf //复制配置文件模板 cp: overwrite `/etc/my.cnf‘? y
[root@www ~]# chown -R mysql.mysql /usr/local/mysql/ //修改属主属组
[root@www ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ //以mysql用户初始化数据库 [root@www ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile //设置环境变量 [root@www ~]# . /etc/profile //立即生效 [root@www ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld //添加启动脚本 [root@www ~]# chmod +x /etc/init.d/mysqld //添加执行权限 [root@www ~]# chkconfig --add mysqld //添加到服务 [root@www ~]# /etc/init.d/mysqld start Starting MySQL.... [ OK ] [root@www ~]# netstat -anpt |grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 64808/mysqld
注：mysql服务器默认通过TCP 3306端口提供服务，通过编辑/etc/my.cnf配置文件中【mysqld】配置段的 "port = 3306"行，可以更改监听端口
访问mysql数据库
mysql数据库系统也是一个经典的C/S（客户端/服务器）架构的应用，要访问mysql数据库使用专门的客户端软件。
1、登录mysql服务器
[root@localhost ~]# mysql -u root //-u 选项用于指定认证用户 [root@localhost ~]# mysqladmin -u root password "123456" //更改mysql密码
mysql> show databases; //查看当前服务器中有哪些库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
mysql> use mysql; //进入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 | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) mysql>
mysql> use mysql; //进入库 Database changed mysql> describe user; //查看表结构 +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Insert_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Update_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Delete_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Drop_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Reload_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Shutdown_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Process_priv | enum(‘N‘,‘Y‘) | NO | | N | | | File_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Grant_priv | enum(‘N‘,‘Y‘) | NO | | N | | | References_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Index_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_db_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Super_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Lock_tables_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Execute_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_slave_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Repl_client_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Show_view_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Alter_routine_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_user_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Event_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Create_tablespace_priv | enum(‘N‘,‘Y‘) | NO | | N | | | ssl_type | enum(‘‘,‘ANY‘,‘X509‘,‘SPECIFIED‘) | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | +------------------------+-----------------------------------+------+-----+---------+-------+ 42 rows in set (0.00 sec)
创建及删除库和表
mysql> create database bai; //创建新的库bai Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bai | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
注：刚创建的数据库是空的，其中不包含任何表，在/usr/local/mysql/data 目录下会自动生成一个与新建的库名相同的空文件夹。
创建新的表
create table语句：用于在当前库中的创建新的表，需指定数据表名称作为参数。并定义该表格所使用的各字段。基本格式如下
create table 表名 （字段1名称 类型，字段2名称 类型，.....，primary key (主键名)）
mysql> create table user (user_name char(16) not null, user_passwd char(48) default ‘‘, primary key (user_name)); Query OK, 0 rows affected (0.06 sec)
删除一个数据表
drop table 语句：用于删除库中的表， 指定 库名，表名 作为参数；若只指定表名参数，则需先通过执行 use 语句切换到目标库。列如，执行以下操作可以删除 bai库中的users表。
mysql> drop table user; //删除user表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql>
删除一个数据库
drop database语句：用于删除指定的库，需要指定库名作为参数。列如，执行以下操作可以删除名为bai的库
mysql> show databases; //查看原有的库 +--------------------+ | Database | +--------------------+ | information_schema | | bai | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database bai; //删除bai库 Query OK, 0 rows affected (0.01 sec) mysql> show databases; //验证已删除 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql>
管理表中的数据记录
1.插入数据记录
insert into 语句：用于向表中插入新的数据记录。语句格式如下所示。
insert into 表名（字段1，字段2，....） values(字段1的值，字段2的值，..........)
执行以下操作将会想bai库中的user表插入一条记录：用户名为"zhangsan",对应的密码为"123456",注意valuse部分的值应与面前指定的各字段逐一对应，
mysql> insert into user (user_name,user_passwd) values (‘zhangsan‘,‘123456‘); Query OK, 1 row affected (0.00 sec)
2.select语句：用于从指定的表中查找符合条件的数据记录。mysql数据库支持标准的sql查询语句，语句格式如下所示。
select 字段名1，字段名2，..... from 表名 where 条件表达式
mysql> select * from user; +-----------+-------------+ | user_name | user_passwd | +-----------+-------------+ | lisi | 123456 | | zhangsan | 123456 | +-----------+-------------+ 2 rows in set (0.00 sec)
注：通配符*：表示所有字段
mysql> use bai;
Database changed
mysql> select user_name,user_passwd from user where user_name= ‘zhangsan‘; //查看zhangsan的用户信息
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| zhangsan | 123456 |
+-----------+-------------+
1 row in set (0.01 sec)
mysql>
3.修改数据记录
update语句：用于修改，更新表中的数据记录。语句格式如下所示。
update 表名 set 字段名 1=字段值1 [,字段值2=字段值2] where 条件表达式
执行以下操作可以修改user表中用户名为 zhangsan 的记录，将密码子串设为空值。验证记录内容可以发现zhangsan用户的密码串值已变为空白。
mysql> update user set user_passwd=PASSWORD(‘‘) where user_name= ‘zhangsan‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +-----------+-------------+ | user_name | user_passwd | +-----------+-------------+ | lisi | 123456 | | zhangsan | | +-----------+-------------+ 2 rows in set (0.00 sec) mysql>
在mysql数据库服务器中，用于访问数据库的各种用户（如root）信息都保存在mysql库的user表中，熟练的管理员可以直接修改其中的数据记录，列如，修改数据库管理员登录密码，如下
mysql> update mysql.user set password=password(‘123456‘) where user= ‘root‘; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush privileges; //刷新用户授权信息 Query OK, 0 rows affected (0.01 sec)
delete 语句：用于删除表中指定的数据记录，语句格式如下所示。
delete from 表名 where 条件表达式
执行以下操作可以删除user表中的用户名为lisi的数据记录，验证记录内容可以发现lisi用户的数据记录已经消失
mysql> delete from user where user_name=‘lisi‘; Query OK, 1 row affected (0.00 sec) mysql> describe user; +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | user_name | char(16) | NO | PRI | NULL | | | user_passwd | char(48) | YES | | | | +-------------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from user; +-----------+-------------+ | user_name | user_passwd | +-----------+-------------+ | zhangsan | | +-----------+-------------+ 1 row in set (0.00 sec)
三、维护mysql数据库
mysql数据库系统的维护工作主要包含用户权限的设置，数据库的备份与恢复。
1.数据库的用户授权
mysql数据库的root用户账号拥有对所有库表的全部权限，频繁使用root账号会为数据库服务器带来一定的安全风险。生产环境中，通常会建立一些低权限的用户，只负责一部分库，表的管理和维护操作，甚至可以对查询，修改，删除记录等各种操作做进一步的细化限制，从而将数据库的风险降至最低。
1.授予权限
grant语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，grant语句将会创建新的用户，否则；grant语句用于修改用户信息。语句格式如下所示：
grant 权限列表 on 库名.表名 to 用户名@来源地址 [ identified by ‘密码‘ ]
执行以下操作可以添加一个名为‘baishuchao‘的数据库用户，并允许其从本机的访问，对bai库中所有表具有查询权限，验证密码为‘123456‘，使用grant语句授权的用户记录，会保存到mysql库的user，db，host，tables_priv等相关表中，无须刷新即可生效。
mysql> grant select on user.* to ‘baishuchao‘@‘localhost‘ identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -u baishuchao -p123456 //验证 baishuchao用户登录 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bai | | test | +--------------------+ 3 rows in set (0.00 sec) mysql>
[root@localhost ~]# mysql -u root -p123456 //验证root用户登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bai |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql>
注：baishuchao用户权限小
列:在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在mysql服务器中。应根据实际情况创建新的用户权限，允许授权用户从网站服务器访问数据库，通常的做法是，创建一个或多个库，并授予所有权限，限制访问的来源ip地址。例如，执行以下操作可以新建web库，并授权ip为192.168.1.1的主机连接，用户名为dbuser，密码为123456，允许在web库中执行所有权限。
mysql> create database web; //创建web库 Query OK, 1 row affected (0.00 sec) mysql> grant all on web.* to ‘dbuser‘@‘192.168.1.1‘ identified by ‘123456‘; //创建用户并授予权限 Query OK, 0 rows affected (0.00 sec) mysql>
2.查看权限
show grants语句：专门用来查看数据库用户的授权信息，通过for子句可指定查看的用户对象（必须与授权时使用的对象名称一致），语句格式如下：
show grants for 用户名@来源地址
执行以下操作可以查看用户dbuser 从主机192.168.1.1 访问数据库时的权限信息。其中usage权限对应的授权记录中包含了用户的连接密码字串
mysql> show grants for ‘dbuser‘@‘192.168.1.1‘; +-----------------------------------------------------------------------------------------------------------------+ | Grants for dbuser@192.168.1.1 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘dbuser‘@‘192.168.1.1‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | | GRANT ALL PRIVILEGES ON `web`.* TO ‘dbuser‘@‘192.168.1.1‘ | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
3.撤销权限
revoke语句：用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到mysql服务器，但将被禁止执行对应的数据库操作。语句格式如下所示。
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址
执行以下操作可以撤销用户baishuchao从本机访问数据库bai的所有权限。
mysql> revoke all on bai.* from ‘baishuchao‘@‘localhost‘; Query OK, 0 rows affected (0.00 sec)
验证一： mysql> show grants for ‘baishuchao‘@‘localhost‘; +-------------------------------------------------------------------------------------------------------------------+ | Grants for baishuchao@localhost | +-------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ‘baishuchao‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | | GRANT SELECT ON `user`.* TO ‘baishuchao‘@‘localhost‘ | +-------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
验证二：
[root@localhost ~]# mysql -u baishuchao -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql>
二、数据库的备份与恢复
及时备份数据库是信息安全管理的重要工作内容之一。mysql数据库的备份可以采用多种方式。列如，直接打包数据库文件夹/usr/local/mysql/data,或者使用专用的导出工具。下面以mysql自带的倾倒工具mysqldump为例，分别介绍数据库的备份与恢复的操作方法。
1.备份数据库
通过mysqldump命令可以将指定的库，表或全部的库导出为sql脚本，便于该命令在不同版本的mysql服务器上使用。列如，当需要升级mysql服务器时，可以先用mysqldump命令将原有库信息导出，然后直接在升级后的mysql服务器中导入即可。
1）执行导出操作
使用mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合shell的> 重定向输出操作，命令格式如下所示：
格式一：导出指定库中的部分表
mysqldump [选项] 库名 [表名1] [表名2] ... > /备份路径/备份文件名
格式二：导出一个或多个完整的库（包括其中所有的表）
mysqldump [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
格式三：备份mysql服务器中所有的库
mysqldump [选项] --all-database > /备份路径/备份文件名
其中，常用的选项包括"-u","-p",分别用于指定数据库用户名，密码。列如，以下操作分别使用格式1，格式2，将mysql库中的user表导出为mysql-user.sql文件，将整个bai库导出为bai.sql文件，所有操作均为root用户的身份进行验证。
[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --databases bai > bai.sql
若需要备份整个mysql服务器中的所有库，应使用格式3.当导出的数据量较大时，可以添加‘--opt’选项优化执行速度。列如，执行以下操作将创建备份文件 all-data.sql，其中包括mysql服务器中的所有库
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:
[root@localhost ~]#
2）查看备份文件内容
通过mysqldump工具导出的sql脚本是文本文件，其中"/*...*/"部分或以"--"开头的行表示注释信息，使用grep，less，cat等文本工具可以查看脚本内容。列如：执行以下操作可以过滤出bai.sql脚本中的数据库操作语句。
[root@localhost ~]# grep -v "^--" bai.sql | grep -v "^/" | grep -v "^$" CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bai` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `bai`; DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `user_name` char(16) NOT NULL, `user_passwd` char(48) DEFAULT ‘‘, PRIMARY KEY (`user_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `user` WRITE; INSERT INTO `user` VALUES (‘zhangsan‘,‘‘); UNLOCK TABLES; [root@localhost ~]#
3）恢复数据库
使用mysqldump命令导出的sql备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作。命令格式如下所示。
mysql [选项] [库名] [表名] < /备份路径/备份文件名
当备份文件中只包含表的备份，而不包括创建库的语句时，则执行导入操作时必须指定库名，且目标库必须存在。例如，执行以下操作可以从备份文件mysql-user.sql中将表导入test库。
[root@localhost ~]# mysql -u root -p test < mysql-user.sql Enter password: [root@localhost ~]# mysql -u root -p123456 //验证导入结果 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ 1 row in set (0.00 sec) mysql>
MySql服务基础