• MySQL Hints and Tips
  • Starting
    • Linux
    • Windows 95 or 98
    • Windows NT or 2000
  • Stopping
  • Windows NT or 2000
    • All versions
  • Creating new users
    • Creating user with limited rights
  • Dumping table definitions and data
  • BLOBS and TEXT
  • Creating a table from a select statement
  • MySQL Server Configuration
  • Specify Password in Configuration File
  • Schema
    • Tables
    • Views
    • Columns
    • Constraints

---

MySQL Hints and Tips

These tips relate to using version 3.23.40 of MySQL

Starting

Linux

Most distributions will install mysql as a service that can be started with:

• /etc/init.d/mysql

Red Hat and Red Hat based distributions like Mandrake can be started with:

• service mysql start

To debug, stop the running service and run mysql from the command line. Ideally su to the mysql user first.

• mysqld --log=/tmp/mysql.log

Beware the log is likely to contain sensitive data, so check the permissions and shred it afterwards.

• shred -u /tmp/mysql.log

Windows 95 or 98

As a background task

• c:\mysql\bin\mysqld

or as a foreground task

• c:\mysql\bin\mysqld --standalone

Debug

• c:\mysql\bin\mysqld --standalone --debug

Output is written to c:\mysqld.trace

Windows NT or 2000

First install with

• c:\mysql\bin\mysqld-nt --install then
• net start mysql

Standalone

• c:\mysql\bin\mysqld-nt --standalone

If started standalone, use mysqldadmin to stop (see below)

Debug (Note: don't execute mysqld-nt use mysqld instead)

• c:\mysql\bin\mysqld --standalone --debug

Output is written to c:\mysqld.trace

Logging

• c:\mysql\bin\mysqld-nt --standalone --log

Log files are written to the database directory as hostname.log. This will show queries as they are executed. Usefull for debugging J2EE applications using CMP when you can't otherwise see the queries being executed.

Stopping

Windows NT or 2000

• net stop mysql

All versions

Stopping where no password has been set for root user

• c:\mysql\bin\mysqladmin -u root shutdown

where a password has been set

• c:\mysql\bin\mysqladmin -u root -p shutdown or
• c:\mysql\bin\mysqladmin -u root -pMyPasword shutdown

Creating new users

• GRANT ALL PRIVILEGES ON *.* TO user@localhost IDENTIFIED BY 'password' WITH GRANT OPTION
• GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION
• GRANT ALL PRIVILEGES ON mydb.* TO user@localhost IDENTIFIED BY 'password' WITH GRANT OPTION

To see the results of your handywork so far:

• use mysql
• Select * from db;
• Select * from tables_priv;

Note: the results depend on exactly which options were chosen, and there may not be entries in either of these tables.

Dont' forget to issue the following after changing any privileges:

• flush privileges;

Creating user with limited rights

This will give only select rights to dumbuser.

• grant select on mydb.mytable to dumbuser@localhost identified by 'secret';

---

Dumping table definitions and data

Example of common uses of the mysqldump utility:

• mysqldump --help
• mysqldump mydatabase
• mysqldump mydatabase mytable1 mytable2
• mysqldump --add-drop-table mydatabase
• mysqldump --add-drop-table --complete-insert mydatabase

---

BLOBS and TEXT

Where you are using BLOB or TEXT columns you may need to increase the value of the maxallowedpacket variable on both the server and client programs.

When using mysqldump:

   $ mysqldump --all-databases --max_allowed_packet=128M

• -- Frank Dean - 29 Sep 2009

Creating a table from a select statement

• =CREATE TABLE t1 SELECT * FROM t2 WHERE c1 = 'X'=

---

MySQL Server Configuration

Useful my.cnf entries

[mysqld]
# Specify INNODB as the default table type - a transactional table type but slower than MYISAM
default-table-type=INNODB
# Default character set to use
character-set-server=utf8
collation-server=utf8_general_ci
# For apps that were developed on Windows and won't run on  a case-significant system...
lower_case_table_names=1
# Support clients using the old (less secure) password system
old_passwords=true

[mysql]
default-character-set=utf8

Specify Password in Configuration File

Create .my.cnf in your home directory and include the password property under the 'client' section.

cat >> ~/.my.cnf <<EOF
[client]
password=secret
EOF

Schema

• SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='mydb';

Tables

(MySql 5.0.1+)

• SELECT TABLE_NAME, ENGINE, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='mydb';

Views

(MySql 5.0.1+)

Display view names:

• SELECT TABLENAME FROM INFORMATIONSCHEMA.VIEWS;

Display view definition:

• SELECT VIEWDEFINITION FROM INFORMATIONSCHEMA.VIEWS WHERE TABLE_NAME'myview';=

Columns

• SELECT TABLENAME, COLUMNNAME, CHARACTERSETNAME, COLLATIONNAME FROM INFORMATIONSCHEMA.COLUMNS WHERE TABLE_SCHEMA'mydb';=

Constraints

• SELECT * FROM INFORMATIONSCHEMA.KEYCOLUMNUSAGE WHERE CONSTRAINTSCHEMA'mydb' AND TABLE_NAME='mytable';=
• SELECT CONSTRAINTNAME, CONSTRAINTTYPE FROM INFORMATIONSCHEMA.TABLECONSTRAINTS WHERE CONSTRAINTSCHEMA'mydb' AND TABLENAME='mytable';=

-- Frank Dean - 04 September 2003

• Viki
• Business
• Tech