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;
