MySQL数据库的日常使用及维护

q66262

　　MySQL数据库
　　一：使用MySQL数据库：
　　1、查看数据库结构：
　　查看当前服务器中有哪些库：
　　SHOWDATABASES 语句：用于列出当前MySQL服务器中包含的库；经过初始化后的MySQL服务器，默认建立了三个库：test、mysql和information_schema（其中mysql库中包含了用户认证相关的表）
　　mysql> SHOW DATABASES;

　　查看当前使用的库中有哪些表：
　　SHOWTABLES 语句：用于列出当前所在的库中包含的表。操作之前，需要先使用USE语句切换到所使用的库：
　　mysql> USE mysql;
　　mysql> SHOW TABLES;

　　Mysql数据库的数据文件默认存放在/usr/local/mysql/var/目录下，每个数据库对应一个子目录，用于存储数据表文件；
　　每一个数据表对应为三个文件，后缀名分别为”.frm”” .MYD ”” .MYI ”。

• 　　查看表的结构：
  

• 　　DESCRIBE 语句：用于显示表的结构，即组成表的各字段（列）的信息；
  
• 　　需要指定“库名.表名”作为参数；
  
• 　　若只指定表名参数，则需先通过“USE”语句切换到目标库。
  

　　创建及删除库和表：
　　b、创建新的库：
　　CREATEDATABASE 语句：用于创建一个新的库，需指定数据库名称作为参数。
　　Eg：创建一个名为welcome的库：
　　mysql> CREATE DATABASE welcome;

　　新建的空数据库在/usr/local/mysql/var/目录下会自动生成一个与新建的库名相同的空文件夹。
　　c、创建新的表：
　　CREATETABLE 语句：用于在当前库中创建新的表，需指定数据表名称作为参数，并定义该表格所使用的各字段。
　　格式：CREATETABLE表名（字段1名称类型，字段2名称类型，… ，PRIMARY kEY （主键名））
　　在welcome库中创建users表。其中，字段定义部分的DEFAULT用于设置默认的密码字串，PRIMARY用于设置主键字段名。
　　mysql> USE welcome;
　　mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

　　删除一个数据表：
　　DROPTABLE 语句：用于删除库中的表，需要指定“库名.表名”作为参数；若只指定表名参数，则需先通过“USE”语句切换到目标库。
　　Eg：删除welcome库中的users表：
　　mysql> DROP TABLE welcome.users;

　　删除一个数据库：
　　DROPDATABASE 语句：用于删除指定的库，需要指定库名作为参数。
　　Eg：删除名为welcome的库：
　　mysql> DROP DATABASE welcome;

　　3、管理表中的数据记录：
　　插入数据记录：
　　INSERTINTO 语句：用于向表中插入新的数据记录。
　　格式： INSERTINTO 表名（字段1，字段2，…） VALUES（字段1的值，字段2的值，…）
　　向welcome库中的users表中插入一条记录：用户www，对应的密码为“123456”：
　　mysql> use welcome;
　　mysql> INSERT INTO users(user_name,user_passwd) VALUES('www',PASSWORD('123456'));

　　Eg：向welcome库中的users表中插入一条新的记录，用户qqq，对应密码“654321”。
　　mysql> use welcome;
　　mysql> INSERT INTO users VALUES('qqq',PASSWORD(654321));

　　查询数据记录：
　　SELECT 语句：用于从指定的表中查找符合条件的数据记录。
　　格式： SELECT字段名1，字段名2，… FROM表名WHERE 条件表达式
　　表示所有字段可以使用通配符“*”，若要列出所有的数据记录则可以省略WHERE条件子句。
　　Eg：查看welcome库中的users表内的所有数据记录：
　　mysql> select * from welcome.users;

　　根据特定的条件查找记录，WHERE条件子句是必不可少的。
　　Eg：查找users表中用户名为qqq的记录，输出其用户名、密码字段的信息：
　　mysql> SELECT user_name,user_passwd from welcome.users where user_name='qqq';

　　修改数据记录：
　　UPDATE 语句：用于修改、更新表中的数据记录。
　　UPDATE表名SET字段名1=字段值1[，字段名2=字段值2]WHERE 条件表达式
　　Eg：修改users表中用户名为www的记录，将密码字串设为空值，并验证：
　　mysql> UPDATE welcome.users SET user_passwd=PASSWORD('') WHERE user_name='www';
mysql> select * from welcome.users;

　　mysql数据库服务器中，用于访问数据库的各种用户信息都保存在mysql库的user表中。
　　Eg：将数据库用户root的密码设为123456，当再次使用“mysql–uroot-p”访问mysql数据库服务器时，必须使用此密码进行验证。
　　mysql> UPDATE mysql.user SET password=PASSWORD('123456') WHERE user='root';
　　mysql> FLUSH PRIVILEGES;

　　[root@ns ~]# mysqldump -u root -p password '123456'

　　删除数据记录：
　　DELETE 语句：用于删除表中指定的数据记录。
　　格式： DELETE   FROM   表名   WHERE   条件表达式
　　Eg：删除users表名为www的数据记录，并验证：
　　mysql> DELETE  FROM welcome.users WHERE user_name='www';
　　mysql> SELECT * FROM welcome.users;

　　在mysql数据库服务器中，默认添加了从本机访问数据库的空用户（user、password均为空）。基于数据库安全性考虑，应该删除这些空用户。
　　mysql> SELECT user,host,password  FROM  mysql.user WHERE user='';
　　mysql> DELETE FROM mysql.user WHERE user='';

　　二：维护MySQL数据库：
　　1、数据库的用户授权：
　　Mysql数据库的root用户账号拥有对其所有库、表的全部权限，频繁使用root账号会给数据库服务器带来安全风险。所以实际工作中需要建立一些低权限的用户：
　　授予权限：
　　GRANT 语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户，否则用于修改已有用户信息。
　　GRANT权限列表 ON 库名.表名TO 用户名@来源地址[ IDENTIFIEDBY ‘密码‘]
　　使用GRANT语句的注意事项：

• 　　权限列表：用于列出授权使用的各种数据库操作，以逗号分隔。All表示所有权限；
  
• 　　库名.表名：用于指定授权操作的库和表的名称，其中可以使用“*”。Eg：使用“welcome.*”表示授权操作的对象为welcome库中的所有表。
  
• 　　用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源的地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址。Eg：“%.benet.com”、“192.168.10.%”等
  
• 　　IDENTIFIED BY：设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略则用户的密码将为空。
  
• 　　使用GRANT语句授权的用户记录，会保存到mysql库的user、db、host、tables_priv等。
  

　　Eg：添加一个名为zqq的数据库用户，并允许其从本机访问，对welcome库中的所有表具有查询权限，验证密码为“123456”。
　　mysql> GRANT select ON welcome.* TO 'zqq'@'localhost' IDENTIFIED BY '123456';

　　Eg：以用户zqq的身份连接到数据库，当执行授权的数据库操作时将被允许，而执行非授权的数据库操作时将被拒绝：

• 　　在企业服务器的应用中，数据库与网站服务器有时候是相互独立的。因此在mysql服务器中，应根据实际情况创建新的用户授权，允许授权用户从网站服务器访问数据库；
  
• 　　通常的做法是：创建一个或几个网站专用的库，并授权所有权限，限制访问的来源IP地址。
  

　　Eg：新建bash库，并授权从IP地址为192.168.10.1的主机连接，用户名为zqq，密码123456，允许在bash库中执行所有操作。
　　mysql> CREATE DATABASE bash;
　　mysql> GRANT all ON bash.* TO 'zqq'@'192.168.10.1' IDENTIFIED BY '123456';

　　查看权限：
　　SHOW GRANTS 语句：专门用来查看数据库用户的授权信息，通过FOR语句可指定查看的用户对象（必须与授权时使用的格式： SHOWGRANTSFOR 用户名@来源地址
　　Eg：查看用户zqq从主机192.168.10.1访问数据库时得授权信息。
　　mysql> SHOW GRANTSFOR 'zqq'@'192.168.10.1';

　　撤销权限：
　　REVOKE 语句：用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到mysql服务器，但将禁止执行对应的数据库操作：
　　格式：REVOKE权限列表ON 数据库名.表名FROM用户名@来源地址
　　Eg：撤销用户zqq从本机访问数据库bash的所有权限：
　　mysql> REVOKE all ON welcome.* FROM 'zqq'@'localhost'；
　　mysql> SHOW GRANTS FOR 'zqq'@'localhost';

　　二、数据库的备份与恢复：

• 　　及时备份数据库是信息安全管理的重要内容之一，mysql数据库的备份采用直接打包数据库文件夹/var/local/mysql/var/；或者使用专用的导出工具。
  
• 　　备份数据库：
  　　通过mysqldump命令可以将指定的库、表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。
  

　　A执行导出操作：
　　格式1：导出指定库中的部分表

• 　　Mysqldump[选项]库名 [表名1][表名2] . .>/备份路径/备份文件名
  

　　格式2：导出一个或多个完整的库（包括其中所有的表）

• 　　Mysqldump[选项]--databases库名1[库名2]… >/备份路径/备份文件名
  

　　格式3：备份mysql服务器中的所有的库

• 　　Mysqldump[选项]--all-databases>/备份路径/备份文件名
  

　　其中“ -u ”用来指定数据库的用户名；“ -p ”用来指定数据库的密码
　　Eg：将mysql库中的user表中导出为mysql-user.sql文件，将整个welcome库导出为welcome.sql文件，所有操作都以root用户的身份验证：
　　[root@ns ~]# mysqldump -u root -p mysql > mysql-user.sql
　　[root@ns ~]# mysqldump -u root -p --database welcome > welcome.sql

　　若要备份整个mysql服务器的所有库，应使用格式3，当导出的数据量较大时，可以添加‘--opt‘选项以优化执行速度。
　　Eg：创建备份文件all-database.sql，其中包括mysql服务器中的所有库：
　　[root@ns ~]# mysqldump -u root -p --opt --all-databases > all-data.sql

　　B查看备份文件内容：
　　Eg：过滤出auth.sql脚本中的数据库操作语句：
　　[root@ns ~]# grep -v "^--" welcome.sql | grep -v "^/" | grep -v "^$"

　　恢复数据库：
　　格式： mysql  [选项]  [库名]  [表名]    <     /备份路径/备份文件名
　　当备份文件中只包含表的备份，而不包括创建库的语句时，则执行导入操作时必须指定库名，且目标库必须存在。
　　Eg：从备份文件中mysql-user.sql中将表导入到test库：

　　若备份文件中已经包括完整的库信息，则执行导入操作时无需指定库名。
　　Eg：从备份文件all-data.sql恢复其中的所有库：

　　OK。