📄 mysql database and linux tutorial.mht
字号:
characters.=20
<LI><TT>VARCHAR(M)</TT> : Variable length. Stores only the =
string. If=20
M is defined to be 200 but the string is 20 characters long, =
only 20=20
characters are stored. Slower than CHAR.=20
<LI><TT>INT</TT> : Ranging from -2147483648 to 2147483647 or =
unsigned=20
0 to 4294967295=20
<LI><TT>FLOAT(M,N)</TT> : FLOAT(4,2) - Four digits total of =
which 2=20
are after the decimal. i.e. 12.34 Values are rounded to fit =
format if=20
they are too large.=20
<LI><TT>DATE, TEXT, BLOB, SET, ENUM</TT> </LI></UL>
<P></P>
<LI>Add a user. Use the MySQL SQL console to enter SQL commands. =
The=20
command <TT>mysql</TT> with the correct login/password will =
connect you=20
to the database. The admin tables are stored in the database =
"mysql".=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>[prompt]$</B> mysql -h localhost -u root =
-p<B><I>password</I></B>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.41
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
<B>mysql></B> USE mysql;
<B>mysql></B> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-----------------+
<B>mysql></B> INSERT INTO user (Host, User, Password, Select_priv) =
VALUES ('', '<B><I>Dude1</I></B>', =
password('<B><I>supersecret</I></B>'), 'Y');
<B>mysql></B> FLUSH PRIVILEGES; <B><I>- Required each time one =
makes a change to the GRANT table</I></B>
<B>mysql></B> GRANT ALL PRIVILEGES ON bedrock.* TO =
<B><I>Dude1</I></B>;
<B>mysql></B> FLUSH PRIVILEGES; <B><I>- Required each time one =
makes a change to the GRANT table</I></B>
<B>mysql></B> quit
</PRE></TD></TR></TBODY></TABLE></DD></DL>Note:=20
<UL>
<LI>There is NO space between the <TT>-p</TT> and the =
password! You=20
can omit the password and you will be prompted for it.=20
<LI>The SQL flush command is equivalent to issuing the =
command:=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>[prompt]$</B> mysqladmin reload
</PRE></TD></TR></TBODY></TABLE></DD></DL></LI></UL>
<P></P>
<LI>Test the database:=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>mysql></B> SELECT * from employee;
+-----------------+---------------+-------------+
| Name | Dept | jobTitle |
+-----------------+---------------+-------------+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+-----------------+---------------+-------------+
1 row in set (0.00 sec)
<B>mysql></B> SELECT name FROM employee WHERE dept=3D'Sales';
+---------------+
| name |
+---------------+
| Barney Rubble |
+---------------+
1 row in set (0.00 sec)
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<P></P>
<LI>Quit from the SQL shell:=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>[prompt]$</B> quit
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<P></P>
<LI>Shutting down the database:=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>[prompt]$</B> mysqladmin -u root =
-p<I>password</I> shutdown <B><I>- PREFERRED</I></B>
<B>OR</B>
<B>[prompt]$</B> /etc/rc.d/init.d/mysqld stop
<B>OR</B>
<B>[prompt]$</B> service mysqld stop
</PRE></TD></TR></TBODY></TABLE></DD></DL></LI></OL>
<P><A href=3D"file:///usr/share/doc/mysql-3.23.41/">Documentation =
in=20
/usr/share/doc/mysql-3.23.41/</A> (local file)=20
<UL></UL>
<P>
<HR>
<TABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100%" border=3D0>
<TBODY>
<TR bgColor=3D#ffcc33>
<TD><B><BIG>Security:</BIG></B></TD></TR></TBODY></TABLE>
<P>
<DL>
<DD>Security and database access is controlled by the =
<B>GRANT</B>=20
tables. Access to connect to the database and access to process =
the=20
transaction (table and column access, etc.) are both required.=20
Privileges are searched in the following order:=20
<OL>
<LI>user table=20
<LI>db and host table=20
<LI><TT>tables_priv</TT>=20
<LI><TT>columns_priv</TT> </LI></OL>
<P>Use the user table to grant connection privileges to database =
by a=20
user (host, user name and password). Grant database and table =
access for=20
transaction access. i.e. grant "SELECT", "UPDATE", "CREATE", =
"DELETE",=20
"ALTER" etc. permission for database, table, field (columns) or =
database=20
server access.=20
<P>Access can be granted by network permissions: <TT>GRANT ALL=20
PRIVILEGES on bedrock.* to =
david@'192.168.10.0/255.255.255.0';</TT>=20
<BR>This grants access from nodes 192.168.10.0 - 192.168.10.255. =
Or the=20
network definitions can reference resolvable names:=20
'<TT>%.domain.com</TT>'. The host definition of '<TT>%</TT>' or =
''=20
(null) refers to any host. (..according to the documentation. My =
experience is that in the mysql.user table use only '%' for =
"Host" to=20
refer to any host.)=20
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE> <B>mysql></B> GRANT ALL PRIVILEGES on =
bedrock.* to david@'%';
<B>mysql></B> FLUSH PRIVILEGES;
</PRE></TD></TR></TBODY></TABLE><B>or</B> (more promiscuous)=20
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE> <B>mysql></B> GRANT ALL PRIVILEGES on *.* =
to david@'%' identified by 'david';
<B>mysql></B> FLUSH PRIVILEGES;
</PRE></TD></TR></TBODY></TABLE>
<P>Show privileges: <TT>SHOW GRANTS FOR Dude2@'%';</TT>=20
<P>Network security: Use firewall rules (ipchains or iptables) =
to block=20
internet access to port 3306. (default port used by MySQL) =
</P></DD></DL>
<P>
<H4>Passwords and connecting to the databse:</H4>
<UL>
<LI>Connect: <TT><B>[prompt]$</B> mysql -h host_name -u =
<I>user_name</I>=20
-p<I>password</I></TT>=20
<LI>Using default blank password: <TT><B>[prompt]$</B> mysql -h=20
localhost -u root -p</TT> <BR>If a password is required, you =
will be=20
prompted. Note, blank passwords are a security hole which has =
already=20
lead to one mySQL internet worm. Change any default blank =
passwords.=20
<LI>Delete null/blank users: <TT>DELETE FROM user WHERE User =3D =
'';</TT>=20
<LI>Beware of open access permissions from hosts <TT>'%'</TT>:=20
<TT>SELECT * FROM db WHERE Host =3D '%';</TT>=20
<LI>Change a password:=20
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE> <B>[prompt]$</B> mysqladmin -u root -p password =
<I>new-password</I>
</PRE></TD></TR></TBODY></TABLE><BR>You will be prompted to enter=20
the old root password to complete this command. <BR>or:=20
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE> <B>[prompt]$</B> mysqladmin -u root =
-p<I>old-password</I> password <I>new-password</I>
</PRE></TD></TR></TBODY></TABLE><BR>or:=20
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE> <B>mysql></B> SET PASSWORD FOR =
root@'localhost' =3D PASSWORD('<I>supersecret</I>');
<B>mysql></B> FLUSH PRIVILEGES;
</PRE></TD></TR></TBODY></TABLE>
<LI>As an added security precaution it is wise to delete any =
user id not=20
used. i.e. any defaults generated for demonstration purposes.=20
<LI>Note that the default port used by MySQL is 3306. This can =
be=20
protected with firewall rules. See the <A=20
=
href=3D"http://www.yolinux.com/TUTORIALS/LinuxTutorialIptablesNetworkGate=
way.html">YoLinux=20
IpTables tutorial</A>. </LI></UL>
<P><B><FONT color=3D#ff0000>[Potential Pitfall]</FONT></B>: It is =
very easy=20
to make mistakes which get entered into important tables. If you =
enter the=20
command twice you may have one incorrect and one correct entry. =
Look at=20
the table data after a mistake to see what happened in case it =
needs to be=20
fixed. <BR>Example:=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>mysql></B> USE mysql;
<B>mysql></B> SELECT User,Password,Host from user;
+-------+------------------+------------+
| User | Password | Host |
+-------+------------------+------------+
| root | 99a1544eb571ad63 | localhost |
| | | localhost |
| Dude1 | 81a10dba5f6f2144 | |
| Dude1 | | |
| Dude2 | 92b10dba6f7f3155 | % |
+-------+------------------+------------+
5 rows in set (0.00 sec)
<B>mysql></B> DELETE FROM user WHERE User=3D'' AND =
Host=3D'localhost';
<B>mysql></B> DELETE FROM user WHERE User=3D'Dude1' AND =
Password=3D'';
<B>mysql></B> FLUSH PRIVILEGES;
<B>mysql></B> QUIT
</PRE></TD></TR></TBODY></TABLE>User entries may also be found in the=20
table <TT>mysql.db</TT>. <BR>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>mysql></B> DELETE FROM db WHERE =
User=3D'Dude3' AND Host=3D'localhost';
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<P><B><FONT color=3D#ff0000>[Potential Pitfall]</FONT></B>: Any =
changes=20
(<TT>UPDATE</TT>) to the user table will require a "<TT>FLUSH=20
PRIVILEGES</TT>" before the changes will be effective.=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#cccccc>
<TD><PRE><B>mysql></B> UPDATE user SET Host=3D'%' WHERE =
User=3D'Dude2';
<B>mysql></B> FLUSH PRIVILEGES;
</PRE></TD></TR></TBODY></TABLE>This will allow a connection with=20
<TT>mysql</TT> client from any host: <BR><TT><B>[prompt]$</B> =
mysql -u=20
Dude2 -p<I>password</I> -h <I>node.your-domain.com</I></TT> =
</DD></DL>
<P>
<HR>
<H4>Disabling networking:</H4>If your configuration is a web =
server=20
interacting with a mySQL database running on the same "localhost" =
then one=20
may turn off network access to tighten security. Edit shell =
script:=20
<UL>
<LI><TT>/usr/bin/safe_mysqld</TT> (Fedora Core 3)=20
<LI><TT>/usr/bin/mysqld_safe</TT> (Red Hat Enterprise Linux 4) =
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -