MySQL数据库的日常使用及维护
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;
# mysqldump -u root -p password '123456'
删除数据记录：
DELETE 语句：用于删除表中指定的数据记录。
格式： DELETE FROM 表名 WHERE 条件表达式
Eg：删除users表名为www的数据记录，并验证：
mysql> DELETEFROM welcome.users WHERE user_name='www';
mysql> SELECT * FROM welcome.users;
在mysql数据库服务器中，默认添加了从本机访问数据库的空用户（user、password均为空）。基于数据库安全性考虑，应该删除这些空用户。
mysql> SELECT user,host,passwordFROMmysql.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用户的身份验证：
# mysqldump -u root -p mysql > mysql-user.sql
# mysqldump -u root -p --database welcome > welcome.sql
若要备份整个mysql服务器的所有库，应使用格式3，当导出的数据量较大时，可以添加‘--opt‘选项以优化执行速度。
Eg：创建备份文件all-database.sql，其中包括mysql服务器中的所有库：
# mysqldump -u root -p --opt --all-databases > all-data.sql
B查看备份文件内容：
Eg：过滤出auth.sql脚本中的数据库操作语句：
# grep -v "^--" welcome.sql | grep -v "^/" | grep -v "^$"
恢复数据库：
格式： mysql[选项][库名][表名] < /备份路径/备份文件名
当备份文件中只包含表的备份，而不包括创建库的语句时，则执行导入操作时必须指定库名，且目标库必须存在。
Eg：从备份文件中mysql-user.sql中将表导入到test库：
若备份文件中已经包括完整的库信息，则执行导入操作时无需指定库名。
Eg：从备份文件all-data.sql恢复其中的所有库：
OK。
页:
[1]