📄 mysql database and linux tutorial.mht
字号:
</LI></UL>
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#c0c0c0>
<TD><PRE>..
...
if test -z "$args"
then
$NOHUP_NICENESS $ledir/$MYSQLD $defaults =
--basedir=3D$MY_BASEDIR_VERSION \
--datadir=3D$DATADIR $USER_OPTION =
--pid-file=3D$pid_file \
<B>--skip-networking</B> --skip-locking >> =
$err_log 2>&1
else
eval "$NOHUP_NICENESS $ledir/$MYSQLD $defaults =
--basedir=3D$MY_BASEDIR_VERSION \
--datadir=3D$DATADIR $USER_OPTION =
--pid-file=3D$pid_file \
<B>--skip-networking</B> --skip-locking $args =
>> $err_log 2>&1"
fi
...
..
</PRE></TD></TR></TBODY></TABLE>Add the flag=20
"<TT><B>--skip-networking</B></TT>" marked in bold. </DD></DL>
<P>
<HR>
<TABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100%" border=3D0>
<TBODY>
<TR bgColor=3D#ffcc33>
<TD><B><BIG>MySQL Admin =
Commands:</BIG></B></TD></TR></TBODY></TABLE>
<P>
<UL>
<LI>Statistics: <TT><B>[prompt]$</B> mysqladmin version</TT>=20
<LI>List database environment: <TT><B>[prompt]$</B> mysqladmin=20
variables</TT>=20
<LI>Show if database is running: <TT><B>[prompt]$</B> mysqladmin =
ping</TT>=20
<LI>Show databases available: <BR>
<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> mysqlshow
+-----------+
| Databases |
+-----------+
| bedrock |
| mysql |
| test |
+-----------+
</PRE></TD></TR></TBODY></TABLE></DD></DL><B>OR</B>=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> SHOW DATABASES;
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<LI>Delete database: <TT><B>mysql></B> drop database =
bedrock;</TT>=20
<LI>Show list of active threads in server: <BR>
<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 -h localhost -u root =
-p processlist
+----+------+-----------+----+---------+------+-------+------------------=
+
| Id | User | Host | db | Command | Time | State | Info =
|
+----+------+-----------+----+---------+------+-------+------------------=
+
| 15 | root | localhost | | Query | 0 | | show processlist =
|
+----+------+-----------+----+---------+------+-------+------------------=
+
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<LI>Delete a database: <TT><B>[prompt]$</B> mysqladmin drop=20
<I>database-name</I></TT>=20
<LI>Execute SQL from Linux command line interface:=20
<BR><TT><B>[prompt]$</B> mysql -h localhost -u root -p -e =
"select=20
host,db,user from db" mysql</TT>=20
<LI>Execute SQL command file from Linux command line interface:=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 =
<I>database-name</I> < text-file-with-sql-statements.sql
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<LI>Loadtest (benchmark) the system:=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> cd sql-bench
<B>[prompt]$</B> run-all-tests
<B>or</B>
<B>[prompt]$</B> mysql -vvf test < ./tests/auto_increment.tst
</PRE></TD></TR></TBODY></TABLE></DD></DL></LI></UL>
<P>
<HR>
<TABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100%" border=3D0>
<TBODY>
<TR bgColor=3D#ffcc33>
<TD><B><BIG>Sample SQL:</BIG></B></TD></TR></TBODY></TABLE>
<P>SQL requests are either administrative or data-related. The =
following=20
are sample SQL segments and are not necessarily pertinent to the =
previous=20
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> CREATE DATABASE bedrock;
<B>mysql></B> USE bedrock;
<B>mysql></B> SHOW tables;
<B>mysql></B> SHOW DATABASES;
<B>mysql></B> LOAD DATA LOCAL INFILE "data.txt" INTO TABLE bedrock;
<B>mysql></B> SELECT DISTINCT dept FROM bedrock;
<B>mysql></B> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), =
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
<B>mysql></B> SELECT * FROM pet WHERE species =3D "snake" OR species =
=3D "bird";
<B>mysql></B> SELECT * FROM pet WHERE species =3D "dog" AND sex =3D =
"f";
<B>mysql></B> SELECT * FROM pet WHERE birth >=3D "1998-1-1";
<B>mysql></B> SELECT name, birth FROM pet ORDER BY birth DESC;
<B>mysql></B> SELECT * FROM pet WHERE name LIKE "b%";
<B>mysql></B> SELECT * FROM pet WHERE name REGEXP "^b";
<B>mysql></B> SELECT species, COUNT(*) FROM pet GROUP BY species;
<B>mysql></B> SELECT MAX(article) AS article FROM shop;
<B>mysql></B> DROP TABLE tmp;
<B>mysql></B> CREATE TABLE retired_employee (
Name char(20) DEFAULT '' NOT NULL,
Dept char(10) DEFAULT '' NOT NULL,
JobTitle char(20),
UNIQUE name_dept (Name,Dept)
);
<B>mysql></B> CREATE UNIQUE index name_dept on employee (name,dept); =
- avoids duplicate keys
<B>mysql></B> INSERT INTO employee VALUES ("Jane =
Smith","Sales","Customer Rep");
<B>mysql></B> INSERT INTO employee VALUES ('Jane =
Smith','Sales','Account Manager');
<B>mysql></B> INSERT INTO employee VALUES ('Jane =
Smith','Engineerin','Manager');
<B>mysql></B> UPDATE employee SET dept=3D'HR' WHERE name=3D'Jane =
Smith';
Use "auto_increment" integer column:
<B>mysql></B> ALTER TABLE employee ADD EmpId INT NOT NULL =
AUTO_INCREMENT PRIMARY KEY;=20
<B>mysql></B> SHOW INDEX FROM employee;
<B>mysql></B> ALTER TABLE employee DROP INDEX name_dept; - get rid =
of=20
<B>mysql></B> SELECT VERSION();
<B>mysql></B> SELECT NOW();
<B>mysql></B> SELECT USER();
<B>mysql></B> SELECT * FROM employee WHERE name LIKE "%Sm%";
<B>mysql></B> SELECT * FROM employee WHERE name REGEXP "^Ja";
<B>mysql></B>=20
</PRE></TD></TR></TBODY></TABLE>See section 3 of MySQL manual for more=20
examples. </DD></DL>
<P>
<HR>
<TABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100%" border=3D0>
<TBODY>
<TR bgColor=3D#ffcc33>
<TD><B><BIG>Loading Data:</BIG></B></TD></TR></TBODY></TABLE>
<P>Command: <TT>LOAD DATA LOCAL INFILE '<I>file.dat</I>' INTO =
TABLE=20
employer;</TT>=20
<P>Input tab delimited file: <TT>file.dat</TT>=20
<DL>
<DD>
<TABLE cellSpacing=3D1 cellPadding=3D4 width=3D"100%" =
bgColor=3D#000000=20
border=3D1>
<TBODY>
<TR bgColor=3D#c0c0c0>
<TD><PRE>Fred Flinstone Quarry Worker Rock Digger
Wilma Flinstone Finance Analyst=20
Barney Rubble Sales Neighbor
Betty Rubble IT Neighbor
</PRE></TD></TR></TBODY></TABLE></DD></DL>
<P>
<HR>
<TABLE cellSpacing=3D0 cellPadding=3D2 width=3D"100%" border=3D0>
<TBODY>
<TR bgColor=3D#ffcc33>
<TD><B><BIG>Dump/Backup/Transfer=20
Database:</BIG></B></TD></TR></TBODY></TABLE>
<P>The <TT>mysqldump</TT> command will read the mySQL database and =
generate a SQL command text file. This allows data to be migrated =
to other=20
versions of mySQL (i.e. upgrade from typical Red Hat (RH7.x to =
FC3) mySQL=20
release 3.23.58 to a more advanced mySQL 4.1 or 5.0) or to other =
SQL=20
databases. SQL command file generated can create tables, insert =
data, ....=20
<P>
<DL>
<DD>
<TABLE border=3D1>
<TBODY>
<TR bgColor=3D#c0c0c0>
<TH>Option</TH>
<TH>Description</TH></TR>
<TR>
<TD>-A<BR>--all-databases</TD>
<TD vAlign=3Dtop>Dump all the databases.</TD></TR>
<TR>
<TD>-B<BR>--databases</TD>
<TD vAlign=3Dtop>Dump the specified databases.</TD></TR>
<TR>
<TD>-h<BR>--host=3D</TD>
<TD vAlign=3Dtop>Specify host to connect to.</TD></TR>
<TR>
<TD>-p<BR>--password=3D</TD>
<TD vAlign=3Dtop>Specify password. If you do not specify a =
password,=20
then you will be queried.</TD></TR>
<TR>
<TD>-u<BR>--user=3D</TD>
<TD vAlign=3Dtop>Specify user. Defaults to current user =
logged=20
in.</TD></TR>
<TR>
<TD>--opt</TD>
<TD vAlign=3Dtop>Same as: --add-drop-table --add-locks --all =
--extended-insert --quick --lock-tables</TD></TR>
<TR>
<TD>--add-drop-table</TD>
<TD vAlign=3Dtop>Add a "drop table" SQL statement before =
each=20
"create" SQL statement.</TD></TR>
<TR>
<TD>--add-locks</TD>
<TD vAlign=3Dtop>Add "lock" SQL statements around "insert" =
SQL=20
statements.</TD></TR>
<TR>
<TD>-a<BR>--all</TD>
<TD vAlign=3Dtop>Include all mySQL specific SQL "create"=20
options.</TD></TR>
<TR>
<TD>-e<BR>--extended-insert</TD>
<TD vAlign=3Dtop>Allows utilization of the new, much faster =
INSERT=20
syntax. Database you are migrating to must support this=20
notation.</TD></TR>
<TR>
<TD>-q<BR>--quick</TD>
<TD vAlign=3Dtop>Don=E2=80=99t buffer query, dump directly =
to stdout.</TD></TR>
<TR>
<TD>-l<BR>--lock-tables</TD>
<TD vAlign=3Dtop>Lock all tables for read.</TD></TR>
<TR>
<TD>-?<BR>--help</TD>
<TD vAlign=3Dtop>Display command line=20
options.</TD></TR></TBODY></TABLE></DD></DL>
<P><B>Examples:</B>=20
<UL>
<LI>Dump database to a file:=20
<UL>
<LI>Dump specified database: <BR><TT>mysqldump --opt =
<I>database</I>=20
> <I>db-dump-file</I>.sql</TT>=20
<LI>Dump specified table in database: <BR><TT>mysqldump --opt=20
<I>database</I> <I>table-name</I> > =
<I>db-dump-file</I>.sql</TT>=20
<LI>Dump multiple databases: <BR><TT>mysqldump --opt =
-databases=20
<I>database1</I> <I>database2</I> <I>database3</I> >=20
<I>db-dump-file</I>.sql</TT>=20
<LI>Dump everything: <BR><TT>mysqldump --opt --all-databases =
>=20
<I>total-db-dump-file</I>.sql</TT> <BR><TT>mysqldump -u =
<I>user-id</I>=20
-h <I>host-name</I> --opt --all-databases >=20
<I>total-db-dump-file</I>.sql</TT> </LI></UL>
<LI>Import dumped file: <BR><TT>mysql <I>database</I> <=20
<I>db-dump-file</I>.sql</TT>=20
<LI>Export from one database and import to another:=20
<UL>
<LI>Transfer specifed database from one database to another:=20
<BR><TT>mysqldump --opt <I>database</I> | mysql=20
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -