Web Tool Bag  
Home · Articles · Downloads · Discussion Forum · Web Links · News Categories · Synonyms DatabaseMay 24 2017 03:30:03
Navigation
Home
Articles
Downloads
Discussion Forum
Web Links
News Categories
Synonyms Database
Search
Users Online
Guests Online: 1
No Members Online

Registered Members: 856
Unactivated Members: 118
Newest Member: lakim
Forum Threads
Newest Threads
Uncaught Error: _reg...
Module build failed:...
Installation
mochi script questions
Redirect with captch...
Hottest Threads
Installation [12]
Any questions and... [5]
Captcha picture d... [4]
Integrate with Vi... [4]
Mods: Sucess/Than... [4]
 
Latest Articles
PHP Fatal error: Ca...
Mouse pointer disapp...
UBUNTU install via U...
Geany tried to acces...
How to create MySql ...
MySQL tips
MySQL tips
Please see the MySQL documentation for more information.
Section and page numbers used below refer to the PDF format of the MySQL manual.
shell> indicates commands to be executed from the directory where MySQL is installed.
mysql> indicates commands to be executed while connected to the server.

Bringing up the server
use the safe_mysqld command to bring up the server
shell> bin/safe_mysqld -u root &

Shutting down the server
use the mysqladmin command to shut down the server
shell> bin/mysqladmin -u root shutdown
Note: there are many command options through mysqladmin including:
shell> bin/mysqladmin ping Display if the server is up or not
shell> bin/mysqladmin -help Display mysqladmin command options

Connecting to the server
shell> bin/mysql -h host -u user_name -p [db_name]
for example:
shell> bin/mysql -h ender.ics.uci.edu -u medinfo -p UMLS2001
then you will be prompted for your password.
Note: The default host is localhost.
Note: you can also specify your password on the command line without a space or equals sign
shell> bin/mysql -u medinfo -ppassword
Note: specifying a db is optional. to change db once you are connected:
mysql> use db_name

Creating a new user (See Ch. 6: Access Privilege System)
Note: you can view all users, their encrypted passwords, and their privileges in the user table of the mysql database.
mysql> USE mysql;
mysql> SELECT * FROM user;

1. connect to the server as the root user
shell> bin/mysql -u root -p mysql

2. to create a global super user who can connect from anywhere:
mysql> GRANT ALL PRIVILEGES ON *.* TO user@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;

notes: *.* indicates all tables of all databases
(for example, a specific table in the Metathesaurus would be UMLS2001.MRCOC)
% is the wildcard within domain names

3. then use revoke to take away privileges that are not needed
mysql> REVOKE priv-type ON *.* FROM user@'%';

4. or, create a user who has no privileges at all:
mysql> GRANT USAGE ON *.* TO user@localhost;

5. then use GRANT to add privileges
mysql> GRANT priv-type ON *.* TO user@localhost;

privilege types:
ALL, ALTER, CREATE, DELETE, DROP, FILE, INDEX, INSERT, PROCESS, RELOAD, SELECT, SHUTDOWN, UPDATE, USAGE

To change your password:
mysql> SET PASSWORD FOR user@localhost IDENTIFIED BY PASSWORD('new_password');
Note: This syntax does not work for me. Instead I have to update the user table directly:
mysql> UPDATE user SET PASSWORD=PASSWORD('new_password') WHERE User='user_name';

To remove a user:
mysql> USE mysql;
mysql> DELETE FROM user WHERE User='user_name';

Using the logs (See Ch. 23: The MySQL log files)
you have to specify when you start the server what logs your want:
shell> bin/safe_mysqld -u root --log-bin=binlog &
this creates a binary log in a file named binlog.

all logs are found in mysql/data/
you can force mysqld to reopen the log files by executing FLUSH LOGS.

There are 6 types of logs:
1. error log - start/run/stop of mysqld. appears by default 'hostname'.err
2. isam log - all changes to ISAM tables.
3. query log - established connections and executed queries. 'hostname'.log
4. update log - manual suggests using the binary log instead.
5. binary log - all statements that change something.
6. slow log - all queries that took more than long_query_time to execute or didn't use indexes.

Compressed tables (See Sections 8.1.2.3 and 15.12)

The default table type in MySQL is MyISAM. MyISAM tables can be compressed using the myisampack tool and uncompressed using myisamchk. After packing, the table is read only. There is "very little access overhead" (p. 303).

"myisampack works by compressing each column in the table separately. The information needed to decompress columns is read into memory when the table is opened. This results in much better performance when accessing individual records because you only have to uncompress exactly one record, not a much larger disk block as when using Stacker on MS-DOS. Usually myisampack packs the data file 40-70%." (p. 457)

shell> myisampack [options] filename
Each filename should be the index (.MYI) file.

So to compress the table MRSO in the UMLS2001 db:
shell> bin/myisampack data/UMLS2001/MRSO.MYI
After you have run myisampack, you must run myisamchk to re-create the index. "At this time you can also sort the index blocks and create statistics needed for the MySQL optimizer to work more efficiently." (p. 464)
shell> myisamchk [options] filename
shell> bin/myisamchk -rq --analyze --sort-index data/UMLS2001/MRSO.MYI
The tables can be uncompressed with myisamchk.
shell> bin/myisamchk -u data/UMLS2001/MRSO.MYI
Note: There is contradictory info on myisampack in the manual:
"myisampack can pack BLOB and VARCHAR columns." (p. 300)
"Can handle fixed or dynamic-length records, but not BLOB or TEXT columns." (p. 304)
"myisampack can also pack BLOB and TEXT columns." (p. 458)

Batch mode (See Section 9.6)
Put the commands you want to run into a file, then tell mysql to read from the file:
Syntax: shell> bin/mysql -h host -u user -p < scriptfile > outputfile
Example to connect as medinfo@localhost and read the commands in loadMETA and put output into loadMETA.out:
shell> bin/mysql -u medinfo -p < /home/medinfo/loadscripts/loadMETA > output/loadMETA.out

Basic commands
to be executed from mysql> or from a file to be used in batch mode.
[square brackets] indicate optional words.

Create table (Section 7.7)
Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(col_name col_type [NOT NULL | NULL] [DEFAULT default_value]
[PRIMARY KEY
or KEY [index_name] (index_col_name)
or INDEX [index_name] (index_col_name)]
col_name col_type ... )];

Example: CREATE TABLE MRATX (
CUI VARCHAR (8) NOT NULL INDEX MRATX_CUI_X (CUI),
SAB VARCHAR (7) NOT NULL,
REL VARCHAR (1) NOT NULL,
ATX VARCHAR (242) NOT NULL);

Note: A column is NULL by default (meaning NULL values are allowed).
Note: Key and index are synonymns.

Load data into a table (Section 7.23)
Syntax: LOAD DATA LOCAL INFILE "filename" INTO TABLE table_name;
Example: LOAD DATA LOCAL INFILE "/var/tmp/UMLS2001/META/MRATX" INTO TABLE MRATX FIELDS TERMI NATED BY "|";

Note: When LOAD DATA LOCAL INFILE reads in a row with an empty field
for example: C0000814|DXP94|||S0352044|||
it puts an empty string '' in the table for that field, not the NULL value.
so all columns for a table whose data is read in from a file should be NOT NULL.
if you want a NULL value in the column, put /N in the text file.
See Section 21.16 for more info.

Note: There is contradictory info about if MySQL can index a column that is specified as NULL.

"Only the MyISAM table type supports inexes on columns that can have NULL values. In other cases you mucst declare such columns NOt NULL or an error results." (p.234)

"In MySQL, as in many other SQL servers, you can't index columns that can have NULL values. You must declare such columns NOT NULL. Conversely, you cannot insert NULL into an indexed column." (p. 524)


Note: The last field in rows from files of the LEX_DB are not terminated by a "|"
for example: absurd|adj|absurdity|noun
so to indicate the last field is closed by a return instead of a pipe:
LOAD DATA LOCAL INFILE "/var/tmp/UMLS2001/LEX/LEX_DB/SM.DB" INTO TABLE SM_DB
FIELDS TERMINATED BY "|" LINES TERMINATED BY "\n";

Create index (Section 7.36)
To create an index after the table has been created use CREATE INDEX.
(Indexes can also be created in the CREATE TABLE command or using ALTER TABLE).
Syntax: CREATE INDEX index_name ON tbl_name (col_name);
Example: CREATE INDEX MRATX_CUI_X ON MRATX (CUI);

Alter table (Section 7.8)
This can be used to add or remove an indexes, primary keys, or columns after the table has been created.
Syntax: ALTER TABLE tbl_name ADD COLUMN
or ADD INDEX
or ADD PRIMARY KEY
or DROP COLUMN
or DROP PRIMARY KEY
or DROP INDEX index_name
Example: ALTER TABLE MRLO DROP INDEX MRLO_CUI_X;

Show (Section 7.28)
provides info about dbs, tables, columns or status of the server.
Syntax: SHOW DATABASES
or SHOW [OPEN] TABLES [FROM db_name]
or SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW TABLE STATUS [FROM db_name] lots of info about each table in the db
or SHOW STATUS provides server status info
or SHOW VARIABLES shows values of some MySQL system vars
or SHOW [FULL] PROCESSLIST shows which threads are running
or SHOW GRANTS FOR user lists grant commands that would duplicate the grants for a user
or SHOW CREATE TABLE tbl_name shows a CREATE TABLE statement that would create the given table

Example: SHOW INDEX FROM MRLO FROM UMLS2001;
Note: You can use db_name.tbl_name as an alternative to the tbl_name FROM db_name syntax.
Example: SHOW INDEX FROM UMLS2001.MRLO;

Describe (Section 7.30)
provides info similar to SHOW COLUMNS
Syntax: DESCRIBE [or DESC] tbl_name col_name;
Example: DESC MRLO CUI;

Drop
Syntax: DROP TABLE [IF EXISTS] tbl_name;
Example: DROP TABLE IF EXISTS MRATX;

Posted by admin on January 22 2007 12:16:25 4305 Reads · Print
Ratings
Rating is available to Members only.

Please login or register to vote.

No Ratings have been Posted.
Login
Username

Password



Not a member yet?
Click here to register.

Forgotten your password?
Request a new one here.
Member Poll
Which PHP framework do you preffer?

Symfony

Zend

CodeIgniter

PHP on TRAX

eZ Components

Fusebox

PhpOpenbiz

Prado

QPHP

Seagull

You must login to vote.
Shoutbox
You must login to post a message.

Vince
03/10/2011 21:17
Hi, How to remove Register from Login screen? I don't want them to register and have full access! if you leave register then they should not have any rights until the admin assigns them

webtoolz
26/09/2011 11:28
Please describe your problem with more details. Thank you.

bimmer98
22/11/2010 20:31
Help. There was a problem with the request; error regarding feedbackzdr form program

Copyright © 2017 - www.webtoolbag.com